MySQLtoolkit: mysql-table-checksum

Posted by paulmoen

Mysqltoolkit is a bunch of MySQL utilities based on perl written by Baron Schwartz.

Essentially he found no tool or software available to help him, so decided to write his own.
Not only that, he turned around and released them for any DBA to use.

As soon as I reviewed the list of tools available I immediately wanted to give mysql-table-checksum a test. Like many DBAs who support MySQL, master-slave replication is almost synonymous with MySQL databases. For all of its shortcomings, MySQL replication is the easiest database to set up replication on.

So the best test was getting the checksum to work, the nicer option though was testing the checksum of the master database against a slave database.
So I fired up my trusted EC2 MySQL 5.1 AMI, twice, practiced making a LVM snapshot backup (as practice makes perfect) and made a simple MySQL master-slave replication pair.

Comments:

Mysql-table-checksum works as stated. I had to get the permissions right on the slave for it work on the slaves as well.
You will need to install Perl DBI and DBD::MySQL, I have found running the cpan module is the easiest method to handling any dependencies.
If this was blended back into the MySQL distro at some stage, I am guessing a compiled program would be nicer so there is no additional work required to run.

As before I have included my edited screen dumps of the process.

Have Fun

Paul


Creating the Sakila sample database into MySQL master database

mysql -u root -p < sakila-schema.sql
Enter password: 
mysql -u root -p < sakila-data.sql
Enter password:  mysql -u root -p 
Enter password:

Welcome to the MySQL monitor. 
Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta-log
MySQL Community Server (GPL) 
Type 'help;' or '\h' for help.
Type '\c' to clear the buffer. 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| test               |
+--------------------+
4 rows in set (0.00 sec)


Install DBD::Mysql (dependency is DBI)

yum install perl-DBD-MySQL.i386


Install missing perl module

cpan> install Term::ReadKey

Installing MySQLtoolkit

perl Makefile.PL

Writing Makefile for mysqltoolkit

make

Manifying blib/man1/mysql-find.1
Manifying blib/man1/mysql-duplicate-key-checker.1
Manifying blib/man1/mysql-table-sync.1
Manifying blib/man1/mysql-deadlock-logger.1
Manifying blib/man1/mysql-slave-restart.1
Manifying blib/man1/mysql-slave-delay.1
Manifying blib/man1/mysql-profile-compact.1
Manifying blib/man1/mysql-archiver.1
Manifying blib/man1/mysql-query-profiler.1
Manifying blib/man1/mysql-visual-explain.1
Manifying blib/man1/mysql-table-checksum.1
Manifying blib/man1/mysql-checksum-filter.1
Manifying blib/man1/mysql-show-grants.1
Manifying blib/man3/mysqltoolkit.3pm

make test

cp lib/mysqltoolkit.pm blib/lib/mysqltoolkit.pm
cp bin/mysql-find blib/script/mysql-find
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-find
cp bin/mysql-duplicate-key-checker blib/script/mysql-duplicate-key-checker
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-duplicate-key-checker
cp bin/mysql-table-sync blib/script/mysql-table-sync
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-table-sync
cp bin/mysql-deadlock-logger blib/script/mysql-deadlock-logger
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-deadlock-logger
cp bin/mysql-slave-restart blib/script/mysql-slave-restart
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-slave-restart
cp bin/mysql-slave-delay blib/script/mysql-slave-delay
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-slave-delay
cp bin/mysql-profile-compact blib/script/mysql-profile-compact
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-profile-compact
cp bin/mysql-archiver blib/script/mysql-archiver
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-archiver
cp bin/mysql-query-profiler blib/script/mysql-query-profiler
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-query-profiler
cp bin/mysql-visual-explain blib/script/mysql-visual-explain
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-visual-explain
cp bin/mysql-table-checksum blib/script/mysql-table-checksum
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-table-checksum
cp bin/mysql-checksum-filter blib/script/mysql-checksum-filter
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-checksum-filter
cp bin/mysql-show-grants blib/script/mysql-show-grants
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-show-grants
No tests defined for mysqltoolkit extension.

make install

Installing /usr/lib/perl5/site_perl/5.8.5/mysqltoolkit.pm
Installing /usr/share/man/man1/mysql-find.1
Installing /usr/share/man/man1/mysql-duplicate-key-checker.1
Installing /usr/share/man/man1/mysql-table-sync.1
Installing /usr/share/man/man1/mysql-deadlock-logger.1
Installing /usr/share/man/man1/mysql-slave-restart.1
Installing /usr/share/man/man1/mysql-slave-delay.1
Installing /usr/share/man/man1/mysql-profile-compact.1
Installing /usr/share/man/man1/mysql-archiver.1
Installing /usr/share/man/man1/mysql-query-profiler.1
Installing /usr/share/man/man1/mysql-visual-explain.1
Installing /usr/share/man/man1/mysql-table-checksum.1
Installing /usr/share/man/man1/mysql-checksum-filter.1
Installing /usr/share/man/man1/mysql-show-grants.1
Installing /usr/share/man/man3/mysqltoolkit.3pm
Installing /usr/bin/mysql-find
Installing /usr/bin/mysql-duplicate-key-checker
Installing /usr/bin/mysql-table-sync
Installing /usr/bin/mysql-deadlock-logger
Installing /usr/bin/mysql-slave-restart
Installing /usr/bin/mysql-slave-delay
Installing /usr/bin/mysql-profile-compact
Installing /usr/bin/mysql-archiver
Installing /usr/bin/mysql-query-profiler
Installing /usr/bin/mysql-visual-explain
Installing /usr/bin/mysql-table-checksum
Installing /usr/bin/mysql-checksum-filter
Installing /usr/bin/mysql-show-grants
Writing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/mysqltoolkit/.packlist
Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod

mysql-table-checksum help printout

mysql-table-checksum --help

Usage: mysql-table-checksum [OPTION]... HOST [HOST...]

 --algorithm       -a   Checksum algorithm (ACCUM|CHECKSUM|BIT_XOR)
 --askpass              Prompt for username and password for connections
 --chunkcol             Explicitly specifies a column to use for chunking
 --chunksize       -C   Number of rows to checksum at a time (disallows -a CHECKSUM)
 --chunksize-exact      Try not to exceed chunk sizes
 --[no]count       -r   Do the count (default)
 --[no]crc         -c   Do the CRC (default)
 --databases       -d   Only do this comma-separated list of databases
 --defaults-file   -F   Only read default options from the given file
 --emptyrepltbl         Empty table given by --replicate before starting
 --engine          -e   Only do this comma-separated list of storage engines
 --explain              Show, but do not execute, checksum queries
 --explainhosts         Print connection information and exit
 --float-precision      Precision for FLOAT and DOUBLE column comparisons
 --function        -f   Cryptographic hash function (SHA1, MD5...)
 --help                 Show this help message
 --ignoredb        -g   Ignore this comma-separated list of databases
 --ignoretbl       -n   Ignore this comma-separated list of tables
 --index           -i   Index to use for ACCUM checksum algorithm
 --lock            -k   Lock table on master until done on slaves (implies -l)
 --[no]optxor      -o   Optimize BIT_XOR with user variables (default)
 --password        -p   Password to use when connecting
 --port            -P   Port number to use for connection
 --quiet           -q   Do not print checksum results
 --recursecheck         Do --replcheck recursively (implies --replcheck)
 --replcheck            Connect to slaves and check --replicate results
 --replicate       -R   Replicate checksums in a table (disallows -a CHECKSUM)
 --separator       -s   Separator for CONCAT_WS()
 --slavelag        -l   Report how far slaves lag master
 --sleep                Sleep time between checksums
 --sleep-coef           Sleep time as a multiple of last checksum time
 --socket          -S   Socket file to use for connection
 --tab             -b   Output separated with tabs
 --tables          -t   Only do this comma-separated list of tables
 --user            -u   User for login if not current user
 --[no]verify      -v   Verify checksum compatibility across servers (default)
 --version              Output version information and exit
 --wait            -w   How long to do MASTER_POS_WAIT() on slaves (implies -kl)
 --where           -W   Only do rows matching this WHERE clause (disallows -a CHECKSUM)

