Partitioning Advice

From: Ben Carbery <ben(dot)carbery(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Partitioning Advice
Date: 2012-06-06 05:07:02
Message-ID: CACp6DjD66JTtpExHy-VNpCBn=FJyzuYmVgh2G3OL6uAkFPY_VA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Ben

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Darren Duncan 2012-06-06 05:19:51 Re: Populate Table From Two Other Tables
Previous Message Divyaprakash Y 2012-06-06 04:59:54 Re: Postgresql support for windows 8 and Windows 2012 server