MySQL Connection Failed?

[Anon]

Hi,

Today as I was working on my site as usual. I suddenly came across the following error message:

Warning: MySQL Connection Failed: Too many connections in /home/xxxx/www/xxxx/SharedFunctions.php on line 21
FATAL ERROR: Cannot connect to database server!

Inside the file SharedFunctions.php there is a function that connects to the MySQL database. On the top of every viewable page, I call the function to connect the MySQL. Occasionally, when I try to access any of those pages, I would get the above message. If I reload it, it won't show up again but it's not a good thing to let my visitors see it. I didn't realize this problem before.

Why am I getting this error message? Too many connections? Is that a server problem of my hosting company? Am I putting too much load on the database? My site is still not up yet, so there shouldn't be any traffic at all!

Thanks in advance!

[Anon]

I've not seen this one before in MySQL but it seems similar to something which happens in ASP.

Check that the balance of the database is ok. In other words, whereever you use a "mysql_connect()" call to open a database, when you've finnished with it, be sure to do a "mysql_close()" call as well as stale connections build up over a period of time and you end up with the server not being able to flush its cache.

You get the same problem in ASP if you leave objects unclosed and the server eventually gives up.

Hope this helps.

[Anon]

The server I work on also has this problem. I dont think it is getting so much traffic that the server cannot handle it and I dont know why it's doing that. I have mysql_close() after I am done with the server but the error still comes up almost 10% of the time I try to access a php page and wont just go away after I refresh, usually I have to wait about 20 seconds then refresh to get the page to load right. Could there be any other cause other than not including mysql_close()? or any solutions?

[Anon]

I've seen this happen only with mysql under
windows (not unix) my general strategy has been to use php as a module and allow permanent/persistant connections for users (for speed that is)

try running mysqladmin flush-hosts (cleares it up fast)

try running mysqladmin processlist (this will show you the actual connections open)

now for mysql_connect() the session is closed automatically when the script finishes executing unless you pre-maturely close if with mysql_close();

for mysql_pconnect() the connection stays there in "Sleep" mode until another run of the script takes it from sleep and uses it again (it cannot be closed by mysql_close())

So long as your webserver is running (w. PHP modules in it the pconnection is open. Even if nobody is hitting the site. augmenting this number would help if you had a large trafficed site, or consistantly move to mysql_connect vs pconnect and augment that number even more.

Its all about PEAK connections and Sustained Connections. Play w. Variables.

[rosborne]

So, in lame mans terms, what is the real benefit of pconnect?

It looks to me that if you use pconnect, no matter what the max_connection value is set to, you will eventually end up hitting it and having this same problem.

At the start of every page that uses a db connection I have an include file that runs mysql_pconnect etc, and then at the base of that page I was using an include file that ran mysql_close (I know now that this achieved nothing but I didnt know at the time).

I have a couple of high traffic sites that hammer the mysql server all day and night, so need an efficient way of running. Reading above I'm abiously doing it wrong at the moment.

Any help would be great appreciated please.

Thanks,

Richard

[djdecay]

Well mysql_pconnect keeps the connection for the life of the php process,
puts it in a stack, and re-uses it whenever a new pconnect with the same variables is called, so if you had a script
say

-> pconnect open sql
-> send query
-> get data
-> print data

And your sustained user average is 200 to this script you would have 200 open connecitons ready @ any time, without the need to formulate them again.

if you did the same with plain connect and close you 'should' have 0 open connections left and peaked at 200 open connecitons.

no lets say you had a nested script with a require(someotherscript) in it
that needed database connectivity to the same database. if it used p_connect
it would just re-use one of the connecitons available allready if you used connect/close you would have a possible 400 simultaneous connections at peak time with problems of you tracking which one are you closing, unless you wrote your stuff w. assumption that connect is going to be made and so is close at the header and footer of your scripts, so any requires() you put in need not include database connectivity code.

If you have a HIGH Traffic Site, with say 50,000 unique hits a day, find out whats your simultaneous peak connection # to the SQL (meaning all at once)
and then use Pconnect, augmenting your connection limit to that number, just make sure it works because I've seen pconnect (only on Windoze boxes) not find old connections and create new ones for no sake and hit the limit of number of connections allowed. On one site I was getting 1600 hits a day, using pconnect, it maintained 16 open sessions to the mysql server and never hicked up.

The Slash Dot guy knows more about tuning perfomance of PHP/MySQL Apache than I do, I reffer you to his writings somewhere on slashdot.

[rosborne]

So then...
I hope to have an e-commerce site where the products are stored in a MySQL database.

The website has a control panel where the customer can update their products. Not rocket science I would have thought.

However, whilst testing the site with just three users the site hits the max_connections of 300 (?!!?). This was using pconnect.

Their is only 3 pages that query the database, and the display function is decided by the url...for example (products.php?action=full_list&subcategory=4)

I run "show processlist" and see a big list of processes on that sites username and all sleeping, and as I said that site has only three visitors testing the site.

Excuse my ignorance, but whats "slashdot"? Sorry if that makes me sound dumb.

Thanks,
Richard

[Anon]

http://www.slashdot.org

[[deleted]]

"And your sustained user average is 200 to this script you would have 200 open connecitons ready @ any time, without the need to formulate them again."

Not true. If you had 200 webserver-threads you'd have 200 connections. But the number of threads is not equal to the number of users, it is often much higher (one pageview requires many hits, many hits can only be serverd by many webserver threads)

"no lets say you had a nested script with a require(someotherscript) in it
that needed database connectivity to the same database. if it used p_connect
it would just re-use one of the connecitons available allready if you used connect/close you would have a possible 400 simultaneous connections at peak time"

Nope, the 'connection pool' of PHP prevents that. You can only create one connection to database X with credentials Y.
If you try to open another connection with the same parameters, php will give you the exact same connection that you had opened previously. You cannot open two connections to the same database with the same parameters, pconnect or not.

"with problems of you tracking which one are you closing,"
PHP will close all non-persistant database connections when the script ends, you cannot leave a connection open by not closing it.

"and then use Pconnect, augmenting your connection limit to that number,"

Allmost. The max connections of your database should be the max number of threads that your database can start, times the number of different connections you need.
If your webserver can start 250 threads, and your scripts together use 5 different databases with 2 users each, you'd need 25052=2500 connections.

In short: forget all about pconnect as quickly as you can. :-)