mysql-table-checksum efficiently checksums MySQL tables on one or more hosts.
If you specify multiple hosts, the first is assumed to be the master.
Connection options are read from MySQL option files.  For more details, please
read the documentation:

  perldoc mysql-table-checksum

Running simple checksum:

mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS

DATABASE TABLE                     CHUNK HOST      ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    db                            1 localhost MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0    0 NULL NULL
mysql    event                         1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    func                          1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    general_log                   1 localhost CSV             0                                     NULL    0    0 NULL NULL
mysql    help_category                 1 localhost MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0    0 NULL NULL
mysql    help_keyword                  1 localhost MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0    0 NULL NULL
mysql    help_relation                 1 localhost MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0    0 NULL NULL
mysql    help_topic                    1 localhost MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    1    0 NULL NULL
mysql    host                          1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    ndb_binlog_index              1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    plugin                        1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    proc                          1 localhost MyISAM          6 d388df6dabb27d2451457c3ce0f778ef938f246e    0    0 NULL NULL
mysql    procs_priv                    1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    servers                       1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    slow_log                      1 localhost CSV             0                                     NULL    0    0 NULL NULL
mysql    tables_priv                   1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone                     1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_leap_second         1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_name                1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition          1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition_type     1 localhost MyISAM          0                                     NULL    0    0 NULL NULL
mysql    user                          1 localhost MyISAM          3 53720f720d5057c0154b176727cdadbba0fe3779    0    0 NULL NULL
DATABASE TABLE         CHUNK HOST      ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 localhost InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0    0 NULL NULL
sakila   address           1 localhost InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0    0 NULL NULL
sakila   category          1 localhost InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0    0 NULL NULL
sakila   city              1 localhost InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0    0 NULL NULL
sakila   country           1 localhost InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0    0 NULL NULL
sakila   customer          1 localhost InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0    0 NULL NULL
sakila   film              1 localhost InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0    0 NULL NULL
sakila   film_actor        1 localhost InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0    0 NULL NULL
sakila   film_category     1 localhost InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0    0 NULL NULL
sakila   film_text         1 localhost MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    0    0 NULL NULL
sakila   inventory         1 localhost InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    0    0 NULL NULL
sakila   language          1 localhost InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0    0 NULL NULL
sakila   payment           1 localhost InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    1    0 NULL NULL
sakila   rental            1 localhost InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    0    0 NULL NULL
sakila   staff             1 localhost InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0    0 NULL NULL
sakila   store             1 localhost InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0    0 NULL NULL

Timing the runs of various different algorithms available

time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=ACCUM

real    0m2.232s
user    0m0.200s
sys     0m0.020s

time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=CHECKSUM

real    0m2.176s
user    0m0.230s
sys     0m0.000s

time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=BIT_XOR

real    0m1.984s
user    0m0.220s
sys     0m0.020s

Create a slave using LVM snapshot backup

mysql> create user checksum_user@'mysql-master'  identified by 'ch3cksum';
Query OK, 0 rows affected (0.00 sec)

mysql> grant super on *.* to checksum_user@'mysql-master' identified by 'ch3cksum';
Query OK, 0 rows affected (0.00 sec)


Doesn't work. Need to grant "GRANT ALL PRIVILEGES to root user from master host"


GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql-master' IDENTIFIED BY '...'

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS
DATABASE TABLE                     CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    db                            1 mysql-master MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0    0 NULL NULL
mysql    event                         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    func                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    general_log                   1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    help_category                 1 mysql-master MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0    0 NULL NULL
mysql    help_keyword                  1 mysql-master MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0    0 NULL NULL
mysql    help_relation                 1 mysql-master MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0    0 NULL NULL
mysql    help_topic                    1 mysql-master MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    0    0 NULL NULL
mysql    host                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    plugin                        1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    proc                          1 mysql-master MyISAM          6 9f1b2aad83c5274e4d95881ea0e241933c141862    0    0 NULL NULL
mysql    procs_priv                    1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    servers                       1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    slow_log                      1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    tables_priv                   1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone                     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_name                1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    user                          1 mysql-master MyISAM          4 4166a9a1d0b61dd90167da4631e7ac1fadcb716e    0    0 NULL NULL
DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 mysql-master InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0    0 NULL NULL
sakila   address           1 mysql-master InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0    0 NULL NULL
sakila   category          1 mysql-master InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0    0 NULL NULL
sakila   city              1 mysql-master InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0    0 NULL NULL
sakila   country           1 mysql-master InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0    0 NULL NULL
sakila   customer          1 mysql-master InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0    0 NULL NULL
sakila   film              1 mysql-master InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0    0 NULL NULL
sakila   film_actor        1 mysql-master InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0    0 NULL NULL
sakila   film_category     1 mysql-master InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0    0 NULL NULL
sakila   film_text         1 mysql-master MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    0    0 NULL NULL
sakila   inventory         1 mysql-master InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    0    0 NULL NULL
sakila   language          1 mysql-master InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0    0 NULL NULL
sakila   payment           1 mysql-master InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    1    0 NULL NULL
sakila   rental            1 mysql-master InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    1    0 NULL NULL
sakila   staff             1 mysql-master InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0    0 NULL NULL
sakila   store             1 mysql-master InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0    0 NULL NULL


On slave, check root@mysql-master has privileges to connect

mysql> show grants for 'root'@'mysql-master';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@mysql-master                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql-master' IDENTIFIED BY PASSWORD '*81D69EF224A834201E2351343112163A99A5476F' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Running command on master only

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS

