5 ms·

The problem with conventional databases

33 points by Sam_Odio 20y ago

Readmore 20y ago
So what we need is a DB that stores itself completely in memory and only writes to disk every Nminutes?
- paul 20y ago
  No, it can write continually, but in a sequential fashion.
  - Readmore 20y ago
    I just read up on memcached, now I get it. Very nice ;)
  - jmarinez 20y 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 20y 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.
- paul 20y ago
  Fixed. Thanks
- Sam_Odio 20y ago
  I think he meant the disk, not the DRAM, is slower.
dfranke 20y 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.
- paul 20y 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.
  - dfranke 20y 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.
    - paul 20y ago
      Just write each transaction as a single record to your transaction log file, ensure that only whole records are replayed (like with a checksum), and you're done. Since there are no b-trees or other complicated structures on disk, the problem gets a lot easier.
      - dfranke 20y ago
        It sounds like you're describing very-nearly a textbook implementation of redo logging. I think what's making things easy for you is not the fact that you're using flat files, but rather that the entire contents of a transaction fit in memory. Most of the complexity involved in a commercial DBMS's log implementation stems from dealing with how to handle a transaction that modifies 20GB of data when you have 8GB of memory. Edit: and yes, the irony is noted that I'm telling a Googler that he's not working with enough data.
nostrademons 20y 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 20y 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_sansei 20y ago
8 GB of flash memory cost about $80 It does? Where can I get that?
- paul 20y ago
  $59.99: http://www.newegg.com/Product/Product.asp?Item=N82E16820151029&ATT=20-151-029&CMP=OTC-Froogle http://www.newegg.com/Product/Product.asp?Item=N82E16820151029&ATT=20-151-029&CMP=OTC-Froogle
rtm 20y 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 http://citeseer.ist.psu.edu/531029.html ).
dyu 20y ago
What about dirty read, locking, lost update and all those problems that a DBMS takes care for you? Just wondering.
rtm 20y 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 20y 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 20y 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

33 points by Sam_Odio 20y ago

Readmore 20y ago

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

paul 20y ago
No, it can write continually, but in a sequential fashion.
- Readmore 20y ago
  I just read up on memcached, now I get it. Very nice ;)
- jmarinez 20y 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 20y 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.

paul 20y ago
Fixed. Thanks
Sam_Odio 20y ago
I think he meant the disk, not the DRAM, is slower.

dfranke 20y 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.

paul 20y 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.
- dfranke 20y 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.
  - paul 20y ago
    Just write each transaction as a single record to your transaction log file, ensure that only whole records are replayed (like with a checksum), and you're done. Since there are no b-trees or other complicated structures on disk, the problem gets a lot easier.
    - dfranke 20y ago
      It sounds like you're describing very-nearly a textbook implementation of redo logging. I think what's making things easy for you is not the fact that you're using flat files, but rather that the entire contents of a transaction fit in memory. Most of the complexity involved in a commercial DBMS's log implementation stems from dealing with how to handle a transaction that modifies 20GB of data when you have 8GB of memory. Edit: and yes, the irony is noted that I'm telling a Googler that he's not working with enough data.

nostrademons 20y 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 20y 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_sansei 20y ago

8 GB of flash memory cost about $80 It does? Where can I get that?

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

rtm 20y 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 http://citeseer.ist.psu.edu/531029.html ).

dyu 20y ago

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

rtm 20y 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 20y 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 20y 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