|
OnlineDataDrift
Online datadrift checks for MySQL
OverviewThis describes a tool for generating checksums on tables in a MySQL database. It can be used to check for data drift between copies of tables on a master and slave. The tool is intended to be easy to use and efficient. The tool is easy to use because it can run concurrent with the normal workload on a master or slave, it does not require an idle server, and servers do not have to be drained or restored when it runs. The tool is efficient because it computes the checksum incrementally over a period of time. The rate at which the check is made is configurable. In order to be efficient and easy to use, this tool does not compute the checksum for all tables or all rows in one table at the same point in time. The tool computes a checksum for each chunk of rows in a table. It is likely that this will skip a few rows from some tables. This is a trade-off. By using short-running statements, the statements can be run on a primary and get replayed on the This is similar to mk-table-checksum but uses features added to the Google MySQL patch. The big difference is that this uses the last_value aggregate function to avoid estimating row ranges. DesignThe tool does the following:
When the tool is done, the result tables can be compared between a master and slaves. If differences are found, then there has been data drift. The checksum statements are run directly on masters. They are written to the binlog and replayed on all slaves by MySQL replication. They are executed on the primary and replicas at the same point in time as measured by transactions committed. Row EstimationRun SHOW TABLE STATUS for each database to get an estimate on the number of rows in each table. Determine the rate at which the checksum SQL statements should be run from the estimated row count, the number of rows per chunk (--rows_per_chunk) and the time in which the job should finish (--hours_to_run). Statements are run sequentially. If --hours_to_run is too small, the checksum job will not finish in the expected time. Statements will not be run concurrently to catch up. Checksum SQLThe tool scans a table in primary key order a chunk of rows at a time. This will scan some rows multiple times because the last row scanned from the previous statement is the first row scanned from the statement that follows. It may miss some rows because multiple statements are used for many tables and the same snapshot is not used for all statements. The scan is done by the following. This ignores the code to compute the checksum for simplicity.
SQL to determine the primary key values for the first row in the table: select @next_p1 := p1, @next_p2 := p2 from Foo order by p1, p2 limit 1 SQL to scan one chunk of rows assuming rows_per_chunk is 1000. This requires a new SQL aggregate function, last_value, that returns the last value of the input expression per group. The end of the table has been reached when the returned count is less than rows_per_chunk. The next scan starts at the primary key value at which this scan stops. Including this row in the checksum for two chunks is not a problem. -- be paranoid, don't trust evaluation for mutating user variables
set @start_p1 := @next_p1
set @start_p2 := @next_p2
select @next_p1 := last_value(p1), @next_p2 := last_value(p2), count(*)
from
(select * from Foo force index (PRIMARY)
where (p1 = @start_p1 and p2 > @start_p2) or p1 > @start_p1 limit 1000) fThe checksums for all columns must be computed. The SQL aggregate function ordered_checksum is used for this. The following must be added to the SQL above: ordered_checksum(p1), ordered_checksum(p2), ordered_checksum(c1), ordered_checksum(c2) The result table has a fixed number of columns but the number of expression produced depends on the number of columns in the checked tables. This is resolved by concatenating expressions together and storing the result as a string in the Offsets and Checksums columns of the result table. The result table has the columns:
Finally, all of this is to be stored in a result table, so a replace statement is used. The statement for the example table is: replace into tc (DatabaseName, TableName, Chunk, JobStarted, ChunkDone, Offsets, Checksums, Count)
select 'test', 'Foo', 1, @job_started, now(),
concat_ws(':', 'p1', @start_p1, @next_p1 := last_value(p1),
'p2', @start_p2, @next_p2 := last_value(p2)),
concat_ws(':', 'p1', ordered_checksum(p1),
'p2', ordered_checksum(p2),
'c1', ordered_checksum(c1),
'c2', ordered_checksum(c2)),
@count := count(*)
from
(select * from Foo force index (PRIMARY)
where (p1 = @start_p1 and p2 > @start_p2) or p1 > @start_p1
order by p1, p2 limit @rows_per_chunk) fTool optionsConfiguration options for the tool
Open IssuesFloating point columnsThe values of float and double columns might be different between the primary and replica for acceptable reasons. For example, the order of evaluation for aggregation may be different when a statement is executed on a replica. If rounding is required during the computation, then the result may be different. We don't know the extent of this problem. If it generates a lot of false warnings, then we should either ignore some floating point columns or provide a new SQL function that formats such columns as with a reduced amount of precision. This can be done by formatting as a decimal string with fewer digits of precision (3 for float, 6 for double). Tables without primary keysThere are a few options to handle tables without primary keys:
|
Sign in to add a comment
what's the name after compiling mysql？onlinedrift？But why I can not see this tool in $MYSQL_HOME/bin/
We didn't publish the source for this tool. Maatkit has a similar tool with source -- http://www.maatkit.org/doc/mk-table-checksum.html
so that's it。Tks！