MySQL Master-Master replication table sync

I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync.

If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution.

Anyhow. I have a master-master replication just lying around to test this kind of stuff after finishing the multi-instance master-master replication pair last week. Plus I have already had some past experience using table-checksum tool which is part of Maatkit (or MySQL toolkit as it used to be known). Amazing how you publish some stuff and then you get the next idea for an article almost immediately.

Overview:

To test Baron’s assertion that Maatkit mk-table-sync is the best tool to re-sync a master-master replication pair.

Install:

  1. Setup a master-master replication pair.
  2. Download and install Maatkit.
  3. Load the Sakila sample database.
  4. Make some changes on one master which are not replicated to the other.
  5. Verify that the tables are out-of-sync using mk-table-checksum
  6. Use mk-table-sync to re-sync the tables.

Summary:

Again Maatkit lives up to my expectations. Both mk-table-checksum and mk-table-sync discovered and re-synced the tables without any issues.
On a side note, without this toolkit, you could still have rebuilt the other master from the dedicated slave, but this is so much faster and easier.

Ideas for the motivated reader:

  1. As always be careful when using set global sql_slave_skip_counter = 1;
  2. Download and test the Maatkit tools for yourself.
  3. Run mk-table-checksum periodically to discover if your slaves are actually consistent with your master.
  4. Having multiple recovery methods is good, you should be experienced and confident to use them all.

Detailed Screen Dump with comments:



Maatkit Dependencies

yum install perl-DBI perl-DBD-MySQL gcc
Time::HiRes

wget http://search.cpan.org/CPAN/authors/id/J/JH/JHI/Time-HiRes-1.9712.tar.gz

download maatkit-1753 from Sourceforge

perl Makefile.PL
make install

Break the master-master replication

On master2:

stop slave;

On master1:

mysql> use sakila
Database changed

mysql> insert into film values(1001,'Welcome to DBA Dojo','A place on the way towards being a DBA',2007,1,1,30,45,25.00,90000.00,'G','Behind the Scenes',now());
Query OK, 1 row affected, 1 warning (0.00 sec)

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


mysql> select * from film where film_id = 1001;
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features | last_update |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| 1001 | Welcome to DBA Dojo | A place on the way towards being a DBA | 2007 | 1 | 1 | 30 | 45.00 | 25 | 999.99 | G | Behind the Scenes | 2008-03-03 05:42:58 |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

On master 2

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 film where film_id = 1001\G
Empty set (0.00 sec)

On Master 1 run a simple table checksum


mk-table-checksum h=master1,u=root,p=$PASSWD h=master1 --databases=sakila

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 1 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL

Check the local slave


mk-table-checksum h=master1,u=root,p=$PASSWD h=slave1 --databases=sakila
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 slave1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila address 0 slave1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 slave1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila city 0 slave1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 slave1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 slave1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 slave1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 slave1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 slave1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 slave1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 slave1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 slave1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila payment 0 slave1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 slave1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 slave1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 slave1 MyISAM NULL 1107595282 0 0 NULL NULL


Creating the CHECKSUM table

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;

Forget the port and you will get this error

mk-table-checksum h=master1,u=root,p=$PASSWD h=master2,u=root,p=$PASSWD \
--databases=sakila

DBI connect(';host=master2;mysql_read_default_group=mysql','root',...) failed: Can't connect to MySQL server on 'master2

And we have a winner, mk-table-checksum has found the issue with the film table

mk-table-checksum h=master1,u=root,p=$PASSWD \
h=master2,u=root,p=$PASSWD,P=3308 --databases=sakila

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
...
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master2 MyISAM NULL 1421174266 0 0 NULL NULL
...

Now use Baron's mk-table-sync example... testing first.

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--test

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 0 0 0 Chunk sakila.film

Run the real thing

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--verbose --execute

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 1 0 0 Chunk sakila.film


On master2

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

On Slave 2, which is a cascade slave of master 1

mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.20-beta-log

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

mysql> use sakila
Database changed
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

MySQL Multi Master-Master on EC2

Background:

As I mentioned in the previous post on running multiple mysql instances on EC2 using mysqld_multi. This was the first step in running 2 or more EC2 Amazon Machine Image (AMI) as a Multi-instance Master-Master (MIMM) replication cluster.
The idea cames from a blog article from Apokalyptik.com
It is about improving the availability of your databases on EC2 and allowing easy backups without affecting either master instance.
The other benefit I see, is these two masters are read-write. So you can choose your load-balancer and not worry about what to do if one of the master instances goes down.
I am going to test using mysql-proxy to perform that role of load-balancing a multi master replication cluster next.
Then we are going to hammer the hell out of the configuration using mysqlslap, then sysbench and then the granddaddy of OLTP benchmarks DBT.

If you follow the screen dump of what I did I am using a bunch of previous work. Each of the previous steps and posts builds on the last.

Summary:

  1. The most complex part of this was getting the /etc/my.cnf right. The rest was plain vanilla mysql replication. You can view myMIMM.cnf
  2. Some of the links below never mention log-slave-updates. This is fine if your in a circular or one way replication setup. In this setup each master is a slave of the other, and each master has a dedicated slave. To propagate changes from one master to the dedicated slave needs log-slave-updates on each master instance.
  3. mysqld_multi stop does not seem to stop the instances. I had to use mysqladmin shutdown instead, connecting to specific sockets.

Other Resources:

http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
http://www.howtoforge.com/mysql_master_master_replication
http://capttofu.livejournal.com/1752.html
http://jayant7k.blogspot.com/2006/06/multi-master-replication-in-mysql.html
http://code.google.com/p/mysql-master-master/

Install:

Refer to the above links for more detailed setups. Essentially the high level steps are

  1. Setup your partitioning, I used LVM and my LVM script so I can use snapshots for backups in the future.
  2. Download and install MySQL.
  3. Load your data into that single instance, then shutdown.
  4. Map out your replication topology.
  5. Copy the data to form the basis of all the new master and slave instances.
  6. Modify the /etc/my.cnf so it has the setting for running mysqld_multi. For an example follow my post on Multiple instances or follow the example my.cnf below.
  7. Assign appropriate names, sockets, port numbers and even datafile names so that at 3am you don’t mistake one instance for another.
  8. Modify the /etc/hosts file. So you can use nicer names rather than DNS or IP addresses.

Session Output: Comments in bold




Run make_mnt_lvm.sh

[root@domU-12-31-38-00-29-35 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 4.0G 1.2G 2.7G 31% /
/dev/mapper/vg-myvmdisk1
30G 77M 28G 1% /data1/mysql
/dev/mapper/vg-myvmdisk2
30G 77M 28G 1% /data2/mysql
/dev/mapper/vg-myvmdisk3
30G 77M 28G 1% /backup/mysql

Copy existing mysql data directory to new homes

cp -R /usr/local/mysql/data/* /data1/mysql/data
cp -R /usr/local/mysql/data/* /data2/mysql/data
mv /usr/local/mysql/data /usr/local/mysql/data_old
chown -R mysql:mysql /data1/mysql/data
chown -R mysql:mysql /data2/mysql/data


[root@domU-12-31-38-00-29-35 mysql]# ls -l /data1/mysql/data
total 1504
-rw-r----- 1 root root 5242880 Feb 25 04:46 ib_logfile0
-rw-r----- 1 root root 5242880 Feb 25 04:46 ib_logfile1
-rw-r----- 1 root root 10485760 Feb 25 04:46 ibdata1
drwxr-x--- 2 root root 4096 Feb 25 04:46 mysql
-rw-r----- 1 root root 263 Feb 25 04:46 mysql-bin.000001
-rw-r----- 1 root root 125 Feb 25 04:46 mysql-bin.000002
-rw-r----- 1 root root 38 Feb 25 04:46 mysql-bin.index
drwxr-x--- 2 root root 4096 Feb 25 04:46 test

Create the multi_mysql aware /etc/my.cnf

[root@domU-12-31-38-00-29-35 mysql]# cat /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql1.sock

[mysqld_multi]
mysqld = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user = multi_root
password = multi_pass

[mysqld1]
port = 3306
socket = /tmp/mysql1.sock
pid-file = /data1/mysql/data/mysql1.pid
datadir = /data1/mysql/data/
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


log-bin=mysql1-bin

server-id = 1

# For Master-Master replication
auto_increment_increment = 10
auto_increment_offset = 1

# Slave configuration

# The replication master for this slave - required
master-host = master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3308

[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
pid-file = /data2/mysql/data/mysql2.pid
datadir = /data2/mysql/data
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data2/mysql/data
innodb_data_file_path = ibdata2:10M:autoextend
innodb_log_group_home_dir = /data2/mysql/data
innodb_log_arch_dir = /data2/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

log-bin=mysql2-bin
log-slave-updates

server-id = 2

# Slave configuration

# The replication master for this slave - required
master-host = master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Second master on separate machine

Notes:
I am using higher server_id 3 and 4.
higher mysqld configurations mysqld3 and mysqld4
Higher port numbers 3308 and 3309

Reason: At 3am in the morning you don't want to be working on the wrong instance.


[client]
port = 3308
socket = /tmp/mysql1.sock

[mysqld_multi]
mysqld = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user = multi_root
password = multi_pass

[mysqld3]
port = 3308
socket = /tmp/mysql1.sock
pid-file = /data1/mysql/data/mysql1.pid
datadir = /data1/mysql/data/
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


log-bin=mysql1-bin

server-id = 3

# For Master-Master replication
auto_increment_increment = 10
auto_increment_offset = 2

# Slave configuration

# The replication master for this slave - required
master-host = master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306
skip-slave-start

[mysqld4]
port = 3309
socket = /tmp/mysql2.sock
pid-file = /data2/mysql/data/mysql2.pid
datadir = /data2/mysql/data
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data2/mysql/data
innodb_data_file_path = ibdata2:10M:autoextend
innodb_log_group_home_dir = /data2/mysql/data
innodb_log_arch_dir = /data2/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

log-bin=mysql2-bin
log-slave-updates

server-id = 4

# Slave configuration

# The replication master for this slave - required
master-host = master2
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3308


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Starting up

mysqld_multi start 1 --verbose --password=multi_pass
mysqld_multi start 2 --verbose --password=multi_pass


[root@domU-12-31-38-00-29-35 data]# ps -ef|grep mysqld_safe
root 3178 1 0 04:59 ttyp0 00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3306 --socket=/tmp/mysql1.sock

--pid-file=/data1/mysql/data/mysql1.pid --datadir=/data1/mysql/data/ --skip-locking --key_buffer=16M

--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K

--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --innodb_data_home_dir=/data1/mysql/data

--innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/data1/mysql/data

--innodb_log_arch_dir=/data1/mysql/data --log-bin=mysql1-bin --server-id=1
root 3669 1 0 05:21 ttyp0 00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3307 --socket=/tmp/mysql2.sock

--pid-file=/data2/mysql/data/mysql2.pid --datadir=/data2/mysql/data/ --log-error=/data2/mysql/data/ --skip-locking

--key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K

--read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M

--innodb_data_home_dir=/data2/mysql/data --innodb_data_file_path=ibdata2:10M:autoextend

--innodb_log_group_home_dir=/data2/mysql/data --innodb_log_arch_dir=/data2/mysql/data

--innodb_buffer_pool_size=128M --log-bin=mysql2-bin --server-id=2

Checking connectivity

[root@domU-12-31-38-00-29-35 data]# mysql -u root -p -S /tmp/mysql1.sock
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.

mysql> show variables like '%dir%';
+--------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+--------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /data1/mysql/data/ |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+--------------------+-----------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> exit
Bye

[root@domU-12-31-38-00-29-35 data]# mysql -u root -p -S /tmp/mysql2.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
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 variables like '%dir%';
+--------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+--------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /data2/mysql/data/ |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+--------------------+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

Fire up new machine and setup as slave on master1 (this will be master2 soon)

[root@ip-10-251-75-150 ~]# mysqld_multi --verbose --password=multi_pass start 3
[root@ip-10-251-75-150 ~]# ps -ef|grep mysqld_safe
root 3006 1 0 05:34 ttyp0 00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3308 --socket=/tmp/mysql1.sock

--pid-file=/data1/mysql/data/mysql1.pid --datadir=/data1/mysql/data/ --skip-locking --key_buffer=16M

--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K

--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --innodb_data_home_dir=/data1/mysql/data

--innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/data1/mysql/data

--innodb_log_arch_dir=/data1/mysql/data --innodb_buffer_pool_size=256M --innodb_additional_mem_pool_size=20M

--innodb_log_file_size=64M --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1

--innodb_lock_wait_timeout=50 --log-bin=mysql1-bin --server-id=3 --auto_increment_increment=10

--auto_increment_offset=2 --master-host=master1 --master-user=repluser --master-password=replpassword

--master-port=3306 --skip-slave-start
root 3235 2791 0 05:34 ttyp0 00:00:00 grep mysqld_safe


mysql -u root -p$PASSWD -S /tmp/mysql1.sock

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.

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'slave2' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

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

Add privileges on Master1

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master2' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

Test connectivity from master2 to master 1

[root@ip-10-251-75-150 ~]# mysql -u repluser -preplpassword --host=master1 --port=3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql>

Check master position on master1

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

Start slave on master2

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: master1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000001
Read_Master_Log_Pos: 475
Relay_Log_File: mysql1-relay-bin.000003
Relay_Log_Pos: 621
Relay_Master_Log_File: mysql1-bin.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: 475
Relay_Log_Space: 883
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)

Setup master1 as slave on master2

Test connectivity for master1 to master2

[root@ip-10-251-69-190 ~]# mysql -u repluser -preplpassword --host=master2 --port=3308
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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


Check master status on master2

mysql -u root -p$PASSWD -S /tmp/mysql1.sock

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
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 master status\G
*************************** 1. row ***************************
File: mysql1-bin.000003
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On master1 start slave

[root@ip-10-251-69-190 ~]# mysql -u root -p$PASSWD -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

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: master2
Master_User: repluser
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql1-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysql1-relay-bin.000004
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql1-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: 106
Relay_Log_Space: 554
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)

Ok fire up the slaves on each master


Addition of replication settings

# Slave configuration

# The replication master for this slave - required
master-host = master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port = 3306
log-slave-updates
skip-slave-start


setup replication user

mysql -u root -p$PASSWD -S /tmp/mysql1.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'slave1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

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

On Master mysqld 1
mysql> show master status\G
*************************** 1. row ***************************
File: mysql1-bin.000002
Position: 320
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Test connectivity for slave replication user

mysql -u repluser -preplpassword --host=master1 --port=3306

Start slave if it isn't already

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

Check the status.

Note: The Last_IO_error is just that. The last recorded error



mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000002
Read_Master_Log_Pos: 535
Relay_Log_File: mysql2-relay-bin.000003
Relay_Log_Pos: 681
Relay_Master_Log_File: mysql1-bin.000002
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: 535
Relay_Log_Space: 983
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: 1130
Last_IO_Error: error connecting to master 'repluser@master1:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)


Load some sample data into the master1 instance

http://dev.mysql.com/doc/sakila/en/sakila.html

[root@ip-10-251-69-190 ~]# cd /backup/mysql/
[root@ip-10-251-69-190 mysql]# wget http://downloads.mysql.com/docs/sakila-db.zip
--05:55:09-- http://downloads.mysql.com/docs/sakila-db.zip
=> `sakila-db.zip'
Resolving downloads.mysql.com... 213.136.52.53
Connecting to downloads.mysql.com|213.136.52.53|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 670,566 (655K) [application/x-zip]

100%[============================================================================>] 670,566 281.15K/s

05:55:13 (280.50 KB/s) - `sakila-db.zip' saved [670566/670566]



[root@domU-12-31-38-00-29-35 sakila-db]# mysql -u root -p$PASSWD -S /tmp/mysql1.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> source sakila-schema.sql

...

mysql> source sakila-data.sql
...

mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

Checking that slave propagation

mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
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)

mysql> use sakila;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

Checking master2

[root@ip-10-251-75-150 ~]# mysql -u root -p$PASSWD -P 3308
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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)

mysql> use sakila
Database changed
mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)

Checking slave2

mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

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: master2
Master_User: repluser
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql1-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysql2-relay-bin.000004
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql1-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: 106
Relay_Log_Space: 554
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)

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

Where is the sakila database?!?

This is related to log-slave-updates!
http://dev.mysql.com/doc/refman/5.1/en/replication-options.html#option_mysqld_log-slave-updates

Master 2 took the changes from Master 1, however it never recorded the changes in its binlog!

Stopping the instances...

I found mysqld_multi stop doesn't stop or shutdown anything. So you have to go and shutdown the
mysql instances using mysqladmin shutdown -S yourSocket -uroot -Pyourpassword

mysqladmin shutdown -S /tmp/mysql2.sock -uroot -p$PASSWD
mysqladmin shutdown -S /tmp/mysql1.sock -uroot -p$PASSWD

[root@ip-10-251-75-150 ~]# ps -ef|grep mysql
root 27356 2791 0 06:37 ttyp0 00:00:00 grep mysql

MySQL SolidDB vs MySQLSlap EC2

Overview:

I am always keen to try and test out new technologies. The idea of having a choice in storage engines is a great feature of MySQL. In MySQL 5.1, the MySQL engine API is available to write your own storage engine.

In the meantime, there are a couple of MySQL storage engines produced by 3rd party companies. SolidDB is one of them.

I wanted to install and then test the SolidDB engine against mysqlslap. I have heard and read a little about SolidDB in the past. However I was amazed that if you use SolidDB you don’t get any of the other storage engines. It is the SolidDB or the highway, 3rd party engines are not usable.

Where to get SolidDB:

To download SolidDB use this link http://dev.soliddb.com/en/download/download_files.php.

I used the MySQL SolidDB 5.0.51.
ftp://developers.soliddb.com/pub/5.0.51-0081/solidmysql-5.0.51-linux-i686-0081.tar.gz

Documentation and FAQ:

If in doubt always have the documentation handy.
http://dev.soliddb.com/doc
http://dev.soliddb.com/doc/doc_html/079/soliddb_storageengine.html

Plus the FAQ is always a good idea when using a product for the first time.
http://www.solidtech.com/en/products/relationaldatabasemanagementsoftware/enterprise/faqs.asp

Summary:

The results for the SolidDB engine were as good as the Innodb. Nothing widely variant from the numbers. I will run another bunch of tests on a larger instance and really push it to see if there are any upper limits.
Once that is out of the way, the next thing would be to throw sysbench and then DBT benchmark at it.
I have no idea as to the level of adoption out in the real world, however the limitation of only using the solidDB engine may be limiting that. I am happy to be proving wrong on this. Feel free to comment.

Setup:



Download MySQL SolidDB
tar -zxvf solidmysql-5.0.51-linux-i686-0081.tar.gz -C /var/lib
cd /var/lib/solidmysql-5.0.51-linux-i686-0081
cp /var/lib/solidmysql-5.0.51-linux-i686-0081/support-files/my-soliddb.cnf /etc/my.cnf
/var/lib/solidmysql-5.0.51-linux-i686-0081/bin/mysqld_safe --default-storage-engine=solidDB &

Sample my.cnf file

more support-files/my-soliddb.cnf
# Example MySQL config file.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
thread_stack = 64K

# These are solidDB parameters
#
default-storage-engine = soliddb
soliddb_cache_size=256M
soliddb_durability_level=3

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

Checking out the SolidDB system variables

./mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51 Source distribution

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

mysql> create database solid
-> ;
Query OK, 1 row affected (0.01 sec)

mysql> use solid;
Database changed
mysql> show variables like '%solid%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_soliddb | YES |
| soliddb_cache_size | 268435456 |
| soliddb_durability_level | 3 |
| soliddb_logdir | |
| soliddb_backupdir | |
| soliddb_admin_command | |
| soliddb_checkpoint_deletelog | ON |
| soliddb_filespec | |
| soliddb_lock_wait_timeout | 30 |
| soliddb_db_block_size | 0 |
| soliddb_log_block_size | 0 |
| soliddb_backup_block_size | 0 |
| soliddb_checkpoint_interval | 50000 |
| soliddb_checkpoint_time | 300 |
| soliddb_io_threads | 5 |
| soliddb_lockhash_size | 1000000 |
| soliddb_pessimistic | ON |
| soliddb_write_threads | 1 |
| soliddb_extend_increment | 500 |
| soliddb_readahead | 4 |
+------------------------------+-----------+
20 rows in set (0.00 sec)

Test and Results:


mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.364 seconds
Minimum number of seconds to run all queries: 0.303 seconds
Maximum number of seconds to run all queries: 0.557 seconds
Number of clients running queries: 1
Average number of queries per client: 1000

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.391 seconds
Minimum number of seconds to run all queries: 0.311 seconds
Maximum number of seconds to run all queries: 0.508 seconds
Number of clients running queries: 25
Average number of queries per client: 40

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.418 seconds
Minimum number of seconds to run all queries: 0.392 seconds
Maximum number of seconds to run all queries: 0.461 seconds
Number of clients running queries: 50
Average number of queries per client: 20

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.456 seconds
Minimum number of seconds to run all queries: 0.403 seconds
Maximum number of seconds to run all queries: 0.500 seconds
Number of clients running queries: 100
Average number of queries per client: 10

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.633 seconds
Minimum number of seconds to run all queries: 0.599 seconds
Maximum number of seconds to run all queries: 0.683 seconds
Number of clients running queries: 200
Average number of queries per client: 5


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

cat /tmp/mysqlslap_q1000_soliddb.csv

soliddb,mixed,0.371,0.247,0.451,1,1000
soliddb,mixed,0.381,0.337,0.462,25,40
soliddb,mixed,0.418,0.369,0.483,50,20
soliddb,mixed,0.523,0.440,0.639,100,10
soliddb,mixed,0.703,0.634,0.804,200,5

Increase the size of soliddb_cache_size from 250M to 750M

soliddb_cache_size=750M


mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.336 seconds
Minimum number of seconds to run all queries: 0.240 seconds
Maximum number of seconds to run all queries: 0.425 seconds
Number of clients running queries: 1
Average number of queries per client: 1000

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.382 seconds
Minimum number of seconds to run all queries: 0.331 seconds
Maximum number of seconds to run all queries: 0.454 seconds
Number of clients running queries: 25
Average number of queries per client: 40

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.420 seconds
Minimum number of seconds to run all queries: 0.364 seconds
Maximum number of seconds to run all queries: 0.455 seconds
Number of clients running queries: 50
Average number of queries per client: 20

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.491 seconds
Minimum number of seconds to run all queries: 0.426 seconds
Maximum number of seconds to run all queries: 0.518 seconds
Number of clients running queries: 100
Average number of queries per client: 10

Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.657 seconds
Minimum number of seconds to run all queries: 0.604 seconds
Maximum number of seconds to run all queries: 0.740 seconds
Number of clients running queries: 200
Average number of queries per client: 5

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

cat /tmp/mysqlslap_q1000_soliddb_750M.csv

soliddb,mixed,0.315,0.266,0.392,1,1000
soliddb,mixed,0.397,0.355,0.463,25,40
soliddb,mixed,0.420,0.364,0.475,50,20
soliddb,mixed,0.495,0.446,0.556,100,10
soliddb,mixed,0.701,0.621,0.810,200,5

http://dev.soliddb.com/doc/doc_html/079/soliddb_storageengine.html#solid-autoincrement

Lets test the autoincrement out with a write only test

mysqlslap --concurrency=1,25,50,100,200 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --engine=soliddb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=1000 --user=root \
--csv=/tmp/mysqlslap_q1000_soliddb_750M_write.csv

soliddb,write,0.395,0.305,0.468,1,1000
soliddb,write,0.398,0.335,0.460,25,40
soliddb,write,0.507,0.437,0.757,50,20
soliddb,write,0.522,0.453,0.594,100,10
soliddb,write,0.754,0.682,0.808,200,5

Same again with 10,000 queries

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

mysql> exit
Bye
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --engine=soliddb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=10000 --user=root --csv=/tmp/mysqlslap_q10000_soliddb_750M_write.csv

soliddb,write,5.207,4.382,6.535,1,10000
soliddb,write,4.686,4.002,5.141,25,400
soliddb,write,4.792,4.101,5.425,50,200
soliddb,write,5.633,4.472,7.151,100,100
soliddb,write,5.784,4.630,6.733,200,50

And back to a mixed load for 10,000 queries.

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

cat /tmp/mysqlslap_q10000_soliddb_750M.csv

soliddb,mixed,3.784,3.314,4.277,1,10000
soliddb,mixed,3.791,3.624,4.310,25,400
soliddb,mixed,4.043,3.516,4.857,50,200
soliddb,mixed,4.280,3.845,5.159,100,100
soliddb,mixed,4.464,3.969,5.602,200,50


vmstat 1 10

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 1222700 39820 315480 0 0 16 4566 374 4444 6 3 87 4
1 1 0 1222700 39824 315476 0 0 0 33840 2347 22313 23 11 6 59
19 2 0 1221448 39828 315472 0 0 0 13968 956 12484 55 9 9 26
9 2 0 1221324 39828 315472 0 0 0 7608 568 14670 59 41 0 0
1 1 0 1221324 39832 315468 0 0 0 6012 491 15793 61 20 0 18
7 1 0 1221460 39832 315468 0 0 0 8044 626 14697 60 20 0 20
18 2 0 1221324 39836 315464 0 0 0 13196 923 15057 77 10 6 6
6 2 0 1221200 39836 315464 0 0 0 3088 257 6661 21 5 0 74
1 0 0 1221200 39840 315460 0 0 0 5924 476 15433 70 20 0 9
1 1 0 1221200 39840 315460 0 0 0 7216 562 14654 62 24 0 13


MySQL vs MySQLSlap Round 3

In this continuing series on using mysqlslap to pound mysql databases.
http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html
http://blog.dbadojo.com/2008/01/mysql-vs-mysqlslap-round-2.html

I was moving towards running mysqlslap to test any concurrency limits inherent in mysql-proxy. However I was unable to get the small instances on EC2 to handle even small concurrent levels (500 concurrent users).

At least this means that if you are using a small spec machine (or EC2 small instance) you should use connection pooling to limit the absolute number of sessions and promote reuse of existing sessions. This is web connected databases 101. Simple stuff.
We are now pushing the envelope, throwing big iron at the problem, scaling up rather than scaling out. This is a common alternative if you are unwilling or unable to rework or redesign your database application or architecture to use a scaling out solution.

Ok on with the show. I fired up the largest instance available in EC2.
This is Fedora Core 8 64 Bit running mysql-5.1.22-rc-linux-x86_64-glibc23

We have 4 CPUS, 15 Gig of RAM and plenty of disk space.

Results:

  1. MySQL running the Innodb storage engine scaled reasonably well.
  2. MySQL had no difficulty handling as many as 8000 concurrent sessions.
  3. Running more queries with less connections was slower than the opposite. i.e running more sessions in parallel reduced the time to completion. This is good and should be expected.
  4. In MySQLslap the total number of queries is just that. It is not the number of queries per individual session.
  5. Running many concurrent sessions without any queries will hang MySQLslap… a bug or feature?!? If a session doesn’t run anything then it will never? return.

Coming up:

  1. MySQLSlap vs mysql-proxy: load balancing
  2. MySQLSlap vs mysql-proxy: read/write split
  3. MySQLSlap vs MySQL SolidDB.
  4. MySQLSlap vs MySQL PrimeBase XT (PBXT) storage engine.

Showing the specification of the box, disk and top to show CPU and memory

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 9.9G 767M 8.6G 9% /
/dev/sdb 414G 199M 393G 1% /mnt

top - 03:54:46 up 2 min, 1 user, load average: 0.02, 0.02, 0.00
Tasks: 47 total, 2 running, 45 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.9%us, 0.7%sy, 0.0%ni, 95.9%id, 2.4%wa, 0.0%hi, 0.0%si, 0.1%st
Cpu1 : 0.1%us, 0.2%sy, 0.0%ni, 99.5%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.1%us, 0.2%sy, 0.0%ni, 99.4%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.4%us, 0.2%sy, 0.0%ni, 99.3%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 15728640k total, 373744k used, 15354896k free, 3668k buffers
Swap: 0k total, 0k used, 0k free, 37608k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 16 0 10312 712 592 S 0 0.0 0:00.03 init
2 root RT 0 0 0 0 S 0 0.0 0:00.01 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root 10 -5 0 0 0 S 0 0.0 0:00.00 events/0

cat /proc/meminfo

MemTotal: 15728640 kB
MemFree: 15355020 kB
Buffers: 3676 kB
Cached: 37600 kB
SwapCached: 0 kB
Active: 29308 kB
Inactive: 15452 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 15728640 kB
LowFree: 15355020 kB
SwapTotal: 0 kB
SwapFree: 0 kB
Dirty: 4 kB
Writeback: 0 kB
Mapped: 6584 kB
Slab: 10016 kB
CommitLimit: 7864320 kB
Committed_AS: 9436 kB
PageTables: 592 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 124 kB
VmallocChunk: 34359738243 kB

my cnf files for the test...


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

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
log-bin=mysql-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


heavy INNODB my.cnf

grep -v "#" my-innodb-heavy-4G.cnf|sed -e '/^$/d'
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
back_log = 50
max_connections = 100
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 = 2G
innodb_data_file_path = ibdata1:10M:autoextend
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

My.cnf file for test.

diff my-innodb-heavy-4G_withoutcomments.cnf /etc/my.cnf

8c8
max_connections = 1024
30c30
key_buffer_size = 256M
40c40
innodb_buffer_pool_size = 8G
45c45
innodb_log_buffer_size = 16M

First run, simple run up to 1000 concurrent sessions:

mysqlslap --concurrency=1,25,50,100,200,500,1000 --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=$PASSWD

tail -f /tmp/mysqlslap_q1000.csv

blackhole,mixed,0.072,0.068,0.084,1,1000
blackhole,mixed,0.044,0.018,0.059,25,40
blackhole,mixed,0.041,0.035,0.069,50,20
blackhole,mixed,0.051,0.043,0.067,100,10
blackhole,mixed,0.078,0.059,0.102,200,5
blackhole,mixed,0.116,0.096,0.183,500,2
blackhole,mixed,0.204,0.164,0.311,1000,1
myisam,mixed,0.356,0.327,0.456,1,1000
myisam,mixed,0.168,0.157,0.184,25,40
myisam,mixed,0.187,0.140,0.227,50,20
myisam,mixed,0.193,0.175,0.207,100,10
myisam,mixed,0.198,0.189,0.215,200,5

MyISAM locks up at 500 concurrent sessions...


mysql -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19323
Server version: 5.1.22-rc-log MySQL Community Server (GPL)

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

mysql> show processlist;
+-------+------+-----------+-----------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+-----------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 2 | root | localhost | mysqlslap | Sleep | 77 | | NULL |
| 19275 | root | localhost | mysqlslap | Query | 77 | Locked | INSERT INTO t1 VALUES (NULL,1839719074,1147388315,'jbPuYPJuWuAF3FKMtuKwCtCBdruTJKwuvLhKms6Hm9OhOlwHX |
...
| 19322 | root | localhost | mysqlslap | Query | 77 | Locked | INSERT INTO t1 VALUES (NULL,2101913295,1100418235,'XQ0K9x30cYaFq2RvMZzKHKgmxy4uKBpreh4fX7f7XEEM8a9Nz |
| 19323 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-------+------+-----------+-----------+---------+------+--------+------------------------------------------------------------------------------------------------------+
48 rows in set (0.00 sec)

Rerunning with just INNODB engine.

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

tail -f /tmp/mysqlslap_q1000_innodb.csv

innodb,mixed,0.195,0.186,0.232,1,1000
innodb,mixed,0.193,0.161,0.228,25,40
innodb,mixed,0.306,0.206,0.745,50,20
innodb,mixed,0.301,0.226,0.355,100,10
innodb,mixed,0.354,0.256,0.565,200,5
innodb,mixed,0.404,0.355,0.462,500,2
innodb,mixed,0.622,0.496,0.860,1000,1

Trying for 2000 concurrents session, unix barfs on socket error, needed to increase open file limit.

mysqlslap --concurrency=2000 --iterations=10 --number-int-cols=2 --number-char-cols=3 \
--auto-generate-sql --csv=/tmp/mysqlslap_q1000_innodb.csv --engine=innodb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=1000 --user=root --password=$PASSWD

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
...

ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 122944
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
max rt priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 122944
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@domU-12-31-39-00-00-61 ~]# ulimit -a mysql
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 122944
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
max rt priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 122944
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

Set the open files to 4096

ulimit -n 4096

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

Results:

innodb,mixed,10.311,9.758,10.759,2000,0

Ok going crazy. Increasing concurrency test to 8000

ulimit -n 16384

mysql -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59768
Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL)

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

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

mysql> show variables like '%open%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| have_openssl | DISABLED |
| innodb_open_files | 300 |
| open_files_limit | 8192 |
| table_open_cache | 2048 |
+-------------------+----------+
4 rows in set (0.00 sec)

The test, starting at 3000, stop at 8000

mysqlslap --concurrency=3000,4000,5000,6000,7000,8000 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap_q1000_innodb.csv \
--engine=innodb --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=1000 --user=root --password=$PASSWD


show innodb status

mysql tables in use 1, locked 0
MySQL thread id 73478, query id 628793 localhost root statistics
SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 WHERE id = '86'
---TRANSACTION 0 559503, ACTIVE 0 sec, process no 24914, OS thread id 1231399232 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 72432, query id 630648 localhost root statistics
SELECT intcol1,intcol2,charcol1,charcol2,charcol3 FROM t1 WHERE id = '37'
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
26 OS file reads, 639254 OS file writes, 639129 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1816.98 writes/s, 1816.71 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17700857, used cells 14, node heap has 1 buffer(s)
0.02 hash searches/s, 1784.49 non-hash searches/s
---
LOG
---
Log sequence number 0 273601811
Log flushed up to 0 273601795
Last checkpoint at 0 200657790
1 pending log writes, 0 pending chkp writes
639290 log i/o's done, 1786.10 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9232961490; in additional pool allocated 16773376
Dictionary memory allocated 37240
Buffer pool size 524288
Free buffers 522840
Database pages 1447
Modified db pages 1394
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 64, created 1383, written 2709
0.00 reads/s, 0.00 creates/s, 4.62 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 1998 queries in queue
1 read views open inside InnoDB
Main thread process no. 24914, id 1140881728, state: sleeping
Number of rows inserted 319416, updated 0, deleted 0, read 10852873
910.30 inserts/s, 0.00 updates/s, 0.00 deletes/s, 789.73 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.24 sec)

Results: Notice how the whole timing slows down when there aren't enough queries
for the sessions.

innodb,mixed,0.195,0.186,0.232,1,1000
innodb,mixed,0.193,0.161,0.228,25,40
innodb,mixed,0.306,0.206,0.745,50,20
innodb,mixed,0.301,0.226,0.355,100,10
innodb,mixed,0.354,0.256,0.565,200,5
innodb,mixed,0.404,0.355,0.462,500,2
innodb,mixed,0.622,0.496,0.860,1000,1
innodb,mixed,10.311,9.758,10.759,2000,0
innodb,mixed,20.512,16.948,25.606,3000,0

mysqlslap --concurrency=3000,4000,5000,6000,7000,8000 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--csv=/tmp/mysqlslap_q1000_innodb.csv --engine=innodb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \

--number-of-queries=10000 --user=root --password=$PASSWD


innodb,mixed,5.466,4.771,6.007,3000,3

Killed at 3000 as running no queries was slower than running as least 1 query... interesting.



Ok. Going harder again, increasing the number of queries to 100,000.



mysqlslap --concurrency=25,50,100,200,500,1000,2000,5000,7000,8000 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--csv=/tmp/mysqlslap_q100000_innodb.csv --engine=innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=100000 --user=root --password=$PASSWD

innodb,mixed,22.739,22.073,24.245,25,4000
innodb,mixed,25.742,24.546,27.208,50,2000
innodb,mixed,28.168,26.855,30.160,100,1000
innodb,mixed,29.032,28.212,31.196,200,500
innodb,mixed,32.129,31.216,33.788,500,200
innodb,mixed,36.847,35.449,38.028,1000,100

2nd Run

innodb,mixed,23.062,22.516,23.560,25,4000
innodb,mixed,26.703,25.016,28.788,50,2000
innodb,mixed,28.460,26.001,30.368,100,1000
innodb,mixed,29.599,28.589,31.178,200,500
innodb,mixed,32.085,31.154,35.011,500,200
innodb,mixed,35.461,34.482,36.585,1000,100
innodb,mixed,18.829,18.180,19.518,2000,50
innodb,mixed,8.149,7.231,9.889,5000,20
innodb,mixed,6.093,5.681,6.338,7000,14
innodb,mixed,5.527,5.219,6.042,8000,12


MySQL vs MySQLslap round 2

I was going to post some more on the clash of the titans i.e. mysql-proxy vs mysqlslap, however after doing some retesting, I never got past ramping the number of concurrent sessions in mysqlslap.

I wanted to make sure the Mysql database could handle the raw load, before adding another layer in the form of mysql-proxy.

Back in August I did some preliminary work with MySQL and mysqlslap.

So given what I found in round 2 of this leadup fight I thought you might be interested.

Outline:

Use mysqlslap to test how many concurrent sessions that MySQL 5.1 can handle without failing or overloading the system. Using a command similar to this.

mysqlslap –user=root –password=$PASWD -i10 -c500 -v -a -x2 -y3 \
–auto-generate-sql-add-autoincrement –engine=blackhole,myisam,innodb \
–auto-generate-sql-load-type=mixed

Summary:

As I mentioned before in this article mysqlslap proves Innodb auto-increment issue. You need to be aware of the extra overhead that the INNODB storage engine has. Especially if you are using auto-incrementing keys.

  • Performance degrades from Blackhole, MyISAM to Innodb with/without auto-incr keys
  • At 500 concurrent sessions, Innodb died with a deadlock, caused by the auto-incr tree being too deep or taking too long to return. See the error below.
  • The small instance is unable to handle a 500 concurrent user load in MySQL database. I saw loads as high as 300 on this poor instance. This was true whether I ran mysqlslap locally on the same machine as the database or remotely.
  • I need to fire up the larger instances and try again.
  • Stay tuned for Round 3…

Raw Tests and Results:
(right trimmed on random insert value)



Set max_connections higher

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

mysql> show global variables like 'max%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 1000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
+----------------------------+----------------------+
19 rows in set (0.00 sec)

mysql> exit
Bye

First Test 200 concurrent users:

mysqlslap --user=root --password=$PASWD -i10 -c200 -v -a -x2 -y3 \
--auto-generate-sql-add-autoincrement --engine=blackhole,myisam,innodb \
--auto-generate-sql-load-type=mixed

Turning off preserve-schema!
Benchmark
Running for engine blackhole
Average number of seconds to run all queries: 0.569 seconds
Minimum number of seconds to run all queries: 0.512 seconds
Maximum number of seconds to run all queries: 0.803 seconds
Number of clients running queries: 200
Average number of queries per client: 0

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.744 seconds
Minimum number of seconds to run all queries: 0.581 seconds
Maximum number of seconds to run all queries: 1.027 seconds
Number of clients running queries: 200
Average number of queries per client: 0

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.347 seconds
Minimum number of seconds to run all queries: 2.145 seconds
Maximum number of seconds to run all queries: 2.452 seconds
Number of clients running queries: 200
Average number of queries per client: 0

Second test 500 concurrent users:

mysqlslap --user=root --password=$PASWD -i10 -c500 -v -a -x2 -y3 \
--auto-generate-sql-add-autoincrement --engine=blackhole,myisam,innodb \
--auto-generate-sql-load-type=mixed

Turning off preserve-schema!
Benchmark
Running for engine blackhole
Average number of seconds to run all queries: 1.619 seconds
Minimum number of seconds to run all queries: 1.276 seconds
Maximum number of seconds to run all queries: 2.358 seconds
Number of clients running queries: 500
Average number of queries per client: 0

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 2.068 seconds
Minimum number of seconds to run all queries: 1.552 seconds
Maximum number of seconds to run all queries: 2.810 seconds
Number of clients running queries: 500
Average number of queries per client: 0

mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx6...)
ERROR : Deadlock found when trying to get lock; try restarting transaction
mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx6...)
ERROR : Deadlock found when trying to get lock; try restarting transaction
mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx6
...)ERROR : Deadlock found when trying to get lock; try restarting transaction

SHOW INNODB STATUS

Interesting... looks like the auto-generate-inc key has failed on INNODB.
This line is the circuit breaker...
TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH



mysql> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
080122 7:25:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 78, signal count 77
Mutex spin waits 0, rounds 3579400, OS waits 15
RW-shared spins 112, OS waits 52; RW-excl spins 12, OS waits 11
------------------------
LATEST DETECTED DEADLOCK
------------------------
080122 7:25:44
*** (1) TRANSACTION:
TRANSACTION 0 438281, ACTIVE 1 sec, process no 18300, OS thread id 631324784 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320, 0 row lock(s)
MySQL thread id 38524, query id 1622441 localhost root update
INSERT INTO t1 VALUES (NULL,434457257,1657262432,1573177440,'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iK
S2Sx68u8pyjnA7rT6N0rk8mmwc1RIiJmlfkjnesSSRGWazrYMsO602tpe','m8wTRi6yIpeoh680EeaEMzcEFbEHKvn64jbNHZM
mDzm9xv6AbMdccZsE360uNQjdazEspDe24P4tC24nOhG1687AEzwTPX6')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mysqlslap`.`t1` trx id 0 438281 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 438280, ACTIVE (PREPARED) 1 sec, process no 18299, OS thread id 631308399 preparing
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 38523, query id 1622440 localhost root update
INSERT INTO t1 VALUES (NULL,434457257,1657262432,1573177440,'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0i
S2Sx68u8pyjnA7rT6N0rk8mmwc1RIiJmlfkjnesSSRGWazrYMsO602tpe','m8wTRi6yIpeoh680EeaEMzcEFbEHKvn64jbNHZ
mDzm9xv6AbMdccZsE360uNQjdazEspDe24P4tC24nOhG1687AEzwTPX6')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `mysqlslap`.`t1` trx id 0 438280 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

TABLE LOCK table `mysqlslap`.`t1` trx id 0 438781 lock mode AUTO-INC waiting
TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH
*** WE ROLL BACK TRANSACTION (2)



Rerunning without autoinc, box load has skyrocketed!

top - 07:37:42 up 1:19, 2 users, load average: 269.77, 184.13, 93.13
Tasks: 1066 total, 6 running, 1059 sleeping, 0 stopped, 1 zombie
Cpu(s): 50.0% us, 50.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1740944k total, 867816k used, 873128k free, 31540k buffers
Swap: 917496k total, 0k used, 917496k free, 638804k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14673 root 17 0 2608 1528 744 R 27.9 0.1 0:00.23 top
13899 root 15 0 1059m 58m 1084 S 11.2 3.5 0:00.02 mysqlslap
14160 mysql 15 0 199m 61m 5024 S 5.6 3.6 0:00.02 mysqld
1 root 16 0 1684 520 452 S 0.0 0.0 0:00.03 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 R 0.0 0.0 0:00.00 ksoftirqd/0

Ok running remote mysqlslap against mysqldb1. Trying auto-increment again...
For mysqlslap running on mysqldb2 hitting mysqldb1.

Fails again on INNODB step

mysqlslap --user=root --password=$PASWD --host=mysqldb1 -i10 -c500 \
-v -a -x2 -y3 --engine=blackhole,myisam,innodb \
--auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement

Turning off preserve-schema!
Benchmark
Running for engine blackhole
Average number of seconds to run all queries: 4.939 seconds
Minimum number of seconds to run all queries: 1.345 seconds
Maximum number of seconds to run all queries: 9.154 seconds
Number of clients running queries: 500
Average number of queries per client: 0

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 6.799 seconds
Minimum number of seconds to run all queries: 4.411 seconds
Maximum number of seconds to run all queries: 21.113 seconds
Number of clients running queries: 500
Average number of queries per client: 0

mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx68...)
ERROR : Deadlock found when trying to get lock; try restarting transaction
mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx68...)
ERROR : Deadlock found when trying to get lock; try restarting transaction
mysqlslap: Cannot run query INSERT INTO t1 VALUES
(NULL,434457257,1657262432,1573177440,
'BmXJ4TTmfOiqCj68zYIGxDFjbJlpd9pgvCr0iKmDcT4o4Ao4ayCOCR7fftT8uAoqFfqprS2Sx68...)
ERROR : Deadlock found when trying to get lock; try restarting transaction
Killed

Multiple MySQL instances on EC2

Overview:

I am keen to see what other people’s thoughts of MySQL and using MySQL on EC2 are in general. So I read any blogs which discuss them with interest.

In the latter part of last year I read an interesting article on proposing to run multiple MySQL instances on a single EC2.

Running multiple MySQL instances on one box is reasonably straightforward. Most of the exposure I have seen is either running multiple replication slaves on one box or as a cascaded slave for backups.
For example two separate machines are master instances and both their slaves are on a single box. This is not to provide failover, rather a location to run backups from.
In the second option, the standard replication pair run on separate machines and the slave machine has two slave instances, one “live” the other a cascaded slave of that instance so it can be used as a backup without stopping or affecting either the master or “live” slave.

So the first step down the path of the Apokalyptik Multiple Instance Master-Master (MIMM) replicated pairs configuration is to get two instances running on one EC2 AMI.

Resources:

MySQL Documentation has plenty of useful references:

http://dev.mysql.com/doc/refman/5.0/en/multiple-unix-servers.html
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html

Steps:

  1. Install MySQL
  2. Create instance
  3. Edit /etc/my.cnf to include the necessary options.
  4. Create directories required for each instance
  5. Move the original instance into both new data directories.
  6. Startup using mysqld_multi

Summary:

  • Whilst the configuration file looks complex, it is not really. You add a new group for mysqld_multi and you can cut and paste your existing mysqld for each mysqldN group.
  • Make sure each instance is not going to consume more than 40% of available memory including if each instance is at max connections.
  • Make sure the data directory for each instance has to correct permissions.

My Example:



Sample file running mysql_multi and two mysqld instances.

[root@ip-10-251-46-8 mysql]# cat /etc/my.cnf.tmp

[client]
port = 3306
socket = /tmp/mysql1.sock

[mysqld_multi]
mysqld = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user = multi_root
password = multi_pass

[mysqld1]
port = 3306
socket = /tmp/mysql1.sock
pid-file = /usr/local/mysql/data1/mysql1.pid
datadir = /usr/local/mysql/data1
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql1-bin

server-id = 1


[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
pid-file = /usr/local/mysql/data2/mysql2.pid
datadir = /usr/local/mysql/data2
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

log-bin=mysql2-bin

server-id = 2




[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Checking that mysqld_multi is working correctly and reading /etc/my.cnf

[root@ip-10-251-46-8 mysql]# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
[root@ip-10-251-46-8 mysql]# mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
[root@ip-10-251-46-8 mysql]# mysqld_multi report 3
Reporting MySQL servers
No groups to be reported (check your GNRs)

Note: Make sure the directory permissions for datadirs and other directories are correct.

Starting mysqld individually

[root@ip-10-251-46-8 mysql]# mysqld_multi start 1 --verbose --password=...
[root@ip-10-251-46-8 mysql]# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is running
[root@ip-10-251-46-8 mysql]# mysqld_multi start 2 --verbose --password=...
[root@ip-10-251-46-8 mysql]# mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running

Checking that two mysqld_safe processes were spawned

[root@ip-10-251-46-8 mysql]# ps -ef|grep mysqld_safe
root 3539 1 0 06:33 ttyp0 00:00:00 /bin/sh /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3306 --socket=/tmp/mysql1.sock --pid-file=/usr/local/mysql/data1/mysql1.pid --datadir=/usr/local/mysql/data1 --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql1-bin --server-id=1
root 3681 1 0 06:34 ttyp0 00:00:00 /bin/sh /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3307 --socket=/tmp/mysql2.sock --pid-file=/usr/local/mysql/data2/mysql2.pid --datadir=/usr/local/mysql/data2 --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --log-bin=mysql2-bin --server-id=2
root 3827 2795 0 06:35 ttyp0 00:00:00 grep mysqld_safe

Checking client connectivity

[root@ip-10-251-46-8 mysql]# mysql -u root -p -S /tmp/mysql1.sock
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 variables like '%dir%';
+----------------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /usr/local/mysql/data1/ |
| innodb_data_home_dir | |
| innodb_log_arch_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+----------------------------+-----------------------------------------------------------------------+

[root@ip-10-251-46-8 mysql]# mysql -u root -p -S /tmp/mysql2.sock
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 variables like '%dir%';
+----------------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-----------------------------------------------------------------------+
| basedir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/ |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir | /usr/local/mysql/data2/ |
| innodb_data_home_dir | |
| innodb_log_arch_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| plugin_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+----------------------------+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)

MySQL Proxy on EC2

MySQL Proxy is a tool to sit between the client and database. It can load balance, redirect queries, track slow queries amongst other possible uses.

http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html
http://forge.mysql.com/wiki/MySQL_Proxy

There is a ramp up within MySQL to get MySQL Proxy into a production ready product. So it is time to check out the product and see if the stated simple out-of-the-box functionality works.

Like most of the articles here, they are a mix of past experience and exploration. Some of these products are completely new to me like MySQL Cluster and MySQL proxy. Others like Oracle standbys and silent installs using response files are just putting my knowledge on the web so others can find the short cut to the solution they are searching for.

I ran MySQL Proxy 0.6 on MySQL 5.1 on CentOS 4.4 on Amazon EC2 on a small 32 bit instance.

Comments:

The main features I wanted to test was the ability to load balancing and also failover on the lost of one node. For these tests I ran MySQL proxy on the same server as the database. However there is nothing stopping you running them on the client (which could be a webserver) or on a dedicated machine.
The load balancing as explained by the documentation is a round robin by default. You can modify this behavior by passing your own script.
The failover was also seemless. It requires some more robusting testing with Mysqlslap running to see how it handles the switchover during load.

Install:

  1. wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz/from/http://mirrors.24-7-solutions.net/pub/mysql/
  2. tar -xzvf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
  3. cp mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy /sbin
  4. cp -R mysql-proxy-0.6.0-linux-rhas4-x86/share/* /usr/share/

Running MySQL Proxy and testing failover and load balancing.


mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy

Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module

Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file= PID file in case we are started as daemon

mysql-proxy &
[1] 2861

ps -ef|grep mysql-proxy

root 2861 2795 0 01:36 ttyp0 00:00:00 mysql-proxy
root 2865 2795 0 01:37 ttyp0 00:00:00 grep mysql-proxy

Checking with nmap what ports are listening

nslookup `hostname`

Server: 172.16.0.23
Address: 172.16.0.23#53

Non-authoritative answer:
Name: domU-12-31-35-00-1D-B1.z-2.compute-1.internal
Address: 10.255.34.63

nmap 10.255.34.63

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 01:40 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql

Connecting to the MySQL Proxy admin port 4041

mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

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

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from proxy_config;
+----------------------------+----------------+
| option | value |
+----------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+----------------+
6 rows in set (0.00 sec)

From 2nd machine

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'domU-12-31-38-00-32-36.compute-1.internal';
Query OK, 0 rows affected (0.00 sec)

mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal -u root -p$PASSWD

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

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

mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal --port=4040 -u root -p$PASSWD

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

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

Checking the admin interface with those connections:

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | proxy | 10 | |
| 3 | server | 10 | |
+------+--------+-------+------+
4 rows in set (0.00 sec)

Using the --proxy-backend-addresses parameter to load balance and failover

mysql-proxy --proxy-backend-addresses=10.255.34.63:3306 --proxy-backend-addresses=10.252.53.192:3306 &
[1] 5170

nmap 10.255.34.63

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:05 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql

Nmap run completed -- 1 IP address (1 host up) scanned in 0.259 seconds

nmap 10.252.53.192

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:06 EST
Interesting ports on domU-12-31-38-00-32-36.compute-1.internal (10.252.53.192):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
870/tcp open unknown
3306/tcp open mysql

Connecting from Node 1 goes to node 1

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server

mysql --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql>

Checking the connections again via admin port

mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

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

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from proxy_config;
+----------------------------+--------------------+
| option | value |
+----------------------------+--------------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 10.255.34.63:3306 |
| proxy.backend_addresses[1] | 10.252.53.192:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+--------------------+
7 rows in set (0.00 sec)

Connecting on Node 2 pointing at node 1 and we are on node 1

mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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 |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

Stopping node 1 MySQL database to test failover

service mysql.server stop
Shutting down MySQL. [ OK ]

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld.c.302: connect(10.255.34.63:3306) failed: Connection refused
network-mysqld-proxy.c.3713: connecting to backend (10.255.34.63:3306) failed, marking it as down for ...
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server

Note: I had to fix an issue with authenication of root@hostname.

On mysql db on 2nd node:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' identified by 'xxxxxx';
Query OK, 0 rows affected (0.00 sec)


mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld-proxy.c.3610: backend 10.255.34.63:3306 was down for more than 10 sec, waking it up

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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 |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

On 2nd node, point mysql at node1 and we are back on node2. Failover works!

mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> exit
Bye
[root@domU-12-31-38-00-32-36 ~]# nslookup `hostname`
Server: 172.16.0.23
Address: 172.16.0.23#53

Non-authoritative answer:
Name: domU-12-31-38-00-32-36.compute-1.internal
Address: 10.252.53.192


mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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 |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye

On node1, point at node 1 and we are going to node2

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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 processlist;
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| 11 | root | domU-12-31-35-00-1D-B1.z-2.compute-1.internal:46276 | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

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


node1 connecting to node2 ip (goes to node1)

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-38-00-32-36.compute-1.internal' is not allowed to connect to this MySQL server

Added authenication on node2!!!

grant all privileges on *.* to root@'domU-12-31-38-00-32-36.compute-1.internal' identified by 'xxxxx';

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
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 |
| test |
+--------------------+
3 rows in set (0.00 sec)

Testing load balancing:
Node 2 goes to node 2 when node 1 is already connected.

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
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 |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye

Node 2 goes to node 1 when node 1 is not connected.

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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 |
| test |
+--------------------+
3 rows in set (0.00 sec)


Making Logical Volumes on EC2

The MySQL backups using LVM Snapshots post is now by far the most popular post.

Here is shell script which helps get the Logical Volumes (LV) setup in the first place.



### START OF SCRIPT ###

#!/bin/sh
# Name: make_mnt_LV.sh
# Script to make EC2 /mnt into a LVM volume

modprobe dm-snapshot

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 ###


Sample Output

chmod 750 make_mnt_LV.sh

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 4.0G 1.2G 2.7G 31% /
/dev/sda2 147G 189M 140G 1% /mnt

lvdisplay

No volume groups found

./make_mnt_LV.sh


Physical volume "/dev/sda2" successfully created
Volume group "vg" successfully created
Logical volume "myvmdisk1" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 32 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Logical volume "myvmdisk2" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Logical volume "myvmdisk3" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
--- Logical volume ---
LV Name /dev/vg/myvmdisk1
VG Name vg
LV UUID h10Ev4-EVYY-CoGl-2AHS-sjqe-BNHE-297EyT
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:0

--- Logical volume ---
LV Name /dev/vg/myvmdisk2
VG Name vg
LV UUID UM6GmA-s0vr-Mt6u-kh8m-wvMr-TMtD-rb34kk
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/myvmdisk3
VG Name vg
LV UUID nCKCDh-MgIf-92il-FQHu-XduC-yJsO-koIGgM
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:2

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 4.0G 1.2G 2.7G 31% /
/dev/mapper/vg-myvmdisk1
30G 77M 28G 1% /data1/mysql
/dev/mapper/vg-myvmdisk2
30G 77M 28G 1% /data2/mysql
/dev/mapper/vg-myvmdisk3
30G 77M 28G 1% /backup/mysql

Modifying your MySQL my.cnf to use the new data directories available.

http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html


What do we have already?

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/

Make a backup and edit using your favourite text editor:

cp /etc/my.cnf /etc/my.cnf.old
vi /etc/my.cnf

Check again:

This setup will place one INNODB datafile on each of the newly created Logical Volumes.
An alternative would be to place the logfiles on one of them instead.

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
datadir=/data1/mysql/data
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = /data1/mysql/data/ibdata1:10M:autoextend;/data2/mysql/data/ibdata2:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data

Sysbench vs MySQL on EC2

I was reading how Morgan was slightly disappointed at the results of his sysbench test. He ran sysbench on his laptop and then on EC2 and got a large difference in the results. Thorsten from RightScale also ran some sysbench tests.

I was keen to either replicate or disprove their results. Given their parameters I replicated their results.
Rather than stop there, I decided to see what was main determining factor.

I discovered that the number of threads made little difference to the final result. The size of the table made the largest difference. However when I continued to decrease the size, the results became reverted to the same as the original.

How to install sysbench on EC2:

  1. yum -y install sysstat gcc
  2. cd /mnt
  3. wget http://optusnet.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
  4. tar -xzvf sysbench-0.4.8.tar.gz
  5. cd sysbench-0.4.8
  6. ./configure
  7. make
  8. make install

Running sysbench against MySQL on EC2:

  1. install MySQL 5.1
  2. cp /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-large.cnf /etc/my.cnf
  3. service mysql.server start
  4. export LD_LIBRARY_PATH=/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/
  5. sysbench help
  6. mysqladmin -u root -pyourpasswordhere create database sbtest
  7. sysbench –test=oltp –mysql-table-engine=myisam –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpasswordhere prepare
  8. sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpassword –oltp-read-only run
  9. sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpassword cleanup

Results:

  1. Table-size=1000000, Threads= {2,4,6,8,10,12,16}: Avg transactions: 100000 (126.06 per sec.)
  2. Table-size=100000, Threads= {2,4,8,16}: Avg transactions: 100000 (411.74 per sec.)
  3. Table-size=50000, Threads= {4,8,16}: Avg transactions: 100000 (125.59 per sec.)

Comments:

  1. Given that the table size seems to have a big impact on the transaction performance, it would suggest that tuning the size of cache (key and sort) should make a difference. The jury is still out on that.
  2. Some of the sql running appeared in the slow-query.log at the default settings. The main culprit is as always, a range scan on a index followed by a filesort.

Coming soon:

  1. Repeat runs using difference storage engines.
  2. Further investigation on changing various memory and sort system variables.
  3. Repeating the tests for various table sizes.

The whole aim of this series of articles on benchmarks is the determine the optimal design for databases in general on EC2. Once we have a reports from varied sources of benchmarking tools, we can get closer to knowing the appropriate size and design of databases using EC2.

Have Fun

Paul

IOZone benchmark vs EC2 heat maps


I have been using the IOZone benchmarking tool to test the IO ability of EC2 running CentOS 4.

In the last post I showed the 3D surface area chart showing how as the file size grows, the io performance degrades, quite sharply as the file migrates from CPU cache to memory cache to disk.

I redid the charts as what Excel calls contour charts, but remind me of heat maps.

The change was striking, suddenly you can easily see the boundary that IOZone has found for
various file and record sizes. The other standout feature was the appearance of holes or cool spots in the charts at specific file and record sizes.

I went back and specifically tested the file and record combination, for example to do read, write and random read/write on a 16M file with 1M record size I used this command:

iozone -R -r 1m -s 16m -i 0 -i 1 -i2

I have used similar settings for the throughput test as well.

Comments:

The whole reason for stepping back and running the benchmark tools is that the results are suggesting that choosing the appropriate column datatype, rowsize, tablesize and memory buffers is potentially going to have a large impact on your databases performance.

Look at these contour maps and you will start to see what I mean. The presence of a ridge as the filesize increases for a band of record sizes is also very interesting.

Previous Articles:

IOZone Benchmark vs EC2 – Part 1

Resources:
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_writes.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_reads.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_random_writes.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_random_reads.JPG
Zipped Excel Spreadsheet for IOZone Benchmark data and charts

Have Fun
Paul