Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

DBI + MySQL 5.6 = problems

by ethrbunny (Monk)
on Jan 07, 2014 at 15:58 UTC ( #1069670=perlquestion: print w/replies, xml ) Need Help??
ethrbunny has asked for the wisdom of the Perl Monks concerning the following question:

For about 5 years I've been using a set of scripts to download data from 2-300 remote sites and copy to a central DB. The main script forks 12 sub-scripts each of which brings down data from a specific location and uses DBI to write it locally. Once the data is copied, the sub-script exits. The main script cycles through the list running these 12 at a time. This has been all well and good with MySQL 5.1 and 5.5.

Page forward to about a month ago when I upgraded to v5.6. When running this same process it gets through 20-30 of the sub-scripts and then rejects every other connection until all remaining of the 2-300 have been finished. If I restart it the results are the same (20 or so and then errors for the rest).

Looking at the processlist on MySQL shows no more than 25 jobs at any given moment. There are no errors in the MySQL logs. If I set the fork limit to 5 jobs it gets through all of them without failing.
Suggestions on places to look for clues as to what is happening?

Replies are listed 'Best First'.
Re: DBI + MySQL 5.6 = problems
by ysth (Canon) on Jan 07, 2014 at 16:18 UTC
    max_user_connections? max_connections?

    What does "rejects every other connection" mean? What fails, and with what error?

    A math joke: r = | |csc(θ)|+|sec(θ)| |-| |csc(θ)|-|sec(θ)| |
Re: DBI + MySQL 5.6 = problems
by Bowie J. Poag (Initiate) on Jan 07, 2014 at 18:33 UTC
    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. :)



Re: DBI + MySQL 5.6 = problems
by ww (Archbishop) on Jan 07, 2014 at 19:30 UTC
    "about a month ago ... I upgraded to v5.6."

    That suggests you're gonna' be going for one of them thar infernal combustion wagons purty soon. T'aint nuttin wrong with horses, so why bother?

    Suggestion (tho I hate to suggest going thru the 'pain' -- if that's the way you see it -- of upgrading again): why not upgrade to something that's still semi-current, like 5.16 or to an ever newer version, 5.18 and take advantage of the new capabilities added since 5.6.

    Clearly, you haven't let your MySQL get nearly as far outdated.

    Come, let us reason together: Spirit of the Monastery

    If you didn't program your executable by toggling in binary, it wasn't really programming!

      I think the OP probably meant version 5.6 of MySQL rather than version 5.6 of perl...

      Update: Linkified
        More careful reading than I accorded the OP initially says...
        • You're right!

        /me slinks away chagrinned at mine own careless reading.

        Come, let us reason together: Spirit of the Monastery

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1069670]
Approved by Corion
Front-paged by Arunbear
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2018-05-20 22:42 GMT
Find Nodes?
    Voting Booth?