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'
DBA Dojo 