pt-table-checksum help required

Question

I am trying to figure out pt-table-checksum, as I am using it for the first time. It look likes the documentation is complex and not easy to understand stuff.

I have a complex replication topology setup: I have master-master active topology setup geographically separated and both syncing each other. Further a number of slaves under each master. Also, some of the slaves are using filter table replication under both masters.

The database I am using is around 200GB in size, and slaves are balancing reads. I need to make sure pt-table-checksum will not break replication and will not cause any loads on server which hurts app usage.

My requirements are

1. Connect on master1 and check master2 and all slaves under both masters and inform for any deficiences in between master slaves data.

2. Is it possible to check only specific tables' specific slaves ? e.g. check from master1 and all underlying slaves of master1 but do not touch master2 and its underlying slaves?

3. How to take care of replication filters ? e.g. master1 has slaves slave1 slave2 where slave2 replicating only some of the database tables while slave1 replicates the entire database. I need pt-table-checksum to check for differences on slave1 for entire database and check only tables that are replicating on slave2.

I am running pt-table-checksum (2.0.1) on test machines on an employees database. I inserted some records in employees table with sql_log_bin off to prevent them from replicating so I can test pt-table-checksum to report differences and sync on slave.

Created user on master and on slave with SUPER, REPLICATION SLAVE privilege. The user is testuser with testpass being the password

master$ ./pt-table-checksum --replicate=test.checksum --create-replicate-table --nocheck-replication-filters --databases=employees localhost
Cannot connect to h=slave-ip

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
07-09T13:48:38      0      0        9       1       0   0.082 employees.departments
07-09T13:48:39      0      0   331603       7       0   1.001 employees.dept_emp
07-09T13:48:39      0      0       24       1       0   0.040 employees.dept_manager
07-09T13:48:39      0      0   300028       1       0   0.577 employees.employees
07-09T13:48:48      0      0  2844047      13       0   8.006 employees.salaries
07-09T13:48:49      0      0   443308       1       0   0.989 employees.titles

You can see employees table has some extra records. Secondly I executed the following command.

master$ ./pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=employees --host=slave-ip --user=testuser  --password=testpass

Command executed without any slave. Nothing displayed. When I checked on slave employees count is still old and not updated as master.

Answer 1

You say you are using pt-table-checksum 2.0.1. I would recommend updating to 2.1, as there are many improvements in the tool.

Next, let me address your test. You say the slave was not updated after the first or second commands that you ran. The second command looks to be trying to connect directly to the slave. pt-table-checksum won't report any differences unless the server you're connecting to has slaves.

Also, the --replicate-check-only option will not do any checksumming. (from the docs):

If specified, pt-table-checksum doesn’t checksum any tables. It checks replicas for differences found by previous checksumming, and then exits.

Your first command doesn't seem to be able to connect to the slave host, which is why it doesn't report any differences. Make sure the user/pass that is connecting to the master can also connect to the slave.

Now, as for your complex setup, you are right to worry about breaking replication. With some slaves replicating only certain tables, you should heed the warning here:

If the replicas are configured with any filtering options, you should be careful not to checksum any databases or tables that exist on the master and not the replicas.

You can specify which databases you want to checksum with the --databases option, and give a specific list of tables with the --tables option. Alternatively you can use the --ignore-databases and --ignore-tables options to provide a list of databases/tables to not checksum.

This will probably mean you will want separate pt-table-checksum commands based on which slaves you are trying to checksum. You will probably have to use the 'dsn' --recursion-method to accomplish this (I've never done it, personally)

As for load, pt-table-checksum comes with some options to throttle itself. Namely --max-load and --max-lag.

The tool keeps track of how quickly the server is able to execute the queries, and adjusts the chunks as it learns more about the server’s performance. It uses an exponentially decaying weighted average to keep the chunk size stable, yet remain responsive if the server’s performance changes during checksumming for any reason. This means that the tool will quickly throttle itself if your server becomes heavily loaded during a traffic spike or a background task, for example.

Answer 2

In order to do rate limiting the script will need to connect to each of the slaves to look at the slave status.

Make sure you have the same username, password, grants for this user on the master and all slaves. Ensure you can connect to the slaves via mysql client on the master.

If you can but the tool still cannot for some reason first run

export PTDEBUG=1

in your shell. This will have what ever percona toolkit tool you run dump copious amounts of debug data to help you track down where exactly things are going wrong (including the exact connect strings they end up trying to use to connect).

To account for different tables between different slaves I believe you will have to use a dsn for the recursion-method argument. The DSN will point to a simple table that gives a list of slaves to connect to (instead of detecting from the processlist, for example).

Have two different runs pointing to a different recursion-method table and their appropriate --tables arguments.

A few notes about finding differences. Since this is a live process, it is very possible some data will change between runs in a multi master setup for a given run. Even in a simple master slave setup it will only tell you there was some difference in a given chunk; could be 1 row, could be 1,000 or more.

In either case a simple way to find exactly what is different is to use the min/max bounds for the chunk and just select into outfile on both servers then run a simple commandline diff.

pt-table-sync can also be uses to use the table created by pt-table-checksum to find the exact differences and output replace statements to be run on the master to get them in sync. Note this tool does have an --execute option to go ahead and run those replace statements for you, but I've yet to full trust that and will always just do a --print to first manually inspect what it's doing to ensure it's the correct thing.