Under heavy load (~10k+ queries per seconds) our mysql server appears to be preventing some connections from being accepted.

The max_connections is more than sufficient, and it never reaches it before it starts preventing connections. It is set to 2000, and never goes past 500 connections.

The intermittent error we get is:

FastCGI-stderr: PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11)


The more qps the server is handling the more likely we are to get the error. It is nonexistent for us at anything below 7000 qps, but once it goes past that it seems to rapidly increase in connection errors. There is still ~70% idle on the cpu and still occurs when there is 10+ gigs of ram free.

We've amended our php scripts to retry connecting 1-10 times, and it usually connects, however this is drastically slowing our site down since things are now trying multiple times to connect.

I have spent a lot of the past few days tinkering with settings in my.cnf and have come up with nothing that even improves it at all.

Thanks in advanced for any help!

Seeing as no-one has responded yet I thought I'd ask:

• Are you running the queries on a single server or a farm of database servers where read queries get farmed out to multiple servers?
• Are you using solid state disks?
• What type of hardware do you run this on at the moment?
• What is your application? 10k queries per second is a lot so perhaps the application can be redesigned.

are you using connection pooling consider pconnect in place of connect

The queries are on a single DB on the same server as the web server. We are not using solid state disks, our set up right now is 4x6core cpu+32 gigs of ram+4x15k rpm drives in raid 10.

On the bright side we think we fixed the errors after a bit more tweaking. The issue, however, was NOT related to mysql, and was a misconfigured setting in our web server/php which were causing these issues by limiting the number of php instances that could be run, increasing this to approximately what max_connections were seemed to stop the error. We also set mysql.connect_timeout = -1 in php.ini which (I think) helped before we adjusted the web server configuration.

Unfortunately, we didn't gain as much speed as we hoped by fixing these errors so now we are looking at more optimizations we can do. Our app is a game with 1000s of simultaneous users. We've spent a lot of time reducing queries and making the queries as efficient as possible but as traffic increases we are looking for more optimizations we can do.

Yes we did try pconnect unfortunately it offered no benefit for us.

If you are approaching the max throughput on your system then it might be worth thinking about how to possibly split your database up across separate servers ie having different zones in the game on different database servers. I also read this comparison regards SSD vs RAID vs RAM disks which may be of interest. Oddly even the RAM option was only managing 17k trans / min which makes your figure of 10k trans / sec look surprisingly fast.

How would a couple of 1000 users generate 10k queries / sec? I'm not sure how these types of games work but wouldn't every single user have to all move, grab new weapons and shoot at the same time to generate this number of queries? (I'm just curious).