I have a (InnoDB) table , with around 10 Millions rows, that uses a multicolumn primary key (4 INTs). When adding a column using pt-online-schema-change with no --chunk-index specified, it tries to use the PrimaryKey Constraint.
This fails during the 1st chunk transfer because:
Error copying rows from [table] to [table]_new: Error copying rows at chunk 1 of [table] because MySQL used only 8 bytes of the PRIMARY index instead of 16. See the --[no]check-plan documentation for more information.
The query that is generated for chunking is as such (for the select portion):
SELECT blah, blah, blah FROM `[table]` FORCE INDEX(`PRIMARY`) WHERE ((`pk_1` > ?) OR (`pk_1` = ? AND `pk_2` > ?) OR (`pk_1` = ? AND `pk_2` = ? AND `pk_3` > ?) OR (`pk_1` = ? AND `pk_2` = ? AND `pk_3` = ? AND `pk_4` >= ?)) AND ((`pk_1` < ?) OR (`pk_1` = ? AND `pk_2` < ?) OR (`pk_1` = ? AND `pk_2` = ? AND `pk_3` < ?) OR (`pk_1` = ? AND `pk_2` = ? AND `pk_3` = ? AND `pk_4` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 20683 copy nibble*/
When give the --no-check-plan option is used to continue past this but starts soon receive a message as such:
Rows are copying very slowly. --chunk-size has been automatically reduced to 1. Check that the server is not being overloaded, or increase --chunk-time. The last chunk selected 1 rows and took 33.835 seconds to execute.
When using an index with a single column, the tool operates quickly (30 mins) on the same table.
I would suggest a fix of not trying retrieve the full chunk size every time but reduce WHERE clause to only have equality condition up to the inner most column of the index. (pk_1 = ? AND pk_2 = ? AND pk_3 = ? AND pk_4 >= ?)
Of course this wouldn't guarantee the chunk of each write be the chunk size but it would guarantee fast queries and the index being used to its max length ever time.
This affects pt-osc and ptc currently because these tools use the NibbleIterator module. I discovered this problem too awhile ago, but haven't gotten to fixing it because it's a weird and complex problem, and nibbling/chunking is not easy to change in the tools.
Three things are happening: first, NibbleIterator does make sub-optimal WHERE clauses with multi-column indexes. With 3+ column indexes, the tool makes a WHERE clause with mostly superfluous predicates. Second, the MySQL query planner/optimizer should cancel out these superfluous parts, but instead they confuse MySQL and make it use only part of the index. Using part of the index is where the "MySQL used only 8 bytes of the PRIMARY index instead of 16" comes from. We added --[no]check-plan this to detect when this happen in the wild because--third thing--this doesn't happen all the time. Sometimes MySQL isn't confused, and sometimes it is. When it is and uses part of the index, the result is an index scan. That's not too bad unless the table is large (e.g. millions of rows), and then even an index scan become expensive; and that's why --no-check-plan results in "Rows are copying very slowly".
So yes, we need to fix NibbleIterator, but I don't see this happening for awhile. I would like it to happen for PT 3.0, tentatively schedule for this summer, because we plan to rewrite pt-table-sync to use NibbleIterator like pt-table-checksum.
So the only workaround is currently to use a simpler index, i.e. 1 or 2 columns.