Details
-
Improvement
-
Status: Done
-
Medium
-
Resolution: Done
-
None
-
None
-
None
-
0
Description
We've seen in a number of cases when checksumming tables, albeit, those with only a few rows can be more complex than they need to be. During pt-tc bootstrap, it already knows a few metadata about a table which can short circuit some of the checksum complexities.
For example, a table with only three rows with complex PK and UK combination has to go through nibbling when it can be simply checksummed as a single chunk.
# RowChecksum:5973 18118 Row checksum: CRC32(CONCAT_WS('#', CRC32(`b`), CRC32(`bn`), convert(`c` using utf8mb4), `n`, CONCAT(ISNULL(`c`), ISNULL(`n`)))) # RowChecksum:6012 18118 Chunk checksum: COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', CRC32(`b`), CRC32(`bn`), convert(`c` using utf8mb4), `n`, CONCAT(ISNULL(`c`), ISNULL(`n`)))) AS UNSIGNED)), 10, 16)), 0) AS crc # NibbleIterator:6845 18118 EXPLAIN SELECT * FROM `test`.`vb` WHERE 1=1 # NibbleIterator:6847 18118 $VAR1 = { # extra => undef, # id => '1', # key => undef, # key_len => undef, # possible_keys => undef, # ref => undef, # rows => '3', # select_type => 'SIMPLE', # table => 'vb', # type => 'ALL' # }; # # NibbleIterator:6715 18118 One nibble: yes # NibbleIterator:6777 18118 Auto-selecting best index
In this case of the exploratory explain, we have a good idea that that table only has three rows and can be checksummed as a single chunk.
This can reduce retries and/or false positives on tables that are small and changes frequently between nibbles.
Simple test case below.
[email protected]:~$ ./sandboxes/rsandbox_5_6_44/m test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.6.44-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. master [localhost:21045] {msandbox} (test) > drop table if exists vb; Query OK, 0 rows affected (0.00 sec) master [localhost:21045] {msandbox} (test) > create table vb (b binary, bn binary, c char(1), n int unsigned, primary key (b, bn), unique key (b, c)) engine=innodb; Query OK, 0 rows affected (0.02 sec) master [localhost:21045] {msandbox} (test) > insert into vb (b, bn, c, n) values (binary 'a', binary 'a', 'a', 1); Query OK, 1 row affected (0.00 sec) master [localhost:21045] {msandbox} (test) > insert into vb (b, bn, c, n) values (binary 'a', binary 'b', 'b', 1); Query OK, 1 row affected (0.01 sec) master [localhost:21045] {msandbox} (test) > insert into vb (b, bn, c, n) values (binary 'b', binary 'a', 'a', 2); Query OK, 1 row affected (0.00 sec)
Attachments
Issue Links
- blocks
-
SFR-226 Loading...
