Mass SQL connections, plz help!

[Socks]

We're having this issue intermittently where SQL fucks up the page and gives a "too many connections" error. It's saying we have around 750,000 current connections right now. What's the deal with that shit?!

[baddog]

Possible cron jobs on the server in one or more of your scripts?

[m4tt]

Are you using persistent connections (pconnect)?

[Socks]

no cron jobs, and it's around 75,000 connections not 750, sorry.. Still it's causin us problems..

[Socks]

Yes we're using pconnect 1:1 (http to mysql)

[m4tt]

I believe that's your problem I've had the same problems in the past and using regular connections instead of persistent has solved them.

[Gasper]

try optimizing your tables

[Socks]

Is there a way to put some kind of timeout limit on the persistant connections? Apparently changing from persistent to regular would be a ton of work

[m4tt]

You really should do the work and change them...


"You are probably using a multi-process web server such as Apache. Since database connections cannot be shared among different processes a new one is created if the request happen to come to a different web server child process."

Edit: That quote is from this page, assuming you use PHP.. read up..

http://us3.php.net/manual/en/feature...onnections.php

[Socks]

And they never close? I think that's our problem.. We just reset it all, we have 53 people on our website, and 87 connections.. They will all stay open

[Socks]

Persistent connections are SQL links that do not close when the execution of your script ends.

okay yeah, read first, ask questions next.. ;) Sec

[m4tt]

They will not close unless you kill them manually.. i've had this problem on Windows, Linux, and FreeBSD. Just don't use them ;)

[Socks]

Thank you for your help man, it's really appreciated.. We're gonna stop using them. I think it makes sense for a website like GFY where you have the same people logging in multiple times staying on the site for hours at a time, but for our site it just doesn't make sense.

[m4tt]

What language are you using? PHP?

It's very simple to switch from pconnect to regular connections with PHP. They work exactly the same way it's just a matter of replacing mysql_pconnect with mysql_connect

[KidCock]

From experience, persistent connections never worked very well for us. Even using mysql_pclose() in PHP didn't always work 100%.

You are definately probably better off with normal connections for your site. Any important script however should use pconnect so that it won't "Lost conneciton to server during query" on you.

Also, if it is a lot of work to change the pconnect's to connects, you are definately not building your code in a modular fashion ;>....For instance I have a db.inc which is basically a DB wrapper...thus I would call open_db("update") or open_db("select") for instance, the actual mysql_connect is in db.inc =)

Makes modifying things a LOT easier.

In your case however, you can use "rep" to just replace like so:

rep -R 'mysql_pconnect' 'mysql_connect' *

from your basedir.

[m4tt]

You should be using PEAR (pear.php.net) anyways