too many connections

[Anon]

something like this has been posted before, but none of them have had similar symptoms to what I'm experiencing.

Most everyone who mentions this error says it's intermittent - once in a while, during high traffic times, the server will say there are too many connections, and if a user waits a moment and tries again, it will work.

For me, it works consistently (for everyone) for a certain time, and then after that, it tells everyone that there are too many connections. The only way I've found to fix it, and only temporarily, is to restart mysqld. I haven't yet investigated to see whether the mysql daemon contains locked processes, or what is going on inside mysql; I thought someone here might have a suggestion, as the server is remote and I find asking you people easier than installing an SSH client to investigate further.

Relevant information: This is a very high-traffic site (~20-30GB of dynamic text alone per month). I know that PHP automatically closes database connections when finished, but I'm wondering if closing them ASAP (which, right now, I don't do) might increase efficiency. Also, I'm not using persistent connections. Is there a significant reason why I should be?

Also relevant is that I have never had problems like this before, until today, and today it's happened four or five times. I even restarted the server once. By the way, the server is freeBSD, 1.13GHz, 512MB RAM. Hope someone can help me.

[crimaniak]

Don't use mysql_pconnect(), where is the bug.
make simple page with mysql_pconnect(), run it then look mysql processlist to see if it is pconnect() bug. if there is one more connection after each run change pconnect to connect.

[[deleted]]

pconnect is not the issue.
Pconnect re-uses connections, it does not increase nor decrease the number of connections.

If your scripts need 105 connections, you will need 105 connections, there's nothing you can do about it.

The only solution to this is to increase the max number of connections that mysql will allow. (and make sure you have enough memory to deal with them, 2.5-3MB per connection!)

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

I confess I was suspicious that your answer was not going to solve my problem - for even in the middle of the night, when there is little traffic, the server would stop accepting connections.

So, I buckled down and installed an SSH client to monitor the process list on the mysql server, and lo and behold, a single process would "stall" on "copying to tmp table", and each successive process would lock, filling the connection buffer. if I killed the offending process, the rest would complete just fine and dandy. The one causing the problem was always a very long SELECT with multiple joins on a table that is nearly 1GB in size, and over 13000 rows.

naturally, I suspected there was a problem with the copying to the temp table, and the first thing I thought of was disk space. Running df revealed that mysql was logging to a 3GB partition, and that the partition was nearly full. I deleted the now unnecessary log files, and now the server is running fine. So, next time someone has this symptom, have them check their disk space.

[Anon]

it seems the problem was an inefficient query, hogging too much processor time and memory. still not a problem with max_connections, though.