Merge lp:~percona-toolkit-dev/percona-toolkit/pt-tc-likely-to-skip-table-when-row-count-is-around-chunk-size-times-chunk-size-limit-1389041 into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.14

[Frank Cizmich]

Problem:
pt-table-checksum often skips a table when its size is close to --chunk-size * --chunk-size-limit
This is because it checks if the slave table exceeds that size and decides it's too big to do in one chunk.
Since the size of the slave table is just an estimate of the EXPLAIN query, it is often the case that it exceeds the size of the master table by only a few rows. This leads to it skipping tables that could really be done in one chunk with no problem.

Solution:
Add a 20% tolerance to the size estimate.

Note: includes test

[Daniel Nichter (daniel-nichter)]

Why not just increase --chunk-size-limit? There's always going to be "if only it was just N% bigger!" So this seems to just move the problem because what if the new border is --chunk-size * --chunk-size-limit * 1.2?

review: Needs Information

[Frank Cizmich (frank-cizmich)]

Daniel, at first glance it seems as you say, but look what is happening:

chunk-size-limit * chunk-size (without the extra 20%) has already been used earlier in the code to decide that the table will be done in one chunk ( see line 6469 )

The part where I added the 20% tolerance is when it is checking if the slave table can also be done in 1 chunk. This happens *after* it has been decided that the master table can do it.

( see line 9926 )

"if ( $nibble_iter->one_nibble() ) {" # if we're going to do it in one chunk ..
  ... check and be a bit more tolerant with slave here...

The net effect is that now a table will not be skipped for minor discrepancies.

Unmerged revisions

614. By Frank Cizmich on 2015-02-19

dont skip when slave rowcount barely higher than chunk-size*chunk-size-limit

=== modified file 'bin/pt-table-checksum'
--- bin/pt-table-checksum	2015-01-23 10:19:56 +0000
+++ bin/pt-table-checksum	2015-03-31 19:14:15 +0000
@@ -9939,7 +9939,7 @@
                   PTDEBUG && _d('Table on', $slave->name(),
                      'has', $n_rows, 'rows');
                   if ( $n_rows
-                       && $n_rows > ($tbl->{chunk_size} * $chunk_size_limit) )
+                       && $n_rows > ($tbl->{chunk_size} * $chunk_size_limit) * 1.2 ) # 20% tolerance
                   {
                      PTDEBUG && _d('Table too large on', $slave->name());
                      push @too_large, [$slave->name(), $n_rows || 0];

=== modified file 't/pt-table-checksum/basics.t'
--- t/pt-table-checksum/basics.t	2015-01-05 22:15:27 +0000
+++ t/pt-table-checksum/basics.t	2015-03-31 19:14:15 +0000
@@ -57,6 +57,7 @@
 }
 
 
+
 # ############################################################################
 # Default checksum and results.  The tool does not technically require any
 # options on well-configured systems (which the test env cannot be).  With
@@ -526,6 +527,42 @@
    "sql_mode ONLY_FULL_GROUP_BY is overidden"
 );
 
+# #############################################################################
+# Bug #1389041 : Tool sometimes skips tables when
+# row count ~ chunk-size*chunk-size-limit 
+# https://bugs.launchpad.net/percona-toolkit/+bug/1389041
+# #############################################################################
+$sb->load_file('master', "t/pt-table-checksum/samples/create_skip_table.sql");
+
+# insert 40 rows in master;
+for (1..40) {
+   $master_dbh->do("INSERT INTO test.test VALUES (NULL)");
+}
+$sb->wait_for_slaves();
+# insert 20 more rows in slave1;
+for (1..20) {
+   $slave1_dbh->do("INSERT INTO test.test VALUES (NULL)");
+}
+$sb->wait_for_slaves();
+
+# now master has 40 rows, and slaves have 60
+
+$output = output(
+   sub { $exit_status = pt_table_checksum::main(@args,
+      qw(--databases test --tables test --chunk-size 25 --chunk-size-limit 2)) },
+   stderr => 1,
+);
+
+ok(
+   $output !~ /skipping table test/i,
+   "Doesn't skip table when row count discrepancies are small. (bug 1389041)"
+);
+
+
+# cleanup
+$master_dbh->do("DROP DATABASE test");
+$sb->wait_for_slaves();
+
 
 # #############################################################################
 # Done.

=== added file 't/pt-table-checksum/samples/create_skip_table.sql'
--- t/pt-table-checksum/samples/create_skip_table.sql	1970-01-01 00:00:00 +0000
+++ t/pt-table-checksum/samples/create_skip_table.sql	2015-03-31 19:14:15 +0000
@@ -0,0 +1,9 @@
+CREATE DATABASE IF NOT EXISTS `test`;
+
+USE `test`;
+
+DROP TABLE IF EXISTS `test`;
+CREATE TABLE `test` (
+  `a` int PRIMARY KEY AUTO_INCREMENT
+) ENGINE=InnoDB; 
+