6 ms·

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 per

by 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.
      - mattculbreth 20y ago
        This is a very good point. Some types of applications will find this scheme a bit difficult, though I'm in the midst of hacking through some POC work around it. I've got a database in the 100GB range potentially so it's a bit of a different domain. I really think this concept works though for 8/10 applications which are using databases because, well, you've always used databases. Memory, save changes to flat files, read it all in during startup, it's really an elegant way to go about it.
        owinebar 20y ago
        If you want to see elegant, read dan bernstein's code. Let the filesystem be your database.
        mattculbreth 20y ago
        Cool, I'll do that. Which set of his code? I've found his website and there's a bunch there.
        owinebar 20y ago
        It's been a few years since I was a sysadmin, but I believe either his DNS system or qmail system will show it. When I first read them, I thought they were very annoying, because it's not one monolithic program, and there are a lot of one-line shell scripts that do sequential operations using exec. The way he describes it, he's getting rid of a lot of parsing by using the directory tree instead of text files following. But this use of directories can also be good for some of the more degenerate uses of databases. Mostly I recall the "aha" moment of "So, this is the Unix philosophy in action." The design is very elegant.
      - paul 20y ago
        Yes, exactly, they are solving a number of much harder problems. However, these problems are irrelevant to 99% of web sites. How often does Twitter need to perform a 20GB transaction?
lupin_sansei 20y 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.
rtm 20y 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.)
- dfranke 20y 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.
  - rtm 20y ago
    Deferred logging is a huge deal! Appending a record to a log takes one (or a half) of a rotation. A rotation takes about the same amount of time as a disk seek. So a DB that synchronously appends the on-disk log for each transaction will be slow. The huge win is if you can append many transactions to the log in each rotation. To do that you have to gather up many updates per disk operation. So deferred logging is critical. I suspect the reason PostgreSQL doesn't really support delayed log flush is that they are thinking about ACID transactions, where you really need the data to be on disk immediately. A more technical issue is that the log data must be on disk before the corresponding permanent data (otherwise crash recovery will break), and I suspect postgresql.conf's "fsync" option has the effect of not fsync()ing the log at all, which indeed would cause permanent corruption after a crash.
    - dfranke 20y ago
      Don't drives have hardware buffers designed so that you don't have to worry about that?
BitGeek 20y ago
I know you beleive that, but just look at the number of startups using MySQL.
- dfranke 20y ago
  I said any decent DBMS.

by dfranke 20y ago

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.
    - mattculbreth 20y ago
      This is a very good point. Some types of applications will find this scheme a bit difficult, though I'm in the midst of hacking through some POC work around it. I've got a database in the 100GB range potentially so it's a bit of a different domain. I really think this concept works though for 8/10 applications which are using databases because, well, you've always used databases. Memory, save changes to flat files, read it all in during startup, it's really an elegant way to go about it.
      - owinebar 20y ago
        If you want to see elegant, read dan bernstein's code. Let the filesystem be your database.
        mattculbreth 20y ago
        Cool, I'll do that. Which set of his code? I've found his website and there's a bunch there.
        owinebar 20y ago
        It's been a few years since I was a sysadmin, but I believe either his DNS system or qmail system will show it. When I first read them, I thought they were very annoying, because it's not one monolithic program, and there are a lot of one-line shell scripts that do sequential operations using exec. The way he describes it, he's getting rid of a lot of parsing by using the directory tree instead of text files following. But this use of directories can also be good for some of the more degenerate uses of databases. Mostly I recall the "aha" moment of "So, this is the Unix philosophy in action." The design is very elegant.
    - paul 20y ago
      Yes, exactly, they are solving a number of much harder problems. However, these problems are irrelevant to 99% of web sites. How often does Twitter need to perform a 20GB transaction?

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

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

dfranke 20y 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.
- rtm 20y ago
  Deferred logging is a huge deal! Appending a record to a log takes one (or a half) of a rotation. A rotation takes about the same amount of time as a disk seek. So a DB that synchronously appends the on-disk log for each transaction will be slow. The huge win is if you can append many transactions to the log in each rotation. To do that you have to gather up many updates per disk operation. So deferred logging is critical. I suspect the reason PostgreSQL doesn't really support delayed log flush is that they are thinking about ACID transactions, where you really need the data to be on disk immediately. A more technical issue is that the log data must be on disk before the corresponding permanent data (otherwise crash recovery will break), and I suspect postgresql.conf's "fsync" option has the effect of not fsync()ing the log at all, which indeed would cause permanent corruption after a crash.
  - dfranke 20y ago
    Don't drives have hardware buffers designed so that you don't have to worry about that?

BitGeek 20y ago

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

dfranke 20y ago
I said any decent DBMS.