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