Tuesday, October 15, 2013

Simple benchmark on MySQL Proxy vs HAProxy

A Proxy is a software that acts as an intermediary between clients seeking resources from servers.

In the MySQL world, the most common proxies are:
- MySQL Proxy
- HAProxy

While the first one is able to understand the MySQL protocol and provides a lot of functionality, it is often not used in production as tagged as very slow, unstable and CPU hungry.
HAProxy is widely used with MySQL setups as it is said to be very efficient in term of performance and very stable, although it acts as a Layer 7 proxy and doesn’t recognize the MySQL protocol.

The adjectives slow and efficient are meaningless words if not supported by numbers and fact, so let’s try to make some real comparison.



We are going to tests performance of mysqld vs MySQL Proxy vs HAProxy running 2 very simple benchmark :
1) sysbench OLTP read-only;
2) mysqlslap


Benchmarks were performed on a box with two processors Intel(R) Xeon(R) CPU E5-2620 ( 12 cores and 24 threads total ) , running Debian 7.1 , Linux 3.2 and MySQL 5.6.11 .

sysbench benchmark


sysbench table was created as follow:

 sysbench --test=oltp --oltp-table-size=100000 --mysql-user=root --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=test --db-ps-mode=disable --mysql-port=3306 prepare  
 sysbench 0.4.12: multi-threaded system evaluation benchmark  
 No DB drivers specified, using mysql  
 Creating table 'sbtest'...  
 Creating 100000 records in table 'sbtest'...  



Let's test the performance of mysqld without any proxy:


# for i in 1 2 4 8 16 32 64 128 ; do sysbench --num-threads=$i --max-requests=0 --max-time=60 --test=oltp --oltp-table-size=100000 --mysql-user=root --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=test --db-ps-mode=disable --mysql-port=3306 --oltp-read-only run | grep 'transactions:' ; done
    transactions:                        13758  (229.29 per sec.)
    transactions:                        27387  (456.42 per sec.)
    transactions:                        73474  (1224.52 per sec.)
    transactions:                        144594 (2409.78 per sec.)
    transactions:                        231488 (3857.90 per sec.)
    transactions:                        282317 (4704.87 per sec.)
    transactions:                        280961 (4682.07 per sec.)
    transactions:                        282064 (4700.08 per sec.)




Now it is time to try MySQL Proxy.


mysql-proxy was started as follows:

 # mysql-proxy --admin-username=root --admin-password=pass --admin-lua-script=/usr/share/mysql-proxy/admin.lua


# for i in 1 2 4 8 16 32 64 128 ; do sysbench --num-threads=$i --max-requests=0 --max-time=60 --test=oltp --oltp-table-size=100000 --mysql-user=root --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=test --db-ps-mode=disable --mysql-port=4040 --oltp-read-only run | grep 'transactions:' ; done
    transactions:                        15874  (264.56 per sec.)
    transactions:                        30653  (510.85 per sec.)
    transactions:                        56029  (933.78 per sec.)
    transactions:                        60014  (1000.15 per sec.)
    transactions:                        65829  (1096.99 per sec.)
    transactions:                        62449  (1040.50 per sec.)
    transactions:                        56317  (938.00 per sec.)
    transactions:                        51712  (859.88 per sec.)





Now it is time to try HAProxy.

The configuration file is very simple:

 # cat haproxy.cnf  
 global  
     log 127.0.0.1  local0  
     log 127.0.0.1  local1 notice  
     maxconn 4096  
     uid 99  
     gid 99  
     daemon  
     #debug  
     #quiet  
 defaults  
     log   global  
     mode  http  
     option tcplog  
     option dontlognull  
     retries 3  
     maxconn 2000  
     contimeout   5000  
     clitimeout   50000  
     srvtimeout   50000  
 listen mysql-cluster 0.0.0.0:9000  
   mode tcp  
   balance roundrobin  
   option mysql-check  
   server db01 127.0.0.1:3306 check  


HAProxy was executed as follow:

 # ./haproxy -f haproxy.cnf -db  


And sysbench:


