Re: DBI + MySQL 5.6 = problems

[Bowie J. Poag]

Hmm. Sounds like there might be an upper limit to the number of concurrent connections allowed in MySQL...If not, this might be an issue of having too many filehandles open. The first is a simple fix -- Change the upper limit in MySQL's config and bounce the daemon. The other is a little more involved.

While your script is running, open up another shell on the same box, and (as root) issue this command exactly as you see it, directly at your shell prompt, hitting return at the end of each statement:

(by the way, this assumes you're using bash or something syntax-compatible with bash...if it doesn't work, hey, run /bin/bash or write a Perl script that runs system("lsof | wc -l"); every so often.. :))

while true
do
lsof | wc -l
sleep 1
done

This will dump out a number every second that reflects how many open filehandles exist on the entire host. Watch this value as your script proceeds. In Unix, there's usually an upper limit for non-root users on the number of filehandles that can be open at any given time. To check what this limit is, you can have a look at the 'ulimit' command while logged in as the user the script is running under, or, as root, look at your ulimits config you're imposing on users.. It's usually in /etc/security/ somewhere. Depends on the environment, but, individual users are typically limited to about 1024.

Meanwhile, check your code to make sure you're explicitly closing filehandles when you no longer need them. For every open() call, there should be a correspoding close() call. Perl will automagically close all open filehandles upon exiting or dying, but, if you're forking a ton of child processes which in turn keep tons of filehandles open, it may be hanging onto all of them until the parent process quits. That would explain some of the symptoms you're seeing.

My guess is, the number yeilded by the little impromptu shell script above will steadilly grow, and either hold steady for a while before collapsing, or collapse outright. The collapse will also coincide with your script failing, since the act of Perl failing will relinquish tons of filehandles... if i'm right. :)

Cheers,

Bowie