Trust but Verify: A Playbook for Database Consistency Checks

Introduction

During a recent engagement with a customer, we discussed methods for checking the integrity of the data between a primary node and replica nodes within a cluster.

In this blog post, we take a look at a relatively unknown command-line option that is part of the trepctl command, namely trepctl check.

The Challenge

At its core, Tungsten Replicator — whether used as a standalone product or as part of a Tungsten Clustering installation — focuses on the movement of data by extracting the transactions from the binary log and applying them to the targets.

The replicator does not know if the content of the target table matches the source 100%, nor do we check this as part of the replication process — it would simply take far too much time and resources, and in busy systems when replication is critical, introducing such intensive scans would cause havoc.

As part of the initial deployment, you would be responsible for the initial provisioning of the replica nodes, either by using manual processes or utilizing the tprovision tool — thus ensuring your replica databases are in sync and match.

We then encourage best practices to ensure the databases do not get out of sync, such as ensuring applications do not make writes to replica nodes! If you are running active/active clusters then the risk of data drift, although small if best practices are followed, is still a risk all the same.

So how do you keep a check on this?

The Solution

Well, firstly, there is a handy tool set from the team at Percona called pt-table-checksum. This is a great tool for doing such checks, but it isn’t completely tungsten-aware and as a result the output may not be 100% accurate. I don’t discuss the use of that tool in this blog post, however if you are interested in learning how it works then I recommend consulting the Percona documentation directly.

As part of Tungsten Replicator, we do have the ability for carrying out checks on entire schemas or specific tables and reporting if they are out of sync. Whilst this check does not tell you what the differences are, the results will help you to narrow down your investigations. So how does this work?

The `trepctl check` Command

In this example I am running a 3 node cluster with nodes db1 (the primary) and db2/db3 (the replicas) I have a small schema with a few tables that are populated, but I have suspicion that one of the tables has a data issue, namely the “employees” table within the “hr” schema, so I am going to use the check command to investigate this further.

On the primary node, I will simply execute trepctl check — this will scan the table and create an entry into the `consistency` table within the tungsten tracking schema. This table will contain the table name, a row count and a CRC checksum. This will then replicate throughout the cluster, once this reaches the replicas, each replica will also initiate the same check automatically, and they will then update their own local `consistency` table with a row count and a CRC checksum of the same local table. Querying this data, we can then see if there are differences with the checksums.

On a small table, this would be sufficient, but what if you have a table with many many rows? Well, in this case you can use the limit option, which will only scan a subset of the data. I will do this in my example below to show how this output can be used to help narrow down the investigations. My table only has 107 rows, so I can use subsets of 20 rows at a time.

On my primary node, let's call the trepctl check command as follows:

shell> trepctl check hr.employees -limit 0,20
shell> trepctl check hr.employees -limit 21,40
shell> trepctl check hr.employees -limit 41,60
shell> trepctl check hr.employees -limit 61,80
shell> trepctl check hr.employees -limit 81,100
shell> trepctl check hr.employees -limit 101,120

If I then look at the consistency table on the primary node, this is what I see:

mysql> select * from consistency;
+----+-----------+----+------------+-----------+----------+----------+----------------------------------+------------+------+--------+
| db | tbl       | id | row_offset | row_limit | this_crc | this_cnt | master_crc                       | master_cnt | ts   | method |
+----+-----------+----+------------+-----------+----------+----------+----------------------------------+------------+------+--------+
| hr | employees |  1 |          0 |        20 | NULL     |     NULL | 3ab6d7a40eec92ad228dcbe849984451 |         20 | NULL | md5    |
| hr | employees |  2 |         21 |        40 | NULL     |     NULL | 02a4f427c283b7ebd84e48ce7479ba10 |         40 | NULL | md5    |
| hr | employees |  3 |         41 |        60 | NULL     |     NULL | 316e8e861e47a1c051d98c609a7fbb84 |         60 | NULL | md5    |
| hr | employees |  4 |         61 |        80 | NULL     |     NULL | 7ac54c5ff032c2bad673c4c70f6ee16d |         46 | NULL | md5    |
| hr | employees |  5 |         81 |       100 | NULL     |     NULL | 92aecfacdc66c8e5e6d71ce838459831 |         26 | NULL | md5    |
| hr | employees |  6 |        101 |       120 | NULL     |     NULL | f73b860c89a180482acf94873fda9161 |          6 | NULL | md5    |
+----+-----------+----+------------+-----------+----------+----------+----------------------------------+------------+------+--------+
6 rows in set (0.00 sec)

