Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Deleting millions of rows in small chunks with common_schema
+6 Vote Up -0 Vote Down

I wrote pt-archiver for jobs like deleting or archiving rows from a big table in small chunks. These days, that’s the kind of task I like doing inside the database, and Shlomi’s magical common_schema feels a lot more suited for this than an external Perl script.

When I say it’s magical, it really does feel magical. It’s amazing how he’s created an entire expressive scripting language that runs in MySQL and feels just right for the job.

Right now I’m watching this kind of stuff scroll by in my terminal:

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2871119 |
+---------------------+
1 row in set (7 min 42.67 sec)

+---------------------+
| rows_deleted_so_far |
+---------------------+
|             2872119 |
+---------------------+
1 row in set (7 min 42.75 sec)

Notice that the execution time is steadily increasing. Each chunk of 50,000 rows takes a small amount of time to delete, but the time shown is since the beginning of the job’s execution.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.