Not long ago, we found a long-running query in one of our clients on Managed Services. While reviewing this long-running query, we found it was generated by a pt-archiver operation. That doesn’t sound good, right?
I was able to reproduce the issue on my test environment. In this article, I will show you how to use pt-archiver to purge data from a table using a secondary index. First, we need to have the full picture before going any deeper. Here is a representation of the table structure:
1 2 3 4 5 6 7 8 | Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4000001 DEFAULT CHARSET=latin1 |
As you can see, this test table was generated with sysbench. The criteria defined to perform the purge process was to remove all rows where column “k” is lower than 1,500,000. In my test environment, this represents around 40K rows.
1 2 3 4 5 6 | mysql> select count(1) from `dbtest`.`sbtest1` where (k < 1500000); +----------+ | count(1) | +----------+ | 41687 | +----------+ |
Examining the pt-archiver operation used, we found that the options used were logical according to the archiving criteria and the table structure. These are the pt-archiver options used:
1 2 3 4 | pt-archiver --source=h=localhost,P=3306,D=dbtest,t=sbtest1,b=0,i=k_1 --where='k < 1500000' --limit=1000 --primary-key-only --bulk-delete --purge |
As you can see, it is suggesting the usage of index “k_1”, which references the column used on the purging criteria, and it is also using the option “–primary-key-only” which is suggested if you just want to purge data, which is our case. The option “–primary–key-only” makes pt-archiver include only the primary key columns on the SELECT clause to avoid fetching all columns from the table; this simple feature speeds up the purge process, especially on wide tables.
However, reviewing the sentence executed in the database, I found that the query running was not using the suggested index:
1 | SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (k < 1500000) AND (`id` < '4000000') ORDER BY `id` LIMIT 1000 |
Now, if we get the actual explain plan for this query, we will see it is not optimal at all, as it is fetching almost two million rows and forcing the usage of the “PRIMARY” index. No wonder why this was a long-running query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | mysql> explain format=json SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dbtest`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (k < 1500000) AND (`id` < '4000000') ORDER BY `id` LIMIT 1000G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "783367.33" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "sbtest1", "access_type": "range", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "id" ], "key_length": "4", "rows_examined_per_scan": 1928044, "rows_produced_per_join": 642617, "filtered": "33.33", "cost_info": { "read_cost": "654843.92", "eval_cost": "128523.41", "prefix_cost": "783367.33", "data_read_per_join": "117M" }, "used_columns": [ "id", "k" ], "attached_condition": "((`dbtest`.`sbtest1`.`k` < 1500000) and (`dbtest`.`sbtest1`.`id` < '4000000'))" } } } } |
But why is pt-archiver doing this? Why isn’t it using the index suggested? After playing around with pt-archiver options, I realized that the usage of option “–primary-key-only” inhibits the usage of the suggested secondary index. This was reported initially on ticket PT-157, and it was marked as fixed. However, we can confirm that the most recent version of pt-archiver 3.4.0 is behaving as expected. So I opened this ticket PT-2112 to follow up on the fix.
Anyways, we need to fix the issue on our client, and to do so, we just need to remove the “–primary-key-only” option, which will allow us to use the suggested index. The downside of doing this is that it will fetch all columns from the table. We can see the SQL statement pt-archiver will generate by specifying the option “–dry-run”; this is a cool feature to see if the statement generated is optimal.
1 2 3 4 5 6 7 | michael@testbox1:~$ pt-archiver > --source=h=localhost,P=3306,D=dbtest,t=sbtest1,b=0,i=k_1 > --where='k < 1500000' --limit=1000 > --bulk-delete --purge --dry-run SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `dbtest`.`sbtest1` FORCE INDEX(`k_1`) WHERE (k < 1500000) ORDER BY `k` LIMIT 1000 SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `dbtest`.`sbtest1` FORCE INDEX(`k_1`) WHERE (k < 1500000) AND ((`k` >= ?)) ORDER BY `k` LIMIT 1000 DELETE FROM `dbtest`.`sbtest1` WHERE (((`k` >= ?))) AND (((`k` <= ?))) AND (k < 1500000) LIMIT 1000 |
If we examine the explain plan for this generated statement we can see that the query cost for this new sentence is up to 7X times better than the previous one, and we are fetching only 79K rows compared to the 2M from the previous query. So definitely a better query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | mysql> explain format=json SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `dbtest`.`sbtest1` FORCE INDEX(`k_1`) WHERE (k < 1500000) ORDER BY `k` LIMIT 1000G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "111060.21" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "sbtest1", "access_type": "range", "possible_keys": [ "k_1" ], "key": "k_1", "used_key_parts": [ "k" ], "key_length": "4", "rows_examined_per_scan": 79328, "rows_produced_per_join": 79328, "filtered": "100.00", "index_condition": "(`dbtest`.`sbtest1`.`k` < 1500000)", "cost_info": { "read_cost": "95194.61", "eval_cost": "15865.60", "prefix_cost": "111060.21", "data_read_per_join": "14M" }, "used_columns": [ "id", "k", "c", "pad" ] } } } } |
Conclusion
Hopefully, we will see this feature fixed in the future, but for now, we can just use pt-archiver as we did to take advantage of the secondary indexes. Also, as a recommendation, always use the “–dry-run” option to test your pt-archiver command and make sure the query produced is optimal for your database.
Well done, Mike
I actually think doing things in Primary Key order is the right thing to do. But not the way pt-archiver is doing it currently.
By deleting using the index, there are probably a lot of random PK lookups done in the table, and if deleting more than one row per InnoDB page, it might end-up loading this page many times in the buffer pool, which might waste IOs. Obviously, this will not be the case if you only delete a small percentage of the rows, but when deleting by a secondary index, there is a horrible worse case scenario of loading the table many times.
By deleting things in PK order, the behavior is predictable (always reading the full table once), which is a better worse case than reading the table N times (let’s say 16 times if there are 16 rows to delete in each page and this is done in random order with the table not fitting in RAM –> a 1 TB table might end-up being read 16 times !).
But when deleting things in PK order, we need to be sure things are “bounded”. Below query can read up to 4.000.000 rows (4M) if all rows from id 0 to 4M exist and have k > 1.500.000 (1.5M). This is very close to unbounded.
idFROMdbtest.sbtest1FORCE INDEX(PRIMARY) WHERE (k < 1500000) AND (id< ‘4000000’) ORDER BYidLIMIT 1000The right way of doing this is first extracting a bounded range (100K rows in this case, which is 40 times less than above and should be done in constant time, we start with :start_of_range being 0, then the next one will be the id returned by this query , until it is null, in which case we need a last pass on max(id)):
idas end_of_range FROMdbtest.sbtest1FORCE INDEX(PRIMARY) WHERE (id>= :start_of_range) ORDER BYidLIMIT 1 OFFSET 100000Then in that range, delete all rows with k < 1.5M by doing below many times, deleting 1K row max each time:
dbtest.sbtest1WHERE (id>= :start_of_range) AND (id< :end_of_range) AND (k < 1500000) LIMIT 1000And then grabbing the next range. The size of the range should be such as it stays in the buffer pool while the deletes are done.
Note that the described algorithm is very lean in the number of id fetch client-side (only begin and end range), compared to what other queries are doing (fetching large chunks of id).
Also note that the described algorithm can still be improved, because the delete might scan deleted-marked rows many times (in reference to purge being behind). Optimizing this is left as an exercise to the reader (hint: it involves a SELECT before the DELETE and narrowing-down the range as delete progresses).
I have a similar senario, my concern here is that the SELECT pt-archiver generated can not guarantee the uniqueness and correctness.
SELECT /*!40001 SQL_NO_CACHE */
id,k,c,padFROMdbtest.sbtest1FORCE INDEX(k_1) WHERE (k < 1500000) AND ((k>= ?)) ORDER BYkLIMIT 1000Indeed, if you archive with deletion the original rows, it will be fine, but if you give
--no-delete, I suspect the SELECT will archive duplicated rows, e.g. there are 1100 rows with k=100, in the first run, it will pick 1000 rows, with the last k still be 100, in the next run:SELECT /*!40001 SQL_NO_CACHE */
id,k,c,padFROMdbtest.sbtest1FORCE INDEX(k_1) WHERE (k < 1500000) AND ((k>= 100)) ORDER BYkLIMIT 1000There is no guarantee MySQL will not pick the rows already fetched in the first run.
In fact I think the correct SELECT should be like:
SELECT /*!40001 SQL_NO_CACHE */
id,k,c,padFROMdbtest.sbtest1FORCE INDEX(k_1) WHERE (k < 1500000) AND ((k= 100 andid>?) or(k>100) ) ORDER BYk,idLIMIT 1000The above query can both use the index k_1 and guarantee there is no duplicate rows will be picked.