Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1757

``pt-table-checksum`` can now handle small tables as a single chunk.

Details

    • Improvement
    • Status: Done
    • Medium
    • Resolution: Done
    • None
    • 3.2.0
    • 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.

      jreal@jreal-dbx:~$ ./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

          Activity

            People

              carlos.salguero Carlos Salguero (Inactive)
              revin Jervin Real (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 week, 6 hours
                  1w 6h

                  Smart Checklist