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'


Advertisements

2 thoughts on “MySQL-Proxy vs MySQLSlap Round 1

  1. This command gives me the following error:

    mysqlslap: Cannot run query CREATE TABLE `t1` (id serial,intcol1 INT(32) ,intcol2 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128)) ERROR : No database selected

  2. @Jonathan:

    Can you post the full mysqlslap command line you tried to use please?
    Plus the version of mysqlslap you are using by running this command.
    mysqlslap –version

    I would check your database connectivity first by using mysql.
    If that is unable to connect using the username and password (and host) then mysqlslap won't be able to either.

Comments are closed.