4 ms·

I don't know ... we used to interleave our hard disk formats because a hard disk could stream data faster than an i386 CPU could ingest it, and there was plenty

I don't know ... we used to interleave our hard disk formats because a hard disk could stream data faster than an i386 CPU could ingest it, and there was plenty of database research done prior to 1990.

masklinn 7y ago
I assume the IO you're talking about is (/was) sequential. And DBs are specifically engineered to sequentialise their IO (e.g. clustered indexes). But even if SSDs do blow HDDs out the water on sequential IO, it's on random IO that the difference is most stark, the cost difference between sequential and random is much lower on SSD than on HDDs, and both random IO throughput and random IOPS shoot through the roof relative to spinning rust. An SSD might be an order of magnitude better than an HDD on sequential IO, but will often be 2 or 3 on random IO. That means the loss of throughput from sequential IO to random IO (while still there due to commands overhead and the like) is much, much, much smaller on an SSD than on an HDD. And the story is similar on the latency front, an HDD might have a command latency in the low tens milliseconds, an SSD in the low-mid tens microseconds.
- briffle 7y ago
  Postgresql allows you to tune the 'cost' of sequential and random IO, to help the planner figure out the best way to get the data. https://www.postgresql.org/docs/11/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS https://www.postgresql.org/docs/11/runtime-config-query.html...
- dorlaor 7y ago
  With LSM approach (log structured merge tree) we have at Scylla, we make all the writes batched/sequential and receive an awesome boost. Some reads can be sequential too if you scan through the clustering keys.
- temac 7y ago
  > low-mid tens microseconds From a modern CPU point of view, that's an eternity.
  - warrenm 7y ago
    Still 1000x faster than milliseconds for HDD
jandrewrogers 7y ago
Database algorithms are typically designed for worst case storage throughput i.e. the bandwidth floor, which for hard disks is not sequential access patterns. In the spinning hard disk days you assumed a few MB/s per disk, because you would often see that in real systems. Even with a disk array, it usually wasn't enough to keep the CPU busy if real-world access patterns kept disk throughput near the floor.

masklinn 7y ago

I assume the IO you're talking about is (/was) sequential. And DBs are specifically engineered to sequentialise their IO (e.g. clustered indexes). But even if SSDs do blow HDDs out the water on sequential IO, it's on random IO that the difference is most stark, the cost difference between sequential and random is much lower on SSD than on HDDs, and both random IO throughput and random IOPS shoot through the roof relative to spinning rust. An SSD might be an order of magnitude better than an HDD on sequential IO, but will often be 2 or 3 on random IO. That means the loss of throughput from sequential IO to random IO (while still there due to commands overhead and the like) is much, much, much smaller on an SSD than on an HDD. And the story is similar on the latency front, an HDD might have a command latency in the low tens milliseconds, an SSD in the low-mid tens microseconds.

briffle 7y ago
Postgresql allows you to tune the 'cost' of sequential and random IO, to help the planner figure out the best way to get the data. https://www.postgresql.org/docs/11/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS https://www.postgresql.org/docs/11/runtime-config-query.html...
dorlaor 7y ago
With LSM approach (log structured merge tree) we have at Scylla, we make all the writes batched/sequential and receive an awesome boost. Some reads can be sequential too if you scan through the clustering keys.
temac 7y ago
> low-mid tens microseconds From a modern CPU point of view, that's an eternity.
- warrenm 7y ago
  Still 1000x faster than milliseconds for HDD

jandrewrogers 7y ago

Database algorithms are typically designed for worst case storage throughput i.e. the bandwidth floor, which for hard disks is not sequential access patterns. In the spinning hard disk days you assumed a few MB/s per disk, because you would often see that in real systems. Even with a disk array, it usually wasn't enough to keep the CPU busy if real-world access patterns kept disk throughput near the floor.