HAProxy community

Php application slow where use haproxy

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

global
    maxconn         5000
    nbproc          5

defaults
retries 3
option redispatch
timeout client 120s
timeout connect 10s
timeout server 120s

Galera Cluster Frontend configuration

frontend galera_cluster_frontend
bind 0.0.0.0:3307
mode tcp

log global
option  dontlognull

option tcplog
default_backend galera_cluster_backend

Galera Cluster Backend configuration

backend galera_cluster_backend

bind 0.0.0.0:3307

 mode tcp
 option tcpka
 
option tcplog
option log-health-checks
retries                   3

balance roundrobin

balance leastconn
server db-server-01 e2e-34-201:3306  check weight 1
server db-server-02 e2e-34-202:3306  check weight 3
server db-server-03 e2e-34-203:3306  check weight 2

Please share solution asap.

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.

Still looking…

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

My haproxy.log doesn’t report anything except the stop/start logs.
Not sure yet how to configure more logging there (option tcplog is enabled)

See the following post and replace http with tcp:

I suggest you:

  • share the output of haproxy -vv
  • share your configuration
  • 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