November 24, 2016 at 13:37About the impact of full-page writes
- Transfer
By adjusting the postgresql.conf , you may have noticed that there is a parameter full_page_writes . The comment next to it says something about the partial recording of pages, and people usually leave it on - which is not bad, which I will explain later in this article. However, it is very useful to understand what full_page_writes does, as the impact on system performance can be significant.
Unlike my last post about setting checkpointsThis is not a tutorial on how to configure the server. There is not much of everything that you could configure, in fact, but I will show you how some decisions at the application level (for example, the choice of data types) can interact with the recording of full pages.
So what is full page recording? As a comment from postgresql.conf says , this is a way to recover from partial page writing - PostgreSQL uses 8kB pages (by default), while other parts of the stack use different chunks. The Linux file system typically uses 4kB pages (it is possible to use smaller pages, but 4kB is the maximum on x86), on the hardware level, older drives use 512B sectors, while newer ones write data in larger chunks (usually 4kB, or even 8kB).
Thus, when PostgreSQL writes an 8kB page, the rest of the storage layers can break it into smaller pieces that are processed separately. This is a problem of atomicity of the record. An 8 kilobyte PostgreSQL page can be split into two 4kB pages of the file system, and then into 512B sectors. Now, what happens if the server crashes (power failure, kernel error, ...)?
Even if the server uses a storage system designed to deal with such failures (SSD with capacitors, RAID controllers with batteries, ...), the kernel already splits the data into 4kB pages. There is a possibility that the database wrote an 8kB data page, but only part of it got to disk before the failure.
From this point of view, now you probably think that this is exactly what we have the Transaction Log (WAL) for and you are right! So, after starting the server, the database will read the WAL (from the last checkpoint executed), and apply the changes again to make sure that the data files are correct. Simply.
But there is a trick - the recovery does not apply the changes blindly, often it needs to read data pages, etc., which implies that the page is no longer corrupted in some way, for example, in connection with a partial recording. Which seems like a little bit internally contradictory, because to fix data corruption, we mean that the data was not damaged.
Full page writing is a bit of a solution to this puzzle - when you change the page for the first time after the checkpoint, the whole page will be written to WAL. This ensures that during recovery, the first WAL record associated with this page stores the entire page, freeing us from reading the potentially damaged page from the data file.
Of course, the negative consequence of this is an increase in the size of the WAL - a change in one byte on the 8kB page will lead to its full entry in the WAL. Full recording of a page occurs only on the first recording after a checkpoint, that is, reducing the frequency of checkpoints is one way to improve the situation, in fact, there is a small “explosion” of a complete recording of pages after a checkpoint, after which relatively few complete records occur before it ends.
There are still some unexpected interactions with design decisions made at the application level. Let's assume that we have a simple table with a primary key, UUID or BIGSERIAL, and we write data into it. Will there be a difference in the size of the generated WAL (assuming that we write the same number of lines)?
It seems reasonable to expect approximately the same size of WALs in both cases, but the following diagrams clearly demonstrate that there is a huge difference in practice:
Shown here are the sizes of the WALs obtained from the hourly test, clocked up to 5000 insertions per second. With BIGSERIAL as the primary key, this resulted in ~ 2GB of WAL while the UUID produced more than 40GB. The difference is more than noticeable, and most of the WAL is associated with the index behind the primary key. Let's look at the types of records in the WAL:
Obviously, the vast majority of records are full-page images (FPI), i.e. the result of a full page record. But why is this happening?
Of course, this is due to the inherent randomness of UUID. New BIGSERIALs are sequential, and therefore are written in the same branches of the btree index. Since only the first page change causes a full page entry, such a small number of WAL entries are FPIs. UUID is a completely different matter, of course, the values are completely inconsistent and each insert is likely to fall into a new index branch (assuming that the index is quite large).
The database cannot do anything special with this - the load is random in nature, which causes a large number of full page entries.
Of course, it is not so difficult to achieve a similar increase in recording even with BIGSERIAL keys. It just requires a different type of load, for example, updates, random updates of records will change the distribution, the diagram looks like this:
Suddenly, the difference between the data types has disappeared - access is done randomly in both cases, leading to approximately the same size of the produced WALs. Another difference is that most of the WAL is associated with a “heap”, i.e. tables, not indexes. “HOT” cases were reproduced for the possibility of HOT UPDATE optimization (that is, updates without having to touch the index), which almost completely eliminates all WAL traffic related to indexes.
But you can protest that most applications do not modify the entire data set. Usually, only a small part of the data is “active” - people are interested in messages over the past few days on forums, unresolved orders in online stores, etc. How does this affect the results?
Fortunately, pgbench supports uneven distributions, and, for example, with an exponential distribution relating to 1% of the data set ~ 25% of the time, the diagrams will look like this:
If you make the distribution even more asymmetric, concerning 1% of the data ~ 75% of the time:
This once again shows how big a difference the choice of data types can cause, and how important is the setting of hot updates.
Another interesting question is how much WAL traffic can be saved using smaller pages in PostgreSQL (which requires compiling a custom package). In the best case, this can save up to 50% of the WAL, thanks to only 4kB logging, instead of 8kB pages. For a load with evenly distributed updates, it looks like this:
In general, the savings are not quite 50%, but the decrease from ~ 140GB to ~ 90GB is still quite noticeable.
This may seem blatant after explaining all the dangers of partial recording, but turning off full page recording may be a viable option, at least in some cases.
First, I wonder if Linux file systems are still vulnerable to partial writes. The parameter was introduced in PosqtgreSQL version 8.1, released in 2005, so perhaps many file system improvements have since solved this problem. This is probably not a universal approach for any workloads, but maybe, given some additional conditions (for example, the use of 4kB pages in PostgreSQL) will it be enough? In addition, PostgreSQL never overwrites only a part of an 8kB page, but only a full page.
I did a lot of tests recently, trying to call a partial record, but could not even cause a single case. Of course, this is not proof that the problem does not exist. But even if it is, checksums can be sufficient protection (this will not fix the problem, but at least point to a damaged page).
Secondly, many modern systems rely on replicas that use streaming replication - instead of waiting for the server to reboot after a hardware failure (which can take quite a while) and then spend even more time on the recovery, the systems simply switch to hot standby. If the database on the damaged master was cleaned (and then cloned from the new master), partial records are not a problem.
But, I'm afraid if we begin to recommend this approach, then "I don’t know how the data was corrupted, I just made full_page_writes = off on the systems!" will be one of the most common offers right before the death of the DBA (along with “I saw this snake on reddit, it is not poisonous”).
Not much can be done to set up a complete page record directly. For a larger number of loads, most of the complete records occur immediately after the checkpoint, after which they disappear until the next checkpoint. So it’s quite important to set up the checkpoints so that they don’t follow each other too often.
Some solutions at the application level can increase the randomness of writing to tables and indexes - for example, UUIDs are random in nature, turning even the usual load from inserts into random index updates. The scheme used in the examples was quite trivial - in practice, there would be secondary indexes, foreign keys, etc. Using BIGSERIAL as primary keys (and leaving UUID as secondary keys) can at least reduce the increase in the record.
I am really interested in discussing the need to completely write pages on different kernels / file systems. Unfortunately, I did not find a large amount of resources, if you have any relevant information, let me know.
Unlike my last post about setting checkpointsThis is not a tutorial on how to configure the server. There is not much of everything that you could configure, in fact, but I will show you how some decisions at the application level (for example, the choice of data types) can interact with the recording of full pages.
Partial Recording / Torn Pages
So what is full page recording? As a comment from postgresql.conf says , this is a way to recover from partial page writing - PostgreSQL uses 8kB pages (by default), while other parts of the stack use different chunks. The Linux file system typically uses 4kB pages (it is possible to use smaller pages, but 4kB is the maximum on x86), on the hardware level, older drives use 512B sectors, while newer ones write data in larger chunks (usually 4kB, or even 8kB).
Thus, when PostgreSQL writes an 8kB page, the rest of the storage layers can break it into smaller pieces that are processed separately. This is a problem of atomicity of the record. An 8 kilobyte PostgreSQL page can be split into two 4kB pages of the file system, and then into 512B sectors. Now, what happens if the server crashes (power failure, kernel error, ...)?
Even if the server uses a storage system designed to deal with such failures (SSD with capacitors, RAID controllers with batteries, ...), the kernel already splits the data into 4kB pages. There is a possibility that the database wrote an 8kB data page, but only part of it got to disk before the failure.
From this point of view, now you probably think that this is exactly what we have the Transaction Log (WAL) for and you are right! So, after starting the server, the database will read the WAL (from the last checkpoint executed), and apply the changes again to make sure that the data files are correct. Simply.
But there is a trick - the recovery does not apply the changes blindly, often it needs to read data pages, etc., which implies that the page is no longer corrupted in some way, for example, in connection with a partial recording. Which seems like a little bit internally contradictory, because to fix data corruption, we mean that the data was not damaged.
Full page writing is a bit of a solution to this puzzle - when you change the page for the first time after the checkpoint, the whole page will be written to WAL. This ensures that during recovery, the first WAL record associated with this page stores the entire page, freeing us from reading the potentially damaged page from the data file.
Record increase
Of course, the negative consequence of this is an increase in the size of the WAL - a change in one byte on the 8kB page will lead to its full entry in the WAL. Full recording of a page occurs only on the first recording after a checkpoint, that is, reducing the frequency of checkpoints is one way to improve the situation, in fact, there is a small “explosion” of a complete recording of pages after a checkpoint, after which relatively few complete records occur before it ends.
UUID vs BIGSERIAL keys
There are still some unexpected interactions with design decisions made at the application level. Let's assume that we have a simple table with a primary key, UUID or BIGSERIAL, and we write data into it. Will there be a difference in the size of the generated WAL (assuming that we write the same number of lines)?
It seems reasonable to expect approximately the same size of WALs in both cases, but the following diagrams clearly demonstrate that there is a huge difference in practice:
Shown here are the sizes of the WALs obtained from the hourly test, clocked up to 5000 insertions per second. With BIGSERIAL as the primary key, this resulted in ~ 2GB of WAL while the UUID produced more than 40GB. The difference is more than noticeable, and most of the WAL is associated with the index behind the primary key. Let's look at the types of records in the WAL:
Obviously, the vast majority of records are full-page images (FPI), i.e. the result of a full page record. But why is this happening?
Of course, this is due to the inherent randomness of UUID. New BIGSERIALs are sequential, and therefore are written in the same branches of the btree index. Since only the first page change causes a full page entry, such a small number of WAL entries are FPIs. UUID is a completely different matter, of course, the values are completely inconsistent and each insert is likely to fall into a new index branch (assuming that the index is quite large).
The database cannot do anything special with this - the load is random in nature, which causes a large number of full page entries.
Of course, it is not so difficult to achieve a similar increase in recording even with BIGSERIAL keys. It just requires a different type of load, for example, updates, random updates of records will change the distribution, the diagram looks like this:
Suddenly, the difference between the data types has disappeared - access is done randomly in both cases, leading to approximately the same size of the produced WALs. Another difference is that most of the WAL is associated with a “heap”, i.e. tables, not indexes. “HOT” cases were reproduced for the possibility of HOT UPDATE optimization (that is, updates without having to touch the index), which almost completely eliminates all WAL traffic related to indexes.
But you can protest that most applications do not modify the entire data set. Usually, only a small part of the data is “active” - people are interested in messages over the past few days on forums, unresolved orders in online stores, etc. How does this affect the results?
Fortunately, pgbench supports uneven distributions, and, for example, with an exponential distribution relating to 1% of the data set ~ 25% of the time, the diagrams will look like this:
If you make the distribution even more asymmetric, concerning 1% of the data ~ 75% of the time:
This once again shows how big a difference the choice of data types can cause, and how important is the setting of hot updates.
8kB and 4kB pages
Another interesting question is how much WAL traffic can be saved using smaller pages in PostgreSQL (which requires compiling a custom package). In the best case, this can save up to 50% of the WAL, thanks to only 4kB logging, instead of 8kB pages. For a load with evenly distributed updates, it looks like this:
In general, the savings are not quite 50%, but the decrease from ~ 140GB to ~ 90GB is still quite noticeable.
Do we need a full page recording?
This may seem blatant after explaining all the dangers of partial recording, but turning off full page recording may be a viable option, at least in some cases.
First, I wonder if Linux file systems are still vulnerable to partial writes. The parameter was introduced in PosqtgreSQL version 8.1, released in 2005, so perhaps many file system improvements have since solved this problem. This is probably not a universal approach for any workloads, but maybe, given some additional conditions (for example, the use of 4kB pages in PostgreSQL) will it be enough? In addition, PostgreSQL never overwrites only a part of an 8kB page, but only a full page.
I did a lot of tests recently, trying to call a partial record, but could not even cause a single case. Of course, this is not proof that the problem does not exist. But even if it is, checksums can be sufficient protection (this will not fix the problem, but at least point to a damaged page).
Secondly, many modern systems rely on replicas that use streaming replication - instead of waiting for the server to reboot after a hardware failure (which can take quite a while) and then spend even more time on the recovery, the systems simply switch to hot standby. If the database on the damaged master was cleaned (and then cloned from the new master), partial records are not a problem.
But, I'm afraid if we begin to recommend this approach, then "I don’t know how the data was corrupted, I just made full_page_writes = off on the systems!" will be one of the most common offers right before the death of the DBA (along with “I saw this snake on reddit, it is not poisonous”).
Conclusion
Not much can be done to set up a complete page record directly. For a larger number of loads, most of the complete records occur immediately after the checkpoint, after which they disappear until the next checkpoint. So it’s quite important to set up the checkpoints so that they don’t follow each other too often.
Some solutions at the application level can increase the randomness of writing to tables and indexes - for example, UUIDs are random in nature, turning even the usual load from inserts into random index updates. The scheme used in the examples was quite trivial - in practice, there would be secondary indexes, foreign keys, etc. Using BIGSERIAL as primary keys (and leaving UUID as secondary keys) can at least reduce the increase in the record.
I am really interested in discussing the need to completely write pages on different kernels / file systems. Unfortunately, I did not find a large amount of resources, if you have any relevant information, let me know.