DATABASE TABLE                     CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    db                            1 mysql-master MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0    0 NULL NULL
mysql    event                         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    func                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    general_log                   1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    help_category                 1 mysql-master MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0    0 NULL NULL
mysql    help_keyword                  1 mysql-master MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0    0 NULL NULL
mysql    help_relation                 1 mysql-master MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0    0 NULL NULL
mysql    help_topic                    1 mysql-master MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    0    0 NULL NULL
mysql    host                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    plugin                        1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    proc                          1 mysql-master MyISAM          6 9f1b2aad83c5274e4d95881ea0e241933c141862    0    0 NULL NULL
mysql    procs_priv                    1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    servers                       1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    slow_log                      1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    tables_priv                   1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone                     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_name                1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    user                          1 mysql-master MyISAM          6 57c3425f2dcd64664840aa6e858916322fdf1cc0    0    0 NULL NULL
DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 mysql-master InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0    0 NULL NULL
sakila   address           1 mysql-master InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0    0 NULL NULL
sakila   category          1 mysql-master InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0    0 NULL NULL
sakila   city              1 mysql-master InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0    0 NULL NULL
sakila   country           1 mysql-master InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0    0 NULL NULL
sakila   customer          1 mysql-master InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0    0 NULL NULL
sakila   film              1 mysql-master InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0    0 NULL NULL
sakila   film_actor        1 mysql-master InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0    0 NULL NULL
sakila   film_category     1 mysql-master InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0    0 NULL NULL
sakila   film_text         1 mysql-master MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    1    0 NULL NULL
sakila   inventory         1 mysql-master InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    0    0 NULL NULL
sakila   language          1 mysql-master InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0    0 NULL NULL
sakila   payment           1 mysql-master InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    0    0 NULL NULL
sakila   rental            1 mysql-master InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    1    0 NULL NULL
sakila   staff             1 mysql-master InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0    0 NULL NULL
sakila   store             1 mysql-master InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0    0 NULL NULL
[root@domU-12-31-36-00-00-42 sakila-db]# mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave
DATABASE TABLE                     CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    columns_priv                  1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    db                            1 mysql-master MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0    0 NULL NULL
mysql    db                            1 mysql-slave  MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0    0 NULL NULL
mysql    event                         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    event                         1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    func                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    func                          1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    general_log                   1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    general_log                   1 mysql-slave  CSV             0                                     NULL    0    0 NULL NULL
mysql    help_category                 1 mysql-master MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0    0 NULL NULL
mysql    help_category                 1 mysql-slave  MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0    0 NULL NULL
mysql    help_keyword                  1 mysql-master MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0    0 NULL NULL
mysql    help_keyword                  1 mysql-slave  MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0    0 NULL NULL
mysql    help_relation                 1 mysql-master MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0    0 NULL NULL
mysql    help_relation                 1 mysql-slave  MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0    0 NULL NULL
mysql    help_topic                    1 mysql-master MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    0    0 NULL NULL
mysql    help_topic                    1 mysql-slave  MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    0    0 NULL NULL
mysql    host                          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    host                          1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    plugin                        1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    plugin                        1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    proc                          1 mysql-master MyISAM          6 9f1b2aad83c5274e4d95881ea0e241933c141862    0    0 NULL NULL
mysql    proc                          1 mysql-slave  MyISAM          6 9f1b2aad83c5274e4d95881ea0e241933c141862    0    0 NULL NULL
mysql    procs_priv                    1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    procs_priv                    1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    servers                       1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    servers                       1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    slow_log                      1 mysql-master CSV             0                                     NULL    0    0 NULL NULL
mysql    slow_log                      1 mysql-slave  CSV             0                                     NULL    0    0 NULL NULL
mysql    tables_priv                   1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    tables_priv                   1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone                     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone                     1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_name                1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_name                1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-master MyISAM          0                                     NULL    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-slave  MyISAM          0                                     NULL    0    0 NULL NULL
mysql    user                          1 mysql-master MyISAM          6 57c3425f2dcd64664840aa6e858916322fdf1cc0    0    0 NULL NULL
mysql    user                          1 mysql-slave  MyISAM          6 57c3425f2dcd64664840aa6e858916322fdf1cc0    0    0 NULL NULL
DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 mysql-master InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0    0 NULL NULL
sakila   actor             1 mysql-slave  InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0    0 NULL NULL
sakila   address           1 mysql-master InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0    0 NULL NULL
sakila   address           1 mysql-slave  InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0    0 NULL NULL
sakila   category          1 mysql-master InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0    0 NULL NULL
sakila   category          1 mysql-slave  InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0    0 NULL NULL
sakila   city              1 mysql-master InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0    0 NULL NULL
sakila   city              1 mysql-slave  InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0    0 NULL NULL
sakila   country           1 mysql-master InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0    0 NULL NULL
sakila   country           1 mysql-slave  InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0    0 NULL NULL
sakila   customer          1 mysql-master InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0    0 NULL NULL
sakila   customer          1 mysql-slave  InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0    0 NULL NULL
sakila   film              1 mysql-master InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0    0 NULL NULL
sakila   film              1 mysql-slave  InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0    0 NULL NULL
sakila   film_actor        1 mysql-master InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0    0 NULL NULL
sakila   film_actor        1 mysql-slave  InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0    0 NULL NULL
sakila   film_category     1 mysql-master InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0    0 NULL NULL
sakila   film_category     1 mysql-slave  InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0    0 NULL NULL
sakila   film_text         1 mysql-master MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    0    0 NULL NULL
sakila   film_text         1 mysql-slave  MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    0    0 NULL NULL
sakila   inventory         1 mysql-master InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    1    0 NULL NULL
sakila   inventory         1 mysql-slave  InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    0    0 NULL NULL
sakila   language          1 mysql-master InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0    0 NULL NULL
sakila   language          1 mysql-slave  InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0    0 NULL NULL
sakila   payment           1 mysql-master InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    1    0 NULL NULL
sakila   payment           1 mysql-slave  InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    0    0 NULL NULL
sakila   rental            1 mysql-master InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    1    0 NULL NULL
sakila   rental            1 mysql-slave  InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    1    0 NULL NULL
sakila   staff             1 mysql-master InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0    0 NULL NULL
sakila   staff             1 mysql-slave  InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0    0 NULL NULL
sakila   store             1 mysql-master InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0    0 NULL NULL
sakila   store             1 mysql-slave  InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0    0 NULL NULL


With BIT_XOR algorithm

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave --algorithm=BIT_XOR
DATABASE TABLE                     CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    columns_priv                  1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    db                            1 mysql-master MyISAM          2 924537BECA7F5E81CF29E64077F12AD3BCC5BB2B    0    0 NULL NULL
mysql    db                            1 mysql-slave  MyISAM          2 924537BECA7F5E81CF29E64077F12AD3BCC5BB2B    0    0 NULL NULL
mysql    event                         1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    event                         1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    func                          1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    func                          1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    general_log                   1 mysql-master CSV             0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    general_log                   1 mysql-slave  CSV             0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    help_category                 1 mysql-master MyISAM         37 7BA18C3681F17D236E38254D00CC43A8E1544AF8    0    0 NULL NULL
mysql    help_category                 1 mysql-slave  MyISAM         37 7BA18C3681F17D236E38254D00CC43A8E1544AF8    0    0 NULL NULL
mysql    help_keyword                  1 mysql-master MyISAM        425 B746F8F8D7D50EA9810923704522CE769C81B494    0    0 NULL NULL
mysql    help_keyword                  1 mysql-slave  MyISAM        425 B746F8F8D7D50EA9810923704522CE769C81B494    0    0 NULL NULL
mysql    help_relation                 1 mysql-master MyISAM        898 FC305F19C53B03FF2DA9670B97B6AEF0916FDE25    0    0 NULL NULL
mysql    help_relation                 1 mysql-slave  MyISAM        898 FC305F19C53B03FF2DA9670B97B6AEF0916FDE25    0    0 NULL NULL
mysql    help_topic                    1 mysql-master MyISAM        478 96B15EF54DEB0510FA0006C4870E1A29634BB4BE    0    0 NULL NULL
mysql    help_topic                    1 mysql-slave  MyISAM        478 96B15EF54DEB0510FA0006C4870E1A29634BB4BE    0    0 NULL NULL
mysql    host                          1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    host                          1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    ndb_binlog_index              1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    plugin                        1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    plugin                        1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    proc                          1 mysql-master MyISAM          6 6D5555085EF1DAA2B2B657DB9507EF5212924F96    0    0 NULL NULL
mysql    proc                          1 mysql-slave  MyISAM          6 6D5555085EF1DAA2B2B657DB9507EF5212924F96    0    0 NULL NULL
mysql    procs_priv                    1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    procs_priv                    1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    servers                       1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    servers                       1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    slow_log                      1 mysql-master CSV             0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    slow_log                      1 mysql-slave  CSV             0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    tables_priv                   1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    tables_priv                   1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone                     1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone                     1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_leap_second         1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_name                1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_name                1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_transition          1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-master MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    time_zone_transition_type     1 mysql-slave  MyISAM          0 0000000000000000000000000000000000000000    0    0 NULL NULL
mysql    user                          1 mysql-master MyISAM          6 ECD344AA2367991CAFEFC28B94C057D894186EA6    0    0 NULL NULL
mysql    user                          1 mysql-slave  MyISAM          6 ECD344AA2367991CAFEFC28B94C057D894186EA6    0    0 NULL NULL
DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 mysql-master InnoDB        200 157EB02E45E58007B07DA789953BA599973FDA63    0    0 NULL NULL
sakila   actor             1 mysql-slave  InnoDB        200 157EB02E45E58007B07DA789953BA599973FDA63    0    0 NULL NULL
sakila   address           1 mysql-master InnoDB        603 6D7722328064948F07916D9AF3FADEB07952D988    0    0 NULL NULL
sakila   address           1 mysql-slave  InnoDB        603 6D7722328064948F07916D9AF3FADEB07952D988    0    0 NULL NULL
sakila   category          1 mysql-master InnoDB         16 B051DA25BFD9CABE4D831ADC79D9B213B3F1E723    0    0 NULL NULL
sakila   category          1 mysql-slave  InnoDB         16 B051DA25BFD9CABE4D831ADC79D9B213B3F1E723    0    0 NULL NULL
sakila   city              1 mysql-master InnoDB        600 482C25D7E0FD3434A5644ABB26963E1E880C5DE0    0    0 NULL NULL
sakila   city              1 mysql-slave  InnoDB        600 482C25D7E0FD3434A5644ABB26963E1E880C5DE0    0    0 NULL NULL
sakila   country           1 mysql-master InnoDB        109 972AFF5C0601C571B80F895785FA6F70AAFE20EA    0    0 NULL NULL
sakila   country           1 mysql-slave  InnoDB        109 972AFF5C0601C571B80F895785FA6F70AAFE20EA    0    0 NULL NULL
sakila   customer          1 mysql-master InnoDB        599 9B42642F2A15D9A0EC255CB6C5E8B9231A8BD04B    0    0 NULL NULL
sakila   customer          1 mysql-slave  InnoDB        599 9B42642F2A15D9A0EC255CB6C5E8B9231A8BD04B    0    0 NULL NULL
sakila   film              1 mysql-master InnoDB       1000 5290C30D4589E69533FE037C1D1D61494968E3C7    0    0 NULL NULL
sakila   film              1 mysql-slave  InnoDB       1000 5290C30D4589E69533FE037C1D1D61494968E3C7    0    0 NULL NULL
sakila   film_actor        1 mysql-master InnoDB       5462 66AB134AF212CBE2FDCF062C488552BD9FDA982A    0    0 NULL NULL
sakila   film_actor        1 mysql-slave  InnoDB       5462 66AB134AF212CBE2FDCF062C488552BD9FDA982A    0    0 NULL NULL
sakila   film_category     1 mysql-master InnoDB       1000 4367D179ECB2168A85434DDB696E80A24FAA1FBF    0    0 NULL NULL
sakila   film_category     1 mysql-slave  InnoDB       1000 4367D179ECB2168A85434DDB696E80A24FAA1FBF    0    0 NULL NULL
sakila   film_text         1 mysql-master MyISAM       1000 BFF6E20B7831A9083386629509C6E1A14D325ED9    0    0 NULL NULL
sakila   film_text         1 mysql-slave  MyISAM       1000 BFF6E20B7831A9083386629509C6E1A14D325ED9    0    0 NULL NULL
sakila   inventory         1 mysql-master InnoDB       4581 95A1455B778287CC1F333ECACF08F1771F9178A1    1    0 NULL NULL
sakila   inventory         1 mysql-slave  InnoDB       4581 95A1455B778287CC1F333ECACF08F1771F9178A1    0    0 NULL NULL
sakila   language          1 mysql-master InnoDB          6 94710D1DA433D122B9F47C526F10BEEC01AD67BF    0    0 NULL NULL
sakila   language          1 mysql-slave  InnoDB          6 94710D1DA433D122B9F47C526F10BEEC01AD67BF    0    0 NULL NULL
sakila   payment           1 mysql-master InnoDB      16049 E8EBF724C16994BC5C499E3B45FB1AA5C0A1CFDD    0    0 NULL NULL
sakila   payment           1 mysql-slave  InnoDB      16049 E8EBF724C16994BC5C499E3B45FB1AA5C0A1CFDD    1    0 NULL NULL
sakila   rental            1 mysql-master InnoDB      16044 8E6D5544BF044732ED3D5CC107A0797E92D27011    0    0 NULL NULL
sakila   rental            1 mysql-slave  InnoDB      16044 8E6D5544BF044732ED3D5CC107A0797E92D27011    1    0 NULL NULL
sakila   staff             1 mysql-master InnoDB          2 5B1AC4028B339F3F2A303367CECCC8C83D9C0360    0    0 NULL NULL
sakila   staff             1 mysql-slave  InnoDB          2 5B1AC4028B339F3F2A303367CECCC8C83D9C0360    0    0 NULL NULL
sakila   store             1 mysql-master InnoDB          2 CB0B4C8B4374925926117ADF8C8958CA43E8ACFF    0    0 NULL NULL
sakila   store             1 mysql-slave  InnoDB          2 CB0B4C8B4374925926117ADF8C8958CA43E8ACFF    0    0 NULL NULL

Creating checksum table so we can check for errors in checksum

CREATE TABLE checksum (
    db         char(64)     NOT NULL,
    tbl        char(64)     NOT NULL,
    chunk      int          NOT NULL,
    boundaries char(64)     NOT NULL,
    this_crc   char(40)     NOT NULL,
    this_cnt   int          NOT NULL,
    master_crc char(40)         NULL,
    master_cnt int              NULL,
    ts         timestamp    NOT NULL,
    PRIMARY KEY (db, tbl, chunk)
 ) ENGINE=InnoDB;

