how do i take the most out of my max_user_connections?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

how do i take the most out of my max_user_connections?

Post by pedrotuga »

I am using mysql and i will continue using because that's what my hosting service provides me.
Today i ran into an unexpected trouble.
max_user_connections exeded :(
I thought it could be a DDOS, but i checked my stats and it didnt look so at all.

I am on a shared server, i think the reason was: the database got slow to repply so a lot of connections were made and... boom! site down :(

I took the site out of the air for a while and now i put is back again and things are going ok, so far...

Ok, i need ideas not to be spammed down.
I never use mysql_close(); should i star using it? isnt the connection closed after the script execution anyway? will i gain some miliseconds by using it?
Any other idea?

I might be doing some comon mistake, if you know those please let me know about them.

thanks in advance
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The longer the connections are open the more chances you have of building up too many connections. So optimizing the queries to execute faster can help and so can terminating the connection immediately after you finish using it for the page. Consolidating the queries to happen near each other in the code can help too.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Agreed - define your dynamic sql, open the connection, execute the sql and close connection.
Been awhile since I used PHP/MySQL, but thinking by doing so you have essentially a disconnected recordset that you can traverse as needed. Anyway, your open/execute/close should just be no more than a "blip".

fv
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

Post by pedrotuga »

I will reviw my code, but i am not sure that will be enugh.
Anyway, i will close all the conections after i get the last resultset needed
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If your shared database server is getting that much traffic it may be time to consider a dedicated server.
Post Reply