{"id":5157,"date":"2012-11-14T11:15:35","date_gmt":"2012-11-14T09:15:35","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5157"},"modified":"2012-11-14T11:20:08","modified_gmt":"2012-11-14T09:20:08","slug":"purging-old-rows-with-queryscript-three-use-cases","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/purging-old-rows-with-queryscript-three-use-cases","title":{"rendered":"Purging old rows with QueryScript: three use cases"},"content":{"rendered":"

Problem: you need to purge old rows from a table. This may be your weekly\/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE<\/strong> is too heavy.<\/p>\n

You can use oak-chunk-update<\/a> or pt-archiver<\/a> to accomplish the task. You can also use server side scripting with QueryScript<\/a>, offering a very simple syntax with no external scripting, dependencies and command line options.<\/p>\n

I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP<\/strong> column exists in table, by which we choose to purge the row. In all cases we assume we wish to purge rows older than 1<\/strong> month.<\/p>\n

We assume the naive query is this:<\/p>\n

\n
DELETE FROM my_schema.my_table WHERE row_timestamp < CURDATE() - INTERVAL 1 MONTH<\/pre>\n<\/blockquote>\n
Case 1: TIMESTAMP column is indexed<\/h4>\n
I almost always index a timestamp column, if only for being able to quickly purge data (but usually also to slice data by date). In this case where the column is indexed, it’s very easy to figure out which rows are older than 1<\/strong> month.<\/p>\n
We break the naive query into smaller parts, and execute these in sequence:<\/p>\n
\n
while (DELETE FROM<\/strong> my_schema.my_table WHERE<\/strong> row_timestamp < CURDATE() - INTERVAL 1 MONTH ORDER BY<\/strong> row_timestamp LIMIT<\/strong> 1000<\/span>)\r\n\u00a0 throttle 1;<\/pre>\n<\/blockquote>\n
How does the above work?<\/p>\n
QueryScript accepts a DELETE<\/strong> statement as a conditional expression in a while loop. The expression evaluates to TRUE<\/strong> when the DELETE<\/strong> affects rows. Once the DELETE<\/strong> ceases to affect rows (when no more rows match the WHERE<\/strong> condition), the while<\/strong><\/a> loop terminates.<\/p>\n
The throttle<\/strong><\/a> command allows us to play nice<\/em>: by throttling we increase the total runtime through sleeping in between loop iterations.<\/p>\n
Case 2: TIMESTAMP column is not indexed, and there is no heuristic for matching rows<\/h4>\n
This case is hardest to tackle by means of optimization: there is no index, and we cannot assume or predict anything about the distribution of old rows. We must therefore scan the entire table so as to be able to purge old rows.<\/p>\n
This does not<\/em> mean we have to do one huge full table scan. As long as we have some way to split the table, we are still good. We can utilize the PRIMARY KEY<\/strong> or another UNIQUE KEY<\/strong> so as to break the table into smaller, distinct parts, and work our way on these smaller chunks:<\/p>\n
\n
split<\/strong> (DELETE FROM my_schema.my_table WHERE row_timestamp < CURDATE() - INTERVAL 1 MONTH<\/span>)\r\n\u00a0 throttle 1;<\/pre>\n<\/blockquote>\n
The split<\/strong><\/a> statement will automagically calculate the chunks and inject filtering conditions onto the query, such that each execution of the query relates to a distinct set of rows.<\/p>\n
Case 3: TIMESTAMP column not indexed, but known to be monotonic<\/h4>\n
This is true for many tables. Rows with AUTO_INCREMENT<\/strong> columns and TIMESTAMP<\/strong> columns are created with CURRENT_TIMESTAMP<\/strong> values. This makes for a monotonic function: as the AUTO_INCREMENT<\/strong> grows, so does the TIMESTAMP<\/strong>.<\/p>\n
This makes for the following observation: it we iterate the table row by row, and reach a point where the current row is not old, then we can stop looking. Timestamps will only increase by value, which means further rows only turn to be newer<\/em>.<\/p>\n
With this special case at hand, we can:<\/p>\n
\n
split<\/strong> (<\/strong>DELETE FROM my_schema.my_table WHERE row_timestamp < CURDATE() - INTERVAL 1 MONTH<\/span>) {\r\n\u00a0 if ($split_rowcount<\/strong> = 0)\r\n\u00a0\u00a0\u00a0 break;\r\n\u00a0 throttle 1;\r\n}<\/pre>\n<\/blockquote>\n
split<\/em> is a looping device, and a break<\/strong><\/a> statement works on split<\/em> just as on a while<\/strong> statement.<\/p>\n
split<\/em> provides with magic variables which describe current chunk status. $split_rowcount<\/strong> relates to the number of rows affected by last chunk query. No more rows affected? This means we’ve hit recent rows, and we do not expect to find old rows any further. We can stop looking.<\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: you need to purge old rows from a table. This may be your weekly\/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy. You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[93,67,26,76,50],"class_list":["post-5157","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-bulk-operations","tag-common_schema","tag-indexing","tag-queryscript","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1lb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5157"}],"version-history":[{"count":29,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5157\/revisions"}],"predecessor-version":[{"id":5786,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5157\/revisions\/5786"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}