homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL connections
Server gets too many connections
paperl




msg:1290678
 3:03 pm on Nov 13, 2005 (gmt 0)

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




msg:1290679
 3:15 pm on Nov 13, 2005 (gmt 0)

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




msg:1290680
 3:25 pm on Nov 13, 2005 (gmt 0)

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




msg:1290681
 3:58 pm on Nov 13, 2005 (gmt 0)

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




msg:1290682
 12:45 am on Nov 14, 2005 (gmt 0)

and Welcome to WebmasterWorld paperl

paperl




msg:1290683
 11:55 am on Nov 14, 2005 (gmt 0)

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




msg:1290684
 10:51 pm on Nov 20, 2005 (gmt 0)

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




msg:1290685
 3:04 am on Nov 21, 2005 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved