[RESOLVED] using mysql_connect and the exceeding connections in the less traffic time?

blackhorse

I am using mysql_connect on dozens sites on mysql rented server. And only one site's traffic is from Europe that has traffic in the early morning hours local time here. But serveral times, I run into too many connections problems in this time period in the past week, how could that happen? I have run this site for over two years without problems. And this site doesn't have the traffic jump in the past week either. Could that be some bugs in the systems of the server?

Thanks!

MarkR

There are several possibilities. Chiefly amongst these are:
1. You're using mysql_pconnect (or some other persistent connection mechanism)
2. Your Apache MaxClients or equivalent is set too high
3. You have some queries which take far too long.

if you're using persistent connections, don't.

Be sure that each page connects only once to the database.

Make sure MaxClients is not too high - this is VERY counter-productive, and its default setting (maybe 100) is often much too high. Most servers can handle a lot less than this, 20-40 is a better setting (depending on traffic).

Try to reproduce it on your dev server, consider getting some specialised hardware / software to generate fake load.

Mark

blackhorse

I didn't use mysql_pconnect, but I checked the server, there are 2 or 3 3rd party systems such as dotproject was using the pconnect. I went to the php.ini and turn off the mysql_pconnect allowed flag to off to force them use the connect instead pconnect.
Going to check that out.
Only some queries "master list etc." may take too long to display with some images to download to browsers. But the execution of the php scripts should be still relatively quick comparing some sites hosting mysql with hundreds of thousdands records.

My concern is about dotproject this kind 3rd party system. It is a powerful fancy system, and it is developed by many top php programmers. But some approaches they use, I am not sure if it will affect my server or not. Such as I have dozens mysql database running on my server. But after I install 1 dotproject, I saw the mysql all database daily back up file size jumps. After I checked out, the huge size is from the dotproject index files. The index files are only for speeding up the search a little bit, right. For the project managment tasks we use, we only have a few dozens projects running. Is it necessary?

I increased the mysql connections limits and open files limits at this moment. I will see it will solve the problems or not.

MarkR

You REALLY need to have some kind of monitoring on your vital production systems.

Install a monitor which polls the number of connection so that you can see when it's getting near. This is not rocket science.

The "SHOW PROCESSLIST" command is very handy.

Consider your application architecture - if possible, ensure that the maximum number of connections that your app servers ever make to the database is less than its configured maximum. If this is not possible, try to make it very unlikely that it will be exceeded.

Mark