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
Description: PGP signature