Do I have to use $sth->finish within scripts to release resources after executing a query?

I believe that is used for disconnecting from the table you are connected to. It is a good idea to have that code in there. But I am sure Steven or Alex may be able to provide more info on that code.

Regards,

Eliot Lee

The reason I asked this question is, I have a forum script which uses MySQL, basically it works fine. But sometimes it becomes very unstable, from top list, I can see lots of alive forum_show.pl processes running in memory, like:

All the forum_show.pl process are alive and running. This seems to happen regardless of how many users are accessing the board. The system slows down so much that.

Maybe my server provider limits the max. connection to database, so I am thinking of adding $sth->finish within scripts to release resource for other queries(?). But I am not sure if this can solve the problem, I am not MySQL expert. And If so, do I have to add $sth->finish after every query? Or only one at the end of every script (a script does several queries)?

You don't need to call ->finish on mysql if the variable goes out of scope and is destroyed. ie:

sub foo {
if (..) {
my $sth = ...
}
# sth is destroyed
}

if you are using globals, and your sth's don't get destroyed by going out of scope, you should call sth->finish.

Cheers,

Alex

--
Gossamer Threads Inc.

Mysql is good about destroying the handles, other databases are not.

One good thing is to always run under use strict, and check your error logs. You'll often be warned if an auto-destruct on the handle was called, and you'll know you need to add a manual release.

Especially with GT's code, using 'strict' and checking your error logs can provide a wealth of information, since they have good error/debug routines in most of their programs.

PUGDOG®
PUGDOG® Enterprises, Inc.
FAQ: http://postcards.com/FAQ

Thanks for your help, I did as Alex said, my script uses mod_perl (but the server doesn't have mod_perl installed) and always under "use strict;". I don't find ant error information from apache error log...so confused:(

When you notice it becomes unstable, run a `mysqladmin proc` to see what the queries are. It could just be you have a query that isn't indexed properly.

Cheers,

Alex

--
Gossamer Threads Inc.

Can I run this command "mysqladmin proc" from MySQLMan (SQL Monitor)? or any other way to get the results which "mysqladmin proc" outputs without using telnet. I can't telnet to server from my office:-(

Sure:

SHOW PROCESSLIST

will do the trick.

Cheers,

Alex

--
Gossamer Threads Inc.

Thanks Alex, I got the following result by using SHOW PROSESSLIST:

Id User Host db Command Time State Info
424880 hiu localhost users Sleep 4 NULL NULL
424881 hiu localhost users Sleep 4 NULL NULL
424882 hiu localhost users Sleep 4 NULL NULL
424883 hiu localhost users Sleep 4 NULL NULL
424884 hiu localhost users Sleep 4 NULL NULL
425508 hiu localhost users Sleep 4 NULL NULL
425510 hiu localhost users Sleep 4 NULL NULL
426063 hiu localhost users Sleep 14 NULL NULL
426064 hiu localhost users Sleep 14 NULL NULL
.......... lots of sleep commands .........
426091 hiu localhost users Query 0 NULL SHOW PROSESSLIST

what's the meaning of "sleep"?

Sounds like you have something not releasing connections. Sleep means it's not doing any queries, but is still conneceted. You will eventually run out of connections to mysql and you will start getting fatal errors from DBI.

Cheers,

Alex

--
Gossamer Threads Inc.

So how can I solve this? use $dbh->disconnect() to manually release connections?