mysql> use mysql
Database changed
mysql>  CREATE TABLE checksum (
   ->      db         char(64)     NOT NULL,
   ->      tbl        char(64)     NOT NULL,
   ->      chunk      int          NOT NULL,
   ->      boundaries char(64)     NOT NULL,
   ->      this_crc   char(40)     NOT NULL,
   ->      this_cnt   int          NOT NULL,
   ->      master_crc char(40)         NULL,
   ->      master_cnt int              NULL,
   ->      ts         timestamp    NOT NULL,
   ->      PRIMARY KEY (db, tbl, chunk)
   ->   ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum
DATABASE TABLE                     CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
mysql    columns_priv                  1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    db                            1 mysql-master MyISAM          2 c62b1e99966f5652a478847b624df26b4dfd1377    0 NULL NULL NULL
mysql    event                         1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    func                          1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
You can't use usual read lock with log tables. Try READ LOCAL instead at line 790 while doing mysql.general_log
mysql    help_category                 1 mysql-master MyISAM         37 9ef62663c76f84a5bd7e875f65ff3f63c2994479    0 NULL NULL NULL
mysql    help_keyword                  1 mysql-master MyISAM        425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c    0 NULL NULL NULL
mysql    help_relation                 1 mysql-master MyISAM        898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466    0 NULL NULL NULL
mysql    help_topic                    1 mysql-master MyISAM        478 0b15c5e97f5cec4b857550848ac5263170e3487e    0 NULL NULL NULL
mysql    host                          1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    ndb_binlog_index              1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    plugin                        1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    proc                          1 mysql-master MyISAM          6 9f1b2aad83c5274e4d95881ea0e241933c141862    0 NULL NULL NULL
mysql    procs_priv                    1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    servers                       1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
You can't use usual read lock with log tables. Try READ LOCAL instead at line 790 while doing mysql.slow_log
mysql    tables_priv                   1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    time_zone                     1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    time_zone_leap_second         1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    time_zone_name                1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    time_zone_transition          1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    time_zone_transition_type     1 mysql-master MyISAM          0                                     NULL    0 NULL NULL NULL
mysql    user                          1 mysql-master MyISAM          6 57c3425f2dcd64664840aa6e858916322fdf1cc0    0 NULL NULL NULL
DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
sakila   actor             1 mysql-master InnoDB        200 e460daf0c941a35e07595f3b76686132d3994e49    0 NULL NULL NULL
sakila   address           1 mysql-master InnoDB        603 175a2192effdad0b4a8c16ee5038de16e7b32223    0 NULL NULL NULL
sakila   category          1 mysql-master InnoDB         16 579c8b0ad142daf527bff6d52abbf904bb873985    0 NULL NULL NULL
sakila   city              1 mysql-master InnoDB        600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0    0 NULL NULL NULL
sakila   country           1 mysql-master InnoDB        109 47146889e8990bc79d4b9aaf7344ffbf279eade1    0 NULL NULL NULL
sakila   customer          1 mysql-master InnoDB        599 5109a39e50e0c29cee8b6805be3c8be2542bba74    0 NULL NULL NULL
sakila   film              1 mysql-master InnoDB       1000 3857ddc3f576d2246e35366b57bbf02d18455bac    0 NULL NULL NULL
sakila   film_actor        1 mysql-master InnoDB       5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad    0 NULL NULL NULL
sakila   film_category     1 mysql-master InnoDB       1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8    0 NULL NULL NULL
sakila   film_text         1 mysql-master MyISAM       1000 355c6e8af91556fcfba2af381539e285e368e2b0    0 NULL NULL NULL
sakila   inventory         1 mysql-master InnoDB       4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9    0 NULL NULL NULL
sakila   language          1 mysql-master InnoDB          6 95ef63c60bdc024031c746232262f77ad7ead342    0 NULL NULL NULL
sakila   payment           1 mysql-master InnoDB      16049 d63f0af5124c1598061e1d99af03785db0a600e3    1 NULL NULL NULL
sakila   rental            1 mysql-master InnoDB      16044 047b7143e51a64348451dfe57d0c78c4da8950f9    1 NULL NULL NULL
sakila   staff             1 mysql-master InnoDB          2 3c08fd6c4482c4e48c2c692c731e57f991e21089    0 NULL NULL NULL
sakila   store             1 mysql-master InnoDB          2 9976f802fe471c5ac6c9c037a28557ff2b24da3f    0 NULL NULL NULL


On Slave:

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| checksum                  |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

mysql> select * from checksum;
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
| db     | tbl                       | chunk | boundaries | this_crc                                 | this_cnt | master_crc                               | master_cnt | ts                  |
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
| mysql  | columns_priv              |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | db                        |     1 | 1=1        | c62b1e99966f5652a478847b624df26b4dfd1377 |        2 | c62b1e99966f5652a478847b624df26b4dfd1377 |          2 | 2007-09-08 02:44:28 |
| mysql  | event                     |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | func                      |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | help_category             |     1 | 1=1        | 9ef62663c76f84a5bd7e875f65ff3f63c2994479 |       37 | 9ef62663c76f84a5bd7e875f65ff3f63c2994479 |         37 | 2007-09-08 02:44:28 |
| mysql  | help_keyword              |     1 | 1=1        | db02c9d0f5a3d3eab678af8cdbb557c84518b14c |      425 | db02c9d0f5a3d3eab678af8cdbb557c84518b14c |        425 | 2007-09-08 02:44:28 |
| mysql  | help_relation             |     1 | 1=1        | 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 |      898 | 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 |        898 | 2007-09-08 02:44:28 |
| mysql  | help_topic                |     1 | 1=1        | 0b15c5e97f5cec4b857550848ac5263170e3487e |      478 | 0b15c5e97f5cec4b857550848ac5263170e3487e |        478 | 2007-09-08 02:44:28 |
| mysql  | host                      |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | ndb_binlog_index          |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | plugin                    |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | proc                      |     1 | 1=1        | 9f1b2aad83c5274e4d95881ea0e241933c141862 |        6 | 9f1b2aad83c5274e4d95881ea0e241933c141862 |          6 | 2007-09-08 02:44:28 |
| mysql  | procs_priv                |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | servers                   |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | tables_priv               |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | time_zone                 |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | time_zone_leap_second     |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | time_zone_name            |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | time_zone_transition      |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | time_zone_transition_type |     1 | 1=1        |                                          |        0 |                                          |          0 | 2007-09-08 02:44:28 |
| mysql  | user                      |     1 | 1=1        | 57c3425f2dcd64664840aa6e858916322fdf1cc0 |        6 | 57c3425f2dcd64664840aa6e858916322fdf1cc0 |          6 | 2007-09-08 02:44:28 |
| sakila | actor                     |     1 | 1=1        | e460daf0c941a35e07595f3b76686132d3994e49 |      200 | e460daf0c941a35e07595f3b76686132d3994e49 |        200 | 2007-09-08 02:44:28 |
| sakila | address                   |     1 | 1=1        | 175a2192effdad0b4a8c16ee5038de16e7b32223 |      603 | 175a2192effdad0b4a8c16ee5038de16e7b32223 |        603 | 2007-09-08 02:44:28 |
| sakila | category                  |     1 | 1=1        | 579c8b0ad142daf527bff6d52abbf904bb873985 |       16 | 579c8b0ad142daf527bff6d52abbf904bb873985 |         16 | 2007-09-08 02:44:28 |
| sakila | city                      |     1 | 1=1        | 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 |      600 | 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 |        600 | 2007-09-08 02:44:28 |
| sakila | country                   |     1 | 1=1        | 47146889e8990bc79d4b9aaf7344ffbf279eade1 |      109 | 47146889e8990bc79d4b9aaf7344ffbf279eade1 |        109 | 2007-09-08 02:44:28 |
| sakila | customer                  |     1 | 1=1        | 5109a39e50e0c29cee8b6805be3c8be2542bba74 |      599 | 5109a39e50e0c29cee8b6805be3c8be2542bba74 |        599 | 2007-09-08 02:44:28 |
| sakila | film                      |     1 | 1=1        | 3857ddc3f576d2246e35366b57bbf02d18455bac |     1000 | 3857ddc3f576d2246e35366b57bbf02d18455bac |       1000 | 2007-09-08 02:44:28 |
| sakila | film_actor                |     1 | 1=1        | 4b2f6d38e7b75bab9cce775748c0a655643bdaad |     5462 | 4b2f6d38e7b75bab9cce775748c0a655643bdaad |       5462 | 2007-09-08 02:44:28 |
| sakila | film_category             |     1 | 1=1        | 45a4418ebcc3a9c4808512414112abbe0f5e64e8 |     1000 | 45a4418ebcc3a9c4808512414112abbe0f5e64e8 |       1000 | 2007-09-08 02:44:28 |
| sakila | film_text                 |     1 | 1=1        | 355c6e8af91556fcfba2af381539e285e368e2b0 |     1000 | 355c6e8af91556fcfba2af381539e285e368e2b0 |       1000 | 2007-09-08 02:44:28 |
| sakila | inventory                 |     1 | 1=1        | 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 |     4581 | 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 |       4581 | 2007-09-08 02:44:28 |
| sakila | language                  |     1 | 1=1        | 95ef63c60bdc024031c746232262f77ad7ead342 |        6 | 95ef63c60bdc024031c746232262f77ad7ead342 |          6 | 2007-09-08 02:44:28 |
| sakila | payment                   |     1 | 1=1        | d63f0af5124c1598061e1d99af03785db0a600e3 |    16049 | d63f0af5124c1598061e1d99af03785db0a600e3 |      16049 | 2007-09-08 02:44:29 |
| sakila | rental                    |     1 | 1=1        | 047b7143e51a64348451dfe57d0c78c4da8950f9 |    16044 | 047b7143e51a64348451dfe57d0c78c4da8950f9 |      16044 | 2007-09-08 02:44:30 |
| sakila | staff                     |     1 | 1=1        | 3c08fd6c4482c4e48c2c692c731e57f991e21089 |        2 | 3c08fd6c4482c4e48c2c692c731e57f991e21089 |          2 | 2007-09-08 02:44:30 |
| sakila | store                     |     1 | 1=1        | 9976f802fe471c5ac6c9c037a28557ff2b24da3f |        2 | 9976f802fe471c5ac6c9c037a28557ff2b24da3f |          2 | 2007-09-08 02:44:30 |
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
37 rows in set (0.00 sec)

Adding a row to slave (sakila.country)

mysql> insert into country values (110,'WoopWoop',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from country order by country_id desc limit 2;
+------------+----------+---------------------+
| country_id | country  | last_update         |
+------------+----------+---------------------+
|        110 | WoopWoop | 2007-09-08 02:50:18 |
|        109 | Zambia   | 2006-02-15 04:44:00 |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

On master

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave --algorithm=BIT_XOR

DATABASE TABLE         CHUNK HOST         ENGINE      COUNT                                 CHECKSUM TIME WAIT STAT  LAG
...
sakila   country           1 mysql-master InnoDB        109 972AFF5C0601C571B80F895785FA6F70AAFE20EA    0    0 NULL NULL
sakila   country           1 mysql-slave  InnoDB        110 EC435FA369E1AEC29DB08161E7D09320451F3D88    0    0 NULL NULL
...


Running mysql-table-checlsum with --replcheck option

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl --replcheck

Results on mysql-master (Host=mysql-master,Server_id=1)
[root@domU-12-31-36-00-00-42 sakila-db]# echo $?
0


Need to set report-host in slave /etc/my.cnf and restart slave

report-host='mysql-slave'

mysql> show slave hosts\G
*************************** 1. row ***************************
       Server_id: 2
            Host: mysql-slave
            Port: 3306
Rpl_recovery_rank: 0
       Master_id: 1
1 row in set (0.00 sec)


Still didn't find the row difference using --replcheck however using SQL on slave did

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl --replcheck

Results on mysql-slave:3306 (Port=3306,Master_id=1,Host=mysql-slave,Server_id=2)

Results on mysql-master (Host=mysql-master,Server_id=1)
[root@domU-12-31-36-00-00-42 sakila-db]# echo $?
0

SQL to run to check for issues with checksums:

SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc  master_crc OR ISNULL(master_crc)  ISNULL(this_crc) AS crc_diff
FROM checksum
WHERE master_cnt  this_cnt OR master_crc  this_crc
OR ISNULL(master_crc)  ISNULL(this_crc);

mysql> use mysql
Database changed
mysql> SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
   ->      this_crc  master_crc OR ISNULL(master_crc)  ISNULL(this_crc) AS crc_diff
   ->   FROM checksum
   ->   WHERE master_cnt  this_cnt OR master_crc  this_crc
   ->      OR ISNULL(master_crc)  ISNULL(this_crc);
+--------+---------+-------+----------+----------+
| db     | tbl     | chunk | cnt_diff | crc_diff |
+--------+---------+-------+----------+----------+
| sakila | country |     1 |        1 |        1 |
+--------+---------+-------+----------+----------+
1 row in set (0.00 sec)


mysql> use test
Database changed
mysql> create table t1 (name varchar(25)) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values ('Fred');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values ('John');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('Dimitri');
Query OK, 1 row affected (0.01 sec)

On Slave

mysql> use test;
Database changed
mysql> select * from t1;
+---------+
| name    |
+---------+
| Fred    |
| John    |
| Dimitri |
+---------+
3 rows in set (0.00 sec)

Stop slave;

mysql> delete from t1 where name = 'Fred';
Query OK, 1 row affected (0.01 sec)

mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+---------+
| name    |
+---------+
| Fred    |
| John    |
| Dimitri |
+---------+
3 rows in set (0.00 sec)

Run mysql-table-checksum again

mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl

mysql> SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc  master_crc OR ISNULL(master_crc)  ISNULL(this_crc) AS crc_diff
FROM checksum
WHERE master_cnt  this_cnt
OR master_crc  this_crc
OR ISNULL(master_crc)  ISNULL(this_crc);
+------+-----+-------+----------+----------+
| db   | tbl | chunk | cnt_diff | crc_diff |
+------+-----+-------+----------+----------+
| test | t1  |     1 |        1 |        1 |
+------+-----+-------+----------+----------+
1 row in set (0.00 sec)

Future Posting Roadmap: 2007

Posted by paulmoen

Posting has been lighter than normal, as I have had the flu and it is footy finals time in Australia.

However I sat down the other day and thought about which areas I hope to cover over the next couple of months.
Given this is a blog and comments are on, if you want something covered which is not on the list feel free to add a comment with a request.

Topics:

Replication:

Determine the IO/sec where the slave starts falling behind.
Test mysqlslap running write workload on master and read workload on slave.
Test running mysqlslap 3rd machine and connection to master and slave.
Test running mysqlslap on replication with auto-increment column so show the effect of increment locking on master and slave.
mysqlslap vs MySQL 5.1 row based replication

The end result is a performance report which answers the following questions:

how many concurrent users causes the slave to fall behind
When does network bandwidth become an issue. (test simple scp to get raw bandwidth)
how does reading from the slave affect the slave’s ability to keep up.

MySQL Cluster:

mysqlslap vs MySQL 5.1 Cluster
running ndb Sizing script against normal database
mysqlslap vs MySQL 5.1 Cluster Replication
benchmarking and performance.

MySQL Toolkit and related:

One article per mysqltoolkit tool.
Article on using innotop

Misc:

Testing mysql partitioning, and maintenance tasks.
How to design your database for your application (using load tables, federated storage)
Using tmpdir as /dev/shm to help with temp table issues.
Point in Time recovery with MySQL. Using slave (start slave until)
Backups using LVM. => Done
innodb_flush_method=O_DIRECT. Reason: see what the difference is, on a standalone instance and replication.

PostgreSQL:

performance
clustering options for redundancy

Sharding:

Using MySql
using OracleXE
using PostgreSQL
using Hibernate

Have Fun

Paul

MySQL Backups using LVM snapshots

Posted by paulmoen

There are a couple of options available to get consistent backups from MySQL.

Use mysqldump with FLUSH TABLES WITH READ LOCK
Use a slave with STOP SLAVE and your favourite backup tool.
For innodb, use the commerical backup tool ibbackup
Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
Shutdown the database.

We are going to demostrate how to use LVM snapshots to reduce time required to hold MySQL consistent. I used these two good articles here and here as a basis for this one.

To use LVM on EC2, you need to umount the /mnt partition and create a physical volume (pvcreate) and then a bunch of logical volumes (lvcreate). Once you have the use of LVM, you can use lvcreate -s to create a snapshot of the logical volume.

The ease of taking backups like this means the time that the mysql database must be unable to handle writes (due to the read lock) is as short as the time takes for the lvcreate to finish.

As normal I have provided a cleared up screen dump of my terminal at the end of this post.

I was able to successfully take a backup, and tar the snapshot and create a slave from the backup.

Here is the procedure to take a backup using LVM.

mysql connection (left open): FLUSH TABLES WITH READ LOCK;
lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
mysql connection: UNLOCK TABLES;

Notes:

You must wait for the FLUSH TABLES to finish, otherwise your backup will NOT be consistent. Peter Zaitsev mentions flushing individual tables can help the speed, followed by a final FLUSH TABLES
Leave the mysql connection open, so the READ LOCK is held until you issue the UNLOCK
lvcreate -s can fail with an error snapshot: Required device-mapper target(s) not detected in your kernel The solution: modprobe dm-snapshot

There is a perl script from Lenz Grimmer called mylvmbackup which I will check out soon.

Have Fun

Paul

This script with create two logical volumes /data1/mysql, /data2/mysql both 30 Gigabytes in size


###  BEGIN OF SCRIPT ###

#!/bin/sh
#
# Script to make EC2 /mnt into a LVM volume

umount /mnt
pvcreate /dev/sda2
vgcreate vg /dev/sda2
lvcreate -L30720M -n myvmdisk1 vg
mkfs -t ext3 /dev/vg/myvmdisk1
mkdir -p /data1/mysql/data
mount /dev/vg/myvmdisk1 /data1/mysql

lvcreate -L30720M -n myvmdisk2 vg
mkfs -t ext3 /dev/vg/myvmdisk2
mkdir -p /data2/mysql/data
mount /dev/vg/myvmdisk2 /data2/mysql

lvcreate -L30720M -n myvmdisk3 vg
mkfs -t ext3 /dev/vg/myvmdisk3
mkdir -p /backup/mysql/data
mount /dev/vg/myvmdisk3 /backup/mysql

lvdisplay

mkdir -p /data1/mysql/data
mkdir -p /data2/mysql/data

chown -R mysql:mysql /data1/mysql
chown -R mysql:mysql /data2/mysql
chown -R mysql:mysql /backup/mysql

### END OF SCRIPT ###

Do the snapshot backup

Flush the tables with read lock to dump to disk and get consistent state of data.

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.20-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Try the create the snapshot...

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
snapshot: Required device-mapper target(s) not detected in your kernel
lvcreate: Create a logical volume

modprobe dm-snapshot

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
Logical volume "dbbackup" created

Check what logical volumes are available

lvdisplay
--- Logical volume ---
LV Name                /dev/vg/myvmdisk1
VG Name                vg
LV UUID                1IpGD0-c3e0-DauB-Xj8p-AZAk-VpNG-RRDbUP
LV Write Access        read/write
LV snapshot status     source of
                       /dev/vg/dbbackup [active]
LV Status              available
# open                 1
LV Size                30.00 GB
Current LE             7680
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:0

--- Logical volume ---
LV Name                /dev/vg/myvmdisk2
VG Name                vg
LV UUID                bIz7vN-zWy8-PT3N-j1GT-URwi-RN8p-o6eRFQ
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                30.00 GB
Current LE             7680
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:1

--- Logical volume ---
LV Name                /dev/vg/dbbackup
VG Name                vg
LV UUID                c4GXbG-3dpv-7zEI-sm8r-eLFq-t3Ud-XdQHAx
LV Write Access        read/write
LV snapshot status     active destination for /dev/vg/myvmdisk1
LV Status              available
# open                 0
LV Size                30.00 GB
Current LE             7680
COW-table size         16.00 GB
COW-table LE           4096
Allocated to snapshot  0.00%
Snapshot chunk size    8.00 KB
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:4


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |  3840558 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


mkdir /mnt/backup
mount /dev/vg/dbbackup /mnt/backup

df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             7.9G  1.3G  6.3G  17% /
/dev/mapper/vg-myvmdisk1
                     30G  3.6G   25G  13% /data1/mysql
/dev/mapper/vg-myvmdisk2
                     30G   77M   28G   1% /data2/mysql
/dev/mapper/vg-dbbackup
                     30G  3.6G   25G  13% /mnt/backup

Make a tarball of /mnt/backup/data

cd /mnt/backup
tar -czvf mysql_backup_`date +%Y%m%d`.tar.gz data/ /etc/my.cnf

mysqlslap proves Innodb auto increment limits scalability

Posted by paulmoen

I was testing some mysqlslap runs which Brian ‘Krow’ Aker was running testing the effect of changing the commit interval. Whilst I was doing that I noticed that he had added the option to add an auto incrementing column to the generated table.

Having read elsewhere here and here that the way innodb handles auto incrementing columns can cause issues, I thought I would test that out. And as the picture shows, it certainly does limit scalability. Here is the mysql documentation on the subject of auto incrementing columns.

That is not the end of the story for auto incrementing columns, if you are looking at replication, in particular master-master replication you have to be very careful how to you handle them.
Auto incrementing and Replication
Auto increment increment and offset

Plus if you are looking at scaling out rather than scaling up, having natural primary keys rather than synthetic primary keys (read primary keys based on sequences or auto incrementing numbers) moving data between shards with natural keys will be a lot easier. Much less work required to prove the ID column in one shard is unique across all shards.

Have Fun

Paul


Commands:

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 --number-int-cols=2 --number-char-cols=3 \
--auto-generate-sql --csv=/tmp/innodb-write-scale.csv \
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--auto-generate-sql-execute-number=500

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--csv=/tmp/test_concurr_noincr.csv --engine=innodb \
--auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 \
--user=root --password

Results:

cat /tmp/innodb-write-scale.csv

innodb,write,0.178,0.128,0.369,1,500
innodb,write,6.106,5.846,6.546,25,500
innodb,write,13.481,13.196,13.737,50,500
innodb,write,24.853,22.928,38.379,75,500
innodb,write,39.932,39.283,41.079,100,500

cat /tmp/test_concurr_noincr.csv

innodb,write,0.178,0.125,0.255,1,500
innodb,write,5.111,4.765,5.503,25,500
innodb,write,10.590,10.052,11.234,50,500
innodb,write,16.091,15.312,16.820,75,500
innodb,write,22.645,21.720,23.698,100,500

MySQL DBT2 Benchmark on EC2 part 1

Posted by paulmoen

In the last couple of articles I have been using the load simulator/generator tool provided with the MySQL 5.1 install called mysqlslap.

I read around on some other blogs and thought it might be also useful to use a benchmarking tool. DBT2 is a TPC-C like benchmark tool provided by OSDL. You can download the software from the DBT sourceforge site.

The TPC-C is a online transaction test benchmark and the overall measure is in Transactions per Minute on TPM. The higher the better.

I chose to use the DBT2 benchmark as this is the tool that MySQL AB themselves have used to benchmark their MySQL Cluster. I also found plenty of useful information of Peter Zaitsev’s MySQLPerformance blog, specifically this presentation (PDF).

On with the show, once again, we need to test a plain standalone MySQL database. Once that is done we can build on that to test MySQL replication (Master-Slave) and MySQL NDB Clusters.

The documentation for the tool is essentially the README and README-MYSQL files in the tarball, the README mentioned a user manual which unfortunately I couldn’t find anywhere.
There is scant mention of prerequisites either, hopefully this article will help fill the void for a while.
I will probably need to create a proper HOWTO document for this as there were a bunch of conflicting information in the README files which was different to the actually files available.

Prerequisites:

Perl 5.8
Perl Modules: Chart::Graph::Gnuplot, Test::Parser, Test::Reporter, XML::Simple, XML::Twig
Linux packages: gcc gnuplot sysstat
MySQL 5.0 for stored procedures.

Comments:

I used CentOS 4.4 again as the base linux distro and added the required packages.
use CPAN to install the perl modules as it will handle any dependencies.
Some of the names are different from the README files.
The TPM results for 20 warehouses and 20 concurrent sessions scaled ok based on the number of threads (Terminal Threads). The lower the number of threads the lower the TPM.
For the most part the benchmark was constrained by IO waits. I was using the /mnt mountpoint on EC2, and whilst a test write of a 100M file using dd if=/dev/zero of=/mnt/data/test1 count=1 bs=100M was quick, the random nature of the IO was a killer.
Innodb logfiles where separated from the ibdata file.

Results:

Using an optimized my.cnf based on my-heavy-innodb-4G.cnf file (see listing below)
Data generated 20 warehouses which equated to a 3 Gigabyte database
Benchmark settings of 100 terminal threads, 20 concurrent sessions, 20 warehouses
Duration of 30 minutes.
2016.02 TPM (transactions per minute)

As I have time I will try and replicate the size (200 warehouses) of Peter Zaitsev’s article, however the data generation step took a fair amount of time.

Have Fun

Paul



Get and install the prerequisites

cd /mnt
wget http://optusnet.dl.sourceforge.net/sourceforge/osdldbt/dbt2-0.40.tar.gz
tar -xzvf dbt2-0.40.tar.gz
yum install gnuplot gcc sysstat
cd dbt2-0.40
./configure --with-mysql --with-mysql-libs=/usr/local/mysql/lib/ \
--with-mysql-includes=/usr/local/mysql/includes
make

Generate the 20 warehouse dataset

mkdir -p /mnt/data
src/datagen -w 20 -d /mnt/data --mysql

warehouses = 20
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900

Output directory of data files: /mnt/data

Generating data files for 20 warehouse(s)...
Generating item table data...
Finished item table data...
Generating warehouse table data...
Finished warehouse table data...
Generating stock table data...
Finished stock table data...
Generating district table data...
Finished district table data...
Generating customer table data...
Finished customer table data...
Generating history table data...
Finished history table data...
Generating order and order-line table data...

Finished order and order-line table data...
Generating new-order table data...
Finished new-order table data...

Load the dataset after adding an additional index as suggested by Zaitsev presentation

cd /mnt/dbt2-0.40/scripts/mysql

vi build_db.sh

Modified the CREATE TABLE add index to NEW_ORDER table

NEW_ORDER="CREATE TABLE new_order (
no_o_id int(11) NOT NULL default '0',
no_d_id int(11) NOT NULL default '0',
no_w_id int(11) NOT NULL default '0',
PRIMARY KEY  (no_d_id,no_w_id,no_o_id),
KEY ix_no_wid_did (no_w_id,no_d_id)
)"

Load the dataset

sh build_db.sh -d dbt2 -f /mnt/data -s /tmp/mysql.sock -u root -p $MYSQLPASS

Loading of DBT2 dataset located in /mnt/data to database dbt2.

DB_ENGINE:      INNODB
DB_SCHEME:      OPTIMIZED
DB_HOST:        localhost
DB_USER:        root
DB_SOCKET:      /tmp/mysql.sock

Creating table STOCK
Creating table ITEM
Creating table ORDER_LINE
Creating table ORDERS
Creating table NEW_ORDER
Creating table HISTORY
Creating table CUSTOMER
Creating table DISTRICT
Creating table WAREHOUSE

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
Loading table orders
Loading table stock
Loading table warehouse

Edit the MySQL stored procedures to fix delimiter (replacing |; with |)

cd /mnt/dbt2-0.40/storedproc/mysql
sed -i -e 's/|\;/|/' *.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order_2.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt order_status.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt payment.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt stock_level.sql

Check that MySQL is ready to go

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dbt2
Database changed
mysql> show tables;
+----------------+
| Tables_in_dbt2 |
+----------------+
| customer       |
| district       |
| history        |
| item           |
| new_order      |
| order_line     |
| orders         |
| stock          |
| warehouse      |
+----------------+
9 rows in set (0.00 sec)

mysql> show table status
 -> ;
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| customer   | InnoDB |      10 | Compact    |  603562 |            665 |   401604608 |               0 |     46907392 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| district   | InnoDB |      10 | Compact    |      43 |           1524 |       65536 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| history    | InnoDB |      10 | Compact    |  600452 |             83 |    49889280 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| item       | InnoDB |      10 | Compact    |  100160 |            110 |    11026432 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| new_order  | InnoDB |      10 | Compact    |  152246 |             51 |     7880704 |               0 |      3686400 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| order_line | InnoDB |      10 | Compact    | 5697509 |             95 |   545259520 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| orders     | InnoDB |      10 | Compact    |  600343 |             60 |    36257792 |               0 |     27885568 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| stock      | InnoDB |      10 | Compact    | 2000025 |            382 |   764411904 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
| warehouse  | InnoDB |      10 | Compact    |      20 |            819 |       16384 |               0 |            0 |         0 |           NULL | 2007-08-31 03:18:15 | NULL        | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 229376 kB |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
9 rows in set (0.77 sec)

mysql> SHOW PROCEDURE STATUS
 -> ;
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db   | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| dbt2 | delivery     | PROCEDURE | root@localhost | 2007-08-30 21:21:11 | 2007-08-30 21:21:11 | DEFINER       |         |
| dbt2 | new_order    | PROCEDURE | root@localhost | 2007-08-30 21:22:33 | 2007-08-30 21:22:33 | DEFINER       |         |
| dbt2 | new_order_2  | PROCEDURE | root@localhost | 2007-08-30 21:22:38 | 2007-08-30 21:22:38 | DEFINER       |         |
| dbt2 | order_status | PROCEDURE | root@localhost | 2007-08-30 21:22:44 | 2007-08-30 21:22:44 | DEFINER       |         |
| dbt2 | payment      | PROCEDURE | root@localhost | 2007-08-30 21:22:51 | 2007-08-30 21:22:51 | DEFINER       |         |
| dbt2 | stock_level  | PROCEDURE | root@localhost | 2007-08-30 21:22:56 | 2007-08-30 21:22:56 | DEFINER       |         |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
6 rows in set (0.00 sec)

Listing of my.cnf

grep -v "#" /etc/my.cnf|sed -e '/^$/d'

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type =myISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir =/mnt/mysql/data/
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

Benchmark runs:

sh run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/16 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 400
TERMINALS PER WAREHOUSE: 20
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 300
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 300 seconds

Stage 3. Processing of results...
Killing client...
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 498: 10227 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
run_workload.sh: line 461: 10185 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/16/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/16

                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.53      0.263 :     0.533           71                0   0.00
New Order  45.58      0.028 :     0.076          918               10   1.10
Order Status   3.67      0.071 :     0.173           74                0   0.00
  Payment  41.46      0.035 :     0.102          835                0   0.00
Stock Level   5.76      0.003 :     0.004          116               37  46.84
------------  -----  ---------------------  -----------  ---------------  -----

503.55 new-order transactions per minute (NOTPM)
1.8 minute duration
0 total unknown errors
400 second(s) ramping up

sh run_workload.sh -c 20 -t 10 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/18 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 200
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 13344 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 13382 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/18/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/18

1188549933
8597
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.99      0.117 :     0.227          344                0   0.00
   New Order  45.19      0.041 :     0.086         3900               33   0.85
Order Status   3.90      0.042 :     0.106          337                0   0.00
     Payment  42.79      0.013 :     0.023         3693                0   0.00
 Stock Level   4.13      0.003 :     0.003          356                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

254.19 new-order transactions per minute (NOTPM)
15.1 minute duration
0 total unknown errors
199 second(s) ramping up


sh run_workload.sh -c 20 -t 5 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/19 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 100
TERMINALS PER WAREHOUSE: 5
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14070 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/19/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/19

1188551055
4260
                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.81      0.085 :     0.174          163                0   0.00
New Order  47.02      0.030 :     0.080         2011               17   0.85
Order Status   3.95      0.038 :     0.113          169                0   0.00
  Payment  41.55      0.010 :     0.021         1777                0   0.00
Stock Level   3.67      0.002 :     0.003          157                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

130.21 new-order transactions per minute (NOTPM)
15.2 minute duration
0 total unknown errors
98 second(s) ramping up

Adjust ulimit and filesize

sh run_workload.sh -c 20 -t 200 -d 300 -w 20 -u root -x $MYSQLPASS

error: you're open files ulimit is too small, must be at least 8020

ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) 10000
pending signals                 (-i) 13664
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 13664
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

