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

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