4 ms·

To be clear here, what the poster means is that piecemeal database writes of, say, 128-byte records can cause a huge amount of write amplification, so 100GB/day

To be clear here, what the poster means is that piecemeal database writes of, say, 128-byte records can cause a huge amount of write amplification, so 100GB/day worth of database writes can end up being 1000GB/day worth of flash rewrites. This issue largely goes away if the database backend appends replacement records rather then rewriting them in place, and uses the index to point to the new copy of the record. At that point the battery-backed ram the RAID system has combined with the appends results in much lower write amplification and a SSD could probably handle it. -Matt

engendered 12y ago
I know of no database products that write 128-byte blocks. Most are at least 2KB, if not larger (SQL Server and pgsql are 8KB blocks). Yes, you could conceivably imagine up some hypothetical situation that would be bad for an SSD, but it is incredibly unlikely. And when I say 100GB of writes, I mean, literally, 100GB of writes, which in actual source data is generally much, much, much, much smaller.
- dillondf 12y ago
  You do understand that flash erase blocks are around 128KB now, right? Random writes to a SSD can only be write-combined to a point. Once the SSD has to actually start erasing blocks and doing collections of those randomly combined sectors things can get dicey real fast in the face of further random writes. It doesn't have a magic wand. The point is that the SSD has no way to determine ahead of time what the rewrite order is going to be as random blocks continue to get written. You can rationalize it all you want, the write amplification is going to happen everywhere in the chain to one degree or another. For that matter, filesystems are ganging blocks together now too (and have been for a long time). It is not uncommon to see 16KB-64KB filesystem block sizes. Nobody gives a shit about a mere 100GB in physical writes to a storage subsystem in a day. A single consumer 512GB crucial can handle a rate like that for almost 30 years. Write amplification effects are not to be underestimated, but fortunately there are only a very few situations where it's an issue. And as I said, the situations can be largely mitigated by database writers getting off their butts and fixing their backends to push the complexity to the indexes and away from trying to optimize the linear layout of the database by seek-writing into it. -Matt
  - engendered 12y ago
    You do understand that flash erase blocks are around 128KB now, right? How is that relevant to your wrong claim about database 128-byte writes? You word that as if you're correcting me. I don't even know what point you're trying to make anymore, but you're trying to buttress your argument with what can best be described as diversions. Most people write far less than they think. Databases aren't particularly magical, any more than the web server log file that is written to a line at a time. Yes, people "give a shit" about a "mere" 100GB of writes, because the vast majority of real projects, including at major corporations, write far less than that per day. So are we just talking about dick measuring now?
    - dillondf 12y ago
      I'm sorry, engendered... but are you an idiot? Do you even understand the context of the conversation? -Matt
      - dang 12y ago
        > are you an idiot? Do you even understand This comment breaks the HN guidelines. We'd appreciate it if you'd read the site rules and follow them: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html
    - AlisdairO 12y ago
      A block might be 8KB, but the actual update you're making to the block might be much smaller. I imagine by '128 byte write' he's talking about a lot of random row updates, where each row is 128 bytes. Now, if you're not too unlucky, many updates will be combined on the same page per checkpoint, but that's not a given. On the other hand, it's reasonably likely that several updates will be combined per erase block per checkpoint. A heavily indexed table can exhibit some pretty random write patterns, though. Additionally, the WAL has to be synced to disk every commit (unlike a web server log file), and WAL records can be very small. WAL is of course append-only, so you'd hope that a good SSD with a battery/cap backup would cache the writes and flush on the SSD erase block filling up.

engendered 12y ago

I know of no database products that write 128-byte blocks. Most are at least 2KB, if not larger (SQL Server and pgsql are 8KB blocks). Yes, you could conceivably imagine up some hypothetical situation that would be bad for an SSD, but it is incredibly unlikely. And when I say 100GB of writes, I mean, literally, 100GB of writes, which in actual source data is generally much, much, much, much smaller.

dillondf 12y ago
You do understand that flash erase blocks are around 128KB now, right? Random writes to a SSD can only be write-combined to a point. Once the SSD has to actually start erasing blocks and doing collections of those randomly combined sectors things can get dicey real fast in the face of further random writes. It doesn't have a magic wand. The point is that the SSD has no way to determine ahead of time what the rewrite order is going to be as random blocks continue to get written. You can rationalize it all you want, the write amplification is going to happen everywhere in the chain to one degree or another. For that matter, filesystems are ganging blocks together now too (and have been for a long time). It is not uncommon to see 16KB-64KB filesystem block sizes. Nobody gives a shit about a mere 100GB in physical writes to a storage subsystem in a day. A single consumer 512GB crucial can handle a rate like that for almost 30 years. Write amplification effects are not to be underestimated, but fortunately there are only a very few situations where it's an issue. And as I said, the situations can be largely mitigated by database writers getting off their butts and fixing their backends to push the complexity to the indexes and away from trying to optimize the linear layout of the database by seek-writing into it. -Matt
- engendered 12y ago
  You do understand that flash erase blocks are around 128KB now, right? How is that relevant to your wrong claim about database 128-byte writes? You word that as if you're correcting me. I don't even know what point you're trying to make anymore, but you're trying to buttress your argument with what can best be described as diversions. Most people write far less than they think. Databases aren't particularly magical, any more than the web server log file that is written to a line at a time. Yes, people "give a shit" about a "mere" 100GB of writes, because the vast majority of real projects, including at major corporations, write far less than that per day. So are we just talking about dick measuring now?
  - dillondf 12y ago
    I'm sorry, engendered... but are you an idiot? Do you even understand the context of the conversation? -Matt
    - dang 12y ago
      > are you an idiot? Do you even understand This comment breaks the HN guidelines. We'd appreciate it if you'd read the site rules and follow them: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newswelcome.html https://news.ycombinator.com/newswelcome.html
  - AlisdairO 12y ago
    A block might be 8KB, but the actual update you're making to the block might be much smaller. I imagine by '128 byte write' he's talking about a lot of random row updates, where each row is 128 bytes. Now, if you're not too unlucky, many updates will be combined on the same page per checkpoint, but that's not a given. On the other hand, it's reasonably likely that several updates will be combined per erase block per checkpoint. A heavily indexed table can exhibit some pretty random write patterns, though. Additionally, the WAL has to be synced to disk every commit (unlike a web server log file), and WAL records can be very small. WAL is of course append-only, so you'd hope that a good SSD with a battery/cap backup would cache the writes and flush on the SSD erase block filling up.