Looking at the running processes list on a server where Postgres is running, one sees two writer processes amongst other background and client processes.
[email protected]:~$ ps -efH | grep postgres postgres 662 1 0 11:47 ? 00:00:01 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf ... postgres 666 662 0 11:47 ? 00:00:00 postgres: writer process postgres 667 662 0 11:47 ? 00:00:00 postgres: wal writer process ...
One process is more specifically named wal writer though. But still confusing – why are there 2 similarly named writers? What do they do? From my experiences from trainings purely this similar naming always causes a lot of confusion, so thought it wouldn’t hurt to lay out the working principles of these processes again in simple terms, together with the pertinent server configuration parameters.
The writer a.k.a. background writer process
Note that here we assume that the high level concept of “checkpoints” together with the checkpointer process and it’s parameters are already familiar to you (as it’s way more impactful compared to the writers). When not, I’d recommend digging into Postgres documentation here.
So to the writer. Introductory sentence in the documentation tells us:
There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. It writes shared buffers so server processes handling user queries seldom or never need to wait for a write to occur. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. …
In short – the writer moves some of the changed data (dirty buffers) already to the disk in the background, so that checkpoint process, happening at regular intervals, would have less work to do. All of this with the point that in the end user/application queries wouldn’t need to suffer too much when checkpointer kicks in with it’s heavy IO requirements, when there are lots of buffers to be processed or checkpoint_completion_target is set too small. All this is relevant of course oly when we’re running a relatively busy database – for idling databases it wouldn’t be a problem at all.
A small gotcha
But did you also catch the possible pitfall from the wording? Basically it warns you about the following – repeatedly dirtied page might be written to disk multiple times during one checkpoint interval, causing unnecessary extra IO load! So here one should thoroughly think about the database workload first, before tuning any relevant parameters to offload the checkpointer. Typical problem scenario would be for example where you have some “counter” like tables that get incremented for every page view or event, resulting in writer re-writing the same page many-many times during one checkpoint cycle. Due to this possible pitfall Postgres default parameters for the writer are also pretty non-intrusive. So my advice there is to generally let them stay as they are, but nevertheless a short “translation” for the parameters visible from documentation:
bgwriter_delay [200ms by default , 10ms – 10s possible] – time to sleep after cleaning up a maximum of bgwriter_lru_maxpages (see below) dirty pages.
bgwriter_lru_maxpages [100 pages by default, 0 – 1000 possible] – maximum amount of pages cleaned per one writer activity round.
bgwriter_lru_multiplier [2.0 by default, 0-10.0 possible] – multiplier ratio determining how many pages should be cleaned for every incoming dirty page, based on counts from last delay periods. Everything > 1.0 means we try to clean more pages than actually dirtied, up to the “maxpages” limit.
From those default settings we can also calculate the maximum amount of bytes per second that the writer can clean. 200ms delay translates to 5 times per second, multiplied with 100 8KB pages gives us ~ 4MB. Pretty conservative indeed! But due to good reasoning as mentioned previously.
[email protected]=# select pg_size_pretty(100 * 5 * 8192::numeric); pg_size_pretty ──────────────── 4000 kB (1 row)
The “wal writer” process
Now what’s deal here? A bit simpler story actually – first, wal writer has only meaning when “synchronous_commit” is set to “off”, with default being “on”, so most setups can already ignore it. More about “synchronous_commit” and it’s different values can be read from one of our recent blogposts here .
But in short – with “synchronous_commit=off” (generally used as a performance crutch) Postgres commits are managed by this wal writer process and they are actually not flushed directly to the transaction log (a.k.a. WAL or XLog), but rather just sent to the operating system and fsync (i.e. real commit, guaranteeing durability) is requested only after “wal_writer_delay” period has passed from the last real commit. The default value is here 200ms again, same as “bgwriter_delay”. One could increase this parameter if needed, but please take into account the friendly warning from the docs, that in worst case the delay could triple, so only set it to an amount of seconds/milliseconds (1ms – 10s range available) that you’re willing to lose data in case of a crash (no fear of data corruption here though) – as this is what setting “synchronous_commit=off” means. Hope it helps!