# for i in 1 2 4 8 16 32 64 128 ; do sysbench --num-threads=$i --max-requests=0 --max-time=60 --test=oltp --oltp-table-size=100000 --mysql-user=root --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=test --db-ps-mode=disable --mysql-port=9000 --oltp-read-only run | grep 'transactions:' ; done
    transactions:                        18735  (312.24 per sec.)
    transactions:                        37342  (622.35 per sec.)
    transactions:                        70616  (1176.87 per sec.)
    transactions:                        70876  (1181.16 per sec.)
    transactions:                        83456  (1390.73 per sec.)
    transactions:                        87357  (1455.58 per sec.)
    transactions:                        66665  (1110.46 per sec.)
    transactions:                        63809  (1062.12 per sec.)



Indeed, HAProxy seems faster than MySQL Proxy .
So far, the statement "MySQL Proxy is slow" seems correct.
 But mysql-proxy has a very interesting option: --event-threads

Running again MySQL Proxy with 8 event threads:

 # mysql-proxy --admin-username=root --admin-password=pass --admin-lua-script=/usr/share/mysql-proxy/admin.lua --event-threads=8 



# for i in 1 2 4 8 16 32 64 128 ; do sysbench --num-threads=$i --max-requests=0 --max-time=60 --test=oltp --oltp-table-size=100000 --mysql-user=root --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=test --db-ps-mode=disable --mysql-port=4040 --oltp-read-only run | grep 'transactions:' ; done
    transactions:                        16219  (270.31 per sec.)
    transactions:                        26256  (437.58 per sec.)
    transactions:                        46753  (779.16 per sec.)
    transactions:                        83624  (1393.64 per sec.)
    transactions:                        118784 (1979.54 per sec.)
    transactions:                        125283 (2087.63 per sec.)
    transactions:                        127235 (2119.87 per sec.)
    transactions:                        126636 (2109.21 per sec.)


Now, no matter the bad reputation , MySQL Proxy with 8 event-threads seems way faster than HAProxy.

The reason behind that is that HAProxy is not multi-threaded (so is MySQL Proxy by default). Adding more threads can increase throughput.

Maybe who claims that HAProxy is faster than MySQL Proxy is not completely right.


After I completed this sysbench test I stopped haproxy and mysql-proxy pressing ctrl-c on the terminal, and ...

 # mysql-proxy --admin-username=root --admin-password=pass --admin-lua-script=/usr/share/mysql-proxy/admin.lua --event-threads=8  
 ^CSegmentation fault  

... mysql-proxy crashed while closing. I can't consider that stable.



Some readers would have notice that with 1-2 threads the throughput of MySQL without proxies is lower than with proxies. I tried it over and over, and I always get the same behaviour: can't explain that.


In image:



mysqlslap benchmark


With mysqlslap I want to just check the latency.
To do so I created an SQL file with 100k simple SELECT statements:

 # for i in `seq 1 100000` ; do echo "SELECT 1111;" ; done > select1.sql  


Running mysqlslap against mysqld:

 # mysqlslap --create-schema=test -u root -ppass -h 127.0.0.1 -P3306 -c 1,2,4,8,16,32,64,128 -q select1.sql  | grep "Average number of seconds to run all queries"
Warning: Using a password on the command line interface can be insecure.
        Average number of seconds to run all queries: 4.719 seconds
        Average number of seconds to run all queries: 5.090 seconds
        Average number of seconds to run all queries: 5.630 seconds
        Average number of seconds to run all queries: 6.285 seconds
        Average number of seconds to run all queries: 7.514 seconds
        Average number of seconds to run all queries: 11.273 seconds
        Average number of seconds to run all queries: 21.714 seconds
        Average number of seconds to run all queries: 43.227 seconds

Running mysqlslap against HAProxy:

 # mysqlslap --create-schema=test -u root -ppass -h 127.0.0.1 -P9000 -c 1,2,4,8,16,32,64,128 -q select1.sql  | grep "Average number of seconds to run all queries"
Warning: Using a password on the command line interface can be insecure.
        Average number of seconds to run all queries: 6.317 seconds
        Average number of seconds to run all queries: 6.537 seconds
        Average number of seconds to run all queries: 7.205 seconds
        Average number of seconds to run all queries: 13.875 seconds
        Average number of seconds to run all queries: 25.288 seconds
        Average number of seconds to run all queries: 50.841 seconds
        Average number of seconds to run all queries: 107.516 seconds
        Average number of seconds to run all queries: 210.910 seconds

