July 10, 2019 at 12:01Recent changes in Linux IO stack from DBA point of view
The main issues of working with the database are related to the features of the device of the operating system on which the database works. Linux is now the main operating system for databases. Solaris, Microsoft and even HPUX are still used in the enterprise, but they will never take first place, even combined. Linux is confidently gaining ground because there are more and more open source databases. Therefore, the issue of database interaction with the OS is obviously about Linux databases. This is superimposed on the eternal DB problem - IO performance. It is good that in recent years Linux has undergone a major overhaul of the IO stack and there is hope for enlightenment.
Ilya Kosmodemyansky ( hydrobiont ) works for Data Egret, a company that consults and supports PostgreSQL, and knows a lot about the interaction between the OS and databases. In a report on HighLoad ++, Ilya talked about the interaction of IO and databases using the example of PostgreSQL, but also showed how other databases work with IO. I looked at the Linux IO stack, what new and good things appeared in it and why everything is not the way it was a couple of years ago. As a useful reminder - a checklist of PostgreSQL and Linux settings for maximum performance of the IO subsystem in the new kernels.
The report video contains a lot of English, most of which we translated in the article.
Fast I / O is the most critical thing for database administrators . Everyone knows what can be changed in working with the CPU, that memory can be expanded, but I / O can ruin everything. If bad with disks, and too much I / O, then the database will moan. IO will become a bottleneck.
Not only the database or only hardware - that's it. Even the high-level Oracle, which is itself an operating system in some places, requires configuration. We read the instructions in the "Installation guide" from Oracle: change such kernel parameters, change others - there are many settings. In addition to the fact that in Unbreakable Kernel, much is already by default wired to Oracle Linux.
For PostgreSQL and MySQL, even more changes are required. This is because these technologies rely on OS mechanisms. A DBA that works with PostgreSQL, MySQL, or modern NoSQL must be a Linux operation engineer and twist different OS nuts.
Everyone who wants to deal with the kernel settings, turns to LWN . The resource is ingenious, minimalistic, contains a lot of useful information, butwritten by kernel developers for kernel developers . What do kernel developers write well? The core, not the article, how to use it. Therefore, I will try to explain everything to you for the developers, and let them write the kernel.
Everything is complicated many times by the fact that initially the development of the Linux kernel and the processing of its stack were lagging behind, and in recent years they have gone very quickly. Neither iron nor developers with articles behind him keep up.
Let's start with the examples for PostgreSQL - here is buffered I / O. It has shared memory, which is allocated in User space from the point of view of the OS, and has the same cache in the kernel cache in Kernel space .
The main task of a modern database :
In a PostgreSQL situation, this is a constant round trip: from the shared memory that PostgreSQL controls in the Page Cache kernel, and then to disk through the entire Linux stack. If you use a database on a file system, it will work on this algorithm with any UNIX-like system and with any database. Differences are, but insignificant.
Using Oracle ASM will be different - Oracle itself interacts with the disk. But the principle is the same: with Direct IO or with Page Cache, but the task is to draw pages through the entire I / O stack as quickly as possible , whatever it may be. And problems can arise at every stage.
While everything is read only , there are no problems. They read and, if there is enough memory, all the data that needs to be read is placed in RAM. The fact that in the case of PostgreSQL in Buffer Cache is the same, we are not very worried.
The first problem with IO is cache synchronization. Occurs when recording is required. In this case, you will have to drive back and forth much more memory.
Accordingly, you need to configure PostgreSQL or MySQL so that it all gets to disk from the shared memory. In the case of PostgreSQL - you still need to fine-tune the background cheating of dirty pages in Linux to send everything to disk.
The second common problem is Write-Ahead Log write failure.. It appears when the load is so powerful that even a sequentially recorded log rests on the disk. In this situation, it also needs to be recorded quickly.
The situation is not much different from cache synchronization . In PostgreSQL, we work with a large number of shared buffers, the database has mechanisms for efficient Write-Ahead Log recording, it is optimized to the limit. The only thing that can be done to make the log itself more efficient is to change the Linux settings.
The shared memory segment can be very large . I started talking about this at conferences in 2012. Then I said that the memory has fallen in price, even there are servers with 32 GB of RAM. In 2019, there may already be more in laptops, more and more often on servers 128, 256, etc.
Really a lot of memory . Banal recording takes time and resources, and the technologies that we use for this are conservative . Databases are old, they have been developed for a long time, they are slowly evolving. The mechanisms in the databases are not exactly right with the latest technology.
Synchronizing pages in memory with disk results in huge IO operations . When we synchronize caches, there is a large stream of IO, and another problem arises -we cannot twist something and look at the effect. In a scientific experiment, researchers change one parameter - get the effect, the second - get the effect, the third. We won’t succeed. We twist some parameters in PostgreSQL, configure checkpoints - we did not see the effect. Then again configure the entire stack in order to catch at least some result. Twist one parameter does not work - we are forced to configure everything at once.
Most PostgreSQL IOs generate page synchronization: checkpoints and other synchronization mechanisms. If you worked with PostgreSQL, you might have seen checkpoints spikes when a “saw” periodically appears on the charts. Previously, many faced this problem, but now there are manuals on how to fix it, it has become easier.
SSDs today greatly save the situation. At PostgreSQL, something rarely rests directly on the value record. Everything depends on synchronization: when a checkpoint occurs, fsync is called and there is a kind of “hitting” one checkpoint on another. Too much IO. One checkpoint has not ended yet, has not completed all its fsyncs, but has already earned another checkpoint, and it started!
PostgreSQL has a unique feature - autovacuum . This is a long history of crutches for database architecture. If autovacuum fails, they usually set it up so that it works aggressively and does not interfere with the rest: there are a lot of autovacuum workers, frequent tripping a little, processing tables quickly. Otherwise, there will be problems with DDL and with locks.
If autovacuum is superimposed on checkpoints, then most of the time the disks are nearly 100% recycled, and this is the source of the problems.
Oddly enough, there is a Cache refill problem . She is usually less known for DBA. A typical example: the database started, and for some time everything slows down sadly. Therefore, even if you have a lot of RAM, buy good disks so that the stack warms up the cache.
All of this seriously affects performance. Problems begin not immediately after restarting the database, but later. For example, checkpoint passed, and many pages are dirty throughout the database. They are copied to disk because you need to synchronize them. Then the requests ask for a new version of the pages from the disk, and the database sags. The graphs will show how the Cache refill after each checkpoint contributes a certain percentage to the load.
The most unpleasant thing in the input / output of the database is Worker IO. When each worker that you request, begins to generate its IO. In Oracle, it’s easier with it, but in PostgreSQL it’s a problem.
Causes of Problems with Worker IOa lot: there is not enough cache to “post” new pages from disk. For example, it happens that all buffers are shared, they are all dirty, checkpoints have not yet been. In order for the worker to perform the simplest select, you need to take the cache from somewhere. To do this, you first need to save it all to disk. You do not have a specialized checkpointer process, and the worker starts fsync to free and populate it with something new.
This raises an even bigger problem: the worker is a non-specialized thing, and the whole process is not optimized at all. It is possible to optimize somewhere at the Linux level, but in PostgreSQL this is an emergency measure.
What problem do we solve when we set up something? We want to maximize the travel of dirty pages between disk and memory.
But it often happens that these things do not directly touch the disk. A typical case - you see a very large load average. Why is that? Because someone is waiting for the disk, and all other processes are also waiting. It seems that there is no explicit disc utilization of the discs, just something blocked the disc there, and the problem is with input / output anyway.
Everything is involved in this problem: disks, memory, CPU, IO Schedulers, file systems and database settings. Now let's go through the stack, see what to do with it, and what good things have been invented in Linux so that everything works better.
For many years, the disks were terribly slow and no one was involved in latency or optimization of the transition stages. Optimizing fsyncs did not make sense. The disc was spinning, heads were moving along it like a phonograph record, and fsyncs was so long that problems did not come up.
It’s useless to look at top queries without tuning the database. You will configure a sufficient amount of shared memory, etc., and you will have a new top query - you will have to configure it again. Here is the same story. The entire Linux stack was made from this calculation.
Maximizing IO performance by maximizing throughput is easy up to a point. An auxiliary PageWriter process was invented in PostgreSQL that unloaded checkpoint. The work has become parallel, but there is still groundwork for the addition of parallelism. And to minimize latency is the task of the last mile, for which super technologies are needed.
These super technologies are SSDs. When they appeared, latency dropped sharply. But at all other stages of the stack, problems appeared: both from the side of the database manufacturers and from the Linux manufacturers. Problems need to be addressed.
Database development centered around maximizing throughput, as did Linux kernel development. Many methods for optimizing the I / O era of spinning disks are not so good for SSDs.
In between, we were forced to back up for the current Linux infrastructure, but with new disks. We watched performance tests from the manufacturer with a large number of different IOPS, and the database did not get any better, because the database is not only and not so much about IOPS. It often happens that we can skip 50,000 IOPS per second, which is good. But if we do not know latency, do not know its distribution, then we can not say anything about performance. At some point, the database will start to checkpoint, and latency will increase dramatically.
For a long time, as now, this has been a big performance problem on virtuala databases. Virtual IO is characterized by uneven latency, which, of course, also entails problems.
There is User space - that memory, which is managed by the database itself. In a DB configured so that everything worked as it should. This can be done in a separate report, and not even one. Then everything inevitably goes through Page Cache or through the Direct IO interface it enters the Block Input / Output layer .
Imagine a file system interface. The pages that were in the Buffer Cache, as they were originally in the database, that is, blocks, come off through it. Block IO layer deals with the following. There is a C structure that describes a block in the kernel. The structure takes these blocks and collects from them vectors (arrays) of input or output requests. Below the BIO layer is the requester layer. Vectors are collected on this layer and will go further.
For a long time, these two layers in Linux were sharpened for efficient recording on magnetic disks. It was impossible to do without a transition. There are blocks that are convenient to manage from the database. It is necessary to assemble these blocks into vectors that are conveniently written to disk so that they lie somewhere nearby. For this to work effectively, they came up with Elevators, or Schedulers IO.
Elevators were primarily involved in combining and sorting vectors. All in order for the block SD driver — the quasidisk driver — the recording blocks to arrive in the order convenient for him. The driver translated from blocks to its sectors and wrote to disk.
The problem was that it was necessary to do several transitions, and at each implement their own logic of the optimal process.
Before kernel 2.6, there was Linus Elevator - the most primitive IO Scheduler, which is written by you guess who. For a long time he was considered absolutely unshakable and good, until they developed something new.
Linus Elevator had a lot of problems. He combined and sorted according to how to record more efficiently . In the case of rotating mechanical disks, this led to the emergence of " starvation" : a situation where the recording efficiency depends on the rotation of the disk. If you suddenly need to read effectively at the same time, but it has already been turned wrong, it is poorly read from such a disk.
Gradually, it became clear that this is an inefficient way. Therefore, starting with kernel 2.6, a whole zoo of schedulers began to appear, which was intended for different tasks.
Many people confuse these schedulers with operating system schedulers because they have similar names. CFQ - Completely Fair Queuing is not the same as OS schedulers. Just the names are similar. It was coined as a universal scheduler.
What is a universal scheduler? Do you think you have an average load or, on the contrary, a unique one? Databases have very poor versatility. Universal load can be imagined as a regular laptop. Everything happens there: we listen to music, play, type text. For this, just universal schedulers were written.
The main task of the universal scheduler:in the case of Linux, create a request queue for each virtual terminal and process. When we want to listen to music in an audio player, IO for the player takes a queue. If we want to backup something using the cp command, something else is involved.
In the case of databases, a problem occurs. As a rule, a database is a process that started, and during operation parallel processes arose that always end in the same I / O queue. The reason is that this is the same application, the same parent process. For very small loads, such scheduling was suitable, for the rest it did not make sense. It was easier to turn off and not use if possible.
Gradually appeared deadline scheduler- It works trickier, but basically it is merge and sorting for spinning disks. Given the design of a specific disk subsystem, we collect block vectors to write them in the optimal way. He had fewer problems with starvation , but they were there.
Therefore, closer to the third Linux kernel appeared noop or none , which worked much better with the spread of SSDs. Including scheduler noop, we actually disable scheduling: there are no sortings, merges, and similar things that CFQ and deadline did.
This works better with SSDs, because SSDs are inherently parallel: it has memory cells. The more of these elements to cram on one PCIe board, the more efficient it will work.
Scheduler from some of its otherworldly, from the point of view of SSD, considerations, collects some vectors and sends them somewhere. It all ends with a funnel. So we kill the concurrency of SSDs, do not use them to the fullest. Therefore, a simple shutdown, when the vectors go randomly without any sorting, worked better in terms of performance. Because of this, it is believed that random reads, random write are better on SSDs.
Starting with kernel 3.13, blk-mq appeared . A little earlier there was a prototype, but in 3.13 a working version first appeared.
I began blk-mq as a scheduler, but it can hardly be called scheduler'om - architecturally it stands alone. This is a replacement for the request layer in the kernel. Slowly, the development of blk-mq led to a major overhaul of the entire Linux I / O stack.
The idea is this: let's use the native ability of SSDs to do efficient concurrency for I / O. Depending on how many parallel I / O streams you can use, there are honest queues through which we simply write as is on the SSD. Each CPU has its own queue for recording.
Currently blk-mqactively developing and working. There is no reason not to use it. In modern cores, from 4 and above, from blk-mq, the gain is noticeable - not 5-10%, but significantly more.
In its current form, blk-mq is directly tied to the NVMe driver Linux. There is not only a driver for Linux, but also a driver for Microsoft. But the idea of making blk-mq and the NVMe driver is the very processing of the Linux stack, from which the databases have greatly benefited.
A consortium of several companies decided to make a specification, this very protocol. Now it already in production version works fine for local PCIe SSDs. Almost ready solution for disk arrays that are connected via optics.
Let's dive into it to understand what it is. The NVMe specification is large, so we will not consider all the details, but just go over them.
The simplest case: there is a CPU, there is its turn, and somehow we go to disk.
More advanced Elevators worked differently. There are several CPUs and several queues. Somehow, for example, depending on which parent process the database workers spun off, IO gets queued up on disks.
blk-mq is a completely new approach. Each CPU, each NUMA zone adds its own input / output in turn. Further, the data falls on the disks, no matter how connected, because the driver is new. There is no SD driver that operates with the concepts of cylinders, blocks.
There was a transition period. At some point, all vendors of RAID arrays began selling add-ons that allowed them to bypass the RAID cache. If SSDs are connected, write directly there. They turned off the use of the SD driver for their products, like blq-mq.
This is how the stack looks in a new form.
From above everything remains as well. For example, databases are far behind. I / O from the database just as before, falls into the Block IO layer. There is the very blk-mq that replaces the query layer, not the scheduler.
In kernel 3.13, the whole optimization ended at about that, but new technologies are used in modern kernels. Special schedulers for blk-mq began to appear , which are designed for stronger parallelism. In modern fourth Linux kernels, two IO schedulers are considered mature. These are Kyber and BFQ. They are designed to work with concurrency and with blk-mq .
BFQ - Budget Fair Queueing - is an analogue With FQ. They are completely different, although one grew from the other. BFQ is a scheduler with complex math. Each application and process has some quota for IO. The quota depends on the amount of IO that the process / application generates. According to this budget, we have a strip in which we write. How well this works is a difficult question. If you are interested in BFQ, there are tons of articles that deal with the mathematics of the process.
Kyber is an alternative . It's like a BFQ, but without math. Kyber has a small scheduler by the amount of code. Its main task is to receive from the CPU and send. Kyber is lighter and works better.
An important point for the whole stack - blk-mq does not look at the SD driver. It does not have another conversion layer, which I complained about when I showed how the IO stack looked before. Everything comes from blk-mq to NVMe driver right away. Blocks to cylinders are not recalculated.
In the new approach, several interesting points arose - latency fell sharply, including this layer. First came the SSDs that worked well — it was possible to recycle this layer. As soon as we stopped converting back and forth, it turned out that both the NVMe layer and blk-mq had their bottlenecks, which also needed to be optimized. Now let's talk about them.
Thomas Krenn has a constantly updated and current Linux I / O stack diagram .
The diagram shows who stood over whom, the relationship between the drivers, which Elevators, part of which layer.
The chart is regularly updated, which helps to monitor the evolution of the kernel for database administrators and other specialists who work with databases.
NVM Express or NVMe is a specification, a set of standards that help you make better use of SSDs. The specification is well implemented on Linux. Linux is one of the driving forces of the standard.
Now in production is the third version. The driver of this version, according to the specification, can pass around 20 GB / s per one SSD block, and NVMe of the fifth version, which is not yet available, up to 32 GB / s . The SD driver has neither interfaces nor mechanisms inside to provide such bandwidth.
Once the databases were written for rotating disks and oriented to them - they have indexes in the form of a B-tree, for example. The question arises: are the databases ready for NVMe ? Are databases capable of chewing such a load?
Not yet, but they are adapting. The PostgreSQL mailing list recently had a couple of commits about
Over the past year and a half, NVMe has added IO polling .
At first there were spinning discs with high latency. Then came the SSDs, which are much faster. But there was a jamb: fsync goes on, recording starts, and at a very low level - deep in the driver, a request is sent directly to the piece of hardware - write it down.
The mechanism was simple - they sent it and we wait until the interrupt is processed. Waiting for interrupt processing is not a problem compared to writing to a spinning disk. It took so long to wait that as soon as the recording ended, the interrupt worked.
Since the SSD writes very quickly, a mechanism for polling the piece of hardware about the recording has forcedly appeared. In the first versions, the increase in I / O speed reached 50% due to the fact that we are not waiting for an interruption, but we are actively asking the piece of iron about the record. This mechanism is called IO polling .
It was introduced in recent versions. In version 4.12, IO schedulers appeared , specially sharpened for working with blk-mq and NVMe, about which I said Kyber and BFQ . They are already officially in the kernel, they can be used.
Now in a usable form there is the so-called IO tagging. Mostly manufacturers of clouds and virtual machines will contribute to this development. Roughly speaking, input from a specific application can be tacked and give it priority. The databases are not ready for this yet, but stay tuned. I think it will be mainstream soon.
In PostgreSQL does not support Direct IO is the number of problems that interfere with enable . Now this is only supported for value, and only if replication is not enabled. It is required to write a lot of OS-specific code , and for now everyone abstains from this.
Despite the fact that Linux swears heavily on the idea of Direct IO and how it is implemented, all databases go there. In Oracle and MySQL, Direct IO is heavily used. PostgreSQL is the only database that Direct IO does not tolerate.
How to protect yourself from fsync surprises in PostgreSQL:
Ilya Kosmodemyansky ( hydrobiont ) works for Data Egret, a company that consults and supports PostgreSQL, and knows a lot about the interaction between the OS and databases. In a report on HighLoad ++, Ilya talked about the interaction of IO and databases using the example of PostgreSQL, but also showed how other databases work with IO. I looked at the Linux IO stack, what new and good things appeared in it and why everything is not the way it was a couple of years ago. As a useful reminder - a checklist of PostgreSQL and Linux settings for maximum performance of the IO subsystem in the new kernels.
The report video contains a lot of English, most of which we translated in the article.
Why talk about IO?
Fast I / O is the most critical thing for database administrators . Everyone knows what can be changed in working with the CPU, that memory can be expanded, but I / O can ruin everything. If bad with disks, and too much I / O, then the database will moan. IO will become a bottleneck.
To make everything work well, you need to configure everything.
Not only the database or only hardware - that's it. Even the high-level Oracle, which is itself an operating system in some places, requires configuration. We read the instructions in the "Installation guide" from Oracle: change such kernel parameters, change others - there are many settings. In addition to the fact that in Unbreakable Kernel, much is already by default wired to Oracle Linux.
For PostgreSQL and MySQL, even more changes are required. This is because these technologies rely on OS mechanisms. A DBA that works with PostgreSQL, MySQL, or modern NoSQL must be a Linux operation engineer and twist different OS nuts.
Everyone who wants to deal with the kernel settings, turns to LWN . The resource is ingenious, minimalistic, contains a lot of useful information, butwritten by kernel developers for kernel developers . What do kernel developers write well? The core, not the article, how to use it. Therefore, I will try to explain everything to you for the developers, and let them write the kernel.
Everything is complicated many times by the fact that initially the development of the Linux kernel and the processing of its stack were lagging behind, and in recent years they have gone very quickly. Neither iron nor developers with articles behind him keep up.
Typical database
Let's start with the examples for PostgreSQL - here is buffered I / O. It has shared memory, which is allocated in User space from the point of view of the OS, and has the same cache in the kernel cache in Kernel space .
The main task of a modern database :
- pick up pages from the disk in memory;
- when a change occurs, mark the pages as dirty;
- write to Write-Ahead Log;
- then synchronize the memory so that it is consistent with the disk.
In a PostgreSQL situation, this is a constant round trip: from the shared memory that PostgreSQL controls in the Page Cache kernel, and then to disk through the entire Linux stack. If you use a database on a file system, it will work on this algorithm with any UNIX-like system and with any database. Differences are, but insignificant.
Using Oracle ASM will be different - Oracle itself interacts with the disk. But the principle is the same: with Direct IO or with Page Cache, but the task is to draw pages through the entire I / O stack as quickly as possible , whatever it may be. And problems can arise at every stage.
Two problems of IO
While everything is read only , there are no problems. They read and, if there is enough memory, all the data that needs to be read is placed in RAM. The fact that in the case of PostgreSQL in Buffer Cache is the same, we are not very worried.
The first problem with IO is cache synchronization. Occurs when recording is required. In this case, you will have to drive back and forth much more memory.
Accordingly, you need to configure PostgreSQL or MySQL so that it all gets to disk from the shared memory. In the case of PostgreSQL - you still need to fine-tune the background cheating of dirty pages in Linux to send everything to disk.
The second common problem is Write-Ahead Log write failure.. It appears when the load is so powerful that even a sequentially recorded log rests on the disk. In this situation, it also needs to be recorded quickly.
The situation is not much different from cache synchronization . In PostgreSQL, we work with a large number of shared buffers, the database has mechanisms for efficient Write-Ahead Log recording, it is optimized to the limit. The only thing that can be done to make the log itself more efficient is to change the Linux settings.
The main problems of working with the database
The shared memory segment can be very large . I started talking about this at conferences in 2012. Then I said that the memory has fallen in price, even there are servers with 32 GB of RAM. In 2019, there may already be more in laptops, more and more often on servers 128, 256, etc.
Really a lot of memory . Banal recording takes time and resources, and the technologies that we use for this are conservative . Databases are old, they have been developed for a long time, they are slowly evolving. The mechanisms in the databases are not exactly right with the latest technology.
Synchronizing pages in memory with disk results in huge IO operations . When we synchronize caches, there is a large stream of IO, and another problem arises -we cannot twist something and look at the effect. In a scientific experiment, researchers change one parameter - get the effect, the second - get the effect, the third. We won’t succeed. We twist some parameters in PostgreSQL, configure checkpoints - we did not see the effect. Then again configure the entire stack in order to catch at least some result. Twist one parameter does not work - we are forced to configure everything at once.
Most PostgreSQL IOs generate page synchronization: checkpoints and other synchronization mechanisms. If you worked with PostgreSQL, you might have seen checkpoints spikes when a “saw” periodically appears on the charts. Previously, many faced this problem, but now there are manuals on how to fix it, it has become easier.
SSDs today greatly save the situation. At PostgreSQL, something rarely rests directly on the value record. Everything depends on synchronization: when a checkpoint occurs, fsync is called and there is a kind of “hitting” one checkpoint on another. Too much IO. One checkpoint has not ended yet, has not completed all its fsyncs, but has already earned another checkpoint, and it started!
PostgreSQL has a unique feature - autovacuum . This is a long history of crutches for database architecture. If autovacuum fails, they usually set it up so that it works aggressively and does not interfere with the rest: there are a lot of autovacuum workers, frequent tripping a little, processing tables quickly. Otherwise, there will be problems with DDL and with locks.
But when Autovacuum is aggressive, it starts to chew on IO.
If autovacuum is superimposed on checkpoints, then most of the time the disks are nearly 100% recycled, and this is the source of the problems.
Oddly enough, there is a Cache refill problem . She is usually less known for DBA. A typical example: the database started, and for some time everything slows down sadly. Therefore, even if you have a lot of RAM, buy good disks so that the stack warms up the cache.
All of this seriously affects performance. Problems begin not immediately after restarting the database, but later. For example, checkpoint passed, and many pages are dirty throughout the database. They are copied to disk because you need to synchronize them. Then the requests ask for a new version of the pages from the disk, and the database sags. The graphs will show how the Cache refill after each checkpoint contributes a certain percentage to the load.
The most unpleasant thing in the input / output of the database is Worker IO. When each worker that you request, begins to generate its IO. In Oracle, it’s easier with it, but in PostgreSQL it’s a problem.
Causes of Problems with Worker IOa lot: there is not enough cache to “post” new pages from disk. For example, it happens that all buffers are shared, they are all dirty, checkpoints have not yet been. In order for the worker to perform the simplest select, you need to take the cache from somewhere. To do this, you first need to save it all to disk. You do not have a specialized checkpointer process, and the worker starts fsync to free and populate it with something new.
This raises an even bigger problem: the worker is a non-specialized thing, and the whole process is not optimized at all. It is possible to optimize somewhere at the Linux level, but in PostgreSQL this is an emergency measure.
Main IO problem for DB
What problem do we solve when we set up something? We want to maximize the travel of dirty pages between disk and memory.
But it often happens that these things do not directly touch the disk. A typical case - you see a very large load average. Why is that? Because someone is waiting for the disk, and all other processes are also waiting. It seems that there is no explicit disc utilization of the discs, just something blocked the disc there, and the problem is with input / output anyway.
Database I / O problems do not always concern disks only.
Everything is involved in this problem: disks, memory, CPU, IO Schedulers, file systems and database settings. Now let's go through the stack, see what to do with it, and what good things have been invented in Linux so that everything works better.
Disks
For many years, the disks were terribly slow and no one was involved in latency or optimization of the transition stages. Optimizing fsyncs did not make sense. The disc was spinning, heads were moving along it like a phonograph record, and fsyncs was so long that problems did not come up.
Memory
It’s useless to look at top queries without tuning the database. You will configure a sufficient amount of shared memory, etc., and you will have a new top query - you will have to configure it again. Here is the same story. The entire Linux stack was made from this calculation.
Bandwidth and latency
Maximizing IO performance by maximizing throughput is easy up to a point. An auxiliary PageWriter process was invented in PostgreSQL that unloaded checkpoint. The work has become parallel, but there is still groundwork for the addition of parallelism. And to minimize latency is the task of the last mile, for which super technologies are needed.
These super technologies are SSDs. When they appeared, latency dropped sharply. But at all other stages of the stack, problems appeared: both from the side of the database manufacturers and from the Linux manufacturers. Problems need to be addressed.
Database development centered around maximizing throughput, as did Linux kernel development. Many methods for optimizing the I / O era of spinning disks are not so good for SSDs.
In between, we were forced to back up for the current Linux infrastructure, but with new disks. We watched performance tests from the manufacturer with a large number of different IOPS, and the database did not get any better, because the database is not only and not so much about IOPS. It often happens that we can skip 50,000 IOPS per second, which is good. But if we do not know latency, do not know its distribution, then we can not say anything about performance. At some point, the database will start to checkpoint, and latency will increase dramatically.
For a long time, as now, this has been a big performance problem on virtuala databases. Virtual IO is characterized by uneven latency, which, of course, also entails problems.
IO stack. As it was before
There is User space - that memory, which is managed by the database itself. In a DB configured so that everything worked as it should. This can be done in a separate report, and not even one. Then everything inevitably goes through Page Cache or through the Direct IO interface it enters the Block Input / Output layer .
Imagine a file system interface. The pages that were in the Buffer Cache, as they were originally in the database, that is, blocks, come off through it. Block IO layer deals with the following. There is a C structure that describes a block in the kernel. The structure takes these blocks and collects from them vectors (arrays) of input or output requests. Below the BIO layer is the requester layer. Vectors are collected on this layer and will go further.
For a long time, these two layers in Linux were sharpened for efficient recording on magnetic disks. It was impossible to do without a transition. There are blocks that are convenient to manage from the database. It is necessary to assemble these blocks into vectors that are conveniently written to disk so that they lie somewhere nearby. For this to work effectively, they came up with Elevators, or Schedulers IO.
Elevators
Elevators were primarily involved in combining and sorting vectors. All in order for the block SD driver — the quasidisk driver — the recording blocks to arrive in the order convenient for him. The driver translated from blocks to its sectors and wrote to disk.
The problem was that it was necessary to do several transitions, and at each implement their own logic of the optimal process.
Elevators: up to kernel 2.6
Before kernel 2.6, there was Linus Elevator - the most primitive IO Scheduler, which is written by you guess who. For a long time he was considered absolutely unshakable and good, until they developed something new.
Linus Elevator had a lot of problems. He combined and sorted according to how to record more efficiently . In the case of rotating mechanical disks, this led to the emergence of " starvation" : a situation where the recording efficiency depends on the rotation of the disk. If you suddenly need to read effectively at the same time, but it has already been turned wrong, it is poorly read from such a disk.
Gradually, it became clear that this is an inefficient way. Therefore, starting with kernel 2.6, a whole zoo of schedulers began to appear, which was intended for different tasks.
Elevators: between 2.6 and 3
Many people confuse these schedulers with operating system schedulers because they have similar names. CFQ - Completely Fair Queuing is not the same as OS schedulers. Just the names are similar. It was coined as a universal scheduler.
What is a universal scheduler? Do you think you have an average load or, on the contrary, a unique one? Databases have very poor versatility. Universal load can be imagined as a regular laptop. Everything happens there: we listen to music, play, type text. For this, just universal schedulers were written.
The main task of the universal scheduler:in the case of Linux, create a request queue for each virtual terminal and process. When we want to listen to music in an audio player, IO for the player takes a queue. If we want to backup something using the cp command, something else is involved.
In the case of databases, a problem occurs. As a rule, a database is a process that started, and during operation parallel processes arose that always end in the same I / O queue. The reason is that this is the same application, the same parent process. For very small loads, such scheduling was suitable, for the rest it did not make sense. It was easier to turn off and not use if possible.
Gradually appeared deadline scheduler- It works trickier, but basically it is merge and sorting for spinning disks. Given the design of a specific disk subsystem, we collect block vectors to write them in the optimal way. He had fewer problems with starvation , but they were there.
Therefore, closer to the third Linux kernel appeared noop or none , which worked much better with the spread of SSDs. Including scheduler noop, we actually disable scheduling: there are no sortings, merges, and similar things that CFQ and deadline did.
This works better with SSDs, because SSDs are inherently parallel: it has memory cells. The more of these elements to cram on one PCIe board, the more efficient it will work.
Scheduler from some of its otherworldly, from the point of view of SSD, considerations, collects some vectors and sends them somewhere. It all ends with a funnel. So we kill the concurrency of SSDs, do not use them to the fullest. Therefore, a simple shutdown, when the vectors go randomly without any sorting, worked better in terms of performance. Because of this, it is believed that random reads, random write are better on SSDs.
Elevators: 3.13 onwards
Starting with kernel 3.13, blk-mq appeared . A little earlier there was a prototype, but in 3.13 a working version first appeared.
I began blk-mq as a scheduler, but it can hardly be called scheduler'om - architecturally it stands alone. This is a replacement for the request layer in the kernel. Slowly, the development of blk-mq led to a major overhaul of the entire Linux I / O stack.
The idea is this: let's use the native ability of SSDs to do efficient concurrency for I / O. Depending on how many parallel I / O streams you can use, there are honest queues through which we simply write as is on the SSD. Each CPU has its own queue for recording.
Currently blk-mqactively developing and working. There is no reason not to use it. In modern cores, from 4 and above, from blk-mq, the gain is noticeable - not 5-10%, but significantly more.
blk-mq is probably the best option for working with SSDs.
In its current form, blk-mq is directly tied to the NVMe driver Linux. There is not only a driver for Linux, but also a driver for Microsoft. But the idea of making blk-mq and the NVMe driver is the very processing of the Linux stack, from which the databases have greatly benefited.
A consortium of several companies decided to make a specification, this very protocol. Now it already in production version works fine for local PCIe SSDs. Almost ready solution for disk arrays that are connected via optics.
The blk-mq driver and NVMe are more than a scheduler. The system aims to replace the entire level of requests.
Let's dive into it to understand what it is. The NVMe specification is large, so we will not consider all the details, but just go over them.
Old approach to elevators
The simplest case: there is a CPU, there is its turn, and somehow we go to disk.
More advanced Elevators worked differently. There are several CPUs and several queues. Somehow, for example, depending on which parent process the database workers spun off, IO gets queued up on disks.
A new approach to elevators
blk-mq is a completely new approach. Each CPU, each NUMA zone adds its own input / output in turn. Further, the data falls on the disks, no matter how connected, because the driver is new. There is no SD driver that operates with the concepts of cylinders, blocks.
There was a transition period. At some point, all vendors of RAID arrays began selling add-ons that allowed them to bypass the RAID cache. If SSDs are connected, write directly there. They turned off the use of the SD driver for their products, like blq-mq.
New stack with blk-mq
This is how the stack looks in a new form.
From above everything remains as well. For example, databases are far behind. I / O from the database just as before, falls into the Block IO layer. There is the very blk-mq that replaces the query layer, not the scheduler.
In kernel 3.13, the whole optimization ended at about that, but new technologies are used in modern kernels. Special schedulers for blk-mq began to appear , which are designed for stronger parallelism. In modern fourth Linux kernels, two IO schedulers are considered mature. These are Kyber and BFQ. They are designed to work with concurrency and with blk-mq .
BFQ - Budget Fair Queueing - is an analogue With FQ. They are completely different, although one grew from the other. BFQ is a scheduler with complex math. Each application and process has some quota for IO. The quota depends on the amount of IO that the process / application generates. According to this budget, we have a strip in which we write. How well this works is a difficult question. If you are interested in BFQ, there are tons of articles that deal with the mathematics of the process.
Kyber is an alternative . It's like a BFQ, but without math. Kyber has a small scheduler by the amount of code. Its main task is to receive from the CPU and send. Kyber is lighter and works better.
An important point for the whole stack - blk-mq does not look at the SD driver. It does not have another conversion layer, which I complained about when I showed how the IO stack looked before. Everything comes from blk-mq to NVMe driver right away. Blocks to cylinders are not recalculated.
In the new approach, several interesting points arose - latency fell sharply, including this layer. First came the SSDs that worked well — it was possible to recycle this layer. As soon as we stopped converting back and forth, it turned out that both the NVMe layer and blk-mq had their bottlenecks, which also needed to be optimized. Now let's talk about them.
Linux IO Stack Diagram
Thomas Krenn has a constantly updated and current Linux I / O stack diagram .
The diagram shows who stood over whom, the relationship between the drivers, which Elevators, part of which layer.
The chart is regularly updated, which helps to monitor the evolution of the kernel for database administrators and other specialists who work with databases.
NVM Express Specification
NVM Express or NVMe is a specification, a set of standards that help you make better use of SSDs. The specification is well implemented on Linux. Linux is one of the driving forces of the standard.
Now in production is the third version. The driver of this version, according to the specification, can pass around 20 GB / s per one SSD block, and NVMe of the fifth version, which is not yet available, up to 32 GB / s . The SD driver has neither interfaces nor mechanisms inside to provide such bandwidth.
This specification is significantly faster than anything that was before.
Once the databases were written for rotating disks and oriented to them - they have indexes in the form of a B-tree, for example. The question arises: are the databases ready for NVMe ? Are databases capable of chewing such a load?
Not yet, but they are adapting. The PostgreSQL mailing list recently had a couple of commits about
pwrite()and such things. PostgreSQL and MySQL developers interact with kernel developers. Of course, I would like more interaction.Recent Developments
Over the past year and a half, NVMe has added IO polling .
At first there were spinning discs with high latency. Then came the SSDs, which are much faster. But there was a jamb: fsync goes on, recording starts, and at a very low level - deep in the driver, a request is sent directly to the piece of hardware - write it down.
The mechanism was simple - they sent it and we wait until the interrupt is processed. Waiting for interrupt processing is not a problem compared to writing to a spinning disk. It took so long to wait that as soon as the recording ended, the interrupt worked.
Since the SSD writes very quickly, a mechanism for polling the piece of hardware about the recording has forcedly appeared. In the first versions, the increase in I / O speed reached 50% due to the fact that we are not waiting for an interruption, but we are actively asking the piece of iron about the record. This mechanism is called IO polling .
It was introduced in recent versions. In version 4.12, IO schedulers appeared , specially sharpened for working with blk-mq and NVMe, about which I said Kyber and BFQ . They are already officially in the kernel, they can be used.
Now in a usable form there is the so-called IO tagging. Mostly manufacturers of clouds and virtual machines will contribute to this development. Roughly speaking, input from a specific application can be tacked and give it priority. The databases are not ready for this yet, but stay tuned. I think it will be mainstream soon.
Direct IO Notes
In PostgreSQL does not support Direct IO is the number of problems that interfere with enable . Now this is only supported for value, and only if replication is not enabled. It is required to write a lot of OS-specific code , and for now everyone abstains from this.
Despite the fact that Linux swears heavily on the idea of Direct IO and how it is implemented, all databases go there. In Oracle and MySQL, Direct IO is heavily used. PostgreSQL is the only database that Direct IO does not tolerate.
Check list
How to protect yourself from fsync surprises in PostgreSQL:
- Set up checkpoints to be less frequent and larger.
- Set up background writer to help checkpoint.
- Pull Autovacuum so that there is no unnecessary spurious I / O.
According to tradition, in November we are waiting for professional developers of highly loaded services in Skolkovo on HighLoad ++ . There is still a month to apply for a report, but we have already accepted the first reports to the program . Sign up for our newsletter and learn about new topics first hand.
