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 5 srvtimeout 5 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
Re: Multiple config files
Ahhh 1.3.19 that just came out supports this, yay. Thanks!
Re: Multiple config files
Ahh only 10 files? http://haproxy.1wt.eu/git?p=haproxy.git;a=commit;h=5d01a63b7862235fdd3119cb29d5a0cfd04edb91 I was hoping more like hundreds i.e. 1 per site on a busy server :-) No big deal I see someone made some haproxy_join script but I'll probably make some dead simple shell script that just cats a bunch of files together, until HAProxy could include like /etc/haproxy/*.cfg like someone suggested in an earlier thread. Cheers
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...@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 5 srvtimeout 5 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
Stats as counters for graphing
Is there any way to get the # of queued connections for a backend as a counter so that i can graph it properly? as far as i can tell at any random time i pull the stats it gives me the current number, which isn't that useful, i need to get the # that happened since the last time i pulled the stats. If there isn't a way. feature request? :) -Karl
RE: Stats as counters for graphing
(ignore previos message that had this response replying to wrong message.) I set my to alert if ever non 0 for queue and for my graphs I just use current sessions, and also total connections (graph as delta / sec) for connection rate. I assume you normally have a queue during busy times if you want to graph it? From: Karl Pietri [mailto:k...@slideshare.com] Sent: Friday, August 07, 2009 1:51 PM To: HAProxy Subject: Stats as counters for graphing Is there any way to get the # of queued connections for a backend as a counter so that i can graph it properly? as far as i can tell at any random time i pull the stats it gives me the current number, which isn't that useful, i need to get the # that happened since the last time i pulled the stats. If there isn't a way. feature request? :) -Karl Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.25/2256 - Release Date: 08/07/09 06:22:00
Re: Stats as counters for graphing
The last couple of days we have had a spike in traffic so it has been queueing, but i can't tell for sure if its just general overloaded during peak or if we have some random large spikes (which would be ok). from other tools i'm pretty sure we are overloaded and so adding new machines, but in either case it would be nice to know for sure. We do graph sessions/connections as a delta and that works well, just was wanting the same for queue. -Karl On Fri, Aug 7, 2009 at 11:30 AM, John Lauro john.la...@covenanteyes.comwrote: (ignore previos message that had this response replying to wrong message…) I set my to alert if ever non 0 for queue and for my graphs I just use current sessions, and also total connections (graph as delta / sec) for connection rate. I assume you normally have a queue during busy times if you want to graph it? *From:* Karl Pietri [mailto:k...@slideshare.com] *Sent:* Friday, August 07, 2009 1:51 PM *To:* HAProxy *Subject:* Stats as counters for graphing Is there any way to get the # of queued connections for a backend as a counter so that i can graph it properly? as far as i can tell at any random time i pull the stats it gives me the current number, which isn't that useful, i need to get the # that happened since the last time i pulled the stats. If there isn't a way. feature request? :) -Karl Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.25/2256 - Release Date: 08/07/09 06:22:00
WARNING: Nasty bug in haproxy 1.3.19
Hi all,
With the very kind help from Cristian Ditoiu, I've just isolated a
very nasty bug introduced in 1.3.19 by the fix for the timer drift :
http://haproxy.1wt.eu/git?p=haproxy-1.3.git;a=commitdiff;h=4de2dea8076782d0685cd1f25edfb961d3a9d40c
If you are running a configuration with some missing timeouts, your
haproxy might crash a few minutes after it starts. In fact, it will
crash as soon as it find an unexpirable task in the wait queue, that
it will dequeue and reuse just as if it was queued.
I'll release 1.3.20 and 1.4-dev2 shortly so that we don't stay too
long with this bug. The simple workaround consists in ensuring that
you never have an instance running without timeouts (there are some
warnings emitted upon startup for this). And anyway you should never
do that as you will certainly observe some session leakage over the
time.
In the mean time, and specially for distro maintainers (as I have
noticed that at least Mandriva and Fedora maintainers have already
updated their packages, which is really kind of them), I'm attaching
the patch I'm going to merge (it's running fine right now). I suggest
you merge it into your own 1.3.19 packages for now.
I have another very minor thing to fix which can wait.
More on all of that with the release, which I hope will be out on
Sunday.
Thanks for your patience, and of course, to all the guys who help
chase bugs !
Willy
diff --git a/src/task.c b/src/task.c
index 3094fde..268f8d9 100644
--- a/src/task.c
+++ b/src/task.c
@@ -155,9 +155,13 @@ void wake_expired_tasks(int *next)
* to take care of this. Note that we might occasionally
requeue it at
* the same place, before eb, so we have to check if this
happens,
* and adjust eb, otherwise we may skip it which is not what
we want.
+* We may also not requeue the task (and not point eb at it) if
its
+* expiration time is not set.
*/
if (!tick_is_expired(task-expire, now_ms)) {
- task_queue(task);
+ if (!tick_isset(task-expire))
+ continue;
+ __task_queue(task);
if (!eb || eb-key task-wq.key)
eb = task-wq;
continue;
