Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re^4: Multiple write locking for BerkeleyDB

by dino (Sexton)
on Apr 23, 2008 at 21:20 UTC ( #682499=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Multiple write locking for BerkeleyDB
in thread Multiple write locking for BerkeleyDB

Thanks for you ip. I'm not sure I can pre load the counters with zero as I don't know what the keys will be until I read the tcpdump input. The table format is currently:
ip|count_in|count_out|count_cross
But this is starting to get off topic and I should read up more on Mysql. (I had hoped, that there was a guide out there that talked about how to drive BerkeleyDB in full {lets do manual locking in perl}) mode and that my search fu was lacking.


Comment on Re^4: Multiple write locking for BerkeleyDB
Re^5: Multiple write locking for BerkeleyDB
by sgifford (Prior) on Apr 24, 2008 at 03:06 UTC
    Try it for your benchmarks. If it turns out to be significantly faster, you can probably figure out a protocol to let you use that nearly all the time, and fall back to something slower if you need to. For example, if you use the ip as the primary key and rows are never deleted, you can:
    1. Try the update. If one row is affected, you are done.
    2. Otherwise, the row does not exist. Try an insert. If it succeeds, you are done.
    3. Otherwise, if it failed with a duplicate key, somebody else just inserted it. Retry the update.
    4. If the update fails again, something is wrong, so give up.
      I did a second run with just updates, with placeholders and separate tables for in/out/cross. The rate went up but only to about 45k/min. The server is running other mysql jobs so that might be the reason. Thanks for your suggestions anyway.
        I suspect you will get much faster results if you update just one table with all three pieces of data in it. Also, as others have said, make sure you have an index on whatever you use in your WHERE clause for the UPDATE. And, if your count_cross column is the sum of count_in and count_out, just leave it out and calculate it when you need it; this will help (at least a little) if reads are much less frequent than writes.

        In my tests elsewhere in this thread, I was able to get about 132K updates/min out of MySQL on a 2-CPU 1GHz Pentium III, so unless your machine is much slower there is probably some performance to be found somewhere.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (13)
As of 2014-10-31 17:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (221 votes), past polls