mysql_close() important?

Discuss Programming
Post Reply
agent007
administrator
administrator
Posts: 254
Joined: Wed Feb 12, 2003 11:26 pm

mysql_close() important?

Post by agent007 » Mon Nov 01, 2004 12:38 pm

hi,

I've got a couple of PHP scripts running on a webserver (shared hosting) As the number of users increased, the database displays an error:
Too many connections
1040
Could this be due to the PHP scripts? cause I havent specified mysql_close() anywhere...Isint PHP supposed to close all open connections?

I'm thinking maybe its the fault of the hosting company/bad setup....?

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Mon Nov 01, 2004 6:37 pm

Which PHP functions are you using to connect to the MySQL database? You can run into problems if you use the mysql_pconnect vs the mysql_connect and yes it does sound like your ISP is limiting the number of MySQL connections, intentionally or unintentionally. Check over this page for a start (including the reader comments at the bottom of the page which will give other hints):

http://us2.php.net/function.mysql-pconnect

agent007
administrator
administrator
Posts: 254
Joined: Wed Feb 12, 2003 11:26 pm

Post by agent007 » Mon Nov 01, 2004 10:31 pm

hi Void,

Am using the mysql_connect function...

thanks..

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Mon Nov 01, 2004 10:44 pm

Your provider needs to up the connection limit, or limit some sites:
http://dev.mysql.com/doc/mysql/en/Too_m ... tions.html

agent007
administrator
administrator
Posts: 254
Joined: Wed Feb 12, 2003 11:26 pm

Post by agent007 » Tue Nov 02, 2004 5:33 am

Void,

The host has increased the max connections to 500. Very soon the site would have over a million hits...I dont think the 500 is enough. Also, considering that mySQL can handle a maximum of 1000 connections according to that link, are there be any alternatives for mysql to handle the load? I am thinking of setting up a mirror..Is this the best way?

thanks..

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Tue Nov 02, 2004 9:51 am

Wow, I can't imagine needing more than 500 simultaneous connections. Do you share the MySQL server with other people that you have no control over? Can you actually query MySQL and see who is using all the connections? You might want to add mysql_close() statements after each or your queries to make sure they close. 500 simultaneous connections is an awful lot. Remember, you can have a significantly higher number of people hitting your site at the same time than you have number of MySQL connections because ones you've made your query and displayed the connection should close. Clustering is an option but I would certainly investigate why so many connections are already required and verify that nothing is broken.

agent007
administrator
administrator
Posts: 254
Joined: Wed Feb 12, 2003 11:26 pm

Post by agent007 » Tue Nov 02, 2004 11:07 am

The site receives about 2000 unique visitors per day..So the 500 SQL connections is pretty low. I've just finished adding the mysql_close() to the PHP scripts, so hopefully that will help. The hosting company doesnt provide SSH/terminal access so there is no way for me to find out whats eating the resources. According to them, the SQL is on a standalone box. They have also increased the RAM to 2GB.

So, since the RAM is increased can the MYSQL connections be increased too? Like maybe 6000? or would the OS crash?

TIA

User avatar
Void Main
Site Admin
Site Admin
Posts: 5716
Joined: Wed Jan 08, 2003 5:24 am
Location: Tuxville, USA
Contact:

Post by Void Main » Tue Nov 02, 2004 11:22 am

agent007 wrote:The site receives about 2000 unique visitors per day..So the 500 SQL connections is pretty low. I've just finished adding the mysql_close() to the PHP scripts, so hopefully that will help. The hosting company doesnt provide SSH/terminal access so there is no way for me to find out whats eating the resources. According to them, the SQL is on a standalone box. They have also increased the RAM to 2GB.

So, since the RAM is increased can the MYSQL connections be increased too? Like maybe 6000? or would the OS crash?

TIA
Depending on what sort of access your visitors are getting a 500 connection limit should be *more* than enough to handle 2000 unique visitors a day, even if those visitors are hitting your site 24x7 which I am sure they are not. 2000 unique visitors is a very small number. Remember, a connection should be opened and closed only during the page load. Once the page is loaded the connection should be closed. I would think 500 connections would normally be enough to service tens of thousands of unique daily visitors with ease. I have to think something is wrong in either the MySQL configuration, the Apache configuration, or in the PHP coding, but again you haven't given very many details as to what sort of application we are talking about.

Post Reply