Re: Multiple write locking for BerkeleyDB

How did you code your MySQL solution? What storage type did you use? I bet this will perform great, and I'm pretty sure it's guaranteed atomic for MyISAM and InnoDB (would have to check to be sure):

UPDATE table SET counter = counter + 1 WHERE counter_id = ?;

(Assuming you have an index on counter_id of course.)

You also need to look out for the usual DB-usage traps - make sure you're caching your connection between calls, tune your configuration to make appropriate use of memory, etc.

-sam

Comment on Re: Multiple write locking for BerkeleyDB Download Code

Replies are listed 'Best First'.
Re^2: Multiple write locking for BerkeleyDB by dino (Sexton) on Apr 23, 2008 at 20:47 UTC
Well I used dbi and a similar syntax to the above "insert with on duplicate key update, to deal with non existing keys (in this case ips)". I'm a pretty inexperienced sqler so its very possible that its not optimal, but the rate was very poor (30k/min) when compared to the Berkeley alternatives.	[reply]
Re^3: Multiple write locking for BerkeleyDB by samtregar (Abbot) on Apr 23, 2008 at 21:07 UTC
Try pre-loading your counters with 0s and just using UPDATE. I bet it's faster than INSERT ON DUPLICATE UPDATE. Assuming you have reasonable hardware 30k/min (500/s) seems pretty poor. But I guess it depends on how many counters there are and how many concurrent connections are trying to write at once. -sam	[reply]
Re^4: Multiple write locking for BerkeleyDB by dino (Sexton) on Apr 23, 2008 at 21:20 UTC
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.	[reply]
Re^5: Multiple write locking for BerkeleyDB by sgifford (Prior) on Apr 24, 2008 at 03:06 UTC
Re^6: Multiple write locking for BerkeleyDB by dino (Sexton) on Apr 24, 2008 at 11:43 UTC
Some notes below your chosen depth have not been shown here
Re^3: Multiple write locking for BerkeleyDB by perrin (Chancellor) on Apr 23, 2008 at 20:53 UTC
For simple things like this, using local sockets with MySQL makes a big difference. It will never be as fast as BerkeleyDB, but it should be very fast.	[reply]