4 ms·

>> the entire Windows kernel I/O stack has been async since the 1990s. Sincere Qs from a novice database developer, what good is all that async IO when you als

>> the entire Windows kernel I/O stack has been async since the 1990s. Sincere Qs from a novice database developer, what good is all that async IO when you also have to consider data integrity? How would you e.g. uphold the integrity of file that stores the state of a autoincrementing field if you didn't use a singleton writer? In what db layer would I be helped by async IO?

willvarfar 7y ago
Not involved, but giving an educated guess: Imagine a simple search like SELECT * FROM my_table WHERE foo = ? Imagine that the column foo is indexed. With blocking IO, the we go through the foo index, finding the rows that have the correct value of foo. The rows we find are on some kind of pages in the storage of the main table. So we now have a list of pages we have to fetch. With blocking IO, we would fetch a page at a time, process it, then request the next page. With async IO, we would request the fetching of all pages, as we discover we need them, and then process those pages in the order the IO subsystem can return them to us. Writes are the same; we have some number of pages we want to write to disk. With blocking IO, we write a page at a time and wait for it to be written. With async you can buffer up the writes, let the OS write them in the order it chooses, and wait for them all to finish.
- mortehu 7y ago
  Note that writes are asynchronous on Linux too, and only become synchronous when the kernel buffer for writing is full. No special code needed.
  - hinkley 7y ago
    Stuff like that is even more confusing because your performance analysis at moderate load tells you absolutely nothing about how the system will behave under high load (people who complain about GC often mention this as one of their grievances) Turns out this causes a lot of issues when working with large diverse teams in particular. If you have a team of 4 you can socialize the idea of avoiding situations like this.
jandrewrogers 7y ago
Data integrity and asynchronous I/O are unrelated concerns. The value of async I/O is that it exposes the concurrency and parallelism of the storage hardware to the software layer, enabling many types of performance optimizations. The vast majority of I/O operations in many database architectures do not have any sequential dependencies on other I/O operations, even within a single execution context. Data integrity is a function of design and implementation correctness, in addition to having robust mechanisms for dealing with data corruption during I/O, hardware failures, etc.
barrkel 7y ago
Using threads for in-flight IOs means burning up lots of memory with stacks, and burning ever more CPU in context switches as IOs complete. In practice this means using some kind of state machine (either explicit or in the form of coroutines) to pick up where logic paused at the initiation of the IO. The logic with respect to locking and concurrency control doesn't change; the costs in memory and CPU do. The CPU cost is increasing all the time due to all the cache attacks being revealed and the mitigations blowing away various cache contents, which is especially damaging in VM contexts like AWS where the mitigations can't be reasonably disabled.
derefr 7y ago
> How would you e.g. uphold the integrity of file that stores the state of a autoincrementing field if you didn't use a singleton writer? I mean, you wouldn't get write-concurrency for that field. And in fact you'd avoid serial PKs in general in a write-concurrent design, in favor of e.g. client-generated UUIDs. Where you'd get wins is when 1000 different clients are each opening a transaction and upserting 1000 rows, with, say, 50% of them being duplicates of other clients' rows. Rather than linearizing+deduping all of those right away, you can just write down all 1000 individual MVCC-world-state WAL log fragments concurrently, as 1000 queued write-batches. This lets you take actual advantage of all those write IOPS that RAIDed NVMe gets you.

willvarfar 7y ago

Not involved, but giving an educated guess: Imagine a simple search like SELECT * FROM my_table WHERE foo = ? Imagine that the column foo is indexed. With blocking IO, the we go through the foo index, finding the rows that have the correct value of foo. The rows we find are on some kind of pages in the storage of the main table. So we now have a list of pages we have to fetch. With blocking IO, we would fetch a page at a time, process it, then request the next page. With async IO, we would request the fetching of all pages, as we discover we need them, and then process those pages in the order the IO subsystem can return them to us. Writes are the same; we have some number of pages we want to write to disk. With blocking IO, we write a page at a time and wait for it to be written. With async you can buffer up the writes, let the OS write them in the order it chooses, and wait for them all to finish.

mortehu 7y ago
Note that writes are asynchronous on Linux too, and only become synchronous when the kernel buffer for writing is full. No special code needed.
- hinkley 7y ago
  Stuff like that is even more confusing because your performance analysis at moderate load tells you absolutely nothing about how the system will behave under high load (people who complain about GC often mention this as one of their grievances) Turns out this causes a lot of issues when working with large diverse teams in particular. If you have a team of 4 you can socialize the idea of avoiding situations like this.

jandrewrogers 7y ago

Data integrity and asynchronous I/O are unrelated concerns. The value of async I/O is that it exposes the concurrency and parallelism of the storage hardware to the software layer, enabling many types of performance optimizations. The vast majority of I/O operations in many database architectures do not have any sequential dependencies on other I/O operations, even within a single execution context. Data integrity is a function of design and implementation correctness, in addition to having robust mechanisms for dealing with data corruption during I/O, hardware failures, etc.

barrkel 7y ago

Using threads for in-flight IOs means burning up lots of memory with stacks, and burning ever more CPU in context switches as IOs complete. In practice this means using some kind of state machine (either explicit or in the form of coroutines) to pick up where logic paused at the initiation of the IO. The logic with respect to locking and concurrency control doesn't change; the costs in memory and CPU do. The CPU cost is increasing all the time due to all the cache attacks being revealed and the mitigations blowing away various cache contents, which is especially damaging in VM contexts like AWS where the mitigations can't be reasonably disabled.

derefr 7y ago

> How would you e.g. uphold the integrity of file that stores the state of a autoincrementing field if you didn't use a singleton writer? I mean, you wouldn't get write-concurrency for that field. And in fact you'd avoid serial PKs in general in a write-concurrent design, in favor of e.g. client-generated UUIDs. Where you'd get wins is when 1000 different clients are each opening a transaction and upserting 1000 rows, with, say, 50% of them being duplicates of other clients' rows. Rather than linearizing+deduping all of those right away, you can just write down all 1000 individual MVCC-world-state WAL log fragments concurrently, as 1000 queued write-batches. This lets you take actual advantage of all those write IOPS that RAIDed NVMe gets you.