Perl and mysql connections

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi All, I'm looking for someone to please give me a few pointers where to start looking on a problem I have. I'm new to all this stuff so be gentle :)

I am using Perl DBI to connect to a mysql database. If I use a localhost connection the response is very quick. However, as soon as I move the mysql server to another machine it takes at least 30 seconds - sometimes minutes.

I don't think it's the network or the mysql server as I can connect and run queries remotely using mysql client at the same speed locally pretty much.

I have tried moving to mod_perl and Apache::DBI to keep a constant connection open but it seems to make no difference.

Does anyone have any ideas where to look or which logs/performance stats to check?

Many thanks,
P.

Comment on Perl and mysql connections

Replies are listed 'Best First'.
Re: Perl and mysql connections by IlyaM (Parson) on Feb 24, 2003 at 12:20 UTC
However, as soon as I move the mysql server to another machine it takes at least 30 seconds - sometimes minutes. In my experience nearly all problems like "connecting to service X is very slow" are caused by DNS misconfiguration. I bet that MySQL service is configured to resolve client IP and DNS server it uses for DNS lookups cannot resolve this IP. So MySQL server had to wait for DNS query timeout. Solution: either disable DNS lookups in mysql or fix DNS. -- Ilya Martynov, ilya@iponweb.net CTO IPonWEB (UK) Ltd Quality Perl Programming and Unix Support UK managed @ offshore prices - http://www.iponweb.net Personal website - http://martynov.org	[reply]
Re: Perl and mysql connections by CountZero (Bishop) on Feb 24, 2003 at 12:38 UTC
Perhaps showing us some code, would enable us to give some more detailed answers. May we suggest the following: Write a minimal DBI-program (just connecting, fetching some data and disconnecting). Run it on a machine with mySQL local and time it. Then run it with mySQL on a different machine and time it again. Report back to Perlmonks, with your code and timings. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: Perl and mysql connections by dash2 (Hermit) on Feb 24, 2003 at 15:09 UTC
Coming at it from the perl angle, run your script under DProf: `perl -d:DProf script.pl` [download] and then use `dprofpp` to see which perl subroutine is causing the delay. From that you may be able to figure out where the problem lies. See Devel::DProf for more details. dave hj~	[reply] [d/l] [select]
Re: Perl and mysql connections by Anonymous Monk on Feb 24, 2003 at 11:24 UTC
I'm new to all this stuff so be gentle :) If you remove that bullseye from your forehead, I won't shoot. Does anyone have any ideas where to look or which logs/performance stats to check? Yeah, first look at your code. How much data are you retrieving? How do you process it? (one record at a time, or all records at once) Caching any of it? (can you make use of caching) Check your error logs for errors, check your mysql logs for errors, try profiling your code, try profiling your mysql.	[reply]