For one our project I needed proxy solution, and mysql-proxy is one of well-known, so it was logical first choice. The obvious question which come in mind is what performance penalty we have using mysql-proxy, version 0.7.1.
So it is easy to test. (By the way sysbench recently was pushed to Launchpad, see lp:sysbench, and Percona is going to be active developer of this project and scripting benchmarks).
I took lp:sysbench with LUA script oltp_complex_ro, and tested for couple connections, here are results (in transactions per second, more is better)
| Threads | MySQL-5.0.77 | MySQL-proxy+MySQL-5.0.77 |
| 1 | 660.02 | 349.86 |
| 2 | 1158.66 | 477.77 |
| 4 | 1223.84 | 485.21 |
| 8 | 1224.22 | 455.69 |
| 16 | 1109.72 | 441.55 |
| 32 | 1059.23 | 419.05 |
| 64 | 909.98 | 414.30 |
| 128 | 882.46 | 406.28 |
ok, and let’s look on response time (for 1 thread).
MySQL
1 2 3 4 5 | per-request statistics: min: 1.31ms avg: 1.51ms max: 5.30ms approx. 95 percentile: 1.56ms |
Proxy+MySQL
1 2 3 4 5 | per-request statistics: min: 2.04ms avg: 2.86ms max: 6.44ms approx. 95 percentile: 4.30ms |
Well, I expected some penalty of using proxy… but 2-3x times, that’s overkill. Worth to consider if you want to run Query Analyzer with MySQL-proxy on your MySQL Enterprise setup.
There is alternative – Dormando-proxy, which I want to try also, but the problem is it crashed under sysbench load, so I was not able to get any results yet.
Vadim,
It is interesting what peak overhead would you observe ie if you’re looking at simple sysbench.
A lot of web applications have a lot of very simple queries (close to single row lookup by key) and these will show maximum overhead.
On the lower part we probably can try some streaming of large data.
And well…. Compared to this the overhead we observe by using full slow query logging on server is negligible.
Hi Vadim, please benchmark the current trunk. It has a threaded network-io (–event-threads=4) and want for the 0.9 release which will remove the global Lua lock which is limiting the throughput too. Without a Lua script trunk should show a huge difference already.
Hi Vadim!
As you’ve found, the single-threaded implementation of 0.7.1 has its limits. And depending on the workload yes the hit can be up to 75% (which matches quite nicely with worst-case benchmarks I have done in the past).
As Peter points out: It is interesting to look at the overhead curve across different applications.
My rule of thumb for 0.7 performance is: The shorter the query execution time, the higher the overhead will be. This is a direct consequence of 0.7 being single-threaded and event-based, because MySQL Proxy 0.7 can make progress whenever it waits for events, so naturally many short queries will mean that Proxy needs to do a lot of work for each query (because the result packets will arrive very shortly after the query was issued, decreasing the time it can spend on other queries).
Lots of work in Lua scripts will also block progress on other connections in 0.7.
For 0.8 (current trunk on Launchpad) the picture is different: The multithreading in it applies to all network communication, even when one thread is spending time in Lua (although in 0.8 only one thread can be in Lua-land at any given time – lifting this is scheduled for 0.9 as Jan says). Thus Proxy can make progress on up to as many connections as you have event-threads running. Connections can wander between threads, so on average you can have as many concurrent active connections being serviced as you have threads.
We are ramping up on regular scalability testing for it, but are not quite there yet (waiting on some infrastructure to be ready) but once we are we will be publishing results regularly.
If you are interested, we’ve spent quite some time improving the code and architecture documentation in the current trunk (you’ll need doxygen/graphviz/mscgen). It explains the architecture clearly, we are hoping 😉
In case you find any bugs, please report them at http://bugs.mysql.com in the Proxy component. If you have additional questions, we are on #mysql-proxy on freenode, as well.
cheers,
-k
It’s worth noting that this is an improvement over the last time I remember Vadim trying out mysql-proxy. Maybe I’m wrong, but I remember it crashing so we couldn’t really benchmark it? Vadim, do you remember that, or am I telling lies?
Have you looked at DBD::Gofer from the Perl world? It doesn’t support transactions but I’d be interested to see how it stacks up.
What about using ha-proxy? Its not an sql specific proxy but seems like it could balance the connections just as well as anything.
Jan,
I will try recent trunk.
Current run was without Lua scripts, but actually I am looking to add some scripts, we I need Lua…
Baron,
It was long time ago and was very early releases, so I do not remember all details. Can’t say it was in previous release.
Erik,
I did not try ha-proxy, it seems it does not support functionality I need, I am looking to handle coming queries in one specific way.
Hi Vadim,
Try Tungsten Connector. (http://www.continuent.com/community/tungsten-connector) It’s written in Java and schedules threads efficiently across multiple cores.
Also, what do you need Lua for? If you provide a use case we can look at how to get it for you. We have a lot of work afoot in this area, for example implementing session consistency load balancing.
Cheers, Robert
BTW Vadim, it looks as if you are hitting the proxy math problem I talked about at your recent and highly esteemed “Performance is Everything” conference. (Slides: https://s3.amazonaws.com/extras.continuent.com/Tungsten-Proxy-Architectures-2009-04-22.pdf)
Hi,
Informative post, but it wasn’t clear to me whether the you used some kind of connection pooling? I.e. is the performance bad because of connection creation, or will the penalty effect each query run thru an open connection.
This is disappointing. So … what are we supposed to use or do for loadbalancing?
Yes, goog question…
I have been adviced not to implement using two databases in my webapplication just to split between writes and reads (master / slaves). So I thought mysql-proxy could be great…
If it leads to 2-3x overheads, is there another alternative to deal with that ?
I’m running MySQL 5.5 with MySQL Enterprise Manager 2.3.6
I was wondering how many use mysql proxy “in the middle” in order to get Query Analyzer functionallity? This is really all I want it for. I’m at a crossroads trying to decide if going forward want to start implimenting mysql agent “in the middle” on all our production databases to get the Query Analyzer functionality. The main drawbacks I’ve found are the following:
– Upgrading the agent requires downtime
– Not able to use IP level access control on user accounts. Have to use ‘webuser’@’%’ for example.
– Not able to see what servers users are logging in from since all logins are from the agent and are displayed as “localhost” in the processlist.
– If agent crashes, access to database is lost.
– Increased overhead.
I’m just not sure it’s worth it!!
Just wondering what others are doing. Is MySQL Agent used “in the middle” commonly in production databases?
Thanks!
Hi.
Is there an alternative proxy now? The main features I want are the following:
– Connection pool.
– Failed Over.
– Sharding.
– Query Router.
In addition it is better support stored-procedure and transparent between Mysql Server and application client.
SQL Relay looks good, but it is not transparent, must break the current code.
Tank,
I am not aware about opensource/free solution.
From commercial side you may take look at ScaleArc.
Look also at Tungsten Enterprise from Continuent. Its Connector has strong failover and query routing capabilities. I do not know of any open source solutions that approach either Tungsten Enterprise or Scalarc capabilities at least for MySQL. (Disclaimer: I work for Continuent and Tungsten is our product.)
Thanks.
BTW, have you tried Mysql Proxy 0.8.2? How is it?
Tank,
I did not try, but I am not aware about performance fixes that could improve scalability in this version.
Hi,
Wanted to use mysql proxy for load balancing my slaves for read. Is there a hardware alternative to mysql proxy that i can use to distribute load between my slaves (reads).
thanks..
Hi,
Still waiting on some feedback. Would really appreciate if anyone knows about a hardware load balancer that can manage load balancing among slaves, as I am not keen on using mysql proxy.
Thanks
Hi,
Can anyone throw some light on mysqlnd_ms, The mysqlnd replication plugin 1.1.0 release.
We are working on it instead of mysql proxy as a load balancer.
Hi hotbollah!
yeah. But it seam to support PHP only, not my C++.
Hi Tank,
You are right about its support for php, infact it comes in built in with new php4 version.
It suits my need. But I was more interested in Hardware based load balancer due to heavy traffic, i am still afraid it might crash under load. Although not tried stress testing on it, would do once we apply it fully on our application.
For you i guess mysql proxy would be a suitable option, spoken to a solution architect at pier1 data centre and she says most of our clients are using mysql proxy…..
Are you aware of any hardware based load balancer, do let me know if you do
cheers.
Hotbollah,
thank you for your post.
I’m sorry I don’t know about hardware based load balancer.
I am trying to work on mysql proxy, hope it can help.
Hi Tank,
After a long trial we have given up on mysqld, and now moving towards mysql-proxy as a solution for production server. was wondering if you can help in installation and configuration, tutorials or anything, as i tried installing with percona server and it just dosent work, or do anything..
thanks..
Hi,
For people convenience I have posted MySQL proxy installation and configuration script. Feel free to try this out for load balancing MySQL database servers or to do read/write split.
http://blog.goplar.com/#category1 —> here is the detail.
Thanks.
Masood
This mysql proxy documentation says MySQL proxy is in Alpha and not to use in production. Is this a typo?
Hi,
It is in alpha indeed. Try at your own risk. I have seen a client running it on live system. We moved it away to Ha-proxy, depending on your requirement you can either choose Haproxy for load balancing. If you want to have more advance features of mysql proxy like read-write split and do not want to make any application changes then choice is yours.
Thanks,
Masood
Yeah, we just got done doing some benchmarks ourselves. We wanted to move to mysql_proxy from haproxy because we wanted better security in the cloud (on haproxy we have to have these admin level password-less accounts to run mySQL traffic through them). 95% TTLB was about 10% slower with haproxy than direct, but 40-60% slower (higher with more load) with mysql_proxy. Sad panda.
Hi,
Suddenly MYSQL-PROXY seems to be an hot issue here. My last comment on this would be that as a consultancy we moved client away from proxy because of it crashing in very busy production. Just to give you an idea client buffer pool size was over 100GB. If you want to use it please go ahead, at least my recommendations are against it and I think percona would not recommend it either.
Masood
Sounds about right. Yeah, we had two hangs with no error messages or other indications there was anything wrong just in our testing, too. Hrm, I guess read/write splitting and secure proxying is still science fiction.
If Ha proxy or mysql proxy is no good for you then please look into f5 load balancers for load balancing. Read/write can be done at application level aswell. Nginx can also be used for load balancing and is quite efficient and lightweight.
Anything wrong with using CSCO ACE?
> Anything wrong with using CSCO ACE?
Yeah, I can’t get Amazon to stick a couple dozen of ’em in their cloud for me 🙂 We have F5’s and Cisco gear but only in our one physical datacenter. Most of our dbs are in the cloud and part of autoscaling groups.
Besides the security issue, one of the things we were hoping to get out of mysql-proxy was better load balancing (least connection, handling dynamic pools of db servers)… We can just close connections/pools more frequently to get some of that but at scale that’s a bummer.
We have been using mysql-proxy in production for more than a year to load-balance reads (the read/write balancing is done in the application). I don’t have any benchmarks or indications into performance gain/penalty. However, I can say that for our traffic (roughly 200 qps on average), mysql-proxy has been pretty stable and required no maintenance.
Because of the traffic you are getting is bare minimum. I was looking at 20K plus queries per second databases for load balancing.
Hi Masood,
I specifically noted the qps to show that mysql-proxy is NOT under heavy traffic in our platform and to avoid any presumption/confusion. I should also note that we are running this platform on low-end VPSs with 512 RAM. For many, mysql-proxy is still NOT ready for production/prime-time. It’s simply not a mature product. However, for some it can be a good/better alternative depending on the requirements and what’s available.
I’m curious to know how HAProxy is working out for you. In general (not knowing your requirements/setup) at 20k qps, hardware load-balancing (like F5) would be a safer option in my humble opinion.
yes, its for dedicated server 32 cores intel xenon, 196gb ram , buffer pool over 100gb in use, database size is over 500gb, and we have 15 servers now moved to xtradb cluster with each server having virident tachion 1.4 TB drives with flash raid 1.
per server getting around 20k queries, peak time is around 30k queries per sec. altogether we have over 200k qps..
So that was our environment..
masood
yes, sorry using multiple f5’s for load balancing, actually it is discussed above already…..
@hotbollah we use it in production and it’s wonderful if you have any specific questions let me know.
(it handles perfectly thousands of qps on our MMM setup)
sathio,
I wonder someone can recommend mysql-proxy when MYSQL (the makers of it) are telling you it is in alpha and not to use in production. It is not a great matter when you are getting thousands of queries, it is when you crash while getting thousand of queries per second and then your customer will ask the very same question, is mysql-proxy safe…?
NOOO.
sorry if I wasn’t clear, I’m no longer using mysql-proxy, but mysqlnd_ms.