Re: Partitioning Advice

On 06/06/2012 01:07 AM, Ben Carbery wrote:
> The new server has a great deal more memory which I am hoping will
> help (shared_buffers = 8GB, total RAM 20GB), but I am looking at what
> might be optimal for the storage configuration. From looking at
> previous conversations here I am thinking of something like this..
>
> 100GB OS (ext3)
> 50GB pg_xlog (ext2)
> 400GB pg_data (ext3 data=writeback noatime?)
>
> Hopefully this would mean the small writes can continue while a large
> read is going.

Latency on ext3 is better on RHEL6 than earlier versions, but it's still
hard to get to keep it low with that filesystem. You should consider
ext4 or xfs instead if you're already running into slow periods limited
by disk I/O.

Large values of shared_buffers can also make write latency spikes worse,
particularly when the underlying storage isn't very capable--which is
likely to be the case in a VM environment. Most of the performance gain
is from going from the tiny default (<=32MB) for shared_buffers to a
moderate size. You'll probably get most of the performance gain setting
that to around 1GB instead, and the worst case performance might improve.

If you already are seeing problems on your existing server, there are
two things you could do to monitor what's going on:

-Turn on log_checkpoints on the server. If you see high numbers for the
"sync=" section, that normally narrows your problem very specifically to
the database's background checkpoints.
-Watch /proc/meminfo , specificially the "Dirty:" number. If that
number gets very high during the same periods the slowdowns happen at,
it might be possible to make things better by decreasing the amount of
caching Linux does. There's some intro material on that subject at
http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html
and http://blog.2ndquadrant.com/tuning_linux_for_low_postgresq/ (note
that some of the links in that second one, to the test pgbench results,
are broken; http://www.highperfpostgres.com/pgbench-results/index.htm is
the right URL now)

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com