On Thu, Dec 01, 2016 at 05:48:51PM +0200, Achilleas Mantzios wrote:
> On 01/12/2016 15:55, Robert Inder wrote:
> > I'm running Postgres9.4 in master/hot-standby mode on a few pairs of servers.
> >
> > While recovering from A Bit Of Bother last week, I came across a
> > posting saying that pg_xlog should be on a separate partition.
> >
> > I tried to find out more about this, by consulting the PostgresQL
> > documentation (i.e.
> > https://www.postgresql.org/docs/9.4/static/index.html )
> > But all I could find was a mention that "It is advantageous if the log
> > is located on a different disk from the main database files".
> >
> > The questions:
> > 1. WHY is this good? Is it (just) to stop pg_xlog filling the
> > database disk/partition? Or are there performance implications?
> > SPECIFICALLY: my database is currently in "/", which is on SSD. Is it
> > better to move pg_xlog to another partition on the same SSD? Or to a
> > physical disk or SAN?
>
> Performance is the reason. You would benefit from moving pg_xlog to a
> different controller with its own write cache or to a different SSD with a
> write cache which is capacitor-backed. So in enterprise/server-class setups
> the above would boost the performance. Using the same SSD with a different
> partition won't give you much.
For performance, on-disk write pattern of data in pg_xlog is sequential
writes, while there will be likely random writes on the main data folder.
On top of that, moving them to a different partition gives more flexibility
in the way to tune checkpoint-related parameters using the partition space
as a constraint for retention policy and checkpoint timings.
--
Michael