RE: HAProxy and MySQL

Nearly an extra .1 seems high, but to be fair it doesn’t appear you did much of a test:

        Number of clients running queries: 1
        Average number of queries per client: 0

 

Simulating only 1 client, I wouldn’t expect any performance improvement, and without doing any queries, you are only benchmarking connection time? Sorry, not really familiar with mysqlslap. I wouldn’t be surprised with it being slower, but would expect more of a .001 difference instead of over .1 seconds slower… Thought about using haproxy for load balancing mysql, but haven’t implemented it yet.  

I am not an haproxy expert, but I think “option persist” doesn’t apply for mode tcp. How do the numbers look if you actually have it benchmark multiple queries and simulate multiple clients?      

From: Evgeniy Sudyr [mailto:eject.in.ua#gmail.com] Sent: Friday, August 07, 2009 10:54 AM
To: haproxy#formilux.org
Subject: HAProxy and MySQL  

Hi, I'm trying to use HAProxy as round robin load balancer for 2 MySQL servers. I'm using mysqlslap for benchmarking.

At the moment I figured that load balanced connection is slowest in times. I need explanation from HAProxy experts why ?

There is my config:
cat /etc/haproxy/haproxy.cfg

global

        maxconn 2000
        pidfile /var/run/haproxy.pid
        user _haproxy
        group _haproxy

defaults
        retries 3
        maxconn 2000
        contimeout      5000
        clitimeout      50000
        srvtimeout      50000

listen MySQL 192.168.100.254:3306
        mode tcp
        balance roundrobin
        option  persist
        server mysql1 192.168.100.131:3306
        server mysql2 192.168.100.132:3306

There is my test results:

HA Proxy Load balancer QUERY1

mysqlslap -u root --password=password -a -h 192.168.100.254 Benchmark

        Average number of seconds to run all queries: 0.125 seconds
        Minimum number of seconds to run all queries: 0.125 seconds
        Maximum number of seconds to run all queries: 0.125 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

HA Proxy Load balancer QUERY2

mysqlslap -u root --password=password -a -h 192.168.100.254 Benchmark

        Average number of seconds to run all queries: 0.125 seconds
        Minimum number of seconds to run all queries: 0.125 seconds
        Maximum number of seconds to run all queries: 0.125 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

 MySQL SERVER1

mysqlslap -u root --password=password -a -h 192.168.100.131 Benchmark

        Average number of seconds to run all queries: 0.015 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 0.015 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

 MySQL SERVER2
mysqlslap -u root --password=password -a -h 192.168.100.132 Benchmark

        Average number of seconds to run all queries: 0.015 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 0.015 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

---
Thanks!
Evgeniy Sudyr

Checked by AVG - www.avg.com
Version: 8.5.392 / Virus Database: 270.13.25/2256 - Release Date: 08/07/09 06:22:00