mysql proxy 0.6.1 performance tests
July 2nd, 2008 by 
atomic The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.
On EC2, I set up 6 “small” images in an example proxy setup:
- One client machine to run sysbench
- One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
- Four identical database servers, running mysql 5.0.45
The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.
mysql-proxy was run with the following command:
mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-43-70.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-75-52.ec2.internal:3306
Using the sysbench database benchmark utility to generate some heavy load on the boxes individually (to verify they were all more or less equal), and on the proxy itself, I got some interesting results:
Read/write operations/second:
Transactions/second:
Somewhat as I expected, mysql-proxy presents a bit of overhead when load is light. With only 8 concurrent threads, running against the proxy pointing to 4 equal database servers is slower than hitting one server itself.
As load goes up, however, you can see that the proxy scales reasonably well, while the accesses to the direct systems begin to tank rapidly after 64 concurrent threads (I lost some of my 128 thread results inadvertently and the instances are destroyed 
I’m fairly comfortable in interoplating the result to be roughly in between 64 and 256 )
Somewhat unsettling, however, was the fact that sysbench or mysql proxy failed to work beyond about 500 concurrent threads. My debug output for sysbench just stops here:
DEBUG: mysql_real_connect(0×9adec70, “ip-10-251-75-160″, “sbtest”, “(null)”, “sbtest”, 4040, “(null)”, CLIENT_MULTI_STATEMENTS)
DEBUG: mysql_init(0×9ae51b0)
DEBUG: mysql_options(0×9ae51b0, MYSQL_READ_DEFAULT_
On the servers themselves, I can see the 125+ connections to each individual box, and all looks normal. I tried tweaking some of the thread settings in ulimit and sysbench itself to no avail, but didn’t have a ton of time to play with it. I decided the results were already interesting enough and will see if I can revisit this another time.
Another reason i am not so concerned is that more than one proxy can be utilised in many typical environments, so the ability of a single mysql-proxy instance to handle 512+ concurrent connections might not be show-stopper.
If anyone out there has done some similar tests with mysql proxy, i welcome your feedback!
Posted in mysql, mysql-proxy, performance | 
July 2nd, 2008 at 10:57 am
Hi, one question about proxy setting.
Proxy send read or write querys to all databases with a some “scheduler algorithm” like a Round Robin ? How are distributed your querys with a proxy test ?
July 2nd, 2008 at 12:17 pm
have you found any memory leaks with it? How large of a memory footprint does your mysql proxy grow to be?
July 2nd, 2008 at 12:55 pm
@pfreixes: For these tests, I’m only using “out-of-the-box” functionality provided by mysql-proxy, using the command line argument in the post. If you want to write your own code, you can do nifty things in the connect_host() hook, like deciding when and where to connect based on whatever criteria.
@sheeri: Not that I noticed, although the tests were only over the course of an hour or two while i had the machines up. But for each successively bigger tests (32, 64, 128,etc.) I did not restart the proxy, so a pretty substantial amount of traffic went through it.
As for memory footprint, argh, I wish I had checked. I watched CPU usage closely (mysql-proxy barely used 100% until the 128-thread tests) but I don’t recall noticing high memory use. I guess that’ll have to be for part 2
July 2nd, 2008 at 1:53 pm
Do you have latency data?
July 2nd, 2008 at 2:53 pm
@realneel:
Sorry, no fancy graphs, but here are the per-request averages I got:
For the proxy:
proxy-8.log: avg: 0.0526s
proxy-16.log: avg: 0.0832s
proxy-32.log: avg: 0.1379s
proxy-64.log: avg: 0.2446s
proxy-128.log: avg: 0.5125s
proxy-256.log: avg: 1.0781s
proxy-300.log: avg: 1.3313s
proxy-350.log: avg: 1.5413s
proxy-480.log: avg: 2.2983s
Baseline, one of the hosts:
ip-10-251-71-21.ec2.internal-1-threads.log: avg: 0.0073s
ip-10-251-71-21.ec2.internal-8-threads.log: avg: 0.0448s
ip-10-251-71-21.ec2.internal-16-threads.log: avg: 0.0944s
ip-10-251-71-21.ec2.internal-32-threads.log: avg: 0.2002s
ip-10-251-71-21.ec2.internal-64-threads.log: avg: 0.4176s
ip-10-251-71-21.ec2.internal-256-threads.log: avg: 3.1390s
Hope that’s legible, if not i’ll create a new post out of the latency data.
July 2nd, 2008 at 11:03 pm
[...] bookmarks tagged concurrent mysql proxy 0.6.1 performance tests saved by 3 others Phillychesse bookmarked on 07/03/08 | [...]
July 4th, 2008 at 10:31 pm
Just a quick follow up on the problems I ran into at 512 connections — it seems as if you need to check your max open file descriptors setting (ulimit -n) before running mysql-proxy. two descriptors per expected connection. Mine was set to 1024 which is why i could not get more than 512 connections.
August 17th, 2008 at 8:16 am
Gang - I am not commenting on any blogs - if someone is posting on your blog saying it’s me - it’s not… The joys of trying to help
August 18th, 2008 at 1:32 pm
I read a simliar post just the other day by Sandra Kosineck but yours is much better.