MySQL vs MySQLSlap Round 3

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


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

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)

MySQL Proxy on EC2

MySQL Proxy is a tool to sit between the client and database. It can load balance, redirect queries, track slow queries amongst other possible uses.

http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html
http://forge.mysql.com/wiki/MySQL_Proxy

There is a ramp up within MySQL to get MySQL Proxy into a production ready product. So it is time to check out the product and see if the stated simple out-of-the-box functionality works.

Like most of the articles here, they are a mix of past experience and exploration. Some of these products are completely new to me like MySQL Cluster and MySQL proxy. Others like Oracle standbys and silent installs using response files are just putting my knowledge on the web so others can find the short cut to the solution they are searching for.

I ran MySQL Proxy 0.6 on MySQL 5.1 on CentOS 4.4 on Amazon EC2 on a small 32 bit instance.

Comments:

The main features I wanted to test was the ability to load balancing and also failover on the lost of one node. For these tests I ran MySQL proxy on the same server as the database. However there is nothing stopping you running them on the client (which could be a webserver) or on a dedicated machine.
The load balancing as explained by the documentation is a round robin by default. You can modify this behavior by passing your own script.
The failover was also seemless. It requires some more robusting testing with Mysqlslap running to see how it handles the switchover during load.

Install:

  1. wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz/from/http://mirrors.24-7-solutions.net/pub/mysql/
  2. tar -xzvf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
  3. cp mysql-proxy-0.6.0-linux-rhas4-x86/sbin/mysql-proxy /sbin
  4. cp -R mysql-proxy-0.6.0-linux-rhas4-x86/share/* /usr/share/

Running MySQL Proxy and testing failover and load balancing.


mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy

Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module

Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file= PID file in case we are started as daemon

mysql-proxy &
[1] 2861

ps -ef|grep mysql-proxy

root 2861 2795 0 01:36 ttyp0 00:00:00 mysql-proxy
root 2865 2795 0 01:37 ttyp0 00:00:00 grep mysql-proxy

Checking with nmap what ports are listening

nslookup `hostname`

Server: 172.16.0.23
Address: 172.16.0.23#53

Non-authoritative answer:
Name: domU-12-31-35-00-1D-B1.z-2.compute-1.internal
Address: 10.255.34.63

nmap 10.255.34.63

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 01:40 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql

Connecting to the MySQL Proxy admin port 4041

mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from proxy_config;
+----------------------------+----------------+
| option | value |
+----------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+----------------+
6 rows in set (0.00 sec)

From 2nd machine

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'domU-12-31-38-00-32-36.compute-1.internal';
Query OK, 0 rows affected (0.00 sec)

mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql --host=domU-12-31-35-00-1D-B1.z-2.compute-1.internal --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Checking the admin interface with those connections:

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | proxy | 10 | |
| 3 | server | 10 | |
+------+--------+-------+------+
4 rows in set (0.00 sec)

Using the --proxy-backend-addresses parameter to load balance and failover

mysql-proxy --proxy-backend-addresses=10.255.34.63:3306 --proxy-backend-addresses=10.252.53.192:3306 &
[1] 5170

nmap 10.255.34.63

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:05 EST
Interesting ports on domU-12-31-35-00-1D-B1.z-2.compute-1.internal (10.255.34.63):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
866/tcp open unknown
3306/tcp open mysql

Nmap run completed -- 1 IP address (1 host up) scanned in 0.259 seconds

nmap 10.252.53.192

Starting nmap 3.70 ( http://www.insecure.org/nmap/ ) at 2007-11-17 02:06 EST
Interesting ports on domU-12-31-38-00-32-36.compute-1.internal (10.252.53.192):
(The 1655 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp open ssh
111/tcp open rpcbind
631/tcp open ipp
870/tcp open unknown
3306/tcp open mysql

Connecting from Node 1 goes to node 1

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server

mysql --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Checking the connections again via admin port

mysql --host=127.0.0.1 --port=4041 --user=root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-agent MySQL Enterprise Agent

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from proxy_connections;
+------+--------+-------+------+
| id | type | state | db |
+------+--------+-------+------+
| 0 | server | 0 | |
| 1 | proxy | 0 | |
| 2 | server | 10 | |
+------+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from proxy_config;
+----------------------------+--------------------+
| option | value |
+----------------------------+--------------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.lua_script | NULL |
| proxy.backend_addresses[0] | 10.255.34.63:3306 |
| proxy.backend_addresses[1] | 10.252.53.192:3306 |
| proxy.fix_bug_25371 | 0 |
| proxy.profiling | 1 |
+----------------------------+--------------------+
7 rows in set (0.00 sec)

Connecting on Node 2 pointing at node 1 and we are on node 1

mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

Stopping node 1 MySQL database to test failover

service mysql.server stop
Shutting down MySQL. [ OK ]

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld.c.302: connect(10.255.34.63:3306) failed: Connection refused
network-mysqld-proxy.c.3713: connecting to backend (10.255.34.63:3306) failed, marking it as down for ...
ERROR 1130 (00000): Host 'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' is not allowed to connect to this MySQL server

Note: I had to fix an issue with authenication of root@hostname.

On mysql db on 2nd node:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'domU-12-31-35-00-1D-B1.z-2.compute-1.internal' identified by 'xxxxxx';
Query OK, 0 rows affected (0.00 sec)


mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
network-mysqld-proxy.c.3610: backend 10.255.34.63:3306 was down for more than 10 sec, waking it up

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

On 2nd node, point mysql at node1 and we are back on node2. Failover works!

mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye
[root@domU-12-31-38-00-32-36 ~]# nslookup `hostname`
Server: 172.16.0.23
Address: 172.16.0.23#53

Non-authoritative answer:
Name: domU-12-31-38-00-32-36.compute-1.internal
Address: 10.252.53.192


mysql --host=10.255.34.63 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye

On node1, point at node 1 and we are going to node2

mysql --host=10.255.34.63 --port=4040 --user=root -p$PASSWD
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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 processlist;
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
| 11 | root | domU-12-31-35-00-1D-B1.z-2.compute-1.internal:46276 | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------------------------------------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)


node1 connecting to node2 ip (goes to node1)

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD
ERROR 1130 (00000): Host 'domU-12-31-38-00-32-36.compute-1.internal' is not allowed to connect to this MySQL server

Added authenication on node2!!!

grant all privileges on *.* to root@'domU-12-31-38-00-32-36.compute-1.internal' identified by 'xxxxx';

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

Testing load balancing:
Node 2 goes to node 2 when node 1 is already connected.

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbt2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye

Node 2 goes to node 1 when node 1 is not connected.

mysql --host=10.252.53.192 --port=4040 -u root -p$PASSWD

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)


Making Logical Volumes on EC2

The MySQL backups using LVM Snapshots post is now by far the most popular post.

Here is shell script which helps get the Logical Volumes (LV) setup in the first place.



### START OF SCRIPT ###

#!/bin/sh
# Name: make_mnt_LV.sh
# Script to make EC2 /mnt into a LVM volume

modprobe dm-snapshot

umount /mnt
pvcreate /dev/sda2
vgcreate vg /dev/sda2
lvcreate -L30720M -n myvmdisk1 vg
mkfs -t ext3 /dev/vg/myvmdisk1
mkdir -p /data1/mysql/data
mount /dev/vg/myvmdisk1 /data1/mysql

lvcreate -L30720M -n myvmdisk2 vg
mkfs -t ext3 /dev/vg/myvmdisk2
mkdir -p /data2/mysql/data
mount /dev/vg/myvmdisk2 /data2/mysql

lvcreate -L30720M -n myvmdisk3 vg
mkfs -t ext3 /dev/vg/myvmdisk3
mkdir -p /backup/mysql/data
mount /dev/vg/myvmdisk3 /backup/mysql

lvdisplay

mkdir -p /data1/mysql/data
mkdir -p /data2/mysql/data

chown -R mysql:mysql /data1/mysql
chown -R mysql:mysql /data2/mysql
chown -R mysql:mysql /backup/mysql

### END OF SCRIPT ###


Sample Output

chmod 750 make_mnt_LV.sh

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 4.0G 1.2G 2.7G 31% /
/dev/sda2 147G 189M 140G 1% /mnt

lvdisplay

No volume groups found

./make_mnt_LV.sh


Physical volume "/dev/sda2" successfully created
Volume group "vg" successfully created
Logical volume "myvmdisk1" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 32 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Logical volume "myvmdisk2" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Logical volume "myvmdisk3" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
--- Logical volume ---
LV Name /dev/vg/myvmdisk1
VG Name vg
LV UUID h10Ev4-EVYY-CoGl-2AHS-sjqe-BNHE-297EyT
LV Write Access read/write
LV Status available
# open 1
LV Size 30.00 GB
Current LE 7680
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:0

--- Logical volume ---
LV Name /dev/vg/myvmdisk2
VG Name vg
LV UUID UM6GmA-s0vr-Mt6u-kh8m-wvMr-TMtD-rb34kk
LV Write Access read/write
LV Status available
# open 1
LV Size 30.00 GB
Current LE 7680
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:1

--- Logical volume ---
LV Name /dev/vg/myvmdisk3
VG Name vg
LV UUID nCKCDh-MgIf-92il-FQHu-XduC-yJsO-koIGgM
LV Write Access read/write
LV Status available
# open 1
LV Size 30.00 GB
Current LE 7680
Segments 1
Allocation inherit
Read ahead sectors 0
Block device 253:2

df -h

Filesystem Size Used Avail Use% Mounted on
/dev/sda1 4.0G 1.2G 2.7G 31% /
/dev/mapper/vg-myvmdisk1
30G 77M 28G 1% /data1/mysql
/dev/mapper/vg-myvmdisk2
30G 77M 28G 1% /data2/mysql
/dev/mapper/vg-myvmdisk3
30G 77M 28G 1% /backup/mysql

Modifying your MySQL my.cnf to use the new data directories available.

http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html


What do we have already?

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/

Make a backup and edit using your favourite text editor:

cp /etc/my.cnf /etc/my.cnf.old
vi /etc/my.cnf

Check again:

This setup will place one INNODB datafile on each of the newly created Logical Volumes.
An alternative would be to place the logfiles on one of them instead.

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
datadir=/data1/mysql/data
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = /data1/mysql/data/ibdata1:10M:autoextend;/data2/mysql/data/ibdata2:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data

Sysbench vs MySQL on EC2

I was reading how Morgan was slightly disappointed at the results of his sysbench test. He ran sysbench on his laptop and then on EC2 and got a large difference in the results. Thorsten from RightScale also ran some sysbench tests.

I was keen to either replicate or disprove their results. Given their parameters I replicated their results.
Rather than stop there, I decided to see what was main determining factor.

I discovered that the number of threads made little difference to the final result. The size of the table made the largest difference. However when I continued to decrease the size, the results became reverted to the same as the original.

How to install sysbench on EC2:

  1. yum -y install sysstat gcc
  2. cd /mnt
  3. wget http://optusnet.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
  4. tar -xzvf sysbench-0.4.8.tar.gz
  5. cd sysbench-0.4.8
  6. ./configure
  7. make
  8. make install

Running sysbench against MySQL on EC2:

  1. install MySQL 5.1
  2. cp /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-large.cnf /etc/my.cnf
  3. service mysql.server start
  4. export LD_LIBRARY_PATH=/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/
  5. sysbench help
  6. mysqladmin -u root -pyourpasswordhere create database sbtest
  7. sysbench –test=oltp –mysql-table-engine=myisam –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpasswordhere prepare
  8. sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpassword –oltp-read-only run
  9. sysbench –num-threads=16 –max-requests=100000 –test=oltp –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpassword cleanup

Results:

  1. Table-size=1000000, Threads= {2,4,6,8,10,12,16}: Avg transactions: 100000 (126.06 per sec.)
  2. Table-size=100000, Threads= {2,4,8,16}: Avg transactions: 100000 (411.74 per sec.)
  3. Table-size=50000, Threads= {4,8,16}: Avg transactions: 100000 (125.59 per sec.)

Comments:

  1. Given that the table size seems to have a big impact on the transaction performance, it would suggest that tuning the size of cache (key and sort) should make a difference. The jury is still out on that.
  2. Some of the sql running appeared in the slow-query.log at the default settings. The main culprit is as always, a range scan on a index followed by a filesort.

Coming soon:

  1. Repeat runs using difference storage engines.
  2. Further investigation on changing various memory and sort system variables.
  3. Repeating the tests for various table sizes.

The whole aim of this series of articles on benchmarks is the determine the optimal design for databases in general on EC2. Once we have a reports from varied sources of benchmarking tools, we can get closer to knowing the appropriate size and design of databases using EC2.

Have Fun

Paul

IOZone benchmark vs EC2 heat maps


I have been using the IOZone benchmarking tool to test the IO ability of EC2 running CentOS 4.

In the last post I showed the 3D surface area chart showing how as the file size grows, the io performance degrades, quite sharply as the file migrates from CPU cache to memory cache to disk.

I redid the charts as what Excel calls contour charts, but remind me of heat maps.

The change was striking, suddenly you can easily see the boundary that IOZone has found for
various file and record sizes. The other standout feature was the appearance of holes or cool spots in the charts at specific file and record sizes.

I went back and specifically tested the file and record combination, for example to do read, write and random read/write on a 16M file with 1M record size I used this command:

iozone -R -r 1m -s 16m -i 0 -i 1 -i2

I have used similar settings for the throughput test as well.

Comments:

The whole reason for stepping back and running the benchmark tools is that the results are suggesting that choosing the appropriate column datatype, rowsize, tablesize and memory buffers is potentially going to have a large impact on your databases performance.

Look at these contour maps and you will start to see what I mean. The presence of a ridge as the filesize increases for a band of record sizes is also very interesting.

Previous Articles:

IOZone Benchmark vs EC2 – Part 1

Resources:
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_writes.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_reads.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_random_writes.JPG
http://s3.amazonaws.com/dbadojo_benchmark/iozone_heatmap_random_reads.JPG
Zipped Excel Spreadsheet for IOZone Benchmark data and charts

Have Fun
Paul

IOzone benchmark vs EC2

Here are some pretty surface area graphs from the EC2 benchmark, the stepping down indicates from CPU cache to Memory cache, the last cliff is down to disk once the file was larger the available memory.

As I mentioned yesterday I was running off a IOzone benchmark on EC2 to see how the disk performs, after reading about it in this online benchmark article.
There are a couple of nice features with this benchmark

  1. Output is saved in a format ready for surface area graphs in Excel
  2. Gnuplot options available as well
  3. It tests stride size to see if there are any stripe boundary or IO library issues.

Another first is you can download my results in this file iozone_benchmark_ec2.zip served from Amazon S3 (Right click SAVE AS)

The IOzone documentation[PDF] which is short, but detailed.

Installing IOzone:

  1. wget http://www.iozone.org/src/current/iozone-3-283.i386.rpm
  2. rpm -Uvh iozone-3-283.i386.rpm
  3. export PATH=$PATH:/opt/iozone/bin

Running a benchmark:

Note: File should be larger than available memory. -g 2G indicates 2 Gigabyte file.

  1. iozone -Ra -g 2G > iozone.out

The full size graphs can be found via Amazon S3

http://s3.amazonaws.com/dbadojo_benchmark/iozone_ec2_write.GIF
http://s3.amazonaws.com/dbadojo_benchmark/iozone_ec2_read.GIF
http://s3.amazonaws.com/dbadojo_benchmark/iozone_ec2_random_read.GIF
http://s3.amazonaws.com/dbadojo_benchmark/iozone_ec2_random_write.GIF

Have Fun

Paul

Top 9 Most popular articles so far

Whilst I have a iozone benchmark running in the background, I thought I would post a quick article listing the top 9 pages on the site up to now (October 2007).

As most people don’t always hit the front page and more than 85%-90% of all visitors are new I thought it would be useful for those people to see what else is floating around on the site.

Installing Oracle 11G using a silent install with a response file
MySQL 5.1 NDB Cluster Part 4 -Testing
MySQL 5.1 NDB Cluster Part 3
MySQL 5.1 NDB Cluster Part 2
MySQL 5.1 NDB Cluster Part 1
MySQL backups using LVM Snapshots
MySQL 5.1 Cluster replication
OpenFiler on EC Part 1
OpenFiler on EC Part 2

Have Fun

Paul