homepage Welcome to WebmasterWorld Guest from 54.167.174.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

5+ Year Member



 
Msg#: 10781 posted 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

10+ Year Member



 
Msg#: 10781 posted 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

5+ Year Member



 
Msg#: 10781 posted 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

10+ Year Member



 
Msg#: 10781 posted 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

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 10781 posted 12:45 am on Nov 14, 2005 (gmt 0)

and Welcome to WebmasterWorld paperl

paperl

5+ Year Member



 
Msg#: 10781 posted 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

10+ Year Member



 
Msg#: 10781 posted 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

5+ Year Member



 
Msg#: 10781 posted 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