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 