We have a large application primarily written in PHP which connects to a MySQL database. We are experiencing “MySQL Server has gone away” and “Too many connections” errors in our application, we think the problem is how we are connecting to the database and managing those connections and so I was wondering if anyone could help us out.

The MySQL database consists of one master server which handles reads and writes and 4 slave servers which handle reads. The application creates two persistent connections (one to the master and one to the slave load balancer) at the beginning of each instance and uses them throughout the course of the instance. In the MySQL config we have set the connection lifetime to 10s as otherwise connections were not being dropped. But this leads to another problem – the application creates a connection to the slave server and makes a request, then the application does something else, then it needs to use the connection to the slave server again but by this time the connection has expired resulting in the “MySQL Server has gone away” error. If we increase the connection lifetime then we get more “Too many connections” error.

So, at the moment it's a case of finding a balance, but I think that we have something more fundamental at fault in the application. The two possible solutions I came up with are:

1.For each request, get a new persistent connection and then close it immediately after fetching the data. As the connections are persistent and therefore cached, there will be very little overhead in creating new connections for each query. It will allow us to have a short timeout to clear up zombie connections.

2.Don't use persistent connections, create new connections to the master and slave in each thread and close them at the end. This will allow a longer timeout as the connections will be limited to the number of php threads, thus eliminating the “MySQL server has gone away” error.

To be honest we're shooting in the dark here, so we'd really appreciate any help or advice!

Hi

I assume you have master and 4 slaves setup to replicate the data from the master to the slaves.

For that setup, you should only write to the master, and read from the slaves. (ie never read from the master)

Also have you looked at mysql proxy ? as this will balance the reads evenly between the Slave DB's.

Are all the servers connected via a LAN ? (so no delays)

Thanks

Hello.

Thanks for the reply. The slaves replicate from the master, there is a load balancer for the slaves and everything is on the same lan - but we do fairly often read from the master - for example immediately after an insert if it's possible that the slaves will not have updated in time.

For example; if inserting a new "thing" to the master server, the next page displaying the newly inserted "thing" will then read from the master.

Hi

In my experiance I have found replication to be almost instant. but I see your dilemma.

Have you tuned mysql to accept more connections ? (I assume you have)

Thanks

Yes, we have tuned the number of connections to db server. Currently we can handle 1000 connections to the master and 150 to each slave. We cannot set more to the slaves because with more they start to lag behind the master.

We are using linux load balancer for loadbalancing db requests, would it be a big difference using mysql proxy?

I think the hardware arrangement is ok, the problem is how we're connecting and managing those connections in the application that's causing the problems.