Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re: Optimizing mysql and inserts

by ruzam (Curate)
on Jul 11, 2012 at 18:24 UTC ( #981228=note: print w/ replies, xml ) Need Help??


in reply to Optimizing mysql and inserts

I regularly run scripts to rebuild MySQL tables from scratch and reload them with data records. A quick line count tells me about 1.2 million text records in various combinations of insert/delete/update. The text records are parsed, and submitted as MySQL requests by Perl, one by one through DBI connection without the benefit of prepared statements. It takes roughly 10 minutes to completely process. This is running on my desktop box, nothing fancy. So unless you have a seriously under powered computer (I doubt it) or a network storage limitation or database locking issues, I doubt that MySQL is your bottleneck.

You need to find out where the code is spending it's time. A few well placed print statements should point you in the right direction fairly quickly.


Comment on Re: Optimizing mysql and inserts
Re^2: Optimizing mysql and inserts
by gossamer (Novice) on Jul 11, 2012 at 19:51 UTC

    I've commented out the "$dbh->do($sql);" in my code, and it's substantially faster. That is definitely the bottleneck.

    ...few minutes later, after a few trial runs...

    I grabbed a my.cnf from a larger server and put it in place of the existing one, and it's magnitudes faster! Turns out it was in how mysql was configured.

    I think it's much better now. I'm actually pretty proud of myself for this, and thanks for everyone's help as well!

    Thanks,
    Alex

      Not that it's Perl related, but what kind of differences did you find in the my.cnf file?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://981228]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (15)
As of 2014-10-23 14:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (125 votes), past polls