mysql-proxy, urgh performance and scalability ?

PREVIOUS POST
NEXT POST

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)

ThreadsMySQL-5.0.77MySQL-proxy+MySQL-5.0.77
1660.02349.86
21158.66477.77
41223.84485.21
81224.22455.69
161109.72441.55
321059.23419.05
64909.98414.30
128882.46406.28

ok, and let’s look on response time (for 1 thread).

MySQL

Proxy+MySQL

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.

PREVIOUS POST
NEXT POST

Comments

  1. says

    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.

  2. says

    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.

  3. says

    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

  4. says

    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?

  5. Michael Peters says

    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.

  6. erik says

    What about using ha-proxy? Its not an sql specific proxy but seems like it could balance the connections just as well as anything.

  7. Vadim says

    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…

  8. Vadim says

    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.

  9. Vadim says

    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.

  10. says

    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

  11. says

    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.

  12. K. Heraud says

    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 ?

  13. Mike says

    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!

  14. Tank says

    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.

  15. says

    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.)

  16. hotbollah says

    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..

  17. hotbollah says

    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

  18. hotbollah says

    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.

  19. hotbollah says

    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.

  20. Tank says

    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.

  21. Hotbollah says

    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..

  22. says

    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

  23. says

    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.

  24. says

    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

  25. says

    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.

  26. says

    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.

  27. says

    > 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.

  28. Omid says

    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.

  29. Omid says

    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.

  30. says

    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

  31. sathio says

    @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)

  32. says

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *