Thread: Needs discussion of pg_xlog
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.4/static/runtime-config-file-locations.html Description: In various places (e.g. stackoverflow), I've seen people suggest having pg_xlog ion a different disk (i.e. not in /var/lib/pgsql/9.X/data). The only mention of this that I've seen is in Section 29.5 (WAL Internals), and that just says "it is advantageous...", with no explanation. If this IS a good idea, then "Server Configuration / File Locations" is where it should be discussed: in particular, why is it a good idea, and how does it affect backups (and in particular base backups to a standby server).
On 12/01/2016 07:00 AM, robert@interactive.co.uk wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.4/static/runtime-config-file-locations.html > Description: > > In various places (e.g. stackoverflow), I've seen people suggest having > pg_xlog ion a different disk (i.e. not in /var/lib/pgsql/9.X/data). > > The only mention of this that I've seen is in Section 29.5 (WAL Internals), > and that just says "it is advantageous...", with no explanation. > > If this IS a good idea, then "Server Configuration / File Locations" is > where it should be discussed: in particular, why is it a good idea, and how > does it affect backups (and in particular base backups to a standby server). The reason it can be advantageous is that pg_xlog has a different write profile that $PGDATA. The WAL is written sequentially versus randomly. JD > -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. Everyone appreciates your honesty, until you are honest with them. Unless otherwise stated, opinions are my own.
"Joshua D. Drake" <jd@commandprompt.com> writes:
> On 12/01/2016 07:00 AM, robert@interactive.co.uk wrote:
>> The only mention of this that I've seen is in Section 29.5 (WAL Internals),
>> and that just says "it is advantageous...", with no explanation.
> The reason it can be advantageous is that pg_xlog has a different write
> profile that $PGDATA. The WAL is written sequentially versus randomly.
Yeah. The traditional understanding of that was you wanted to keep a
write head positioned over the current end-of-WAL, which of course only
applies to spinning rust.
It's still true that under heavy update loads, your I/O volume to WAL is
probably comparable to your I/O volume to everything else, which might
justify a separate SSD just on write bandwidth grounds. But seek delays
aren't part of the calculation anymore.
regards, tom lane