Protecting a mod perl 1.3 site from slow MySql processes

--_000_E03BE6EEFA136E4FAD6D976833DCA0D75ED0EA219CEXCHANGEAHa dm_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello,

I am working on a site that has a modperl (mod_perl 1.3, perl 5.8.0, on lin=
ux)
handler doing some authentication work via DBD::MySql. It connects to
a MySQL server over the network.

Recently during some maintenance on the MySql server, the entire site
was hung up, because the MySQL server was up, but it wasn't
responding. It is important that the site continue to function, even
if MySql is down or slow.

I was able to reproduce something like what may have happened simply
by pausing the MySql thread in my test environment. If I do that, the
web page does just hang.

The perl module wasn't dying, so wrapping everything in eval{} won't
solve the problem.

I tried setting alarm() around the DBI lines, using the Sys::SigAction
module, in order to get the script to timeout if MySql isn't
responsive. This works great if I run it as a plain perl script. But
it seems that alarm() doesn't work when it runs as a perl module.

So now I'm stuck.

I'm just looking for suggestions for possible solutions. Any way that
I can safe guard the site against a misbehaving MySql server.

Thanks,
April

--_000_E03BE6EEFA136E4FAD6D976833DCA0D75ED0EA219CEXCHANGEAHa dm_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">

Re: Protecting a mod perl 1.3 site from slow MySql processes

On Tue, Dec 2, 2008 at 1:56 PM, April Papajohn
wrote:
> I tried setting alarm() around the DBI lines, using the Sys::SigAction
> module, in order to get the script to timeout if MySql isn't
> responsive. This works great if I run it as a plain perl script. But
> it seems that alarm() doesn't work when it runs as a perl module.

You may be running different versions of perl or DBI in your script.
Signals are tricky when using some DBD drivers. I suggest looking at
DBIx::Timeout for some information on this.

Realistically, it's not practical to wrap every single DBI call that
might hang if the server went away in the middle. However, you can
adjust how long MySQL will wait to connect or timeout a dead
connection, and you can use something like Apache::DBI or
DBI->connect_cached which will ping your connection at the beginning
of a request.

- Perrin