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

pt-table-checksum and pt-osc slow if PK contains ENUM field

    XMLWordPrintable

    Details

      Description

      the query executed by pt-table-checksum using filesort and executed slowly:

      SELECT /*!40001 SQL_NO_CACHE */ `c2`, `c2`, `c1`, `c2`, `c1`, `c3` FROM `test`.`t` FORCE INDEX(`PRIMARY`) WHERE ((`c2` > '1') OR (`c2` = '1' AND `c1` > '2018-06-08') OR (`c2` = '1' AND `c1` = '2018-06-08' AND `c3` >= 'WEB')) ORDER BY `c2`, `c1`, CONCAT(`c3`) LIMIT 999, 2;
      CREATE TABLE `t` (
       `c1` date NOT NULL,
       `c2` int(10) unsigned NOT NULL,
       `c3` enum('WEB','A','B','C','D','E','F','G','H','I','J','K','L','M') NOT NULL DEFAULT 'WEB',
       `c4` int(10) unsigned NOT NULL DEFAULT '0',
       PRIMARY KEY (`c2`,`c1`,`c3`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      how to fill test table:

      #!/usr/bin/python3
      # apt-get update ; apt-get install -y python3-mysql.connector
      import mysql.connector
      import multiprocessing
      #
      def insert_data(idx):
       conn = mysql.connector.connect(user='percona', database='test',
      unix_socket='/var/run/mysqld/mysqld.sock')
       cursor = conn.cursor()
       for day in range(1000):
       cursor.execute("INSERT t (c2, c1, c3) VALUES({0},date_add('2015-01-01', interval {1} day),'WEB')".c3(idx,day))
       conn.commit()
       cursor.close()
       conn.close()
      
      pool = multiprocessing.Pool(multiprocessing.cpu_count())
      pool.map(insert_data, range(50000))
      pool.close()
      pool.join()
      

       

      pt-table-checksum 3.0.6:
      Checksumming test.t: 0% 11+12:51:22 remain

      Version before enum fix:
      wget https://raw.githubusercontent.com/percona/percona-toolkit/21f3e60d3f943e1322170251aabec5886dfcc272/bin/pt-table-checksum
      Checksumming test.t: 66% 00:15 remain

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  carlos.salguero Carlos Salguero
                  Reporter:
                  nickolay.ihalainen Nickolay Ihalainen
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: