The problem with conventional databases

http://paulbuchheit.blogspot.com/2007/04/problem-with-conventional-databases.html

by Sam_Odio • 19 years ago

33 38 19 years ago

paulbuchheit.blogspot.com

Readmore2• 19 years ago

So what we need is a DB that stores itself completely in memory and only writes to disk every Nminutes?

paul6• 19 years ago

No, it can write continually, but in a sequential fashion.

Readmore2• 19 years ago

I just read up on memcached, now I get it. Very nice ;)

jmarinez• 19 years ago

I wonder what would happen if you use ZFS on top of a flash memory drive and replace/add a SQL layer next to the ZPL layer. In theory, you'd get transactions and checksums for free and possibly your sequential writes as well.

abstractbill• 19 years ago

"For sequential access, DRAM is about 62x the speed of disk -- it's slower, but only by a few orders of magnitude."

Shouldn't that be "it's faster"?

Excellent post otherwise, btw.

paul6• 19 years ago

Fixed. Thanks

Sam_Odio• 19 years ago

I think he meant the disk, not the DRAM, is slower.

dfranke7• 19 years ago

If your entire database fits in memory, then any decent DBMS will cache it all there, write changes back to disk at such time as to have a minimal impact on performance, all the while ensuring atomicity if someone kicks out the plug while it's in the middle of flushing the buffer. If you're not using a DBMS and you can still say all that about your own solution, you've probably put a lot of work into reinventing the wheel.

paul6• 19 years ago

Ensuring atomicity is actually very easy.

Databases SHOULD do these things, but I haven't seen any evidence that the popular ones do. Feel free to post actual measurements for updates per second from your db.

dfranke7• 19 years ago

How are you handling atomicity? Are you doing full-out undo/redo logging, or just something involving writing to a seperate file and then (atomically) retargeting a symlink?

I'm mostly an academic, so I can tell you a lot more about how things are supposed to work than about how they actually do. The only high-traffic DBMS with real users to which I can easily get access is MS-SQL, which doesn't inspire me to any leaps of faith. But once I finish building my new desktop system (with spiffy RAID array) I'll rig up some benchmarks on MySQL and PostgreSQL.

lupin_sansei2• 19 years ago

Exactly. And it's mighty nice to have a declarative language (SQL) available to write your queries, and perform ad-hoc queries in a terminal, plus referential integrity, triggers, stored procedures etc.

rtm6• 19 years ago

An ordinary DB writes each update to disk twice, once in the log and once in the permanent DB. DBs do defer updating the permanent DB. But they tend to be eager about flushing the log in order to achieve durability. If you read the documentation for PostgreSQL, for example, the only knob that seems to allow you to defer log flushes also says it may corrupt the internals of the DB if there's a crash. (Though there's no fundamental reason PostgreSQL couldn't defer log writes and have correct crash recovery, sacrificing only durability of recent updates.)

dfranke7• 19 years ago

That's a very strange knob. What's the point of doing any logging at all if it doesn't let you recover from a crash?

I don't imagine that deferred logging is a big deal, though, because log writes are by definition sequential. As Paul pointed out, sequential writes just aren't that slow. You can build an array of 62 commodity drives for maybe $4000.

BitGeek• 19 years ago

I know you beleive that, but just look at the number of startups using MySQL.

dfranke7• 19 years ago

I said any decent DBMS.

nostrademons• 19 years ago

So, how do write-intensive sites like LiveJournal handle their updates? I was under the impression that LJ was all memcached + sharding + master/master pairs for the shards.

How do you handle features that rely on frequent UPDATE statements, like say a hitcounter? Is UPDATE LOW PRIORITY (MySQL) enough, or should you work out some application-specific caching/batching mechanism and perform all your updates at once?

staunch• 19 years ago

Really great writeup Paul. I've been preaching "memory is magic" for the last couple years now -- since the capacity:price ratio got so good. Now I have a nice non-fluffy hacker post to send people to.

If there's any silver bullet to scaling most web apps it's properly utilizing gobs of memory. Pushing bytes from memory to your NIC is a pretty damn efficient operation.

lupin_sansei2• 19 years ago

8 GB of flash memory cost about $80

It does? Where can I get that?

paul6• 19 years ago

$59.99: http://www.newegg.com/Product/Product.asp?Item=N82E16820151029&ATT=20-151-029&CMP=OTC-Froogle

rtm6• 19 years ago

A challenge in a pure version of a log-structured database is reclaiming space from the log when data is no longer needed. Sometimes you don't need to, as in Venti ( http://citeseer.ist.psu.edu/531029.html ).

dyu• 19 years ago

What about dirty read, locking, lost update and all those problems that a DBMS takes care for you? Just wondering.

rtm6• 19 years ago

On MySQL 4.1 / InnoDB / FreeBSD / FFS / SCSI, small inserts run at about 167 transactions per second, i.e. one per rotation.

Same setup but --innodb_flush_log_at_trx_commit=0, a million transactions in 200 seconds, or 5000/second.

I don't know if InnoDB wrote its B-Tree to disk during the 200 seconds. Same performance even with InnoDB's buffer pool size set to 200 KB with --innodb_buffer_pool_size=200000.

The MySQL documentation claims that this configuration does crash-recovery correctly, though you may lose the last second's worth of transactions.

ntoshev• 19 years ago

Keeping all data in RAM and just saving changes to a log file is a great architecture. It is a pity that the current "hip" tools/models for web development impose unnecessary cost for using it.

The problem is that the Ruby / Python / PHP stacks typically have one process per request, and they do not share memory. When you use memcached in such environment, it is a separate process and communication with it is rather slow - it involves marshaling the data, which would not be necessary if all requests were just threads in a single address space.You don't have this problem if you use Java/C++.

I have used with some success a huge mmaped file as persistent memory to store my data. It's not exactly using disc as sequential device, that depends on the application and data locality. There are just a few tools available to support that scheme.

stuki• 19 years ago

Ram disks (like www.texmemsys.com) for the log device are a way of speeding up traditional rdbms'. They cost a bit, but You get to keep all the 'cool' database features while minimizing the sync log write bottleneck. Such architectures are also easier to explain to auditors if Your app is of a financial nature. I am not affiliated with this or any other storage vendor. --Stuki

The problem with conventional databases

Related Stories