To use PostgreSQL replication it is necessary to change the wal_level from “minimal” to “hot_standby”. But, which impact does this change have on the amount of WAL actually written to disk? And which impacts does it have in performance? Many people have been asking this question in the past, so I thought I'd give it a try and do a small benchmark.
Table of Contents
The test is pretty simple: I have used PostgreSQL 9.3.3. In each case I used a freshly created test instance ...
|
1 2 3 |
initdb -D $PGDATA createdb $DB pgbench -F 70 -s 100 -i $DB |
We are performing this test with a FILLFACTOR of 70% to make sure that we are as realistic as possible. A scale factor of 100 will provide us with 10 mio rows of test data, which is also a reasonable amount of data to test with.
After running initdb and pgbench we do our first check to see, how much xlog we have written:
|
1 2 3 4 5 |
wal_level: xlog position: xlog in bytes: minimal: 0/2440820 38012960 archive: 0/50118B68 1343327080 hot_standby: 0/5011A5D8 1343333848 |
Many may ask why there is a 35 fold increase in the amount of xlog here?
The reason is actually very simple and can be found deep inside the code of pgbench itself:
|
1 2 3 4 5 6 7 8 9 |
fprintf(stderr, "creating tables...n"); executeStatement(con, "begin"); executeStatement(con, "truncate pgbench_accounts"); res = PQexec(con, "copy pgbench_accounts from stdin"); if (PQresultStatus(res) != PGRES_COPY_IN) { ... |
To import things faster pgbench truncates the underlying tables before inserting data. In case of wal_level = minimal this will make sure that PostgreSQL does not have to write xlog for a table at all (transaction log bypass). Using truncate is a neat trick to speed up imports in general.
In addition to a sharp increase in xlog creation from “minimal” --> “archive” we see that the amount of xlog created by “archive” and “hot_standby” are virtually the same. This is pretty much expected. All “hot_standby” does on top of “archive” is to store some information about running transactions - during an import this is really not a big deal.
In the next step we run a little test to see which differences there are for a typical OLTP workload.
Our test is pretty simple:
|
1 |
pgbench -j 32 -M prepared -t 5000 $DB -c 32 |
This leaves us with a total of 160.000 transactions.
|
1 2 3 4 5 |
wal_level xlog_position: xlog used in test: minimal 0/3DC80778 998506328 archive 0/8B991280 998737688 hot_standby 0/8B96F8F8 998593312 |
In a normal OLTP test there is basically no difference. I assume that the slightly smaller numbers for hot_standby are related to timing issues during checkpoints towards the end of the test. A normal OLTP test writes pretty straight forward logs so there is no real difference. There are no magic things such as xlog bypasses and so on.
The conclusion here is that the WAL level really only makes a difference when you are about to benefit from the WAL bypassing codes.
Find out more crucial PostgreSQL logging tips and tricks in our special blog section all about WAL.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply