unreleased sockets using DBI / DBD::mysql under mod_perl?

[Joern Janoschek <jo...@klahold.de>]

Hi,

we are currently developing several Perl scripts for a big
german site with high traffic and the need of fast DB
connections. The webserver are Apache 1.3.14 setup with mod_perl
(current version as far as I know). The scripts connect to a
Mysql 3.2332 on another machine over a socket connections.
Now the problem: High traffic result to many connections to the
DB. The DB seems to be fast enough (4 Xeon 750 machine with 4
GB) to handle those requests. BUT after some time the DB get
flooded by unauthorised connections. Looking at the DB server
stats it doesn't look like a high demand for processor power...
the machine is idling as far as a DB server can be idle. Those
faulty connection, we can't get rid of. Once they are coming in,
the DB doesn't seem capable of handling, authenticating or
refusing them properly.
We regard this as a some sort of mod_perl socket problems
because performance tests during the night, using PHP with the
same queries and similar load proof to be all correctly
answered. No unauthenticated connection in the processlist of
the mysql, even when there are 500 concurrent DB requests
running.
Taking a peek at the webserver netstats when connecting with
Perl scripts show a lot of open and not closing socket
connections to the mysql... only after some timeout period they
get disconnected.
Currently Perl tests over mod_cgi still have to be made, but I
doubt this will be a problem, but who knows... maybe we have
some DBD::mysql configuration problem here? This is surely the
wrong list to ask... :-) but you'll never know, maybe sonmeone
reading this can point me in the propper direction.

Anyway, anyone experienced the same behaviour before? Any hint
to a solution are greatly appreciated, we need a
fix/solution/workaround soon!

A word on Apache::DBI: Yes, we did try persistant connections to
avoid heavy connection load on the mysql. BUT it simply didn't
work. Well sort of. Persistant connections were established, but
they never got reused. Until about 70000 open connections
resulted in a DB crash. Not nice. Same problem with a
corresponding persistant connection test using PHP... maybe a
Mysql client source configuration problem or something like it?
Anyone experienced this before?

Thanks for any help or hint,

Jörn Janoschek.
KLAHOLD - We make IT work! - http://www.klahold.de
- WebDevelopment - http://www.klahold.de
- Content-Management-System - http://www.interred.de

Re: unreleased sockets using DBI / DBD::mysql under mod_perl?

[Greg Cope <gr...@rubberplant.freeserve.co.uk>]

Joern Janoschek wrote:
> 
> Hi,
> 
> we are currently developing several Perl scripts for a big
> german site with high traffic and the need of fast DB
> connections. The webserver are Apache 1.3.14 setup with mod_perl
> (current version as far as I know). The scripts connect to a
> Mysql 3.2332 on another machine over a socket connections.
> Now the problem: High traffic result to many connections to the
> DB. The DB seems to be fast enough (4 Xeon 750 machine with 4
> GB) to handle those requests. BUT after some time the DB get
> flooded by unauthorised connections. Looking at the DB server
> stats it doesn't look like a high demand for processor power...
> the machine is idling as far as a DB server can be idle. Those
> faulty connection, we can't get rid of. Once they are coming in,
> the DB doesn't seem capable of handling, authenticating or
> refusing them properly.
> We regard this as a some sort of mod_perl socket problems
> because performance tests during the night, using PHP with the
> same queries and similar load proof to be all correctly
> answered. No unauthenticated connection in the processlist of
> the mysql, even when there are 500 concurrent DB requests
> running.
> Taking a peek at the webserver netstats when connecting with
> Perl scripts show a lot of open and not closing socket
> connections to the mysql... only after some timeout period they
> get disconnected.
> Currently Perl tests over mod_cgi still have to be made, but I
> doubt this will be a problem, but who knows... maybe we have
> some DBD::mysql configuration problem here? This is surely the
> wrong list to ask... :-) but you'll never know, maybe sonmeone
> reading this can point me in the propper direction.
> 
> Anyway, anyone experienced the same behaviour before? Any hint
> to a solution are greatly appreciated, we need a
> fix/solution/workaround soon!
> 
> A word on Apache::DBI: Yes, we did try persistant connections to
> avoid heavy connection load on the mysql. BUT it simply didn't
> work. Well sort of. Persistant connections were established, but
> they never got reused. Until about 70000 open connections
> resulted in a DB crash. Not nice. Same problem with a
> corresponding persistant connection test using PHP... maybe a
> Mysql client source configuration problem or something like it?
> Anyone experienced this before?
> 

There's no reason why Apache::DBI should not work - by the sounds of it
the connections strings may have been different ?

Apache::DBI works in an environment where on user ID is used to connect
to the DB mulitple time.  If you use two different connection strings
Apache::DBI will try and make both persistent (IIRC).

So check your DBI connection strings as they should be indentical.

Greg


> Thanks for any help or hint,
> 
> Jörn Janoschek.
> KLAHOLD - We make IT work! - http://www.klahold.de
> - WebDevelopment - http://www.klahold.de
> - Content-Management-System - http://www.interred.de