Bonnie IO Benchmark vs EC2

Andy, a reader of the blog left a comment asking if I could run some benchmarking of EC2.

If someone takes the time to comment, making the effort to respond is always worthwhile. Feedback drives most conversation, business and innovation.

So I went off and google’ed the most appropriate and easiest benchmarking tool.

http://www.tux.org/pub/benchmarks/
http://oss.sgi.com/LDP/HOWTO/Benchmarking-HOWTO.html
http://www.coker.com.au/bonnie++/
http://www.acnc.com/benchmarks.html
http://portal.acm.org/citation.cfm?id=71309 IOBench

I settled on bonnie and bonnie++, both may seem a little long in the tooth given when they were developed but they serve the need to test the raw speed of both the root partition and /mnt partition which comes when you run an EC2 virtual machine or Amazon Machine Image (AMI).

If you want to see other web posts on benchmarking EC2, I found a couple of good articles as well.

DeCare Systems has a bunch of articles on EC2 this one has information on using a java benchmarking tool, Javolution
http://blog.decaresystems.ie/index.php/2007/01/29/amazon-web-services-the-future-of-datacenter-computing-part-1/

Other articles on benchmarking on EC2:
http://paul-m-jones.com/blog/?p=238

Comments:

  1. Bonnie required less dependent packages then bonnie++
  2. Both tools were easy to install and run.
  3. Both tools saturated IO and therefore bypassed any issues with caching when the filesize was sufficiently large

I will followup with some more benchmarks and analysis of the results in the next couple of days and then it is back onto MySQL and Oracle.

Installing bonnie on CentOS 4.4

  1. Download bonnie: wget http://www.tux.org/pub/benchmarks/Disk_IO/bonnie.tar.gz or from Google Code
  2. Install GCC: yum install gcc
  3. Compile: gcc -O2 -o bonnie bonnie.c
  4. Run with 100M file: ./bonnie -d /mnt/bonnie -s 100 -m centos4
  5. Run with 1G file: ./bonnie -d /mnt/bonnie -s 1024 -m centos4

Results for Bonnie

100M file:

              -------Sequential Output-------- ---Sequential Input-- --Random--
-Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks---
Machine MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU
centos4 100 25464 53.0 166352 47.1 189359 55.5 25127 53.7 412332 52.3 36552.4 45.7
centos4 100 25038 52.6 216190 61.2 193490 54.8 24317 52.2 418285 49.0 34320.0 51.5
centos4 100 25535 53.4 123481 37.4 188139 57.0 25472 54.5 417667 48.9 72301.4 90.4
centos4 100 25118 52.7 130512 39.5 191710 54.3 25546 53.6 576862 62.0 80402.0 100.5
centos4 100 24205 52.9 183853 53.9 223497 61.1 24852 51.9 400162 54.7 35898.3 35.9

1 Gig file:


-------Sequential Output-------- ---Sequential Input-- --Random--
-Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks---
Machine MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU
centos4 1024 23573 50.0 28648 6.2 19739 4.5 6892 12.1 409836 38.7 38105.0 38.1

Installing bonnie++ on CentOS 4.4

  1. Download: wget http://www.coker.com.au/bonnie++/bonnie++-1.03a.tgz
  2. Install dependencies: yum install compat-gcc-32-c++.i386 gcc-c++.i386 libstdc++.i386
  3. Configure bonnie++: ./configure
  4. Make bonnie++: make
  5. Run on /mnt: ./bonnie++ -d /mnt/oracle -s 3000 -n 1 -m centOS4 -x 3 -r 1500 -u oracle
  6. Run on /: ./bonnie++ -d /home/oracle -s 3000 -n 1 -m centOS4 -x 3 -r 150

Results for Bonnie++


/ mountpoint:

name,file_size,putc,putc_cpu,put_block,put_block_cpu,rewrite,rewrite_cpu,getc,getc_cpu,get_block,get_block_cpu,seeks,seeks_cpu,num_files,seq_create,seq_create_cpu,seq_stat,seq_stat_cpu,seq_del,seq_del_cpu,ran_create,ran_create_cpu,ran_stat,ran_stat_cpu,ran_del,ran_del_cpu
centOS4,3000M,13469,28,59124,15,19772,1,21629,38,51205,1,254.5,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++
centOS4,3000M,17203,36,57555,15,20025,1,22490,40,49618,0,247.9,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++
centOS4,3000M,23918,49,54411,14,19845,1,23120,41,52089,1,246.4,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++

/mnt mountpoint:

name,file_size,putc,putc_cpu,put_block,put_block_cpu,rewrite,rewrite_cpu,getc,getc_cpu,get_block,get_block_cpu,seeks,seeks_cpu,num_files,seq_create,seq_create_cpu,seq_stat,seq_stat_cpu,seq_del,seq_del_cpu,ran_create,ran_create_cpu,ran_stat,ran_stat_cpu,ran_del,ran_del_cpu
centOS4,3000M,23847,48,45251,12,15149,2,19438,38,41982,5,199.8,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++
centOS4,3000M,23189,49,42246,11,16938,4,21007,41,52733,1,183.9,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++
centOS4,3000M,24195,49,44167,11,19923,2,20465,40,47364,1,185.0,0,1,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++

MySQLtoolkit: mysql-table-checksum

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

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:

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

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

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

MySQL Cluster:

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

MySQL Toolkit and related:

  1. One article per mysqltoolkit tool.
  2. Article on using innotop

Misc:

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

PostgreSQL:

  1. performance
  2. clustering options for redundancy

Sharding:

  1. Using MySql
  2. using OracleXE
  3. using PostgreSQL
  4. using Hibernate

Have Fun

Paul

MySQL Backups using LVM snapshots

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

  1. Use mysqldump with FLUSH TABLES WITH READ LOCK
  2. Use a slave with STOP SLAVE and your favourite backup tool.
  3. For innodb, use the commerical backup tool ibbackup
  4. Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
  5. 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.

  1. mysql connection (left open): FLUSH TABLES WITH READ LOCK;
  2. lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
  3. 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


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

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:

  1. Perl 5.8
  2. Perl Modules: Chart::Graph::Gnuplot, Test::Parser, Test::Reporter, XML::Simple, XML::Twig
  3. Linux packages: gcc gnuplot sysstat
  4. 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

MySQL Replication vs mysqlslap on EC2

Getting familiar with any product means either jumping in the deep end and hoping the weight of new knowledge is not enough to sink you or taking incremental steps walking from the shallow end towards the deep end (of knowledge in depth).

So as I proceed towards a robust test of MySQL Cluster on EC2, I thought it would be useful to see how MySQL holds up against a mysqlslap load simulation as a standard alone database and in a MySQL replication Master->Slave setup.
You can see my results for the standard databases in this post.

So the next step was to setup a standard master-slave replication. I have done this before, but it is always useful to keep the documentation handy. You will find the outline and how to setup MySQL replication from MySQL’s website.

Setting up MySQL replication on EC2 was as simple as firing up two instances of the same AMI with MySQL 5.1 installed and making the necessary changes to the master and slave my.cnf files.
Whilst you could have foregone the process of rebuilding the slave database from a master backup, it is always good practice to run though the steps. You could foregone this step as the database is actually the same on both instances as it is the same AMI.

Once the replication was configured and running I run the same tests as I ran the other day using mysqlslap.

Comments:

  • The slave database never had any trouble even when the master was struggling until I increased the size of the auto-generated table significantly which suggests that the network bandwidth became the bottleneck rather than the IO or CPU bandwidth.
  • MySQL started with the medium my.cnf sample and modified max_connections couldn’t handle more than 750 concurrent sessions without mysql complaining on a lack of resources and causing mysqlslap to die.
  • At 500 concurrent sessions, the MySQL EC2 node was tapped out. Load spiked to 180 at one point.

As I mentioned this was the first step and using a simulated auto-generated workload. I am reviewing the new sample database called Sakila, provided by MySQL.

As I have time I am planning on building this series into a full blown performance test of MySQL on EC2. Essentially I am hoping that this work will allow people to understand exactly how MySQL scales on EC2 so they can resource their expected workload with confidence or alternatively know how to use mysqlslap to produce their own performance and scaling benchmarks.

Next up will be another run of MySQL 5.1 NDB cluster vs mysqlslap.

Have Fun

Paul

Previous Articles:
http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html

Here is the screen dump of my session



cat /etc/hosts

127.0.0.1 localhost
# Mysql replication
10.255.71.159 mysql-master
10.255.83.177 mysql-slave

Much sure my.cnf is set appropriately and restart MySQL:

service mysql.server restart
Shutting down MySQL.. [ OK ]
Starting MySQL [ OK ]

Setup MySQL replication:

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

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

Create the replication user and grant replication privileges:

mysql> create user myslave IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

Lock the tables during the backup to get a consistent backup:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

After the backup is complete unlock the tables again:

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

Grab the master status info

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 488
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Backup the master database using mysqldump

mysqldump --all-databases --lock-all-tables -u root -p >dbdump.db
Enter password:

Copy the files to slave:

scp dbdump.db mysql-slave:/mnt
dbdump.db 100% 392KB 391.8KB/s 00:00

Set the Master setting on the slave:

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_PORT=3306,
MASTER_USER='myslave',
MASTER_PASSWORD='53cr37',
MASTER_LOG_POS=488;

mysql>
mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37',
-> MASTER_LOG_POS=488;
Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 488
Relay_Log_File: domU-12-31-35-00-52-43-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 488
Relay_Log_Space: 423
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Running mysqlslap load simulation tests:

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 \
--user=root --password

cat /tmp/mysqlslap.csv

blackhole,mixed,0.024,0.012,0.072,1,100
blackhole,mixed,0.039,0.020,0.083,25,4
blackhole,mixed,0.072,0.030,0.091,50,2
blackhole,mixed,0.094,0.051,0.115,100,1
myisam,mixed,0.026,0.012,0.075,1,100
myisam,mixed,0.057,0.026,0.091,25,4
myisam,mixed,0.061,0.033,0.124,50,2
myisam,mixed,0.109,0.052,0.119,100,1


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --password

cat /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.192,0.165,0.231,1,1000
blackhole,mixed,0.227,0.179,0.295,25,40
blackhole,mixed,0.253,0.188,0.312,50,20
blackhole,mixed,0.283,0.203,0.345,100,10
myisam,mixed,0.255,0.194,0.318,1,1000
myisam,mixed,0.282,0.205,0.354,25,40
myisam,mixed,0.302,0.275,0.362,50,20
myisam,mixed,0.357,0.306,0.403,100,10


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_q10000.csv

blackhole,mixed,2.145,2.051,2.244,1,10000
blackhole,mixed,2.210,2.107,2.275,25,400
blackhole,mixed,2.128,1.953,2.240,50,200
blackhole,mixed,2.420,2.085,3.031,100,100
myisam,mixed,2.651,2.618,2.751,1,10000
myisam,mixed,2.710,2.637,2.821,25,400
myisam,mixed,2.698,2.637,2.788,50,200
myisam,mixed,2.901,2.599,3.741,100,100

mysqlslap --concurrency=1,25,50,100,150 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql --csv=/tmp/mysqlslap_bigtab_q10000.csv \
--engine=blackhole,myisam --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_bigtab_q10000.csv

blackhole,mixed,2.958,2.877,3.054,1,10000
blackhole,mixed,2.993,2.946,3.219,25,400
blackhole,mixed,2.982,2.897,3.088,50,200
blackhole,mixed,3.247,2.902,3.788,100,100
blackhole,mixed,3.262,2.966,4.075,150,66
myisam,mixed,3.540,3.442,3.628,1,10000
myisam,mixed,3.577,3.516,3.652,25,400
myisam,mixed,3.608,3.537,3.661,50,200
myisam,mixed,3.647,3.508,3.720,100,100
myisam,mixed,4.298,3.608,5.188,150,66

Setting the max_connections setting higher so I can test more than 150
concurrent sessions:


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

mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
--auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password

Sample of the top from the master with mysqlslap running:

top - 08:08:23 up 1:10, 2 users, load average: 161.43, 59.07, 26.07
Tasks: 2056 total, 100 running, 1949 sleeping, 0 stopped, 7 zombie
Cpu(s): 62.8% us, 34.6% sy, 0.0% ni, 0.0% id, 1.6% wa, 0.0% hi, 1.0% si
Mem: 1740944k total, 1548008k used, 192936k free, 20684k buffers
Swap: 917496k total, 0k used, 917496k free, 1323268k cached

mysqlslap --concurrency=1,25,50,100,250,500,1000 --iterations=10 --number-int-cols=10 --number-char-cols=5 \
> --auto-generate-sql --csv=/tmp/mysqlslap_1000c_bigtab_q10000.csv --engine=blackhole,myisam \
> --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
> --number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,1943137703,190133822,450874686,1121980228,'YhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQW9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAju','ZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQLKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8s','gGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0X','EufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E873FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52Zb','YGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQzZc0l4Qi9fT8HrX4S4CoilYbACNSi0mhPMZARzlcWEcg6zkQ3o6eafDcrS4QSq7u5Z4YYh0vwSbuRB') ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)

tail -f /tmp/mysqlslap_1000c_bigtab_q10000.csv

blackhole,mixed,2.717,2.641,2.830,1,10000
blackhole,mixed,2.730,2.644,2.839,25,400
blackhole,mixed,2.668,2.482,2.747,50,200
blackhole,mixed,2.955,2.723,3.481,100,100
blackhole,mixed,2.969,2.853,3.288,250,40
blackhole,mixed,3.849,3.061,4.874,500,20
blackhole,mixed,5.514,3.760,8.081,1000,10
myisam,mixed,3.099,2.937,3.160,1,10000
myisam,mixed,3.142,3.055,3.263,25,400
myisam,mixed,3.217,3.141,3.268,50,200
myisam,mixed,3.217,3.110,3.319,100,100
myisam,mixed,4.256,3.319,5.561,250,40
myisam,mixed,4.306,3.781,7.740,500,20

Checking the table is ok after that failure

[root@domU-12-31-35-00-40-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49254
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 |
| mysqlslap |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysqlslap
Database changed
mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| t1 |
+---------------------+
1 row in set (0.00 sec)

mysql> check table t1 quick;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| mysqlslap.t1 | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.05 sec)


mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1624178779,294458676,629436982,550287458,1828995128,517409934,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)

cat /tmp/mysqlslap_750c_bigtab_q10000.csv