Now let's take a look at the consistency table on the replica that I am suspicious about:

mysql> select * from consistency;
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
| db | tbl       | id | row_offset | row_limit | this_crc                         | this_cnt | master_crc                       | master_cnt | ts   | method |
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
| hr | employees |  1 |          0 |        20 | 55a6b2d3ab7f5e6f2f414ac31d8512c6 |       20 | 3ab6d7a40eec92ad228dcbe849984451 |         20 | NULL | md5    |
| hr | employees |  2 |         21 |        40 | 02a4f427c283b7ebd84e48ce7479ba10 |       40 | 02a4f427c283b7ebd84e48ce7479ba10 |         40 | NULL | md5    |
| hr | employees |  3 |         41 |        60 | 316e8e861e47a1c051d98c609a7fbb84 |       60 | 316e8e861e47a1c051d98c609a7fbb84 |         60 | NULL | md5    |
| hr | employees |  4 |         61 |        80 | 7ac54c5ff032c2bad673c4c70f6ee16d |       46 | 7ac54c5ff032c2bad673c4c70f6ee16d |         46 | NULL | md5    |
| hr | employees |  5 |         81 |       100 | 92aecfacdc66c8e5e6d71ce838459831 |       26 | 92aecfacdc66c8e5e6d71ce838459831 |         26 | NULL | md5    |
| hr | employees |  6 |        101 |       120 | f73b860c89a180482acf94873fda9161 |        6 | f73b860c89a180482acf94873fda9161 |          6 | NULL | md5    |
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
6 rows in set (0.00 sec)

To narrow this down, let's only look at rows that differ:

mysql> select * from consistency where this_crc <> master_crc;
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
| db | tbl       | id | row_offset | row_limit | this_crc                         | this_cnt | master_crc                       | master_cnt | ts   | method |
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
| hr | employees |  1 |          0 |        20 | 55a6b2d3ab7f5e6f2f414ac31d8512c6 |       20 | 3ab6d7a40eec92ad228dcbe849984451 |         20 | NULL | md5    |
+----+-----------+----+------------+-----------+----------------------------------+----------+----------------------------------+------------+------+--------+
1 row in set (0.00 sec)

Here I see that I have a difference in the data for the rows between 0 and 20 so lets use the same limit and compare the table results:

primary-mysql> select * from hr.employees limit 0,20;
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name  | email    | phone_number | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
...
|         112 | Jose Manuel | Urman      | JMURMAN  | 515.124.4469 | 2006-03-07 | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 |
...
20 rows in set (0.00 sec)

And on the replica:

replica-mysql> select * from hr.employees limit 0,20;
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name  | email    | phone_number | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+------------+----------+--------------+------------+------------+----------+----------------+------------+---------------+
...
|         112 | Jose Manuel | Urman      | JMURMAN  | 512.212.7567 | 2006-03-07 | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 |
...
20 rows in set (0.00 sec)

As you can see, using this command, I have been able to quickly narrow down where in my database I have data differences. Granted, on large datasets this process would be a little more time-consuming and iterative to get to the same result, but it is still a simple approach to carrying out some basic consistency checks across your data.

This process however does not come without warnings:

• For the replicator to be able to gather this information, the scan performed on each table will result in a temporary table being created. If you do not use the limit option, or if you do but limit to a still relatively large dataset, you need to be aware of the additional overhead this temporary table may create.
• The second warning is that the initial scan has to get a consistent view of the data and for that reason places a LOCK IN SHARE MODE on the table. This will not prevent other connections reading the data, but it will prevent writes to the table. On the replicas this will not be an issue since only the replicator writes to the tables anyway, but for the initial scan on the primary this may cause bottlenecks.
• It is strongly advised that data consistency checks are performed in maintenance windows when traffic is minimal for the best results and least impact!