Best practice for pt-table-checksum frequency in Oracle Unified Assurance active-active MySQL setup

MySQL & MariaDB
1

Hi Percona community,

I’m looking for best-practice guidance on how often to run pt-table-checksum in our environment.

We run Oracle Unified Assurance, which is a monitoring/alerting platform with frequent ongoing database activity. The MySQL deployment is a 2-node active-active / source-source style setup, where both nodes participate in replication, and we are trying to use pt-table-checksum for periodic drift detection.

Our goal is to detect replication/data drift safely.

Current questions:

  1. How often would you recommend running it in a busy operational system like this?
  2. Is once per day generally the right starting point, or do people commonly run it more frequently in production where I want to detect the drift asap?
  3. Are there any specific cautions for using pt-table-checksum in an active-active topology beyond the usual replication safety checks?

A few environment notes:

  • This is not a static reporting database; it is an actively used application database.
  • We care more about safe and reliable drift detection than aggressive frequency.

From the official documentation, I understand that pt-table-checksum is generally run on one source and relies on replication to propagate checksum statements, but I have not found clear guidance for how people typically handle this in a true active-active topology.

Any practical guidance from people who have done this in production would be really helpful.

Thanks,
Gokul

2

Hello @degokul

You never said how busy your system is. A 2-node S/S could be doing 100 qps, or 10,000 qps.

Daily is plenty. I’ve never heard of anyone running it more frequently.

Remember that MySQL has it’s own built-in replication safety checks. When replication breaks due to “duplicate PK” or “missing record”, those are replication safety checks. What that means is, once you’ve run pt-table-checksum once, you really shouldn’t need to run it again because MySQL is watching new transactions. pt-table-checksum watches older data for drift.

Not really, other than ensuring which of the two sources is your true “golden source”. Meaning if a random row is different, which one is correct? You will have to choose. Ensure you always run the tool from that MySQL as “golden source.”

There’s no difference with S/S. As above, you pick which is the correct, “golden source” of your data, and run the tool there. It checks the replica. That’s it. There’s no need to re-run the tool with the other server as source because you already checked it.