MySQL connections

Server gets too many connections

[paperl]

Hello all,

I have a problem with MySQL connections on my server. There are only about 10 different databases that are regularly accessed, using PHP scripts. This is done using mysql_pconnect, so the connections should be persistent. From what I learned, this would mean that if another connection is necessary for the same database/user combination, an old, already existing connection can be used without the need for establishing a new one. This sounds good to me, because I have only few databases that are often accessed, so this way there are only few connections open. Recently, however, I had a major problem when there were too many connections. There were more than the maximum, so no more connections were allowed. When I showed the processes, I saw that there were a lot of the same connections (same user accessing the same database). I do now understand why. If I use persistant connections, there is no need to create all these duplicate connections, right?

I now kill off manually the connections every now and then so that there are not too many, but that's not a long term solution. I hope somebody knows how to limit the number of connections automatically.

Thanks,
Paul

[charlier]

You might want to reconsider using pconnect, from the php editable manual:

mightye (at) mightye (dot) org
03-Feb-2004 04:19
Normally you do NOT want to use mysql_pconnect. This function is designed for environments which have a high overhead to connecting to the database. In a typical MySQL / Apache / PHP environment, Apache will create many child processes which lie in idle waiting for a web request to be assigned to them. Each of these child processes will open and hold its own MySQL connection. So if you have a MySQL server which has a limit of 50 connections, but Apache keeps more than 50 child processes running, each of these child processes can hold a connection to your MySQL server, even while they are idle (idle httpd child processes don't lend their MySQL connection to other httpd children, they hold their own). So even if you only have a few pages which actually connect to MySQL on a busy site, you can run out of connections, with all of them not actually being used.

In general use mysql_connect() for connecting to MySQL unless that connection takes a long time to establish.

Also if you do use pconnect you might want to look at the use of wait_timeout/interactive_timeout.

[paperl]

Thanks!

Seems like mysql_connect is the way to go then...

Questions: if mysql_pconnect doesn't use already existing connections, what's the use of it?

[charlier]

I think its more oriented towards applications where one user logs in and stays connected for a long time, like what might happen with a storeroom inventory control system.

Not really web apps.

[jatar_k]

and Welcome to WebmasterWorld paperl

[paperl]

Thanks all!

I've been changing my mysql_pconnects to mysql_connect and the number of connections is decreasing indeed. BTW if pconnect should be used in like inventory systems and not in web applications, quite weird that Dreamweaver automatically uses this in the connection scripts, right?

[Herenvardo]

Guess you already know, but make sure to call mysql_free_result() after you finnish using the data.
[quote]BTW if pconnect should be used in like inventory systems and not in web applications, quite weird that Dreamweaver automatically uses this in the connection scripts, right? [/quite]
I've been working with dreamweaver for a while and never noticed before. Anyway, i've worked with it a lot and has not given any problem.

Greetings,
Herenvardö

[paperl]

I did know about closing the connections, but thanks for replying. On my pc Dreamweaver always creates Connection files, and it uses pconnect there. Maybe a specific setting on my machine then, if you don't have the same.