Database Write Speeds

miketpowell

miketpowell

New member
Feb 20, 2009
755
26
0
Las Vegas
I know next to nothing about databases and have a new project that would involve needing to write tiny amounts of data very frequently at blazing speed.

Say I have a banner coded in HTML and set up so there are 3 sections that have 3 variations, when the banner is loaded each section randomly chooses a variation. So there would be a total of 3*3*3 = 27 variations. I would have a database with 54 different entries. After the banner is loaded each time it would ++1 to one of the entries 1-27 to denote it was loaded then if that banner was clicked ++1 to one entry 28-54 that corresponds with the lower ones.

My main concern before under taking this project is basic database write speed. If I'm serving 1,000,000 impressions in an hour I'm going to be writing to the database ~ 300 times a second with bursts much higher then that I imagine. Will I have to be doing something special here to accommodate?
 


What DBMS you using and is it on a different machine than the web server? (i.e: is the database going to be on a dedicated box?)
 
first, this needs to be run in the cloud. something like rackspace or amazon for sure.

second, you'll want your database ram-based, as no hard drive can work that fast. You're basically running a caching server when it's ram based, that gets dumped to the hard drive periodically.
 
Sounds like you're trying to hammer in a screw. Do you really need to write your database that fast, or do you just need to quickly count your hits, and keep them in your database?

Instead of writing to the database 300 times per second, keep your counting variable in memcache (which is fucking F A S T and supports atomic operations which will keep your counting accurate), and every ~15-20 seconds, reset the memcache number to 0 and write num_hits+=N to your database.
 
uplinked said:
Sounds like you're trying to hammer in a screw. Do you really need to write your database that fast, or do you just need to quickly count your hits, and keep them in your database?

Instead of writing to the database 300 times per second, keep your counting variable in memcache (which is fucking F A S T and supports atomic operations which will keep your counting accurate), and every ~15-20 seconds, reset the memcache number to 0 and write num_hits+=N to your database.
Click to expand...

you have hands down the funniest opening lines to every damn response. <3

Lee knows his shit on this, so I would venture what he just wrote is more than enough
 
^^^that

If I'm reading the OP's description accurately there would be 54 rows in a table - all of which would be updated constantly at 300 updates/second.

This is quite a bit different than if you were writing new data to the table at 300 rows/second.

Assuming you are indexing the rows and using that as the main update query "WHERE" then you will have slightly different issues than if you were doing inserts.

First of all you won't have to worry about things like index segments extending or tablespaces extending.

On the down side you may run into row-level locking issues if you are doing that many updates that fast.
 
Yeah I think you might want to rethink your needs and how to fulfill them, like uplinked and Sharksfan suggested.
 
Sharksfan said:
^^^that

If I'm reading the OP's description accurately there would be 54 rows in a table - all of which would be updated constantly at 300 updates/second.

This is quite a bit different than if you were writing new data to the table at 300 rows/second.

Assuming you are indexing the rows and using that as the main update query "WHERE" then you will have slightly different issues than if you were doing inserts.

First of all you won't have to worry about things like index segments extending or tablespaces extending.

On the down side you may run into row-level locking issues if you are doing that many updates that fast.
Click to expand...

he has 54 rows, but he's only writing to two per transaction (increment one row between 1-27 on load, increment one row between 28-54 on click)
 
Thanks for the input guys.

I have very little technical knowledge compared to any real coder so I didn't have a set approach on how this should be done and certainly wasn't set in the way I proposed in the OP.

I'm experienced with using amazon for content delivery and that's certainly where I was planning on hosting the HTML+JS for the banner.

What I'm not experienced at is storing any data in any way really. Storing in memcache then updating the database sounds like a good way to do it but definitely not something I know how to do. But at least I will know how it should be done when I hirer someone to do it.
 
miketpowell said:
Thanks for the input guys.

I have very little technical knowledge compared to any real coder so I didn't have a set approach on how this should be done and certainly wasn't set in the way I proposed in the OP.

I'm experienced with using amazon for content delivery and that's certainly where I was planning on hosting the HTML+JS for the banner.

What I'm not experienced at is storing any data in any way really. Storing in memcache then updating the database sounds like a good way to do it but definitely not something I know how to do. But at least I will know how it should be done when I hirer someone to do it.
Click to expand...

Feel free to hit me up on AIM or PM, dude, I was a big fan of your talk at the a4d meetup in feb, and wanted to tell you so (but you were swamped with other people), so I'll help you figure out this matter as a "Thanks for the presentation" :)
 
You must log in or register to reply here.
Top Bottom