ulimit -n 9000
ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) 10000
pending signals                 (-i) 13664
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 9000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 13664
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

sh run_workload.sh -c 20 -t 100 -d 3600 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
*              DBT-2 test for mysql started
*                                                                      *
*           Results can be found in output/20 directory
************************************************************************
*                                                                      *
*  Test consists of 3 stages:                                          *
*                                                                      *
*  1. Start of client to create pool of databases connections          *
*  2. Start of driver to emulate terminals and transactions generation *
*  3. Processing of results                                            *
*                                                                      *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 2000
TERMINALS PER WAREHOUSE: 100
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 3600
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 3600 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14572 Terminated              ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 14610 Terminated              ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 554: 18714 File size limit exceeded${abs_top_srcdir}/scripts/post-process --dir ${OUTPUT_DIR} --xml >${DRIVER_OUTPUT_DIR}/results.out
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/20/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/20

Run the post-process step manually

./post-process --dir /mnt/dbt2-0.40/scripts/output/20 --xml

Use of uninitialized value at /usr/lib/perl5/site_perl/5.8.5/Test/Parser/Dbt2.pm line
                      Response Time (s)
Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
 Delivery   3.79      5.578 :     5.167         5309                0   0.00
New Order  43.84      6.023 :     5.492        61347              618   1.02
Order Status   3.74      5.270 :     5.021         5240                0   0.00
  Payment  41.30      5.276 :     5.005        57799                1   0.00
Stock Level   7.33      5.004 :     5.513        10252             4883  90.95
------------  -----  ---------------------  -----------  ---------------  -----

2016.02 new-order transactions per minute (NOTPM)
29.8 minute duration
0 total unknown errors
2015 second(s) ramping up

DBA Dojo

A place on the way of the DBA "Tao DBA" Oracle, MySQL databases and more on EC2.

Month: September 2007

MySQLtoolkit: mysql-table-checksum

Future Posting Roadmap: 2007

MySQL Backups using LVM snapshots

mysqlslap proves Innodb auto increment limits scalability

MySQL DBT2 Benchmark on EC2 part 1