pt-online-schema-change slightly different nibble queries

Reported by Baron Schwartz on 2012-03-29
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Low
Daniel Nichter

Bug Description

In looking at the query log for the upcoming 2.1 version of pt-online-schema-change, between each nibble I see the following strange pattern of duplicated queries apparently doing the same thing, but not quite (notice the offset/limit):

# Query_time: 0.000415 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 0
SET timestamp=1333052916;
EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `actor_id`, `actor_id`, `film_id` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '1') OR (`actor_id` = '1' AND `film_id` >= '1')) ORDER BY `actor_id`, `film_id` LIMIT 100, 2 /*next chunk boundary*/;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000312 Lock_time: 0.000031 Rows_sent: 2 Rows_examined: 101
SET timestamp=1333052916;
SELECT /*!40001 SQL_NO_CACHE */ `actor_id`, `actor_id`, `film_id` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '1') OR (`actor_id` = '1' AND `film_id` >= '1')) ORDER BY `actor_id`, `film_id` LIMIT 99, 2 /*next chunk boundary*/;

This is with --chunk-size=100 explicitly set:

./pt-online-schema-change --execute --alter 'add baron int' D=sakila,t=film_actor,P=12345 --alter-foreign-keys-method=auto --chunk-size=100

Baron Schwartz (baron-xaprb) wrote :

Here is the exact SQL of each nibble:

# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000233 Lock_time: 0.000033 Rows_sent: 1 Rows_examined: 0
SET timestamp=1333052916;
EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `actor_id`, `actor_id`, `film_id` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '198') OR (`actor_id` = '198' AND `film_id` >= '406')) ORDER BY `actor_id`, `film_id` LIMIT 100, 2 /*next chunk boundary*/;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000260 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 62
SET timestamp=1333052916;
SELECT /*!40001 SQL_NO_CACHE */ `actor_id`, `actor_id`, `film_id` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '198') OR (`actor_id` = '198' AND `film_id` >= '406')) ORDER BY `actor_id`, `film_id` LIMIT 99, 2 /*next chunk boundary*/;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000314 Lock_time: 0.000032 Rows_sent: 1 Rows_examined: 0
SET timestamp=1333052916;
EXPLAIN SELECT `actor_id`, `film_id`, `last_update` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '198') OR (`actor_id` = '198' AND `film_id` >= '406')) AND ((`actor_id` < '200') OR (`actor_id` = '200' AND `film_id` <= '993')) /*explain pt-online-schema-change 6398 copy nibble*/;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.001491 Lock_time: 0.000040 Rows_sent: 0 Rows_examined: 62
SET timestamp=1333052916;
INSERT LOW_PRIORITY IGNORE INTO `sakila`.`_film_actor_new` (`actor_id`, `film_id`, `last_update`) SELECT `actor_id`, `film_id`, `last_update` FROM `sakila`.`film_actor` FORCE INDEX(`PRIMARY`) WHERE ((`actor_id` > '198') OR (`actor_id` = '198' AND `film_id` >= '406')) AND ((`actor_id` < '200') OR (`actor_id` = '200' AND `film_id` <= '993')) /*pt-online-schema-change 6398 copy nibble*/;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000715 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1333052916;
SHOW WARNINGS;
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.000446 Lock_time: 0.000022 Rows_sent: 1 Rows_examined: 1
SET timestamp=1333052916;
SHOW GLOBAL STATUS LIKE 'Threads_running';
# User@Host: msandbox[msandbox] @ localhost [127.0.0.1]
# Query_time: 0.004201 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0

summary: - pt-online-schema-change duplicate nibble queries
+ pt-online-schema-change slightly different nibble queries
Changed in percona-toolkit:
milestone: 2.1.1 → none
assignee: Daniel Nichter (daniel-nichter) → nobody
importance: Medium → Undecided
tags: added: nibbler pt-table-checksum
Daniel Nichter (daniel-nichter) wrote :

Untargeting because I don't think this causes any negative side-effects, and there's no time left to make it into 2.1.1. But it's worth investigating later.

Daniel Nichter (daniel-nichter) wrote :

Fixed in the linked branch. This was just superficial: the EXPLAIN used the chunk size, but the actual query used the LIMIT value, which is chunk size - 1. Now the EXPLAIN uses the LIMIT value too.

Changed in percona-toolkit:
assignee: nobody → Daniel Nichter (daniel-nichter)
importance: Undecided → Low
status: Confirmed → In Progress
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers