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

Advertisements

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