【TiDB Usage Environment】Production Environment
【TiDB Version】7.5.1
【Reproduction Path】Operations performed that led to the issue
【Encountered Issue: Phenomenon and Impact】Currently, several large tables in the database have severely impacted the core primary database’s business read and write operations. We need to retain only the latest three months of data, and archive historical data elsewhere. This involves the issue of deleting historical data after archiving. Let’s see if anyone has a complete solution for data archiving and mass deletion of historical data from the primary database.
【Resource Configuration】Navigate to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】
For scenarios requiring the cleanup of historical data:
If you can control the table structure design in the early stages, it is best to use partitioned tables. Partition by time, and after archiving, you can quickly clean up historical data.
If you cannot control the table design stage, and you want to quickly delete data, you can consider finding the maximum and minimum values of the data to be deleted, then concatenate the SQL for batch deletion and transfer.
The historical data has not been partitioned, and there are about five to six hundred million records. I want to archive them to Databend. Can I use Dumpling for archiving? What is the appropriate way to delete historical data after archiving?
Since it is a core business database, we are afraid of affecting the business. Do we need to write a script for this? What would be the general logic of the script, or do you have any scripts that we can refer to?
Of course, you can use Dumpling. You can write a script to back up the data from three months ago on a daily or monthly basis, and then the script can delete it in batches afterward, as long as it doesn’t affect the online business.
Delete data in batches, splitting large transactions into smaller ones:
set innodb_lock_wait_timeout=60;
/* define procedure to run loop to delete 10000 rows at a time */
DELIMITER $$
CREATE PROCEDURE DeleteActions()
BEGIN
DECLARE counter INT DEFAULT 1;
REPEAT
delete from actions_action where action NOT IN ('like', 'subscribe') limit 10000; commit;
SET counter = counter + 1;
SELECT SLEEP(2);
UNTIL counter >= 200
END REPEAT;
END$$
DELIMITER ;
/* call the procedure */
CALL DeleteActions();
Equivalent SQL rewriting: create a temporary table, write the data to be retained into the temporary table, then swap the names of the temporary table and the original table, and delete the temporary table.
Check the data volume, how many records per month:
select count(*) from next_eip.t__exec_log where fcreated_time >= DATE_FORMAT(DATE_SUB(now(),interval 3 month),'%Y-%m-%d');
select date_format(fcreated_time,'%Y-%m'),count(*) from next_eip.t__exec_log group by date_format(fcreated_time,'%Y-%m') order by 1 desc;
Create a temporary table:
CREATE TABLE IF NOT EXISTS t__exec_log_tmp like t__exec_log;
Add an exclusive write lock to the table to freeze data changes:
It is not recommended to use partition tables. For deletion, use “delete from XXX where primary key between” to delete. Split it into multiple SQL executions, each controlling below 100,000.
I plan to write a Python script, and I want to include some aspects of system performance monitoring. What metrics need to be monitored, and should I do it when the server is under low stress? Or do you have any scripts in this regard that I can refer to? I need to be cautious in a production environment.
It feels great. Besides being able to clean up data from the past three months, it can also be used for other applications. For example, resetting passwords for accounts that have been inactive for a certain period. Which version introduced this feature?
|
username: 海水孤独
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: