Asynchronous IO for PostgreSQL
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Freund, Andres
For many workloads PostgresSQL currently cannot take full advantage of modern storage systems, like e.g. good SSD. But even for plain old spinning disks, we can achieve higher throughput. One of the major reasons for that is that the majority of storage IO postgres performs is done synchronously (see e.g. slide 8fff in-10-16-pgconf-milan-io/io.pdf for an illustration as to why that is a problem). This talk will discuss the outcome of a prototype to add asynchronous IO support to PostgreSQL. The talk will discuss: * How would async IO support for PG look like architecturally? * Performance numbers * What sub-problems exist that can be integrated separately * Currently that prototype uses linux' new io_uring asynchronous IO support * Which other OSs can be supported? Do we need to provide emulation of OS level async IO? Note that support for asynchronous IO is not directly the same as support for direct IO. This talk will mainly focus on asynchronicity, and direct IO only secondarily.
 Hi, my name is Anders Freund. I'm a Postgres developer and committer. I've worked on Postgres for quite a while now. I at the moment work at Microsoft focusing on open source Postgres and trying to make it better. Today, I want to talk about why I think we could make Postgres better if we added asynchronous IO support to Postgres scale. Why we do want that? One angle to that is that currently the way we do IO and particularly the way we do buffered IO is a major limitation. Why do you think that? One angle to look at is that it is a performance limitation. That's not the only one, but I think it's an important one. For example, if you just look at the sequential scan of a large table in this case, it's an 80-gear-by-tail. We can see that we spent a lot of time doing IO. Over eight workers, we spent 300 seconds and we end up with a total query execution time of 46 seconds. During that, we actually don't ever reach the full throughput of the drive, which is something on the order of 3.2 gigabytes per second, even though we have a lot of workers available to do the work. Why could that be? I think one good way to look at that is that as a CPU profile, because it turns out that most of those processes are actually pretty busy on a CPU user twice. A profile shows us that we spend a lot of the time doing in copy user enhanced. What that function does is it copies, or in this case, it copies data from the kernel page cache into the buffer provided to the read system call. Provided to the read system call. That's what we are doing with a CPU time. We're copying the data from the page cache, even in the case where the data is actually not in the page cache. Even then, we spend a lot of time doing that kind of work. If you overall want to look at how much time do we spend in user space and how much time we spend in kernel space, I fear used the perf to look at that once with the number of cycles as they are executed, and that's affected by things like TurboBoost and changes in frequency in ones in a way that should avoid that. You can see that around half the time. Thereon, it will then do an IO to the drive, like an IO request, and the drive will use DMA direct memory access to copy the data from the drive directly into RAM. It will not go through the CPU at all, instead talk to the memory controller to transfer the data without having to go each byte through the CPU. That makes it much faster because the CPU can do other things at the time. Once it's done, the drive will raise and interrupt and the operating system will then know that, hey, the work that the drive now signals is completed, is finished and now I can mark the page cache content as an outlet and from there on, it will then go on and copy the data, the operating system, into the buffers that the read system call has been provided. It has to be with a smarter mem copy with extra safeguards that prevent as from overwriting parts of the kernel memory or something like that. That's why you don't see a straight mem copy, but most of the time it's spent just basically doing the work that memcohe also does. Once that copy is complete, which in which time the CPU will be fully busy, the Cisco can return and the application now has valid data. But in doing all that time, the application is blocked. Comparing that to the direct IO case, and direct IO here means that we ask the operating system to bypass all buffering basically as much as possible and go straight to the drive as much as it can. We can see that the system call, there's no page cache that it has to look data from, so it can directly issue the IO to the drive and then the drive, and that's the important part, can again do DMA, but this time it doesn't do DMA into the page cache, but directly into the user space buffers that were provided to the system call as parameters, which means that this time we don't need to ever do that memory copy like operation. To summarize, direct IO basically is IO where there's no buffering in the kernel and where DMA often is possible between the user space buffers provided to read and write and so on, and the storage devices, and that the user space has a lot more control, but also a lot more responsibilities when using direct IO. There's no read ahead, there's no buffered writes and read and write are synchronous. Synchronous use is so slow for most purposes due to the latency that it's basically unusable for something like Postgres. Because we cannot do it synchronously, we have to do it asynchronously, meaning IO. There's other parts of the system where we have throughput problems, and that some of that is in the background writer, which often can't keep up, and not just because it has some algorithmic issues, which it has, but also just because of the number of outstanding writes cannot easily be done given the way we do IO. Currently, Checkpointer often can't keep up as well, which is quite bad because in a lot of workloads, all the writes are going to be done by Checkpointer, and delaying Checkpointers can lead to much more disk space being used for the wall and so on. So that's a problem. I think the biggest remaining problem though is that currently the wall writes are often bottlenecked by having to do buffered IO, and particularly by not being able to initiate IO when we have finished a certain amount of new wall, but we said we have to delay it until either commit time or until we have written so much that we can't buffer it anymore, and that means that we start the IO much later, and thus it also obviously finishes much later, and that leads to a lot of throughput issues. And I think basically those are hard to address fully without using IO. What the hell? I don't know what just happened, but whatever. There's also other sources of increased CPU overhead, not just the memory copy, but also the page cache management overhead in the kernel. You can see that, for example, by often in the top, you see casewapd, for example, use up a lot of CPU time. There's also just the overhead of all those very small system calls that we do to read eight kilobyte blocks, and that every time we have to, the kernel has to do some work in the FALSTEM specific code and then some page cache look-ups for that, and that adds up. And there's also the generic cost for this calls, which has also gone up due to the various security mitigations that were had to be introduced in the last few years. There are also reasons around the lack of control that we currently have with Beferd IO. We cannot really manage when the kernel writes data back, and that then leads to latency spikes, because when it writes the data back, suddenly wall writes and other activities are going to be slower, which then leads to user-visible latency spikes, and those can be very large. There's also problems around kernel-level read-ahead not being as effective as we would like it to, and some of that is related to us having one gigabyte segmented files for a table, so even if your table is more larger than one gigabyte, it'll consist out of multiple files, and every time you cross a segment boundary, the kernel doesn't know that it actually should immediately start a read-ahead or that it actually should have started a read-ahead before we reached that point because it doesn't know that those files related any sort of way. It also doesn't actually read-ahead fast enough, particularly not on very fast or on higher latency storage like cloud block storage devices. Our own manual read-ahead that we do, for example, for bit and heap scans also has pretty significant costs. We do a lot of redundant work once for the prefetching and once for the actual read, and the OS also has to do a lot of that duplicated work. And to just as a motivating example for the next 20 minutes, here is like a comparison of how long it takes to read the data, and I chose to, in those numbers here, to just look at the time PDPWarm takes, and PDPWarm is basically just a function in an extension Postgres that reads the data, and you can tell how it does that. And with a read parameter, it just reads the data using the operating system facility and then throws it away. And if you use buffered IEO, that then often will end up with the data in the kernel page cache, which can be useful. And in my new branch that adds support for asynchronous IEO, I added IEO versions of both the read version type of prewarming and also the buffer type of prewarming, which actually reads the data into shared buffers. And you can see that if you just want to read and master, it takes like 35 seconds and we use 80% of this view, and we see 2.5 gigabytes a second of IEO, even though the drive can actually do roughly like nearly 3.2 gigabytes a second. And if you compare that to the AAL version, you can see that it takes a considerably shorter amount of time and uses less CPU even though it takes a shorter amount of time and has a higher bandwidth. It's not quite the full capability of the drive. There's a few optimised agents we would need to fully reach that, but it's getting pretty close. And the difference is actually a bit starker if you look at using the buffer, that is like reading it into PostgreSQL shared buffer, because now we need to have the CPU time to actually do the management of PostgreSQL shared buffers. There's some optimisations we could do there too, but that applies overhead and we need to have CPU time to do that. And if we spent all the time doing memory copies from the kernel during the read system call, then we don't necessarily have that. So that's why we see the total time increase and the CPU time increase and, importantly, the bandwidth to be much slower. And there's just not enough CPU time to execute all those read calls. Comparing that to the asynchronous IEO version, here we can see that we take a bit more time than we're using read, but we can still nearly saturate the drive. There is some degradation performance, but it's still much better than either the read or the buffer version for master, and we are in fully utilising bottleneck. We still have CPU cycles left. So I think that's a pretty good motivating example for wanting to change something. So if it's all that great to have support for asynchronous IEO and buffered IEO, why don't we have that? And one big reason for that is that Linux's asynchronous IEO didn't use to support actually doing buffered IEO asynchronously. And there's a lot of workloads where using direct IEO is not really an option, just because you don't have the work that you're doing. You don't have the work knowledge about the specific workloads to, for example, set up shared buffers large enough. And it's pretty bad if you use direct IEO and your buffer caches or shared buffers is not large enough, because suddenly you pay each IEO that you do, if you do it synchronously, it will hit a disk. And it will do so one by one and each time it will full roundtrip. And if here like a short example of how bad that can be, and if you think back of my earlier example where using direct IEO plus IEO to do the reads PGP Warm, we saw that it was taking like 27 seconds, I think. And if we do like the synchronous version using direct IEO, because the parameter here is read and not read IEO, we can see that it suddenly takes 160 seconds, much, much slower than any of the other variants. And that's because we don't actually have any prefetching that we normally get from the operating system, and we're not using IEO, so we don't get any prefetching from that. And we can see that the throughput is drastically lower and that all the reads are very small, so there's no combining of requests. So it's pretty bad. So the consequences of not using direct IEO correctly can be pretty bad. And the consequence, for example, that can be because the implementation sucks, or it can be that nobody has configured shared buffers, so it's shared buffers, 64 megabytes into data is actually one terabyte large. Obviously that's not going to work well, but it can kind of work okay if we rely on buffer IEO because then the kernel will like save our butt to some degree. And there's other reasons, and it's a pretty large project to add it, and most people are not insane enough to add support for that because I've spent many weeks working on it, and it's a lot of work, and it's unclear whether it's realistic to get into Postgres. It also adds a fair bit of complexity to Postgres. We currently, for example, don't have the necessary read-head logic to do that for sequential scans and all that, but if we want to have direct IEO support, we need all that. So that's, I think, pretty okay reasons to not have it yet, but I think the time is well past where we should add support for it. So what has changed, basically, to make it viable to add it now? For me, the big reason is that Linux has gotten a new asynchronous IEO interface, and that interface is called IEO underscore U-ring, and it's been added to the kernel in version 5.1, and it's a fairly generic interface. There's a lot of different IEO operations that are supported, but they're not any more than were previously supported for the old IEO interface. One can do network IEO, one can do open-close files, and stuff like that, asynchronously, without not just file IEO. I just noticed a missing word here. The way it works is that it basically, in memory that's shared between kernel, and user space, there's two ring buffers, one for submitting IOs to the kernel, and one for getting completion events for those IOs that we submitted from the kernel back. And basically, each of those are single producer, single consumer ring buffers, and the nice thing is that single producer, single consumer ring buffers can actually be done without atomic operation overhead. And it also is very nice because we can submit a lot of different IOs without doing a system call. We can stage them into that queue, and then do the system call to actually let the kernel know, please process them now, and then you can get all of those back, which then saves us from the overhead of doing all of those system calls, which as I mentioned before, were always expensive, but got more expensive for the security mitigations. And it can also make operations asynchronous that aren't internally in the kernel actually, fully asynchronous by executing in the background in a kernel thread. And that means that the benefit of that is that the number of operations it can expose is much larger, because edge cases and so on can just be panted into those background kernel threads, whereas the majority of the operations can do like be done fully asynchronously without needing a dedicated thread in the kernel doing the work. The way it, as I mentioned, there's like basically these two ring buffers, and like one ring buffer has the submissions, and like the application basically just when it wants to do additional IO, it will can add the submission queue entry, and I'll get into what exactly that contains in a second. And once it has done that, it can like update the metadata basically of the queue saying, hey, this is a valid entry, and then from then on the kernel knows, hey, I can process data after that. And when the kernel process data, it will basically read like the other end of the queue, and it will read the entry like transfer it into its internal state, and then it can advance the tail to mark that the, to tell the user space that, hey, you can reuse this entry in the ring buffer now. Once it has done that, it can asynchronously perform that IO, that can be buffer IO, that can be direct IO, that can be various other things. It can be like the network and so on. And once that has completed, and during this time the application is free to do other things, submit more IO, whatever, once IO ring has detected, hey, the operation has completed, and it can be due to interrupts, it can be due to those kernel threads finishing their work. It will put like completion event into the equivalent queue to the user space, and it's basically just the same press that we had earlier where it puts in a queue and into a queue entry, and the user space basically just does what the IO ring earlier did, it updates the tail after getting the data. What do those submission queue entries actually look like? It's basically a pretty simple C-strike that we just write into memory, and there is an out opcode, and then there's parameters for that opcode, and the various of the opcodes are like reading data, writing data, fsyncing, and the network IO waiting for readiness of network sockets and so on. The details aren't actually that interesting. So before we go on how I'm using this in my prototype to add aero support, I think it's important to figure out what are the important design constraints for a real production level support for IO in Postgres. I think the first one is that we continue, as I mentioned, we need to continue to support BufferDio. The second one is that we need to abstract away the platform specific details, because we cannot have like leak all that across all of Postgres, because we start IO in lots of parts, we do it during DDL, we do it query execution, we do it doing backup, we do it doing check pointing. All of those places know about all the platform specific stuff we are going to be, it will be a mess. So all of that needs to be able to be hidden in a fairly small place. And one other thing, even though it might seem like an implementation detail, I think it's a pretty big design constraint is that we need to be able to do a cross process as in Quince IO. What I mean by that is that one process needs to be able to start a number of asynchronous requests, and other processes need to be able to process the completions if the originating, issuing backend isn't doing so. And the reason for that is that it's otherwise extremely easy to get into deadlocks. And while those deadlocks could be avoided otherwise, they would also reduce the potential benefits very, very substantially. Imagine for example, there's an error where one backend has a lock on a database, that's backend A, and backend B does a number of IOs on blocks Y and Z. And after that, backend A wants to access one of those blocks. And because the IO operations haven't finished yet, it has to wait. And that's a case that we already have, and it will just wait for the IO to complete. But it will only, in the existing implementation, it will wait for the originating backend to do so. But because it's now done asynchronously, the originating backend is now able to do other work. For example, it could wait on a lock that A already has. In this case, we would have a pretty classical deadlock. And there wouldn't be any progress anymore, because nobody would see the completion event for the IO, because the one, the backend that issued the IO is busy waiting for a lock. So we need to have, be able, like the backend A in this case needs to be able to see that, hey, this IO has completed, and I can unmark the IO in progress and like really make the buffer B valid. And that means that's a pretty substantial design constraint, I think. And I was able to solve that, but, and if somebody has great ideas how to not have this problem, I think it would be good, but I suspect that's not going to be possible. So how does the interface for Asynchus IO currently look like? Basically, whenever you're wanting some part of the code wants to do IO, it has to get an IO handle, and that's number one here. And with that, on that IO handle, it can optionally, when it wants to for its own benefits, register a completion handler, and it's a completion handler that runs only in the issuing process, even if another backend completes the IO, it will still run in the issuing backend, like a potentially a bit later. And when that's very useful, because inside that completion handler, it can then do stuff like, hey, the signal, the sequential scan that it can continue, it can do things like, hey, I want to, I got some of my prefetching done, so I can issue new prefetching, and it can do, like, submit further writes in a situation where we have limited the number of parallel writes to reduce the impact of the IO system, and all that kind of thing. So like, these completion callbacks I found make it much easier to efficiently issue IO. And after that, or if you don't do that, we actually associate IO with that IO handle. And in this case, we do that, do a read buffer, and the read buffer gets like a bunch of parameters that are very similar to what we currently do, except that it also has some information about which buffer that is. And it's important to note that when that the start read buffer associates an event with the IO that basically can complete the read buffer operation in any other backend, if that, if it receives the completion, or if it has to wait for that completion, and that means like it will, for a read, will mark the buffer as now, there's no IO imprugas anymore, and there's a bunch of complexity associated with that, but that allows us to avoid the deadlocks and also actually reduce us the number of context which is we need and so on. So like has a number of benefits. And we can do like the starting IO operation multiple times. And one thing to note here is that the start IO doesn't actually issue the IO directly, instead it will just state it in a local queue, in a per backend queue basically, and once, because that avoids, if we otherwise would incur the system call overhead every time, which we don't want to. And we then we have to somehow cost the IO, the pending IO to be submitted. And that happens if you issue back end, for example, waits on the IO, or if it says, hey, please flush all pending IOs out. And once that, like the way that happens is that IO.C now submits the IOs via IOU ring. And that's the first part, place basically where IOU ring actually plays a role and none of the calling code has to know anything about it, because all of that is abstracted. Now, most code doesn't actually want to have to deal with in real IOs on like that low level. They, you've, you don't want to add like a lot of interval reads, just management, like how many reads are going on into security, like, you know, in sequential scans into other forms of scans into like DL code. Instead, like there's a number of generic helpers that say, hey, please, this is a sequential or read or a read where I know ahead of time a few more blocks that I need. And then there's a helper, PG streaming read, that knows how to do prefetching based on that. And it works by having a callback that tells it whenever it needs more data, when to have enough deep enough read ahead window, or in a distance from the current position in the scan. And then it will ask that callback that is provided by the caller, hey, which one is the next block that I actually would need to prefetch. And that's big, makes it easier for the, I found that that makes the calling code a lot easier because it doesn't have to be as careful. I'd want to give it like more work to need to issue because that turned out to be very duplicative between different users. That can be just the next block in a sequential order that what the callback returns, or for example, in the case of vacuum, it might not be, it might just be the next block that we actually need to vacuum. So there can be large gaps, and that allows us to still do very efficient AO across that. Those gaps where we don't have to, where the visibility map tells us that we don't have to do AO. And that streaming read helper internally uses the local completion handler to get called back whenever an AO finishes, and it uses from there on then basically inside the callback. So, okay, my read ahead window is this large, but I haven't actually issued that many requests, so now I can issue more. And similarly, there's a streaming write helper that basically has a queue of outstanding writes and make sure to issue them, but also not to have too many of them in parallel outstanding to, so we don't overload the system. And it's likely that he would have different parts of the system configured with different queue depths. So we can have, for example, a vacuum have a low queue depth for like the data file writes, so it's not overly impactful, but still can be like a good amount of progress when there's not a lot of concurrent activity. And the way that works on a, as part of the whole system is that there's basically a number of new shared memory structures that are independent of which AIO implementation we are using, and that's some per backend data structures that also includes like stats about how many AOs are in progress for that backend, which I think will be very useful for like some admission control like and for statistics gathering type work. And then there's a number of these AIO handles, and that's the PGA in progress area. And those are independent of the AIO implementation. And at the moment that only the only implementation that's implemented is IE ring, and the goal would be to have multiple IE rings there currently be create multiple ones, but we only use one. And that has some performance implications. But like I just haven't gotten around to implementing more. And those AIO progress structs basically get turned into something that IE ring understands when submitting pending requests, and then the, those requests actually reference buffers and shared buffers in the, in the case of like a read buffer operation. And with that, IE operation there's the, there's a field where we can associate user data with that, and that then refers again into the PGA or in progress entry that's associated and that allows us to then complete the, like issue the callback that will mark the buffer as valid, and then also do the necessary work that the issuing back and can like call that callback that the completion callback. And attached to like those, IE entries are then used by the, the various helpers or by code directly using it, for example, wall at the moment directly issues like IE is because it has like a bit too specific users that can easily be generalized. And those helpers are then again used by like heap aim for a scan, heap scans and vacuum and for checkpoint and background writer and so on. So what are the results of the prototype? I think it's pretty decent for a prototype that is still like somewhat early in development. See, very significantly increased throughput for both low latency drives and very large increased throughput for high latency and high throughput drives, as for example, aforementioned cloud block devices. And that's because we have a lot better control over how deep the cues are. And this is the case both with BufferDio and non BufferDio and some of the benefit there comes from being able to issue multiple requests for different blocks at the same time. Some of it is allows is from doing being able to do reads for multiple blocks at the same time, which then reduces the overhead on the file system and page cache level level. And for like the biggest benefit beneficiary I think are is analytic style queries because they do often read a lot of data. And so they're very often very intensive and often the data doesn't fit into memory. And I did, for example, run all the TPC-H queries on my workstation and on like using scale 100 and using different amounts of available memory. They were, unless I used like ridiculously small amounts of memory every time both the BufferDio and the asynchronous DirectO variants were faster than the current implementation, which I think is a pretty good result. There's some edge cases where that's not the case in particularly where the benefits reduce and that's when using a lot of workers for parallel query execution. And that's because of the one U-ring that we currently have. And then there's contention on locks protecting that U-ring. And that's just because I haven't gotten around to fixing that. There's also some other cases if you don't vacuum the data before running queries, then like hotpuning and hindpits it can cause problems because the ring buffers used in sequential scans and vacuum don't currently use AEO yet. So there can be like more synchronous writes. Sometimes background writer fixes that especially if like one makes it more aggressive. But it's a problem that we have to fix, but it's again, I think not a fundamental issue. It's just something that needs to be done. For all to P workloads, the benefits are a bit smaller at the moment. They're off like it's good, but better like I saw PG bench workloads with like 30% better. But I found also some cases where it's a bit slower and I think both of those problems are both not yet much larger benefits in the good cases and the slightly bad slower cases are mildly bad. Slow cases are related to wall and to it not yet using a synchronous I O very well. And that's I think partially because it's not that easy to do, but partially also because I haven't yet spent too much time on it. And I think we can actually get like a very significant speed ups there, but it just needs like some largest changes in the wall code. And it's like a significant amount of work on its own. But I think it's unrealistic to add to get this committed without those improvements. There's also a lot of place that should do better prefetching. There's a talk by Thomas Monroe earlier in quote unquote today, I think that addresses some of that. I assume I haven't seen the slides or her just talk because it's not yet hasn't yet happened in my timeline. The vacuum is also considerably faster for the first for the heaps scan because that uses the prefetching logic. And I saw like universal benefits. I didn't find a single case where that the first phase is slower. There is cases where the second where the index scans are slower because currently the B3 vacuum encode doesn't yet use the brief read ahead helper, but that's pretty easy to add. I just haven't gotten around to it. There's also again the problem of like not having yet efficient logic for when to write data back or not using that logic yet. And the other two case where very significant benefits are where I said earlier that there was other biggest problems. There's also big improvements for checkpointer and BG writer and again both for Buffett and non Buffett IO. But the benefits are bigger with non Buffett IO just because that allows us to get better CPU usage by not having to do all those the overhead of like the copying and the management of the page cache. What's next basically with this prototype. I think to fully be able to evaluate it we need to use a in more places and that's basically just the place that I previously mentioned. There's the scalability problems that come from just using one U-ring and like having a bunch of other stupid locking that it just haven't addressed yet. And I think then the big issue is to make sure that wall is can use with the current API with the then current API. And then we should figure out whether it's feasible to use like a process based fallbacks for platforms where we don't have a native asynchronous IO implementation. And that would be easier if postcards were threaded but it's not so we have to make do with processes and that adds some complexity but I think it might be doable and it would be very nice if we wouldn't have to maintain several code types. For using asynchronous IO and not using asynchronous IO because I think the complexity effects of that would be pretty annoying. Yeah, and with that I am done and I hope I have not confused you too much and here's some links to read up. And here's some links to read up on if both if you are confused and not confused and if there are questions, I hope it will work that I can answer them now. Thanks. And we're back with Q&A with Andres. Take it away, sir. So the first question I see here is from Jeff Davis and it's asking, can you clarify where the benefits of AIO come from when still doing buffer IO? And like I said, there are several different cases and depending on which work you're looking at, one benefit is that you just have fewer system calls and you can combine the different request for different pages into one larger request because we have the necessary data and that makes like the file system and the page cache overhead lower because the kernel doesn't have to do the repeated work for each of the different 8 kilobytes IOs and that can be a significant benefit. There's also operations that when doing buffered IO are still synchronous like for example S things and they now can be made asynchronous which can give you quite substantial benefits for stuff like wall or checkpointing and so on. We also often hit short term cases where the operating system basically throttles our rights or whatever and then we, the normally buffered and kind of asynchronous system calls suddenly get very slow and we wait and those kind of delays are not there anymore if we use like as I consider. The next question is from Fabitio Mello and it's about whether the sufficient completion queue can be new bottlenecks and yes, they can be but one has to do a lot of IO. Currently some bottlenecks around that but they're all because we have to acquire locks around them so they're on the locks not actually on the IO ring buffers themselves and the reason we have to take locks is basically because like the file descriptors in the queue entries refer to like local process file descriptors and so if you didn't take the locks it would get very confused. There are some ways to address in different ways but they can be but I don't think it will be a big problem. The next question is from Jay P Dizin. What are you expecting to be the kernel baseline to use in the end-list of work on buffered IO and in IO ring? I would hope that it will work with 5.1. I haven't actually tested 5.1 because I think it's a bit too early because it's not everything is done the right way so it seems like it would work to evaluate that it works with 5.1. I would hope that it works with 5.1 because I think we're not going to use too much of the newer system. I think there might be some limits performance lines where not going to be as fast as the later versions of the current versions but that seems okay and not really our problem. Deep asks, you mentioned that readahead is hampered by the 1GB file binders. How adversely will such an event affect readahead? It really depends on how, what kind of storage you are on. If you're on a very low latency storage device like for example local SSE then it doesn't hurt you too much. You see a small dip but if you instead look at a very fast high latency device like cloud storage situation then you can see that it is a good bit of throughput because it will take quite a while for the performance to catch back up. It takes like 100 megabytes and then it will just tell you the call up at the previous processing speed and that gives you a loss of 10-20% of throughput and depending on whether there is other nodes on top of that sequential scan that are now going to be slower it can be worse. Deep asks, do you see wall as a requirement for inclusion? Yes, I think so. Not necessarily because of the way the performance improvements but more because it might imply design constraints that would be harder to address if you haven't gotten it right initially and then we would have to change more code around it. So I think at the very, very least you would want to have the wall working well in a separate patch, a part of the patch series. We don't necessarily have to commit that in the first iteration but I think we have to know exactly how it works. And Jeff asks, how did you solve the AO across processes issues and basically the reason that it works is that we have the IOE ring, we map them in postmaster so every different process has access to the co-position ring. And upon completion, so if an IO is blocked or if a backend is blocked on a buffer, in the buffer there is a notion like a new reference to which one of those PGAO and progress structs is associated with the IO and then you can go look at that and then you can extract and see which of the rings is being used and with that you can then wait for the events in the ring to have completed and access to the completion associated with that. There are some complexities with having the IO rings shared between processes because when you submit right like events to the submission to you we have to take a lock while submitting because the file descriptors that are in there are going to refer to the submitting process and so if another process submitted to the queue entries it would, the IO worked differently. There are some ways IOE ring can avoid that but I'm not sure, it's not all, that's very worked out well for our case and I don't think the locking around that will be a problem because it can have like one IO ring for each process and just have the completion like basically shared and yeah, so I think that should work out okay, I haven't yet fully done all the value teamwork. And there's another question and do you see this expanding to the network layer too? And yes, I think so. In particular just avoiding when we currently read data from the client, when we finish querying for example, we send out the data then we call like the receive and then receive will say I'm in long locking socket and there's no data available yet and then you use E-PAL to wait for the network IO and if you can combine all of that work into one system call we actually save a lot of CPU cycles and contact switches and for transaction workloads that like reduces the overheads quite essentially I think I've very little done a tiny bit of prototyping it to experiment with that and I got, it doesn't definitely even correct that I didn't want to include the numbers but that's all like pretty good speed up for a PD bench type like read only work. Yeah. And I think that's it for the questions now. Thank you everyone. Thank you, Andres. Goodbye. Thank you.
 Hi, my name is Anders Freund.  I'm a Postgres developer and  committer. I've worked on Postgres for quite a while now.  I at the moment work at Microsoft focusing on  open source Postgres and trying to make it better.  Today, I want to talk about why I think we could make  Postgres better if we added  asynchronous IO support to Postgres scale.  Why we do want that?  One angle to that is that  currently the way we do IO and particularly  the way we do buffered IO is a major limitation.  Why do you think that?  One angle to look at is that it is a performance limitation.  That's not the only one,  but I think it's an important one.  For example, if you just look at  the sequential scan of a large table in this case,  it's an 80-gear-by-tail.  We can see that we spent a lot of time doing IO.  Over eight workers,  we spent 300 seconds and we end up with  a total query execution time of 46 seconds.  During that, we actually don't ever reach  the full throughput of the drive,  which is something on the order of 3.2 gigabytes per second,  even though we have a lot of  workers available to do the work.  Why could that be?  I think one good way to look at that is that as a CPU profile,  because it turns out that most of  those processes are actually pretty busy on a CPU user twice.  A profile shows us that we spend a lot of  the time doing in copy user enhanced.  What that function does is it copies,  or in this case, it copies data from  the kernel page cache into the buffer provided  to the read system call.  Provided to the read system call.  That's what we are doing with a CPU time.  We're copying the data from the page cache,  even in the case where the data is actually not in the page cache.  Even then, we spend a lot of time doing that kind of work.  If you overall want to look at how much time do we spend  in user space and how much time we spend in kernel space,  I fear used the perf to look at that  once with the number of cycles as they are executed,  and that's affected by things like  TurboBoost and changes in frequency in  ones in a way that should avoid that.  You can see that around half the time.  Thereon, it will then do an IO to the drive,  like an IO request, and the drive will use  DMA direct memory access to copy  the data from the drive directly into RAM.  It will not go through the CPU at all,  instead talk to the memory controller  to transfer the data without having to go  each byte through the CPU.  That makes it much faster because the CPU can do  other things at the time.  Once it's done, the drive will raise and  interrupt and the operating system will then know that,  hey, the work that the drive now signals is completed,  is finished and now I can mark  the page cache content as an outlet and from there on,  it will then go on and copy the data,  the operating system, into the buffers  that the read system call has been provided.  It has to be with a smarter mem copy with  extra safeguards that prevent as from  overwriting parts of the kernel memory or something like that.  That's why you don't see a straight mem copy,  but most of the time it's spent just  basically doing the work that memcohe also does.  Once that copy is complete,  which in which time the CPU will be fully busy,  the Cisco can return and the application now has valid data.  But in doing all that time, the application is blocked.  Comparing that to the direct IO case,  and direct IO here means that we ask  the operating system to bypass  all buffering basically as much as possible  and go straight to the drive as much as it can.  We can see that the system call,  there's no page cache that it has to look data from,  so it can directly issue the IO to the drive and then the drive,  and that's the important part,  can again do DMA,  but this time it doesn't do DMA into the page cache,  but directly into the user space buffers  that were provided to the system call as parameters,  which means that this time we don't need to ever do  that memory copy like operation.  To summarize, direct IO basically is IO where there's no  buffering in the kernel and where DMA often is possible  between the user space buffers provided to read and write and so on,  and the storage devices,  and that the user space has a lot more control,  but also a lot more responsibilities when using direct IO.  There's no read ahead,  there's no buffered writes and read and write are synchronous.  Synchronous use is so slow for most purposes due to the latency  that it's basically unusable for something like Postgres.  Because we cannot do it synchronously,  we have to do it asynchronously, meaning IO.  There's other parts of the system where we have throughput problems,  and that some of that is in the background writer,  which often can't keep up,  and not just because it has some algorithmic issues,  which it has, but also just because of the number of  outstanding writes cannot easily be done given the way we do IO.  Currently, Checkpointer often can't keep up as well,  which is quite bad because in a lot of workloads,  all the writes are going to be done by Checkpointer,  and delaying Checkpointers can lead to much more disk space  being used for the wall and so on.  So that's a problem.  I think the biggest remaining problem though is that  currently the wall writes are often bottlenecked  by having to do buffered IO,  and particularly by not being able to initiate IO  when we have finished a certain amount of new wall,  but we said we have to delay it until either commit time  or until we have written so much that we can't buffer it anymore,  and that means that we start the IO much later,  and thus it also obviously finishes much later,  and that leads to a lot of throughput issues.  And I think basically those are hard to address fully  without using IO.  What the hell?  I don't know what just happened, but whatever.  There's also other sources of increased CPU overhead,  not just the memory copy, but also the page cache management overhead  in the kernel.  You can see that, for example, by often in the top,  you see casewapd, for example, use up a lot of CPU time.  There's also just the overhead of all those very small system calls  that we do to read eight kilobyte blocks,  and that every time we have to, the kernel has to do some work  in the FALSTEM specific code and then some page cache look-ups  for that, and that adds up.  And there's also the generic cost for this calls,  which has also gone up due to the various security mitigations  that were had to be introduced in the last few years.  There are also reasons around the lack of control  that we currently have with Beferd IO.  We cannot really manage when the kernel writes data back,  and that then leads to latency spikes,  because when it writes the data back,  suddenly wall writes and other activities are going to be slower,  which then leads to user-visible latency spikes,  and those can be very large.  There's also problems around kernel-level read-ahead  not being as effective as we would like it to,  and some of that is related to us having one gigabyte segmented files  for a table, so even if your table is more larger than one gigabyte,  it'll consist out of multiple files,  and every time you cross a segment boundary,  the kernel doesn't know that it actually should immediately start a read-ahead  or that it actually should have started a read-ahead before we reached that point  because it doesn't know that those files related any sort of way.  It also doesn't actually read-ahead fast enough,  particularly not on very fast or on higher latency storage  like cloud block storage devices.  Our own manual read-ahead that we do, for example,  for bit and heap scans also has pretty significant costs.  We do a lot of redundant work once for the prefetching  and once for the actual read,  and the OS also has to do a lot of that duplicated work.  And to just as a motivating example for the next 20 minutes,  here is like a comparison of how long it takes to read the data,  and I chose to, in those numbers here,  to just look at the time PDPWarm takes,  and PDPWarm is basically just a function in an extension Postgres that reads the data,  and you can tell how it does that.  And with a read parameter, it just reads the data using the operating system facility  and then throws it away.  And if you use buffered IEO, that then often will end up with the data  in the kernel page cache, which can be useful.  And in my new branch that adds support for asynchronous IEO,  I added IEO versions of both the read version type of prewarming  and also the buffer type of prewarming,  which actually reads the data into shared buffers.  And you can see that if you just want to read and master,  it takes like 35 seconds and we use 80% of this view,  and we see 2.5 gigabytes a second of IEO,  even though the drive can actually do roughly like nearly 3.2 gigabytes a second.  And if you compare that to the AAL version,  you can see that it takes a considerably shorter amount of time  and uses less CPU even though it takes a shorter amount of time  and has a higher bandwidth.  It's not quite the full capability of the drive.  There's a few optimised agents we would need to fully reach that,  but it's getting pretty close.  And the difference is actually a bit starker  if you look at using the buffer,  that is like reading it into PostgreSQL shared buffer,  because now we need to have the CPU time  to actually do the management of PostgreSQL shared buffers.  There's some optimisations we could do there too,  but that applies overhead and we need to have CPU time to do that.  And if we spent all the time doing memory copies from the kernel  during the read system call, then we don't necessarily have that.  So that's why we see the total time increase  and the CPU time increase and, importantly, the bandwidth to be much slower.  And there's just not enough CPU time to execute all those read calls.  Comparing that to the asynchronous IEO version,  here we can see that we take a bit more time than we're using read,  but we can still nearly saturate the drive.  There is some degradation performance,  but it's still much better than either the read or the buffer version for master,  and we are in fully utilising bottleneck.  We still have CPU cycles left.  So I think that's a pretty good motivating example for wanting to change something.  So if it's all that great to have support for asynchronous IEO and buffered IEO,  why don't we have that?  And one big reason for that is that Linux's asynchronous IEO  didn't use to support actually doing buffered IEO asynchronously.  And there's a lot of workloads where using direct IEO is not really an option,  just because you don't have the work that you're doing.  You don't have the work knowledge about the specific workloads to, for example,  set up shared buffers large enough.  And it's pretty bad if you use direct IEO and your buffer caches  or shared buffers is not large enough,  because suddenly you pay each IEO that you do,  if you do it synchronously, it will hit a disk.  And it will do so one by one and each time it will full roundtrip.  And if here like a short example of how bad that can be,  and if you think back of my earlier example where using direct IEO plus IEO  to do the reads PGP Warm, we saw that it was taking like 27 seconds, I think.  And if we do like the synchronous version using direct IEO,  because the parameter here is read and not read IEO,  we can see that it suddenly takes 160 seconds, much, much slower than any of the other variants.  And that's because we don't actually have any prefetching  that we normally get from the operating system,  and we're not using IEO, so we don't get any prefetching from that.  And we can see that the throughput is drastically lower  and that all the reads are very small, so there's no combining of requests.  So it's pretty bad.  So the consequences of not using direct IEO correctly can be pretty bad.  And the consequence, for example, that can be because the implementation sucks,  or it can be that nobody has configured shared buffers,  so it's shared buffers, 64 megabytes into data is actually one terabyte large.  Obviously that's not going to work well, but it can kind of work okay  if we rely on buffer IEO because then the kernel will like  save our butt to some degree.  And there's other reasons, and it's a pretty large project to add it,  and most people are not insane enough to add support for that  because I've spent many weeks working on it,  and it's a lot of work, and it's unclear whether it's realistic to get into Postgres.  It also adds a fair bit of complexity to Postgres.  We currently, for example, don't have the necessary read-head logic  to do that for sequential scans and all that,  but if we want to have direct IEO support, we need all that.  So that's, I think, pretty okay reasons to not have it yet,  but I think the time is well past where we should add support for it.  So what has changed, basically, to make it viable to add it now?  For me, the big reason is that Linux has gotten a new asynchronous IEO interface,  and that interface is called IEO underscore U-ring,  and it's been added to the kernel in version 5.1,  and it's a fairly generic interface.  There's a lot of different IEO operations that are supported,  but they're not any more than were previously supported for the old IEO interface.  One can do network IEO, one can do open-close files,  and stuff like that, asynchronously, without not just file IEO.  I just noticed a missing word here.  The way it works is that it basically, in memory that's shared between kernel,  and user space, there's two ring buffers,  one for submitting IOs to the kernel,  and one for getting completion events for those IOs that we submitted from the kernel back.  And basically, each of those are single producer, single consumer ring buffers,  and the nice thing is that single producer, single consumer ring buffers  can actually be done without atomic operation overhead.  And it also is very nice because we can submit a lot of different IOs without doing a system call.  We can stage them into that queue, and then do the system call to actually let the kernel know,  please process them now, and then you can get all of those back,  which then saves us from the overhead of doing all of those system calls,  which as I mentioned before, were always expensive,  but got more expensive for the security mitigations.  And it can also make operations asynchronous that aren't internally in the kernel actually,  fully asynchronous by executing in the background in a kernel thread.  And that means that the benefit of that is that the number of operations it can expose is much larger,  because edge cases and so on can just be panted into those background kernel threads,  whereas the majority of the operations can do like be done fully asynchronously without needing a dedicated thread in the kernel doing the work.  The way it, as I mentioned, there's like basically these two ring buffers,  and like one ring buffer has the submissions,  and like the application basically just when it wants to do additional IO,  it will can add the submission queue entry,  and I'll get into what exactly that contains in a second.  And once it has done that, it can like update the metadata basically of the queue saying,  hey, this is a valid entry, and then from then on the kernel knows, hey, I can process data after that.  And when the kernel process data, it will basically read like the other end of the queue,  and it will read the entry like transfer it into its internal state,  and then it can advance the tail to mark that the, to tell the user space that, hey, you can reuse this entry in the ring buffer now.  Once it has done that, it can asynchronously perform that IO, that can be buffer IO,  that can be direct IO, that can be various other things.  It can be like the network and so on.  And once that has completed, and during this time the application is free to do other things, submit more IO, whatever,  once IO ring has detected, hey, the operation has completed,  and it can be due to interrupts, it can be due to those kernel threads finishing their work.  It will put like completion event into the equivalent queue to the user space,  and it's basically just the same press that we had earlier where it puts in a queue and into a queue entry,  and the user space basically just does what the IO ring earlier did, it updates the tail after getting the data.  What do those submission queue entries actually look like?  It's basically a pretty simple C-strike that we just write into memory,  and there is an out opcode, and then there's parameters for that opcode,  and the various of the opcodes are like reading data, writing data, fsyncing,  and the network IO waiting for readiness of network sockets and so on.  The details aren't actually that interesting.  So before we go on how I'm using this in my prototype to add aero support,  I think it's important to figure out what are the important design constraints for a real production level support for IO in Postgres.  I think the first one is that we continue, as I mentioned, we need to continue to support BufferDio.  The second one is that we need to abstract away the platform specific details,  because we cannot have like leak all that across all of Postgres,  because we start IO in lots of parts, we do it during DDL, we do it query execution,  we do it doing backup, we do it doing check pointing.  All of those places know about all the platform specific stuff we are going to be, it will be a mess.  So all of that needs to be able to be hidden in a fairly small place.  And one other thing, even though it might seem like an implementation detail,  I think it's a pretty big design constraint is that we need to be able to do a cross process as in Quince IO.  What I mean by that is that one process needs to be able to start a number of asynchronous requests,  and other processes need to be able to process the completions if the originating, issuing backend isn't doing so.  And the reason for that is that it's otherwise extremely easy to get into deadlocks.  And while those deadlocks could be avoided otherwise, they would also reduce the potential benefits very, very substantially.  Imagine for example, there's an error where one backend has a lock on a database, that's backend A,  and backend B does a number of IOs on blocks Y and Z.  And after that, backend A wants to access one of those blocks.  And because the IO operations haven't finished yet, it has to wait.  And that's a case that we already have, and it will just wait for the IO to complete.  But it will only, in the existing implementation, it will wait for the originating backend to do so.  But because it's now done asynchronously, the originating backend is now able to do other work.  For example, it could wait on a lock that A already has.  In this case, we would have a pretty classical deadlock.  And there wouldn't be any progress anymore, because nobody would see the completion event for the IO,  because the one, the backend that issued the IO is busy waiting for a lock.  So we need to have, be able, like the backend A in this case needs to be able to see that, hey, this IO has completed,  and I can unmark the IO in progress and like really make the buffer B valid.  And that means that's a pretty substantial design constraint, I think.  And I was able to solve that, but, and if somebody has great ideas how to not have this problem,  I think it would be good, but I suspect that's not going to be possible.  So how does the interface for Asynchus IO currently look like?  Basically, whenever you're wanting some part of the code wants to do IO, it has to get an IO handle,  and that's number one here.  And with that, on that IO handle, it can optionally, when it wants to for its own benefits,  register a completion handler, and it's a completion handler that runs only in the issuing process,  even if another backend completes the IO, it will still run in the issuing backend, like a potentially a bit later.  And when that's very useful, because inside that completion handler, it can then do stuff like, hey,  the signal, the sequential scan that it can continue, it can do things like, hey, I want to,  I got some of my prefetching done, so I can issue new prefetching, and it can do, like,  submit further writes in a situation where we have limited the number of parallel writes to reduce the impact of the IO system,  and all that kind of thing.  So like, these completion callbacks I found make it much easier to efficiently issue IO.  And after that, or if you don't do that, we actually associate IO with that IO handle.  And in this case, we do that, do a read buffer, and the read buffer gets like a bunch of parameters that are very similar to what we currently do,  except that it also has some information about which buffer that is.  And it's important to note that when that the start read buffer associates an event with the IO that basically can complete the read buffer operation  in any other backend, if that, if it receives the completion, or if it has to wait for that completion,  and that means like it will, for a read, will mark the buffer as now, there's no IO imprugas anymore,  and there's a bunch of complexity associated with that, but that allows us to avoid the deadlocks and also actually reduce us the number of context which is we need and so on.  So like has a number of benefits.  And we can do like the starting IO operation multiple times.  And one thing to note here is that the start IO doesn't actually issue the IO directly, instead it will just state it in a local queue, in a per backend queue basically,  and once, because that avoids, if we otherwise would incur the system call overhead every time, which we don't want to.  And we then we have to somehow cost the IO, the pending IO to be submitted.  And that happens if you issue back end, for example, waits on the IO, or if it says, hey, please flush all pending IOs out.  And once that, like the way that happens is that IO.C now submits the IOs via IOU ring.  And that's the first part, place basically where IOU ring actually plays a role and none of the calling code has to know anything about it, because all of that is abstracted.  Now, most code doesn't actually want to have to deal with in real IOs on like that low level.  They, you've, you don't want to add like a lot of interval reads, just management, like how many reads are going on into security, like, you know, in sequential scans into other forms of scans into like DL code.  Instead, like there's a number of generic helpers that say, hey, please, this is a sequential or read or a read where I know ahead of time a few more blocks that I need.  And then there's a helper, PG streaming read, that knows how to do prefetching based on that.  And it works by having a callback that tells it whenever it needs more data, when to have enough deep enough read ahead window, or in a distance from the current position in the scan.  And then it will ask that callback that is provided by the caller, hey, which one is the next block that I actually would need to prefetch.  And that's big, makes it easier for the, I found that that makes the calling code a lot easier because it doesn't have to be as careful.  I'd want to give it like more work to need to issue because that turned out to be very duplicative between different users.  That can be just the next block in a sequential order that what the callback returns, or for example, in the case of vacuum, it might not be, it might just be the next block that we actually need to vacuum.  So there can be large gaps, and that allows us to still do very efficient AO across that.  Those gaps where we don't have to, where the visibility map tells us that we don't have to do AO.  And that streaming read helper internally uses the local completion handler to get called back whenever an AO finishes, and it uses from there on then basically inside the callback.  So, okay, my read ahead window is this large, but I haven't actually issued that many requests, so now I can issue more.  And similarly, there's a streaming write helper that basically has a queue of outstanding writes and make sure to issue them, but also not to have too many of them in parallel outstanding to, so we don't overload the system.  And it's likely that he would have different parts of the system configured with different queue depths.  So we can have, for example, a vacuum have a low queue depth for like the data file writes, so it's not overly impactful, but still can be like a good amount of progress when there's not a lot of concurrent activity.  And the way that works on a, as part of the whole system is that there's basically a number of new shared memory structures that are independent of which AIO implementation we are using, and that's some per backend data structures that also includes like stats about how many AOs are in progress for that backend,  which I think will be very useful for like some admission control like and for statistics gathering type work. And then there's a number of these AIO handles, and that's the PGA in progress area.  And those are independent of the AIO implementation. And at the moment that only the only implementation that's implemented is IE ring, and the goal would be to have multiple IE rings there currently be create multiple ones, but we only use one.  And that has some performance implications. But like I just haven't gotten around to implementing more.  And those AIO progress structs basically get turned into something that IE ring understands when submitting pending requests, and then the, those requests actually reference buffers and shared buffers in the, in the case of like a read buffer operation.  And with that, IE operation there's the, there's a field where we can associate user data with that, and that then refers again into the PGA or in progress entry that's associated and that allows us to then complete the, like issue the callback that will mark the buffer as valid,  and then also do the necessary work that the issuing back and can like call that callback that the completion callback.  And attached to like those, IE entries are then used by the, the various helpers or by code directly using it, for example, wall at the moment directly issues like IE is because it has like a bit too specific users that can easily be generalized.  And those helpers are then again used by like heap aim for a scan, heap scans and vacuum and for checkpoint and background writer and so on.  So what are the results of the prototype? I think it's pretty decent for a prototype that is still like somewhat early in development.  See, very significantly increased throughput for both low latency drives and very large increased throughput for high latency and high throughput drives, as for example, aforementioned cloud block devices.  And that's because we have a lot better control over how deep the cues are.  And this is the case both with BufferDio and non BufferDio and some of the benefit there comes from being able to issue multiple requests for different blocks at the same time.  Some of it is allows is from doing being able to do reads for multiple blocks at the same time, which then reduces the overhead on the file system and page cache level level.  And for like the biggest benefit beneficiary I think are is analytic style queries because they do often read a lot of data.  And so they're very often very intensive and often the data doesn't fit into memory.  And I did, for example, run all the TPC-H queries on my workstation and on like using scale 100 and using different amounts of available memory.  They were, unless I used like ridiculously small amounts of memory every time both the BufferDio and the asynchronous DirectO variants were faster than the current implementation, which I think is a pretty good result.  There's some edge cases where that's not the case in particularly where the benefits reduce and that's when using a lot of workers for parallel query execution.  And that's because of the one U-ring that we currently have.  And then there's contention on locks protecting that U-ring.  And that's just because I haven't gotten around to fixing that.  There's also some other cases if you don't vacuum the data before running queries, then like hotpuning and hindpits it can cause problems because the ring buffers used in sequential scans and vacuum don't currently use AEO yet.  So there can be like more synchronous writes.  Sometimes background writer fixes that especially if like one makes it more aggressive.  But it's a problem that we have to fix, but it's again, I think not a fundamental issue.  It's just something that needs to be done.  For all to P workloads, the benefits are a bit smaller at the moment.  They're off like it's good, but better like I saw PG bench workloads with like 30% better.  But I found also some cases where it's a bit slower and I think both of those problems are both not yet much larger benefits in the good cases and the slightly bad slower cases are mildly bad.  Slow cases are related to wall and to it not yet using a synchronous I O very well. And that's I think partially because it's not that easy to do, but partially also because I haven't yet spent too much time on it.  And I think we can actually get like a very significant speed ups there, but it just needs like some largest changes in the wall code. And it's like a significant amount of work on its own.  But I think it's unrealistic to add to get this committed without those improvements.  There's also a lot of place that should do better prefetching.  There's a talk by Thomas Monroe earlier in quote unquote today, I think that addresses some of that.  I assume I haven't seen the slides or her just talk because it's not yet hasn't yet happened in my timeline.  The vacuum is also considerably faster for the first for the heaps scan because that uses the prefetching logic. And I saw like universal benefits. I didn't find a single case where that the first phase is slower.  There is cases where the second where the index scans are slower because currently the B3 vacuum encode doesn't yet use the brief read ahead helper, but that's pretty easy to add.  I just haven't gotten around to it. There's also again the problem of like not having yet efficient logic for when to write data back or not using that logic yet.  And the other two case where very significant benefits are where I said earlier that there was other biggest problems.  There's also big improvements for checkpointer and BG writer and again both for Buffett and non Buffett IO. But the benefits are bigger with non Buffett IO just because that allows us to get better CPU usage by not having to do all those the overhead of like the copying and the management of the page cache.  What's next basically with this prototype. I think to fully be able to evaluate it we need to use a in more places and that's basically just the place that I previously mentioned.  There's the scalability problems that come from just using one U-ring and like having a bunch of other stupid locking that it just haven't addressed yet. And I think then the big issue is to make sure that wall is can use with the current API with the then current API.  And then we should figure out whether it's feasible to use like a process based fallbacks for platforms where we don't have a native asynchronous IO implementation.  And that would be easier if postcards were threaded but it's not so we have to make do with processes and that adds some complexity but I think it might be doable and it would be very nice if we wouldn't have to maintain several code types.  For using asynchronous IO and not using asynchronous IO because I think the complexity effects of that would be pretty annoying.  Yeah, and with that I am done and I hope I have not confused you too much and here's some links to read up.  And here's some links to read up on if both if you are confused and not confused and if there are questions, I hope it will work that I can answer them now. Thanks.  And we're back with Q&A with Andres. Take it away, sir.  So the first question I see here is from Jeff Davis and it's asking, can you clarify where the benefits of AIO come from when still doing buffer IO?  And like I said, there are several different cases and depending on which work you're looking at, one benefit is that you just have fewer system calls and you can combine the different request for different pages into one larger request  because we have the necessary data and that makes like the file system and the page cache overhead lower because the kernel doesn't have to do the repeated work for each of the different 8 kilobytes IOs  and that can be a significant benefit.  There's also operations that when doing buffered IO are still synchronous like for example S things and they now can be made asynchronous which can give you quite substantial benefits for stuff like wall or checkpointing and so on.  We also often hit short term cases where the operating system basically throttles our rights or whatever and then we, the normally buffered and kind of asynchronous system calls suddenly get very slow and we wait  and those kind of delays are not there anymore if we use like as I consider.  The next question is from Fabitio Mello and it's about whether the sufficient completion queue can be new bottlenecks and yes, they can be but one has to do a lot of IO.  Currently some bottlenecks around that but they're all because we have to acquire locks around them so they're on the locks not actually on the IO ring buffers themselves and the reason we have to take locks is basically because like the file descriptors in the queue entries  refer to like local process file descriptors and so if you didn't take the locks it would get very confused.  There are some ways to address in different ways but they can be but I don't think it will be a big problem.  The next question is from Jay P Dizin.  What are you expecting to be the kernel baseline to use in the end-list of work on buffered IO and in IO ring?  I would hope that it will work with 5.1. I haven't actually tested 5.1 because I think it's a bit too early because it's not everything is done the right way so it seems like it would work to evaluate that it works with 5.1.  I would hope that it works with 5.1 because I think we're not going to use too much of the newer system. I think there might be some limits performance lines where not going to be as fast as the later versions of the current versions but that seems okay and not really our problem.  Deep asks, you mentioned that readahead is hampered by the 1GB file binders. How adversely will such an event affect readahead?  It really depends on how, what kind of storage you are on. If you're on a very low latency storage device like for example local SSE then it doesn't hurt you too much. You see a small dip but if you instead look at a very fast high latency device like cloud storage situation  then you can see that it is a good bit of throughput because it will take quite a while for the performance to catch back up.  It takes like 100 megabytes and then it will just tell you the call up at the previous processing speed and that gives you a loss of 10-20% of throughput and depending on whether there is other nodes on top of that sequential scan that are now going to be slower it can be worse.  Deep asks, do you see wall as a requirement for inclusion? Yes, I think so. Not necessarily because of the way the performance improvements but more because it might imply design constraints that would be harder to address if you haven't gotten it right initially  and then we would have to change more code around it. So I think at the very, very least you would want to have the wall working well in a separate patch, a part of the patch series.  We don't necessarily have to commit that in the first iteration but I think we have to know exactly how it works.  And Jeff asks, how did you solve the AO across processes issues and basically the reason that it works is that we have the IOE ring, we map them in postmaster so every different process has access to the co-position ring.  And upon completion, so if an IO is blocked or if a backend is blocked on a buffer, in the buffer there is a notion like a new reference to which one of those PGAO and progress structs is associated with the IO and then you can go look at that  and then you can extract and see which of the rings is being used and with that you can then wait for the events in the ring to have completed and access to the completion associated with that.  There are some complexities with having the IO rings shared between processes because when you submit right like events to the submission to you we have to take a lock while submitting because the file descriptors that are in there are going to refer to the submitting process  and so if another process submitted to the queue entries it would, the IO worked differently. There are some ways IOE ring can avoid that but I'm not sure, it's not all, that's very worked out well for our case and I don't think the locking around that will be a problem  because it can have like one IO ring for each process and just have the completion like basically shared and yeah, so I think that should work out okay, I haven't yet fully done all the value teamwork.  And there's another question and do you see this expanding to the network layer too? And yes, I think so. In particular just avoiding when we currently read data from the client, when we finish querying for example, we send out the data then we call like the receive  and then receive will say I'm in long locking socket and there's no data available yet and then you use E-PAL to wait for the network IO and if you can combine all of that work into one system call we actually save a lot of CPU cycles and contact switches  and for transaction workloads that like reduces the overheads quite essentially I think I've very little done a tiny bit of prototyping it to experiment with that and I got, it doesn't definitely even correct that I didn't want to include the numbers  but that's all like pretty good speed up for a PD bench type like read only work. Yeah. And I think that's it for the questions now. Thank you everyone.  Thank you, Andres. Goodbye.  Thank you.
Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_0_215.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_216_383.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_384_6935.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_6936_7031.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_7032_7475.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_7476_7691.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_7692_8075.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_8076_8195.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_8196_8363.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_8364_28415.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_28416_28931.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_28932_29183.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_29184_29483.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_29484_29891.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_29892_30587.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_30588_30719.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_30720_31235.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_31236_31487.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_31488_31619.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_31620_31871.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_31872_32003.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_32004_32255.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_32256_32771.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_32772_33023.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33024_33155.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33156_33407.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33408_33539.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33540_33791.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33792_33923.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_33924_34175.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_34176_34307.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_34308_34559.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_34560_34691.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_34692_34943.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_34944_35075.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_35076_35135.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_35136_35207.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_35208_35843.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_35844_36095.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36096_36227.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36228_36479.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36480_36611.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36612_36863.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36864_36995.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_36996_37247.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_37248_37763.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_37764_38015.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_38016_38147.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_38148_38399.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_38400_38915.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_38916_39167.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_39168_39299.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_39300_39551.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_39552_39683.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_39684_39935.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_39936_47723.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_47724_55679.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_55680_55811.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_55812_56063.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_56064_56195.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_56196_56447.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_56448_56963.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_56964_57215.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_57216_58703.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_58704_59675.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_59676_68243.png Asynchronous_IO_for_PostgreSQL_-_Andres_Freund__PG_CD0w3gWBF7s_68244_71462.png