Running mysqlslap against MySQL Proxy:

 # mysqlslap --create-schema=test -u root -ppass -h 127.0.0.1 -P4040 -c 1,2,4,8,16,32,64,128 -q select1.sql  | grep "Average number of seconds to run all queries"
Warning: Using a password on the command line interface can be insecure.
mysqlslap: Error when connecting to server: Bad handshake


So disappointing, it doesn't even connect with MySQL Proxy!
Surely, not something to try on production!


Graphing the number of queries per second:



Conclusions

From these simple benchmarks we can conclude that:
  • HAProxy is fast with few connections, but it has scalability issues;
  • MySQL Proxy has a reasonable throughput and latency , and it scales better than HAProxy;
  • MySQL Proxy is not stable at all
What is really missing is a proxy that:
- is stable as HAProxy
- scales like MySQL Proxy
- is rich of features

7 comments:

  1. I would be interested to know how well proxy works without using the Lua script. You can run proxy like

    ./libexec/mysql-proxy --proxy-backend-addresses=${DB}:3306

    You mention Proxy can be CPU hungry, do you have more info on this?

    ReplyDelete
  2. Roland,

    apparently the admin lua script is mandatory:

    # mysql-proxy --proxy-backend-addresses=127.0.0.1:3306
    2013-10-24 12:46:00: (critical) admin-plugin.c:569: --admin-username needs to be set
    2013-10-24 12:46:00: (critical) mainloop.c:267: applying config of plugin admin failed
    2013-10-24 12:46:00: (critical) mysql-proxy-cli.c:596: Failure from chassis_mainloop. Shutting down.
    # mysql-proxy --admin-username=root --admin-password=pass --proxy-backend-addresses=127.0.0.1:3306
    2013-10-24 12:46:19: (critical) admin-plugin.c:579: --admin-lua-script needs to be set, /lib/mysql-proxy/lua/admin.lua may be a good value
    2013-10-24 12:46:19: (critical) mainloop.c:267: applying config of plugin admin failed
    2013-10-24 12:46:19: (critical) mysql-proxy-cli.c:596: Failure from chassis_mainloop. Shutting down.

    About being CPU hungry, that is what is commonly found googling a bit.

    Nonetheless, here are some numbers, how reported by top, on average.
    Connections / CPU mysql-proxy / CPU mysqld

    1 - 100 - 75
    2 - 195 - 145
    4 - 315 - 275
    8 - 500 - 450
    16 - 535 - 750
    32 - 570 - 830
    64 - 485 - 805
    128 - 485 - 805

    Up to 16 connections , mysql-proxy uses more CPU than mysqld .
    After that point mysql-proxy consumes less CPU than mysqld , but still a lot (imho).

    ReplyDelete
  3. Would be interesting to see if haproxy speeds up in comparison to an 8 threaded mysql-proxy process if you add an "nbproc 8" (or any value greater than one, to use more than one haproxy process) option to the haproxy config file.

    ReplyDelete
  4. If i get you correctly HaProxy is slower than MySQLProxy? I can't read any explanation of why it can be ? Is lib-event faster than libev? Is that related to some bugs ? Did you open a bug report before posting something so opposite with the general common agreement? Could it be one more effect of Amazon or your way of testing a proxy to 127.0.0.1 ? As far as i understand HaProxy single core can saturate any network device but it was never created to saturate a memory bus on the localhost. Hope that we will be in touch in FOSDEM and you tell me more about this and SQLProxy.

    ReplyDelete
  5. Hello Rene. I am very glad you are carrying on with this. MySQL lacks of a decent proxy solution and this one here sounds very promising, especially the query rewrite feature. We 'll definitely give it a try here.

    ReplyDelete
  6. Did you collect process resource usage, like:
    - memory;
    - threads;
    - cpu%.

    Peace,
    R.

    ReplyDelete
  7. Now this article is quite old and I havent checked if this existed in HAProxy back then, but nowadays HAProxy has support for multiple threads.

    Try running with nbproc 8 in your global definition of your haproxy config

    ReplyDelete