I never ever encountered this issue. However, you should probably increase your hash table width in order to reduce its depth. Using "dmesg", you'll see how many entries you currently have:
$ dmesg | grep '^IP route'
IP route cache hash table entries: 32768 (order: 5, 131072 bytes)
You can change this value with the kernel boot command line parameter rhash_entries. First try it by hand then add it to your lilo.conf or grub.conf.
For example: kernel vmlinux rhash_entries=131072
It is possible that you have a very limited hash table because you have assigned little memory to your HAProxy VM (the route hash size is adjusted depending on total RAM).
Concerning tcp_mem, be careful. Your initial settings make me think you were running with 1 GB of RAM, 1/3 of which could be allocated to TCP sockets. Now you've allocated 367872 * 4096 bytes = 1.5 GB of RAM to TCP sockets. You should be very careful not to run out of memory. A rule of thumb is to allocate 1/3 of the memory to HAProxy and another 1/3 to the TCP stack and the last 1/3 to the rest of the system.
I suspect that your "out of socket memory" message comes from default settings in tcp_rmem and tcp_wmem. By default you have 64 kB allocated on output for each socket and 87 kB on input. This means a total of 300 kB for a proxied connection, just for socket buffers. Add to that 16 or 32 kB for HAProxy, and you see that with 1 GB of RAM you'll only support 3000 connections.
By changing the default settings of tcp_rmem and tcp_wmem (middle param), you can get a lot lower on memory. I get good results with values as low as 4096 for the write buffer, and 7300 or 16060 in tcp_rmem (5 or 11 TCP segments). You can change those settings without restarting, however they will only apply to new connections.
If you prefer not to touch your sysctls too much, the latest HAProxy, 1.4-dev8, allows you to tweak those parameters from the global configuration, and per side (client or server).
I am hoping this helps!
How well does it work if you eliminate PHP from the equation? Use the CLI mysql client to connect to the server. Try it from both the db server itself and from the app server:
time mysql -h localhost -D dbname -u username -ppassword -e ''
Best Answer
The typical reason is that route tries to map the IP addresses in the table into their DNS names - and that can be quite slow because of the vagaries of the DNS system and your network.
You can verify that by passing the
-nargument to route, asking it to display only numbers without doing any name resolution. If that is super-fast, you know that it is reverse DNS lookup that delays things.(The time, in that case, is spent waiting on external systems to respond over the network. The local CPU is pretty much idle.)