Mass SQL connections, plz help! - GoFuckYourself.com

Socks · 07-18-2003, 10:58 AM

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 · 07-18-2003, 10:59 AM

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

m4tt · 07-18-2003, 11:00 AM

Are you using persistent connections (pconnect)?

Socks · 07-18-2003, 11:00 AM

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

Socks · 07-18-2003, 11:01 AM

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

m4tt · 07-18-2003, 11:02 AM

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.

07-18-2003, 11:02 AM

try optimizing your tables

Socks · 07-18-2003, 11:05 AM

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 · 07-18-2003, 11:08 AM

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 · 07-18-2003, 11:12 AM

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 · 07-18-2003, 11:13 AM

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 · 07-18-2003, 11:13 AM

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 · 07-18-2003, 11:17 AM

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 · 07-18-2003, 11:22 AM

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 · 07-18-2003, 11:28 AM

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 · 07-18-2003, 11:30 AM

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

07-18-2003, 10:58 AM	#1
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	Mass SQL connections, plz help! 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?!

07-18-2003, 10:59 AM	#2
baddog So Fucking Banned Industry Role: Join Date: Apr 2001 Location: the beach, SoCal Posts: 107,089	Possible cron jobs on the server in one or more of your scripts?

07-18-2003, 11:00 AM	#3
m4tt So Fucking Banned Join Date: May 2003 Location: San Diaygo, CA Posts: 384	Are you using persistent connections (pconnect)?

07-18-2003, 11:01 AM	#5
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	Yes we're using pconnect 1:1 (http to mysql)

07-18-2003, 11:02 AM	#6
m4tt So Fucking Banned Join Date: May 2003 Location: San Diaygo, CA Posts: 384	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.

07-18-2003, 11:02 AM	#7
Gasper Guest Posts: n/a	try optimizing your tables

07-18-2003, 11:05 AM	#8
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	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

07-18-2003, 11:08 AM	#9
m4tt So Fucking Banned Join Date: May 2003 Location: San Diaygo, CA Posts: 384	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

07-18-2003, 11:12 AM	#10
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	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

07-18-2003, 11:13 AM	#11
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	Persistent connections are SQL links that do not close when the execution of your script ends. okay yeah, read first, ask questions next.. ;) Sec

07-18-2003, 11:17 AM	#13
Socks Confirmed User Industry Role: Join Date: May 2002 Location: Toronto Posts: 8,475	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.

07-18-2003, 11:22 AM	#14
m4tt So Fucking Banned Join Date: May 2003 Location: San Diaygo, CA Posts: 384	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

07-18-2003, 11:28 AM	#15
KidCock So Fucking Banned Join Date: May 2003 Location: San Pedro, Costa Rica Posts: 788	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.

07-18-2003, 11:30 AM	#16
m4tt So Fucking Banned Join Date: May 2003 Location: San Diaygo, CA Posts: 384	You should be using PEAR (pear.php.net) anyways