Re: Partitioning Advice

Ben Carbery wrote:
> I have a postgres server I need to move onto a new OS (RHEL6) on a new VM and am looking for advice on
> how to partition the disks to gain some performance improvement.
>
> In the current environment I am given a single VHDD which I have not partitioned at all. The SAN
> performance is pretty good, but we have noticed slowdowns at various times.. The database does a lot
> of logging - constant small writes, with some probably insignificant reads of smaller tables. Delays
> in logging can effect the service which is doing the logging and cause problems upstream. Typically
> this does not happen, but there are hourly jobs which generate stats from the logs. Due to their
> complexity the query planner always chooses to do sequential scans on the main log table. This table
> is truncated monthly when the data is archived to another table, but peaks in size at around 10GB at
> the end of the month. Generally any time the stats jobs are running there are delays which I would
> like to reduce/eliminate. There is also a fair bit of iowait on the cpu.
>
> 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. Currently there
> is no streaming replication so only a gig or so is actually needed for xlogs. We do however use slony
> to sync some smaller tables to a secondary which may or may not affect anything.
>
> This is the first time I have needed to delve into the storage configuration before of a database
> before so any advice or comments welcome.

Since you are on RHEL 6 I would use ext4 throughout.

You say you have I/O problems when "stats jobs" run. Can you describe those jobs
and what they are doing?

If you have a lot of sequential scans on a 10GB table, that will suck majorly
no matter how you tune it.

Two more things that you can try out:
- Change the I/O scheduler to "deadline" by booting with "elevator=deadline".
- Split the 400GB LUN into several smaller LUNs and use tablespaces.

I don't say that that is guaranteed to help, but I have made good experiences
with it.

Yours,
Laurenz Albe