Use pt-table-checksum to verify data consistency

Use pt-table-checksum to verify data consistency

The consistency check of Master/Slave Data is a headache. It is frustrating to be occasionally complained about inconsistency between master and slave data or data inconsistency between several slave databases. Generally, there is only one way to hot-standby the master database and replace all slave databases. This is not only costly, but also a solution similar to the temporary solution, which is quite disturbing. Therefore, we need appropriate tools to answer at least the following three questions:

• Is the latency from the database leading to inconsistent data seen by the user, or is the real Master/Slave Data inconsistent?
• If they are different, what is the proportion?
• Will it appear next time?

A clear answer to these questions helps us determine whether to fix and how to fix them. It also helps us identify inconsistent data and locate the root cause of the problem. Percona's pt-table-checksum is exactly what we want.

Introduction to pt-table-checksum

Pt-table-checksum is one of the famous percona-toolkit tools. It generates the checksum of the data blocks of the master database by executing statement-based SQL statements in the master database, passing the same SQL statements to the slave database, and calculating the checksum of the same data blocks on the slave database, finally, compare the checksum value of the same data block in the master-slave database to determine whether the master-slave data is consistent. This type of validation is performed in sub-tables and in each table is performed in parts. The pt tool itself provides a lot of Throttling options, so it has little impact on online services.

Percona-toolkit installation and introduction

Percona-toolkit: pt-summary, pt-mysql-summary, pt-config-diff, and pt-variable-advisor

Checksum calculation principle 1. Calculation of the checksum value of a single row of data

The pt tool first checks the structure, obtains the Data Types of each column, converts all data types to strings, and then connects them using the concat_ws () function, then, the checksum value of the row is calculated. Checksum uses crc32 by default. You can customize udf with higher efficiency.

2. Calculation of the checksum value of the data block

If the checksum of a row is calculated and compared with the slave database, the efficiency will be very low. The pt tool selects the indexes on the smart analysis table and splits the table data into chunks. The chunk is also used as the unit for calculation. Therefore, the aggregate function BIT_XOR () is introduced (). Its function can be understood as concatenating the data of all rows in the chunk, and then calculating the crc32 value to obtain the checksum value of the chunk. The SQL statement is as follows:
Here, count (*) is used to calculate the number of rows contained in the chunk. After each chunk checksum, the pt tool performs statistical analysis on the time consumed and intelligently adjusts the size of the next chunk to avoid the online impact of a large chunk, at the same time, it is also necessary to avoid the chunk being too small and inefficient.

3. How to ensure consistency

When the pt tool calculates the checksum of a chunk on the master database, the master database may still be updated, and the slave database may delay the relay-log to have updates related to the chunk data, how can we ensure that the master database and slave database calculate the same copy of data? The answer is to add the for update read lock, which ensures the consistency of data in a chunk of the master database. Otherwise, 1000 people may get 1000 different results for the same 1000 rows of data as chekcusm, and you cannot avoid mvcc interference! After obtaining the for update lock, the pt tool starts to calculate the checksum value of the chunk, saves the calculation result to the self-built result table of the pt tool (using the replace into select method), and then releases the lock. The statement is passed to the slave database and the same computing logic is executed.

For more details, please continue to read the highlights on the next page:

1 2 Next Page