Greeting for day!
In my php application when i use mysql direct connection it work perfectly . But when i use haproxy ip for ha , its take too long for retrieve result.
Ha proxy configuration as following
I have the same issue. I’m using haproxy 2.0.13-2 and MariaDB 10.4.13 on Ubuntu 20.04 VMs.
When connecting directly to my first database server, I get responses about 2 times faster than when I use haproxy (I have 3 db servers of the same size working in a Galera cluster). When I increase the resources on the haproxy VM (CPU and memory), it does absolutely nothing better, suggesting there’s a configuration limit. The VM is never overloaded either, nor does it use a lot of RAM.
If I simply use a different database server for each web server (running a PHP app), it also works (so the Galera cluster works) but I would like to have something more robust.
I’m not using the clustercheck script yet (I’m on Ubuntu and so far mentions of clustercheck were mostly for CentOS), but a good number of tutorials don’t use it and just stop at “it’s working” and never get into the details of speed.
I also get many “MySQL server has gone away” errors once I get to generate a lot of traffic on my web servers (so a lot of requests to the DB).
When checking the (web) stats report, I see that the “Session rate max” is always at 60 or 61 for any given DB instance, even though the web server is waiting for the database responses to come.
I’m now playing around with the maxconn variable (if you use clustercheck from https://github.com/asiellb/mariadb-clustercheck your health check is made in http instead of directly in mysql but I’m not sure this changes anything to the following).
It appears you can set a maxconn for each line of “server” (as per the example at the end of https://www.haproxy.com/blog/play_with_maxconn_avoid_server_slowness_or_crash/) and that allows you to play, indeed, with the max sessions for each server, but it doesn’t really increase the “current” sessions a lot.
Sounds like expected behavior to me, your backend server is probably fast, so you are basically measuring TCP handshake and application protocol round trips, which doubles when you are using an intermediate proxy.
What are the specific latency numbers with and without haproxy?
When haproxy closes a connection for timeouts, this would show up in the haproxy logs. In this case, tcplog mode is recommended.
Latency numbers for an Apache Benchmark command of: ab -c 500 -n 5000 https://theapplication/whatever.php
Direct connection of PHP to DB:
Connection Times (ms)
min mean[+/-sd] median max
Connect: 236 294 75.7 264 696
Processing: 190 1447 1425.7 938 16103
Waiting: 121 1439 1427.0 930 16102
Total: 428 1741 1440.5 1240 16352
With haproxy:
Connection Times (ms)
min mean[+/-sd] median max
Connect: 237 277 79.3 252 682
Processing: 3728 11655 7108.2 9876 60091
Waiting: 3728 11652 7108.2 9875 60091
Total: 4188 11932 7097.3 10148 60755
As you can see, the total load for a page is about 8 times higher with haproxy than without it.
I’m not really skilled at understanding the meaning of all the values here, but this is my stats page about halfway through the ab command above. When I user haproxy (obviously).
benchmark with a single active server in your haproxy backend. You may be load-balancing on haproxy here, which could lead to worse performance when each connection hits a different backend server
I know this old - but I wanted to reply to the error : “MySQL server has gone away”
This is probably because your haproxy is closing the connection before the query is finished. Make sure these settings are > then your query time:
timeout connect 60s
timeout client 900s
timeout server 900s
Since these are query timeouts from your internal servers there is no need to reduce the time like you would rando internet users. 900s should be enough for most queries.
On a side note I noticed my query to haproxy port is also slow by about 6 seconds. If you found a solution please post here.
-John