blackhole,mixed,3.682,3.053,4.811,500,20
blackhole,mixed,6.265,3.496,10.161,750,13
myisam,mixed,5.009,3.662,7.803,500,20

mysqlslap --concurrency=500,750 --iterations=10 --number-int-cols=10 \
--number-char-cols=5 --auto-generate-sql \
--csv=/tmp/mysqlslap_write_750c_bigtab_q10000.csv --engine=blackhole,myisam \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=10000 --user=root --password
Enter password:

mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1413946291,2008175571,806084542,1096535128,1877357308,2039695798,
1522423739,1247119446,677182057,1658524254,
'4uoCZg37QkwQxLB0Jl7CkWw6qGxpFQbJLFYmvRlDBig13jtESsJCEFA4Nz...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1338459946,2107889310,393787969,1623810840,1403227682,...
ERROR : Out of resources when opening file './mysqlslap/t1.MYD' (Errcode: 24)
Killed

tail -f /tmp/mysqlslap_write_750c_bigtab_q10000.csv

blackhole,write,3.736,2.935,5.308,500,20
blackhole,write,5.025,3.172,7.826,750,13
myisam,write,4.769,3.630,6.532,500,20

MySQL 5.1 NDB Cluster Replication on EC2

As I mentioned previously once the MySQL Cluster was setup, the next thing to test was setting up a MySQL NDB Cluster Replication i.e. Master Cluster and Slave Cluster.

You would use this type of setup in situations where you want availability and redundancy.
So even if your multiple (n+1) management nodes and data nodes and SQL/API nodes die on one site, you can redirect traffic or load to a replicated site.

Essentially the (row based) replication is done via a pair of NDB SQL nodes, with the master and slave configuration similar to the plain vanilla variety of MySQL replication. I would suggest getting that working first before attempting this, just so you gain some familiarity with how MySQL replication works.

The main differences from normal replication was the creation of the slave NDB Cluster from the clone of the Master and the way to check where the NDB apply is up to, so you can set the slave correctly.
The steps to do this setup start in the MySQL documentation.

I used two pairs of multi-purpose nodes (Management, Data and MySQL combined) to setup the MySQL 5.1 NDB Clusters replication. So it was a 2 data node Master cluster and a 2 data node Slave cluster.
Here are my high level steps:

  1. Configure /etc/hosts. Added mysql-ndb-master and mysql-ndb-slave IP addresses
  2. Configure the configure.ini to reflect the number of data nodes and MySQL nodes required.
  3. On either management node issue the START BACKUP command to create a backup.
  4. Copy all files from all NDB data nodes to one of the Slave management nodes.
  5. Restore the NDB database in order by data node_id.
  6. Configure the Master /etc/my.cnf and restart the Master MySQL node.
  7. Configure the Slave /etc/my.cnf and restart the Slave MySQL node.
  8. Verify that SHOW MASTER STATUS and SHOW SLAVE STATUS are correct.

Once that was done the Cluster replication was up and running. I then proceeded to run mysqlslap on the master SQL node to quickly test the replication and it was fine under light load. (more on that later).

As per normal I have dumped my work out so that you can see the same stuff I did whilst I was setting up.

Have Fun

Paul

Previous articles in the series:
MySQL 5.1 NDB Cluster – Part 1.
MySQL 5.1 NDB Cluster – Part 2.
MySQL 5.1 NDB Cluster – Testing redundancy.
MySQL 5.1 NDB Cluster – sample configuration files.



ndb_mgm> show
Connected to Management Server at: mysql-ndb_mgmd1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @10.255.39.130 (Version: 5.1.20, Nodegroup: 0, Master)
id=2 @10.255.7.34 (Version: 5.1.20, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=3 @10.255.39.130 (Version: 5.1.20)
id=4 @10.255.7.34 (Version: 5.1.20)

[mysqld(API)] 2 node(s)
id=5 @10.255.7.34 (Version: 5.1.20)
id=6 @10.255.39.130 (Version: 5.1.20)

Original 2 data node configure.ini file

[root@domU-12-31-35-00-20-74 ~]# cat /usr/local/mysql-cluster/EC2-configure-2node.ini
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 64
MaxNoOfConcurrentOperations= 128
DataMemory= 256M
IndexMemory= 256M
Diskless= false
TimeBetweenWatchDogCheck= 30000
DataDir= /mnt/mysql-cluster/data
MaxNoOfOrderedIndexes= 500
MaxNoOfAttributes= 1000
TimeBetweenGlobalCheckpoints= 500
NoOfFragmentLogFiles= 4
FragmentLogFileSize=12M
DiskPageBufferMemory= 64M
ODirect= 1
# the following parametes just function as a small regression
# test that the parameter exists
InitialNoOfOpenFiles= 27

#
# Increase timeouts to cater for slow test-machines
# (possibly running several tests in parallell)
#
HeartbeatIntervalDbDb= 30000
HeartbeatIntervalDbApi= 30000
#TransactionDeadlockDetectionTimeout= 7500

[ndbd]
HostName= mysql-ndbd1 # hostname is a valid network adress

[ndbd]
HostName= mysql-ndbd2 # hostname is a valid network adress

[ndb_mgmd]
HostName= mysql-ndb_mgmd1 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
[ndb_mgmd]
HostName= mysql-ndb_mgmd2 # hostname is a valid network adress
DataDir= /mnt/mysql-cluster/mgmd-data #
#PortNumber= CHOOSE_PORT_MGM

[mysqld]

[mysqld]

Create REPLICATION user on master MySQL node

create user myslave IDENTIFIED BY '53cr37';

GRANT REPLICATION SLAVE
ON *.* TO 'myslave'@'mysql-ndb-slave'
IDENTIFIED BY '53cr37';

mysql> SET GLOBAL binlog_format = 'ROW';
Query OK, 0 rows affected (0.01 sec)


mysql> GRANT REPLICATION SLAVE
-> ON *.* TO 'myslave'@'mysql-ndb-slave'
-> IDENTIFIED BY '53cr37';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Hosts file on each Master and slave node

cat /etc/hosts

127.0.0.1 localhost
# Mysql Cluster data node
10.255.46.240 mysql-ndbd1
10.255.19.225 mysql-ndbd2
# Mysql Cluster mgm node
10.255.46.240 mysql-ndb_mgmd1
10.255.19.225 mysql-ndb_mgmd2
# Mysql replication
10.255.46.240 mysql-ndb-master
10.255.11.159 mysql-ndb-slave

Master MYSQL node my.cnf file

[root@domU-12-31-35-00-29-02 BACKUP]# cat /etc/my.cnf
# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
log-bin=row
server-id=1

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Slave /etc/my.cnf file

cat /etc/my.cnf

# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB storage engine
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server
master-host='mysql-ndb-master'
master-port=3306
master-user='myslave'
master-password='53cr37'
server_id=2

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=mysql-ndb_mgmd1,mysql-ndb_mgmd2 # location of management server


Checking slave status

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

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

mysql> show slave status\G
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-ndb-master',
-> MASTER_PORT=3306,
-> MASTER_USER='myslave',
-> MASTER_PASSWORD='53cr37';
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: domU-12-31-35-00-0E-51-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Run backup on master cluster using START BACKUP from ndb_mgm command line

ndb_mgm> start backup
Connected to Management Server at: mysql-ndb_mgmd1:1186
Waiting for completed, this may take several minutes
Node 1: Backup 1 started from node 3
Node 1: Backup 1 started from node 3 completed
StartGCP: 6681 StopGCP: 6684
#Records: 2053 #LogRecords: 0
Data: 33888 bytes Log: 0 bytes

Copy backup to slave from all data nodes

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.1.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.1.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.1.ctl 100% 6832 6.7KB/s 00:00

scp -r BACKUP-1/* mysql-ndb-slave:/mnt/mysql-cluster/data/BACKUP
BACKUP-1.2.log 100% 44 0.0KB/s 00:00
BACKUP-1-0.2.Data 100% 160KB 160.0KB/s 00:00
BACKUP-1.2.ctl 100% 6832 6.7KB/s 00:00

Checking databases are all on SLAVE, otherwise use CREATE SCHEMA

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.20-beta MySQL Community Server (GPL)

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

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

Slave

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta MySQL Community Server (GPL)

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

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

Restore the Backup onto the slave

ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/


ndb_restore -c mysql-ndb-slave:1186 -n 1 -b 1 -m -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 1
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

ndb_restore -c mysql-ndb-slave:1186 -n 2 -b 1 -e -r /mnt/mysql-cluster/data/BACKUP/
Nodeid = 2
Backup Id = 1
backup path = /mnt/mysql-cluster/data/BACKUP/
Ndb version in backup files: Version 5.1.20
Connected to ndb!!
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 1
Restored 0 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

Check apply status on slave

SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;


SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE epoch > 1110
ORDER BY epoch ASC LIMIT 1;



mysql> SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;
+---------------------+
| @latest:=MAX(epoch) |
+---------------------+
| 1110 |
+---------------------+
1 row in set (0.09 sec)

mysql> SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
-> @pos:=Position
-> FROM mysql.ndb_binlog_index
-> WHERE epoch > 1110
-> ORDER BY epoch ASC LIMIT 1;
Empty set (0.00 sec)

Restart MASTER and SLAVE MySQL nodes to make sure /etc/my.cnf is ok

service mysql.server restart


On master:

mysql> show master status\G
*************************** 1. row ***************************
File: row.000001
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On Slave:

[root@domU-12-31-35-00-04-51 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta MySQL Community Server (GPL)

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

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-ndb-master
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: row.000001
Read_Master_Log_Pos: 106
Relay_Log_File: domU-12-31-35-00-04-51-relay-bin.000003
Relay_Log_Pos: 245
Relay_Master_Log_File: row.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 417
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Running mysqlslap to test

mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password


mysqlslap --concurrency=1,25 --iterations=10 --number-int-cols=10 --number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --number-of-queries=1 --user=root --password
Enter password:
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '88' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '36' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '38' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '73' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4,intcol5,intcol6,intcol7,intcol8,intcol9,intcol10,charcol1,charcol2,charcol3 FROM t1 WHERE id = '55' ERROR : Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDBCLUSTER
Killed

cat /tmp/mysqlslap.csv
ndb,mixed,0.129,0.102,0.165,1,100
ndb,mixed,0.035,0.011,0.067,1,10
ndb,mixed,0.004,0.001,0.031,1,1

Concurrency settings were too low, setting higher

[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentTransactions= 2048
MaxNoOfConcurrentOperations= 4096

Restart the cluster.

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=10 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv --engine=ndb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10 --user=root --password


[root@domU-12-31-35-00-29-02 BACKUP]# cat /tmp/mysqlslap.csv
ndb,mixed,0.053,0.012,0.075,1,10
ndb,mixed,1.202,1.040,1.321,25,0
ndb,mixed,2.482,2.386,2.596,50,0
ndb,mixed,10.239,9.398,11.006,100,0

MySQL vs MySQLSlap

In preparation for some more intense testing on the MySQL 5.1 Cluster, I started up a plain vanilla MySQL 5.1 database with MyISAM and INNODB enabled.

I am going to test out various configurations of the cluster ie. 2 data node, 4 data node, multiple SQL/API nodes using a new MySQL 5.1 load emulation tool called mysqlslap.

I read some decent entries on the net about other people who have used it as well, here and here.
You can capture your own sql either dumping the sql from your app, using tcpdump to capture the sql on the wire, or setting the general query log (requires restart) to capture all queries or patching mysql so the slow query log can go below the 1 sec threshold.

Anyway on with the show. This was not really a tuning exercise, I just used the sample medium my.cnf located in /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-medium.cnf, which frankly is probably too small.

Here are results for the plain standalone MySQL database on EC2. I have included the full help of mysqlslap as the MySQL documentation is missing detail about the auto-generate-sql options.

Have Fun

Paul



mysqlslap --help
mysqlslap Ver 0.9 Distrib 5.1.20-beta, for pc-linux-gnu (i686)
Copyright (C) 2005 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Run a query multiple times against the server

Usage: mysqlslap [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf
The following groups are read: mysqlslap client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-?, --help Display this help and exit.
-a, --auto-generate-sql
Generate SQL where not supplied by file or command line.
--auto-generate-sql-add-autoincrement
Add autoincrement to auto-generated tables.
--auto-generate-sql-execute-number=#
Set this number to generate a set number of queries to
run.

--auto-generate-sql-guid-primary
Add GUID based primary keys to auto-generated tables.
--auto-generate-sql-load-type=name
Load types are mixed, update, write, key, or read.
Default is mixed

--auto-generate-sql-secondary-indexes=#
Number of secondary indexes to add auto-generated tables.
--auto-generate-sql-unique-query-number=#
Number of unique queries auto tests
--auto-generate-sql-unique-write-number=#
Number of unique queries for
auto-generate-sql-write-number
--auto-generate-sql-write-number=#
Number of rows to insert to used in read and write loads
(default is 100).

-C, --compress Use compression in server/client protocol.
-c, --concurrency=name
Number of clients to simulate for query to run.
--create=name File or string to use create tables.
--create-schema=name
Schema to run tests in.
--csv[=name] Generate CSV output to named file or to stdout if no file
is named.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'.
-F, --delimiter=name
Delimiter to use in SQL statements supplied in file or
command line.
-e, --engine=name Storage engine to use for creating the table.
-h, --host=name Connect to host.
-i, --iterations=# Number of times to run the tests.
-x, --number-char-cols=name
Number of VARCHAR columns to create table with if
specifying --auto-generate-sql
-y, --number-int-cols=name
Number of INT columns to create table with if specifying
--auto-generate-sql.
--number-of-queries=#
Limit each client to this number of queries (this is not
exact).
--only-print This causes mysqlslap to not connect to the databases,
but instead print out what it would have done instead.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection.
--post-query=name Query to run or file containing query to run after
executing.
--pre-query=name Query to run or file containing query to run before
executing.
--preserve-schema Preserve the schema from the mysqlslap run, this happens
unless --auto-generate-sql or --create are used.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --query=name Query to run or file containing query to run.
-s, --silent Run program in silent mode - no output.
-S, --socket=name Socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-u, --user=name User for login if not current user.
-v, --verbose More verbose output; You can use this multiple times to
get even more verbose output.
-V, --version Output version information and exit.

Running tests, progressively increasing the number of queries

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100 --user=root \
--password

cat mysqlslap.csv

blackhole,mixed,0.021,0.009,0.069,1,100
blackhole,mixed,0.030,0.017,0.080,25,4
blackhole,mixed,0.044,0.025,0.087,50,2
blackhole,mixed,0.080,0.043,0.108,100,1
myisam,mixed,0.017,0.011,0.072,1,100
myisam,mixed,0.053,0.022,0.087,25,4
myisam,mixed,0.062,0.031,0.096,50,2
myisam,mixed,0.101,0.046,0.117,100,1
innodb,mixed,0.023,0.022,0.029,1,100
innodb,mixed,0.083,0.077,0.090,25,4
innodb,mixed,0.098,0.078,0.158,50,2
innodb,mixed,0.229,0.167,0.265,100,1


mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root \
--password

cat /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.161,0.089,0.209,1,1000
blackhole,mixed,0.167,0.154,0.215,25,40
blackhole,mixed,0.193,0.163,0.233,50,20
blackhole,mixed,0.249,0.183,0.284,100,10
myisam,mixed,0.215,0.172,0.237,1,1000
myisam,mixed,0.234,0.185,0.252,25,40
myisam,mixed,0.280,0.250,0.339,50,20
myisam,mixed,0.300,0.270,0.359,100,10
innodb,mixed,0.350,0.236,0.674,1,1000
innodb,mixed,0.425,0.375,0.655,25,40
innodb,mixed,0.424,0.355,0.499,50,20
innodb,mixed,0.640,0.587,0.668,100,10

mysqlslap --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q10000.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=10000 --user=root \
--password

cat /tmp/mysqlslap_q10000.csv

blackhole,mixed,1.678,1.591,1.777,1,10000
blackhole,mixed,1.673,1.608,1.737,25,400
blackhole,mixed,1.739,1.675,1.899,50,200
blackhole,mixed,1.971,1.707,2.424,100,100
myisam,mixed,2.145,2.098,2.232,1,10000
myisam,mixed,2.185,2.056,2.464,25,400
myisam,mixed,2.179,2.060,2.319,50,200
myisam,mixed,2.370,2.151,3.008,100,100
innodb,mixed,3.541,3.077,3.989,1,10000
innodb,mixed,4.005,3.513,4.793,25,400
innodb,mixed,4.329,4.080,5.172,50,200
innodb,mixed,6.438,6.212,6.614,100,100


Oracle 11G on EC2 using silent install

The buzz around the Oracle community was the upcoming release of the new Oracle database.
No code names required, everyone knew it would be 11 something, Alex Gorbachev even ran a poll on the new letter.

Oracle decided to continue down the Grid path, hence the new version was Oracle 11g.

The software is officially released to more than privileged beta testers. You can get it here.

So given I had a spare moment before I start on the MySQL 5.1 Cluster replication build. I decided to download the software and give it a whirl.
As I have mentioned in the past, once you get a hang for using response files you will never go back to the slow java based GUI installer, especially over slow links.

Make sure you read the Linux install guide, the main thing is to get the software packages, users and kernel parameter setup.

I have attached the following process I used to install Oracle 11g on EC2.

Have Fun

Paul



Get the software, accept the license on the website and then copy your
cookie.txt file to the EC2 instance.



wget --load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
--07:32:09-- http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database.zip
=> `linux_11gR1_database.zip'
Resolving download.oracle.com... 208.111.133.54, 208.111.133.53, 208.111.133.52
Connecting to download.oracle.com|208.111.133.54|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1,844,533,232 (1.7G) [application/zip]

100%[====================================================================================>] 1,844,533,232 8.03M/s ETA 00:00

07:36:05 (7.47 MB/s) - `linux_11gR1_database.zip' saved [1844533232/1844533232]


Nice option provided by Alex Gorbachev

cat pkgs

binutils
compat-libstdc++
elfutils-libelf
elfutils-libelf-devel
glibc
glibc-common
glibc-devel
gcc
gcc-c++
libaio-devel
libaio
libgcc
libstdc++
libstdc++-devel
make
sysstat
unixODBC
unixODBC-devel

Use RPM to determine what packages are installed and versions

for pkg in `cat pkgs` ; do rpm -q $pkg --queryformat '%{NAME} %{VERSION} %{ARCH}\n' ; done

binutils 2.15.92.0.2 i386
package compat-libstdc++ is not installed
elfutils-libelf 0.97.1 i386
package elfutils-libelf-devel is not installed
glibc 2.3.4 i686
glibc-common 2.3.4 i386
glibc-devel 2.3.4 i386
gcc 3.4.6 i386
package gcc-c++ is not installed
package libaio-devel is not installed
libaio 0.3.105 i386
libgcc 3.4.6 i386
libstdc++ 3.4.6 i386
package libstdc++-devel is not installed
make 3.80 i386
package sysstat is not installed
package unixODBC is not installed
package unixODBC-devel is not installed

Create a shell script to install packages


cat install_pkgs


yum install binutils \
compat-libstdc++ \
elfutils-libelf \
elfutils-libelf-devel \
glibc \
glibc-common \
glibc-devel \
gcc \
gcc-c++ \
libaio-devel \
libaio \
libgcc \
libstdc++ \
libstdc++-devel \
make \
sysstat \
unixODBC \
unixODBC-devel

./install_pkgs

Setting up Install Process
Setting up repositories
Reading repository metadata in from local files
Parsing package install arguments
No Match for argument: compat-libstdc++
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc to pack into transaction set.
glibc-2.3.4-2.36.i686.rpm 100% |=========================| 126 kB 00:00
---> Package glibc.i686 0:2.3.4-2.36 set to be updated
---> Downloading header for gcc-c++ to pack into transaction set.
gcc-c%2B%2B-3.4.6-8.i386. 100% |=========================| 36 kB 00:00
---> Package gcc-c++.i386 0:3.4.6-8 set to be updated
---> Downloading header for binutils to pack into transaction set.
binutils-2.15.92.0.2-22.i 100% |=========================| 41 kB 00:00
---> Package binutils.i386 0:2.15.92.0.2-22 set to be updated
---> Package libaio-devel.i386 0:0.3.105-2 set to be updated
---> Package unixODBC-devel.i386 0:2.2.11-1.RHEL4.1 set to be updated
---> Downloading header for gcc to pack into transaction set.
gcc-3.4.6-8.i386.rpm 100% |=========================| 43 kB 00:00
---> Package gcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for glibc-devel to pack into transaction set.
glibc-devel-2.3.4-2.36.i3 100% |=========================| 94 kB 00:00
---> Package glibc-devel.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for glibc-common to pack into transaction set.
glibc-common-2.3.4-2.36.i 100% |=========================| 646 kB 00:01
---> Package glibc-common.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for libstdc++ to pack into transaction set.
libstdc%2B%2B-3.4.6-8.i38 100% |=========================| 34 kB 00:00
---> Package libstdc++.i386 0:3.4.6-8 set to be updated
---> Package sysstat.i386 0:5.0.5-15.0.1.el4 set to be updated
---> Downloading header for elfutils-libelf to pack into transaction set.
elfutils-libelf-0.97.1-4. 100% |=========================| 7.4 kB 00:00
---> Package elfutils-libelf.i386 0:0.97.1-4 set to be updated
---> Downloading header for libgcc to pack into transaction set.
libgcc-3.4.6-8.i386.rpm 100% |=========================| 34 kB 00:00
---> Package libgcc.i386 0:3.4.6-8 set to be updated
---> Downloading header for libstdc++-devel to pack into transaction set.
libstdc%2B%2B-devel-3.4.6 100% |=========================| 67 kB 00:00
---> Package libstdc++-devel.i386 0:3.4.6-8 set to be updated
---> Downloading header for elfutils-libelf-devel to pack into transaction set.
elfutils-libelf-devel-0.9 100% |=========================| 7.4 kB 00:00
---> Package elfutils-libelf-devel.i386 0:0.97.1-4 set to be updated
---> Package unixODBC.i386 0:2.2.11-1.RHEL4.1 set to be updated
--> Running transaction check
--> Processing Dependency: glibc-headers = 2.3.4-2.36 for package: glibc-devel
--> Processing Dependency: elfutils-libelf = 0.97.1-3 for package: elfutils
--> Processing Dependency: glibc = 2.3.4-2.25 for package: glibc-headers
--> Processing Dependency: cpp = 3.4.6-8 for package: gcc
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for glibc-headers to pack into transaction set.
glibc-headers-2.3.4-2.36. 100% |=========================| 127 kB 00:00
---> Package glibc-headers.i386 0:2.3.4-2.36 set to be updated
---> Downloading header for elfutils to pack into transaction set.
elfutils-0.97.1-4.i386.rp 100% |=========================| 12 kB 00:00
---> Package elfutils.i386 0:0.97.1-4 set to be updated
---> Downloading header for cpp to pack into transaction set.
cpp-3.4.6-8.i386.rpm 100% |=========================| 36 kB 00:00
---> Package cpp.i386 0:3.4.6-8 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
elfutils-libelf-devel i386 0.97.1-4 base 52 k
gcc-c++ i386 3.4.6-8 base 2.4 M
libaio-devel i386 0.3.105-2 base 9.9 k
libstdc++-devel i386 3.4.6-8 base 8.6 M
sysstat i386 5.0.5-15.0.1.el4 update 104 k
unixODBC i386 2.2.11-1.RHEL4.1 base 829 k
unixODBC-devel i386 2.2.11-1.RHEL4.1 base 772 k
Updating:
binutils i386 2.15.92.0.2-22 base 2.8 M
elfutils-libelf i386 0.97.1-4 base 38 k
gcc i386 3.4.6-8 base 4.4 M
glibc i686 2.3.4-2.36 base 5.9 M
glibc-common i386 2.3.4-2.36 base 16 M
glibc-devel i386 2.3.4-2.36 base 1.9 M
libgcc i386 3.4.6-8 base 63 k
libstdc++ i386 3.4.6-8 base 282 k
Updating for dependencies:
cpp i386 3.4.6-8 base 1.6 M
elfutils i386 0.97.1-4 base 136 k
glibc-headers i386 2.3.4-2.36 base 585 k

Transaction Summary
=============================================================================
Install 7 Package(s)
Update 11 Package(s)
Remove 0 Package(s)
Total download size: 46 M
Is this ok [y/N]: y
Downloading Packages:
(1/18): glibc-2.3.4-2.36. 100% |=========================| 5.9 MB 00:16
(2/18): gcc-c++-3.4.6-8.i 100% |=========================| 2.4 MB 00:06
(3/18): binutils-2.15.92. 100% |=========================| 2.8 MB 00:07
(4/18): libaio-devel-0.3. 100% |=========================| 9.9 kB 00:00
(5/18): unixODBC-devel-2. 100% |=========================| 772 kB 00:02
(6/18): gcc-3.4.6-8.i386. 100% |=========================| 4.4 MB 00:12
(7/18): glibc-devel-2.3.4 100% |=========================| 1.9 MB 00:05
(8/18): glibc-headers-2.3 100% |=========================| 585 kB 00:01
(9/18): elfutils-0.97.1-4 100% |=========================| 136 kB 00:00
(10/18): glibc-common-2.3 100% |=========================| 16 MB 00:44
(11/18): libstdc++-3.4.6- 100% |=========================| 282 kB 00:00
(12/18): sysstat-5.0.5-15 100% |=========================| 104 kB 00:00
(13/18): elfutils-libelf- 100% |=========================| 38 kB 00:00
(14/18): libgcc-3.4.6-8.i 100% |=========================| 63 kB 00:00
(15/18): libstdc++-devel- 100% |=========================| 8.6 MB 00:23
(16/18): elfutils-libelf- 100% |=========================| 52 kB 00:00
(17/18): unixODBC-2.2.11- 100% |=========================| 829 kB 00:02
(18/18): cpp-3.4.6-8.i386 100% |=========================| 1.6 MB 00:04
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : libgcc ####################### [ 1/29]
Updating : glibc-common ####################### [ 2/29]
Updating : glibc ####################### [ 3/29]
Stopping sshd:[ OK ]
Starting sshd:[ OK ]
Updating : binutils ####################### [ 4/29]
Updating : elfutils-libelf ####################### [ 5/29]
Updating : glibc-headers ####################### [ 6/29]
Updating : glibc-devel ####################### [ 7/29]
Updating : libstdc++ ####################### [ 8/29]
Installing: libstdc++-devel ####################### [ 9/29]
Updating : elfutils ####################### [10/29]
Installing: unixODBC ####################### [11/29]
Updating : cpp ####################### [12/29]
Updating : gcc ####################### [13/29]
Installing: gcc-c++ ####################### [14/29]
Installing: libaio-devel ####################### [15/29]
Installing: unixODBC-devel ####################### [16/29]
Installing: sysstat ####################### [17/29]
Installing: elfutils-libelf-devel ####################### [18/29]
Cleanup : glibc ####################### [19/29]
Cleanup : binutils ####################### [20/29]
Cleanup : gcc ####################### [21/29]
Cleanup : glibc-devel ####################### [22/29]
Cleanup : glibc-headers ####################### [23/29]
Cleanup : elfutils ####################### [24/29]
Cleanup : glibc-common ####################### [25/29]
Cleanup : libstdc++ ####################### [26/29]
Cleanup : elfutils-libelf ####################### [27/29]
Cleanup : libgcc ####################### [28/29]
Cleanup : cpp ####################### [29/29]

Installed: elfutils-libelf-devel.i386 0:0.97.1-4 gcc-c++.i386 0:3.4.6-8 libaio-devel.i386 0:0.3.105-2 libstdc++-devel.i386 0:3.4.6-8 sysstat.i386 0:5.0.5-15.0.1.el4 unixODBC.i386 0:2.2.11-1.RHEL4.1 unixODBC-devel.i386 0:2.2.11-1.RHEL4.1
Updated: binutils.i386 0:2.15.92.0.2-22 elfutils-libelf.i386 0:0.97.1-4 gcc.i386 0:3.4.6-8 glibc.i686 0:2.3.4-2.36 glibc-common.i386 0:2.3.4-2.36 glibc-devel.i386 0:2.3.4-2.36 libgcc.i386 0:3.4.6-8 libstdc++.i386 0:3.4.6-8
Dependency Updated: cpp.i386 0:3.4.6-8 elfutils.i386 0:0.97.1-4 glibc-headers.i386 0:2.3.4-2.36
Complete!

Checking compat-libstdc++ issue


yum search compat-libstdc

Searching Packages:
Setting up repositories
Reading repository metadata in from local files


compat-libstdc++-33.i386 3.2.3-47.3 base
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386 2.96-132.7.2 base
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


compat-libstdc++-33.i386 3.2.3-47.3 installed
Matched from:
compat-libstdc++-33


compat-libstdc++-296.i386 2.96-132.7.2 installed
Matched from:
compat-libstdc++-296
The compat-libstdc++-296 package contains 2.96-RH compatibility standard
C++ libraries.


Checking what environment variables are set as the Oracle unix user:

env|grep ORA

ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=/home/oracle/product/10.2.0/db_1

Make the new software ORACLE_HOME:

mkdir -p /home/oracle/product/11.1.0/db_1

Check kernel parameters:

cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename.
# Useful for debugging multi-threaded applications.
kernel.core_uses_pid = 1


# Oracle Specific

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Unzip the downloaded file:

unzip linux_11gR1_database.zip

...


Copy and edit a response file:

cp directory_unzipped/database/install/response/ 11G_SE.rsp

diff se.rsp 11G_SE.rsp

36c36
FROM_LOCATION="/mnt/downloads/database/stage/products.xml"
44c44


> ORACLE_BASE="/home/oracle"
52c52


> ORACLE_HOME="/home/oracle/product/11.1.0/db_1"
60c60


> ORACLE_HOME_NAME="Ora11gR1HOME1"
92c92
SHOW_SPLASH_SCREEN=FALSE
409c409


> s_globalDBName="TRAIN"
416c416


> s_dbSid="TRAIN"
563,564c563,564



> s_superAdminSamePasswd="xxxxxx"
> s_superAdminSamePasswdAgain="xxxxxx"
709c709


> n_configurationOption=3

Unset ORACLE_HOME:

[oracle@domU-12-31-36-00-25-E2 response]$ export ORACLE_HOME=
[oracle@domU-12-31-36-00-25-E2 ~]$ export LD_LIBRARY_PATH=
[oracle@domU-12-31-36-00-25-E2 response]$ env|grep ORA
ORACLE_SID=TRAIN
ORACLE_BASE=/home/oracle
ORACLE_HOME=

Run the install using -silent -responseFile options:

./runInstaller -silent -responseFile /mnt/downloads/database/install/response/11G_SE.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB. Actual 6433 MB Passed
Checking swap space: must be greater than 150 MB. Actual 895 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-08-11_08-21-09AM. Please wait ...[oracle@domU-12-31-36-00-25-E2 database]$ Oracle Universal Installer, Version 11.1.0.6.0 Production
Copyright (C) 1999, 2007, Oracle. All rights reserved.

You can find the log of this install session at:
/home/oracle/oraInventory/logs/installActions2007-08-11_08-21-09AM.log
.................................................................................................... 100% Done.


Loading Product Information
.............................................................................................................. 100% Done.


Starting execution of Prerequisites...
Total No of checks: 14

Performing check for CertifiedVersions
Checking operating system requirements ...
Expected result: One of enterprise-4,enterprise-5,redhat-4,redhat-5,SuSE-10,asianux-2,asianux-3
Actual Result: redhat-4
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Packages
Checking operating system package requirements ...
Checking for make-3.80; found make-1:3.80-6.EL4-i386. Passed
Checking for binutils-2.15.92.0.2; found binutils-2.15.92.0.2-22-i386. Passed
Checking for gcc-3.4.5; found gcc-3.4.6-8-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386. Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386. Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-8-i386. Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-4-i386. Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-15.0.1.el4-i386. Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-8-i386. Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-8-i386. Passed
Checking for unixODBC-2.2.11; found unixODBC-2.2.11-1.RHEL4.1-i386. Passed
Checking for unixODBC-devel-2.2.11; found unixODBC-devel-2.2.11-1.RHEL4.1-i386. Passed
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for Kernel
Checking kernel parameters
Checking for semmsl=250; found semmsl=250. Passed
Checking for semmns=32000; found semmns=32000. Passed
Checking for semopm=100; found semopm=100. Passed
Checking for semmni=128; found semmni=128. Passed
Checking for shmmax=536870912; found shmmax=536870912. Passed
Checking for shmmni=4096; found shmmni=4096. Passed
Checking for shmall=2097152; found shmall=2097152. Passed
Checking for file-max=65536; found file-max=65536. Passed
Checking for VERSION=2.6.9; found VERSION=2.6.16-xenU. Passed
Checking for ip_local_port_range=1024 - 65000; found ip_local_port_range=1024 - 65000. Passed
Checking for rmem_default=4194304; rmem_default=1048576. Failed <<<< rmem_max="4194304;" rmem_max="1048576." wmem_default="262144;" wmem_default="262144." wmem_max="262144;" wmem_max="262144." atleast="2.3.4-2.19" passed
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The kernel parameters do not meet the minimum requirements (see above).
Recommendation: Perform operating system specific instructions to update the kernel parameters.

========================================================
Performing check for GLIBC
Checking Recommended glibc version
Expected result: ATLEAST=2.3.4-2.19
Actual Result: 2.3.4-2.36
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for TotalMemory
Checking physical memory requirements ...
Expected result: 922MB
Actual Result: 1700MB
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for SwapSpace
Checking available swap space requirements ...
Expected result: 2550MB
Actual Result: 895MB
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The system does not have the required swap space.
Recommendation: Make more swap space available to perform the install.

========================================================
Performing check for DetectIfDHCPAssignedIP
Checking Network Configuration requirements ...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.
Recommendation: Oracle supports installations on systems with DHCP-assigned public IP addresses. However, the primary network interface on the system should be configured with a static IP address in order for the Oracle Software to function properly. See the Installation Guide for more details on installing the software on systems configured with DHCP.

========================================================
Performing check for CheckPathForOtherOracleHomes
Checking PATH environment variable...
Check complete. The overall result of this check is: Failed <<<<


Check complete: Failed <<<<
Problem: The installer has detected that the PATH environment variable includes other ORACLE_HOME locations in it.
Recommendation: Restart the installer after removing other ORACLE_HOME locations from PATH environment variable.

========================================================
Performing check for CheckTempDiskSpace
Checking for sufficient diskspace in TEMP location...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CheckLdLibraryPath
Checking LD_LIBRARY_PATH environment variable...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleBase
Validating ORACLE_BASE location (if set) ...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for OracleHomeSpace
Checking Oracle Home path for spaces...
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for DetectAnyInvalidASMHome
Checking for proper system clean-up....
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
Performing check for CompatibilityChecks_DB
Checking for Oracle Home incompatibilities ....
Actual Result: NEW_HOME
Check complete. The overall result of this check is: Passed


Check complete: Passed
========================================================
PrereqChecks complete


Analyzing dependencies
.................................................................................................................. 100% Done.


-----------------------------------------------------------------------------
Summary
Global Settings
Source: /mnt/downloads/database/stage/products.xml
Oracle Base: /home/oracle
Oracle Home: /home/oracle/product/11.1.0/db_1 (Ora11gR1HOME1)
Installation Type: Standard Edition
Product Languages
English
Space Requirements
/ Required 3.38GB (includes 224MB temporary) : Available 6.14GB
New Installations (116 products)
Oracle Database 11g 11.1.0.6.0
Enterprise Edition Options 11.1.0.6.0
Oracle Database 11g 11.1.0.6.0
Oracle Text 11.1.0.6.0
Oracle Net Services 11.1.0.6.0
Oracle Enterprise Manager Console DB 11.1.0.5.0
Oracle Net Listener 11.1.0.6.0
HAS Files for DB 11.1.0.6.0
Oracle Call Interface (OCI) 11.1.0.6.0
Enterprise Manager Agent 10.2.0.3.1
Oracle Programmer 11.1.0.6.0
Oracle Database Gateway for ODBC 11.1.0.6.0
Oracle JVM 11.1.0.6.0
Database Configuration and Upgrade Assistants 11.1.0.6.0
Oracle XML Development Kit 11.1.0.6.0
Generic Connectivity Common Files 11.1.0.6.0
Oracle Multimedia 11.1.0.6.0
Oracle Multimedia Locator 11.1.0.6.0
Oracle Internet Directory Client 11.1.0.6.0
Oracle Database Utilities 11.1.0.6.0
Secure Socket Layer 11.1.0.6.0
PL/SQL 11.1.0.6.0
Oracle Recovery Manager 11.1.0.6.0
Oracle Net 11.1.0.6.0
Assistant Common Files 11.1.0.6.0
Installation Common Files 11.1.0.6.0
Enterprise Manager plugin Common Files 11.1.0.5.0
Oracle LDAP administration 11.1.0.6.0
SQL*Plus 11.1.0.6.0
HAS Common Files 11.1.0.6.0
Oracle Help for the Web 2.0.14.0.0
Oracle UIX 2.2.20.0.0
Precompiler Common Files 11.1.0.6.0
Oracle Clusterware RDBMS Files 11.1.0.6.0
Cluster Verification Utility Common Files 11.1.0.6.0
Oracle Wallet Manager 11.1.0.6.0
Oracle Security Developer Tools 11.1.0.6.0
XML Parser for Java 11.1.0.6.0
Enterprise Manager Minimal Integration 11.1.0.6.0
Oracle Database User Interface 2.2.13.0.0
SQL*Plus Files for Instant Client 11.1.0.6.0
Oracle ODBC Driver 11.1.0.6.0
Required Support Files 11.1.0.6.0
Database SQL Scripts 11.1.0.6.0
OLAP SQL Scripts 11.1.0.6.0
PL/SQL Embedded Gateway 11.1.0.6.0
Oracle Globalization Support 11.1.0.6.0
Character Set Migration Utility 11.1.0.6.0
Oracle Locale Builder 11.1.0.6.0
Secure Socket Layer 11.1.0.6.0
Oracle Java Client 11.1.0.6.0
Oracle JDBC/THIN Interfaces 11.1.0.6.0
Oracle Multimedia Client Option 11.1.0.6.0
Oracle Universal Connection Pool 11.1.0.6.0
Oracle Notification Service 11.1.0.5.0
Oracle Code Editor 1.2.1.0.0I
Oracle Ultra Search Server Rdbms 11.1.0.6.0
Oracle Help For Java 4.2.9.0.0
Oracle Containers for Java 11.1.0.6.0
JAccelerator (COMPANION) 11.1.0.6.0
Database Workspace Manager 11.1.0.6.0
SQLJ Runtime 11.1.0.6.0
Oracle Core Required Support Files 11.1.0.6.0
Platform Required Support Files 11.1.0.6.0
Oracle Ice Browser 5.2.3.6.0
Oracle Application Express 11.1.0.6.0
Oracle SQL Developer 11.1.0.6.0
Oracle JDBC Server Support Package 11.1.0.6.0
regexp 2.1.9.0.0
Enterprise Manager Common Files 10.2.0.3.1
Installation Plugin Files 11.1.0.6.0
Oracle JDBC/OCI Instant Client 11.1.0.6.0
XML Parser for Oracle JVM 11.1.0.6.0
Oracle XML Query 11.1.0.6.0
Oracle Starter Database 11.1.0.6.0
Sample Schema Data 11.1.0.6.0
Precompiler Required Support Files 11.1.0.6.0
Parser Generator Required Support Files 11.1.0.6.0
Oracle Multimedia Locator RDBMS Files 11.1.0.6.0
Oracle Globalization Support 11.1.0.6.0
Oracle Multimedia Annotator 11.1.0.6.0
Oracle Multimedia Java Advanced Imaging 11.1.0.6.0
Oracle Database 11g Multimedia Files 11.1.0.6.0
Agent Required Support Files 10.2.0.3.1
Oracle 11g Warehouse Builder Server 11.1.0.6.0
Oracle Ultra Search Server 11.1.0.6.0
Oracle Ultra Search Middle-Tier 11.1.0.6.0
Oracle Ultra Search Common Files 11.1.0.6.0
Perl Interpreter 5.8.3.0.4
RDBMS Required Support Files 11.1.0.6.0
Oracle Display Fonts 9.0.2.0.0
RDBMS Required Support Files for Instant Client 11.1.0.6.0
Enterprise Manager Agent Core Files 10.2.0.3.1
Enterprise Manager Common Core Files 10.2.0.3.1
Enterprise Manager Grid Control Core Files 10.2.0.3.1
Enterprise Manager Database Plugin -- Agent Support 11.1.0.5.0
Enterprise Manager Database Plugin -- Management Service Support 11.1.0.5.0
Enterprise Manager Repository Core Files 10.2.0.3.1
Enterprise Manager Database Plugin -- Repository Support 11.1.0.5.0
Provisioning Advisor Framework 10.2.0.3.1
XDK Required Support Files 11.1.0.6.0
Oracle RAC Required Support Files-HAS 11.1.0.6.0
SQL*Plus Required Support Files 11.1.0.6.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle Extended Windowing Toolkit 3.4.47.0.0
Bali Share 1.1.18.0.0
Buildtools Common Files 11.1.0.6.0
Oracle Net Required Support Files 11.1.0.6.0
SSL Required Support Files for InstantClient 11.1.0.6.0
LDAP Required Support Files 11.1.0.6.0
Oracle ODBC Driverfor Instant Client 11.1.0.6.0
Oracle Configuration Manager 10.2.6.0.0
Oracle Universal Installer 11.1.0.6.0
Oracle One-Off Patch Installer 11.1.0.6.0
Installer SDK Component 11.1.0.6.0
Sun JDK 1.5.0.1.1
-----------------------------------------------------------------------------


Installation in progress (Sat Aug 11 08:21:39 EDT 2007)
............................................................... 7% Done.
............................................................... 14% Done.
............................................................... 21% Done.
............................................................... 29% Done.
............................................................... 36% Done.
............................................................... 43% Done.
............................................................... 50% Done.
............................................................... 58% Done.
............................................................... 65% Done.
............................................................... 72% Done.
............................................................... 80% Done.
........................ 82% Done.
Install successful

Linking in progress (Sat Aug 11 08:27:00 EDT 2007)
. 83% Done.
Link successful

Setup in progress (Sat Aug 11 08:29:03 EDT 2007)
.................................. 100% Done.
Setup successful

End of install phases.(Sat Aug 11 08:29:16 EDT 2007)
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/home/oracle/product/11.1.0/db_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts

The installation of Oracle Database 11g was successful.
Please check '/home/oracle/oraInventory/logs/silentInstall2007-08-11_08-21-09AM.log' for more details.

cd /home/oracle/product/11.1.0/db_1
./root.sh