364 Mysql connection on the server !

Anon

Hi,

I'm working on a domain name that receives high number of visitors per day.

The site works with PHP/MySql application that is designed specially for it.

We're suffering from the high number of Mysql connections openned. When I started to investigate I found that, every page that is openned include the file (database.inc) that connects to the database using: mysql_connect.

I was told by someone that if you manage to make global variables to connect, then you will not open as much number of connections as you get.

Can anybody please advice.

Thanks

Note: we don't prefer to use pconnect, according to our database, pconnect will not be good.

metin

But it really is "mysql_pconnect" you need!
Why would it not be good?

Anon

I heared that when I need to do lot of queries at the same connection, pconnect shouldn't be good for you.

Any recommendation ?

mithril

Connections called by mysql_connect() are auto-closed by PHP at the end of the transaction (ie. once the page load is complete). You can also manually kill the connections with mysql_close().

Creating a global to store your connection's link id across page loads is impossible in that scenario. Unless you're on an extremely high-volume server (ie. 1,000,000+ page loads/month) you should see a big performance hit. Just make sure you're calling a connection at the beginning of the transaction and reusing it throughout that page load. A global may be of use to you there.

-geoff

mithril

Sorry that should have been \"....should not see a big performance hit.\".

-geoff

metin

I think that you should use persistent connection if possible!
Read more about that here:
http://www.php.net/manual/en/function.mysql-pconnect.php
http://www.php.net/manual/en/features.persistent-connections.php

Anon

I used it now, and still the number of connections are extremly high, 307 connection !

Anon

Actually, the site get more than 1 Million page view per week, not per month.

Now if you suggest using global variables, is there is any hints on how to do that ? Any thing to read about that ?

Thanks for your help

metin

I am just curious about what you mean by
"Creating a global to store your connection's link id across page loads "?
How would you do that?

metin

The question is how you run Php? OS, http-server?
You must run it as a module!
Otherwise persistent connections are not available!

Anon

I'm using Linux Box, with Apache server, any other info ?

metin

If you are running Php as a DSO module,
then persistent connections should be working!
In this case you should have as many
p. connections as Apache child processes!

Anon

Execuse my ignorance, what is DSO module, and how can I check from the server to ensure that service ?

Anon

Just switching to pconnect won't reduce the number of connections. If three hundred people are hitting the script concurrently, three hundred connections will be needed.

http://www.hvt-automation.nl/yapf/faq.php?cmd=viewitem&itemid=26

First up, trim down the duration of the connect/close as much as possible - don't let a script hold an open connection if it's not being used.

After that, it becomes a matter of improving the hardware and software environment your webserver and DBMS are running in.

Anon

Call the phpinfo-function and look after the value of "Server API" in the output!
If it sais: "Apache" - that means that that Php runs as (DSO) module to Apache!
If it sais: "CGI" - that means that Php runs in CGI mode and in this case
persistent connections will not work!

I agree with what Grumbler writes in his post.
Also, if you are running the database on the same
server as the Apache/Php, you could separate them!
Apache can be also optimized further!

In the end, I don't think that having many open connections to
the database is big problem as long as the hardware/environment
dimensioned for that!

Cheers,
Metin