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
DBA Dojo 
Curious if you’ve ever pushed the innodb_buffer_pool_size to 8G or higher? We’re running jobs on the same ec2 extra large box you did some of your tests on and have pushed the buffer from 5G to 10G (box is dedicated to mysql, database is about 120G and is laid out in file per table format) and things…changed. for one, the cpu usage of the db is now into double digits (yeah!) but then sometimes jumps up to over 100% (presumably when it’s dumping the buffers to disk) which is behavior i’ve only spotted once before but now is not uncommon.also, ‘top’ does not report the process to be using more than 10G of memory even though it should be well over 11G–probably a limitation of top, but inconsistent nonetheless with my previous observations and could maybe indicate that mysql simply can’t use a buffer that big?my ‘my.cnf’ looks very similar to yours with the notable difference of i’m only using 2 256M log files instead of 3 as you are–have you found that makes a difference for large innodb buffers? i know that they can be too small, but can they also be too big or too few?we also recently doubled the number of worker processes from 25 to about 45 and saw the load average on the db box go from around 1 to around 5. (this was at the same time we upped the buffer from 5 to 10G.) each worker has 1 db connection and essentially slams the db with reads and writes (we’ve honed the app, a rails app, to minimize deletes, updates, and counts.) we’re averaging now about 2k queries per sec (according to mytop). I haven’t found a lot of info on large mysql installations and don’t have the resources to do proper mysql benchmarking but off the top of your head, does that throughput sound in the ball park?
Hi Mark,Thanks for the comment.In my day job we have some clients with larger than 8 Gig innodb_buffer_pool_size. On EC2, I guess it is another thing to test out.For logfiles the recommended number is two.< HREF="http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb_log_file_size" REL="nofollow"> innodb_log_file_size<>This is me wrongly applying my Oracle experience to MySQL… whoops.The biggest issue is touching the disk on these VMs. IO is going to be slower than equivalent “real” machine.I will do another test of mysqlslap where the number of queries remains constant as the concurrent connections increases. There are some known issues with innodb scaling properly especially for tables involving auto-incrementing keys.