![]() I filled it with 400 rows: mysql> select min(pk),max(pk) from t1 ) ENGINE=InnoDB AUTO_INCREMENT=438 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `pk` int unsigned NOT NULL AUTO_INCREMENT, ![]() Here’s a quick example using a table with time fields to use the WHERE clause against. Of course this could be dangerous if your app allowed for updates and then the range wouldn’t be contiguous, so take this plan with a grain of salt - it might help you identify one big section of records to remove, but I wouldn’t use it to identify the 200mil records you want to evict from the table. Then you can use pt-archiver to use the PK. ![]() For example if this table uses PK auto_increment, you may find that records older than your 9 month cutoff are all in the range of 1-1000. This will mean that the DELETE will run for much longer and could potentially lead to locking conditions, but the tradeoff is that your subsequent full table scan will look at many less rows.Īnother way to look at this is to see whether an alternative indexed field (the primary key or secondary index) could be leveraged. One idea could be to use very large -limit / -txn values. As your purging is ongoing you will be removing rows from the Production table, meaning your full table scan is looking at less and less rows each time. ![]() Hi you will still experience significant load conditions even using -limit since a full table scan will be required to examine all rows on the table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |