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

Advertisements

MySQL-Proxy vs MySQLSlap Round 1

Outline:

Given the mysql-proxy is moving toward a production ready version (at some point). I thought it would be useful to put it up against mysqlslap and see how it fared.

In this round, I wanted to see if mysql-proxy could handle various workloads and concurrent connections in the plain vanilla load-balancing state.

Essentially we are building on past experience.
http://blog.dbadojo.com/2007/12/mysql-proxy-on-ec2.html
http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html

Rather than muck around, I gave mysql-proxy the standard mysqlslap mixed workout. A couple of easy blackhole engines rounds, followed by MyISAM and INNODB.

Summary:

  • As before I am impressed with mysql-proxy, I had been told that mysql-proxy will barf at high concurrent sessions, but in this round I didn’t see it. In fact mysqlslap died before mysql-proxy did.
  • It moved the load between both mysql databases without issues or hiccups.
  • Make sure you have granted the appropriate access first, and test with mysql client program first.

Results:

First run results



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

cat /tmp/mysqlslap_q10000_mysqlproxy_loadbal1.csv

blackhole,mixed,4.963,4.675,5.262,1,10000
blackhole,mixed,2.882,1.732,4.621,25,400
blackhole,mixed,4.689,1.814,7.555,50,200
blackhole,mixed,7.448,2.072,21.603,100,100
myisam,mixed,5.013,4.691,5.343,1,10000
myisam,mixed,2.464,2.115,4.399,25,400
myisam,mixed,4.691,2.310,7.726,50,200
myisam,mixed,7.014,2.596,10.117,100,100
innodb,mixed,5.244,4.852,5.956,1,10000
innodb,mixed,3.912,3.086,5.109,25,400
innodb,mixed,4.804,3.173,7.499,50,200
innodb,mixed,7.972,3.589,12.426,100,100

Rerun mysqlslap with large example my.cnf

cat /tmp/mysqlslap_q10000_mysqlproxy_loadbal2.csv

blackhole,mixed,5.135,4.788,5.443,1,10000
blackhole,mixed,2.473,1.773,4.718,25,400
blackhole,mixed,4.792,1.867,7.525,50,200
blackhole,mixed,6.024,2.220,13.224,100,100
myisam,mixed,5.103,4.844,5.435,1,10000
myisam,mixed,2.495,2.043,5.049,25,400
myisam,mixed,4.804,2.310,7.570,50,200
myisam,mixed,6.466,2.522,12.533,100,100
innodb,mixed,5.064,4.720,5.323,1,10000
innodb,mixed,3.613,3.169,4.830,25,400
innodb,mixed,4.166,3.117,4.702,50,200
innodb,mixed,7.738,4.302,13.236,100,100

mysqlslap again with higher concurrency

mysqlslap --concurrency=100,200,500,1000 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql \
--csv=/tmp/mysqlslap_q1000_highconcurrent_mysqlproxy_loadbal2.csv \
--engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --password=$PASSWD --host=mysqldb1 --port=4040 &


Errors

mysqlslap: Could not create thread

mysqlslap: Error when connecting to server: 1129 Host 'mysqldb1' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'
mysqlslap: Error when connecting to server: 1129 Host 'mysqldb1' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'


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)