We have been receiving some major problems with our DB server as of late. It keeps crashing because it reaches Max Connections. I know the simple answer is to up the Max Connections but I think we have a bigger problemt then that.

I have installed MyTop and I see ALOT of sleeping connections. Does anyone know the full definition of why I see sleeping queries? I think it is because we are using a persistant DB connection and the code doesn't properly finish the query (e.g. $qry->finish;).

To set up ou DB connection this is the code we use:

my $db = DBI->connect("DBI:mysql:$dbname;host=$hostname",'','') || die "Couldn't connect: " . DBI->errstr;
return $db;

Any advice would be greatly appreciated.

Thanks,

Dan Sampson

Are you using 'disconnect' in your script?

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

BTW, when you do;


... that will return whatever is held in $db at the time. Anything after that will not be executed.This is more than likely where your stray connections are coming from :(

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

We use $qr->finish. But I am concerned that it is only in a few spots. At the end of each file we also say $db->disconnect.

Thanks.

Hi,

Persistent connections means just that, the connection will last between multiple requests. If you are using Apache::DBI, it actually overrides $db->disconnect, and turns that into a no-op.

It's perfectly normal under mod_perl or other persistent environments to see sleeping connections. You should set your max connections in MySQL to:

1 * number of unique user/pass/database/dbi_opts * max number of children in apache

So if you have two applications, one connecting to database foo, and the other to bar, and your MaxClients setting in Apache is 150, then you need your max_connections in MySQL to be 300 to ensure you don't run out of active connections.

Let me know if this doesn't make sense.

Cheers,

Alex
--
Gossamer Threads Inc.