Re: ideal storage configuration - Mailing list pgsql-performance

From Matthew Wakeling
Subject Re: ideal storage configuration
Whole thread Raw
In response to ideal storage configuration  (Samuel Gendler)
List pgsql-performance
On Tue, 29 Jun 2010, Samuel Gendler wrote:
> The copy statements execute in a small fraction of the minute in which
> they occur.

I'm going to ask a silly question here. If the system is already coping
quite well with the load, then why are you changing it?

> All old data gets removed by dropping older partitions.  There are no
> updates at all.

That's good.

> 6 internal drives on battery backed raid (I don't know what RAID level
> - is there a way to discover this?), all in a single filesystem, so
> WAL and data are on the same filesystem.  I don't believe that we are
> taking advantage of the battery backed controller, since I only see
> this in /etc/fstab:
> UUID=12dcd71d-8aec-4253-815c-b4883195eeb8 /                       ext3
>   defaults        1 1

That doesn't have anything to do with whether or not the controller has a
BBU cache. If the controller does have a BBU cache, then your writes will
return quicker - and nothing else.

> But inserts are happening so rapidly that I don't imagine that getting
> rid of fsync is going to change performance of the reporting queries
> too dramatically.

Don't get rid of fsync, unless you want to lose your data. Especially with
your workload of large transactions, you do not need the benefit of
reducing the transaction latency, and even that benefit is not present if
you have a BBU cache.

It seems like your current disc array is coping quite well with the write
traffic. If you really want to boost your read speeds for your reporting
queries, then increase the amount of RAM, as Kevin said, and see if you
can fit the active portion of the database into RAM.


 Nog:     Look! They've made me into an ensign!
 O'Brien: I didn't know things were going so badly.
 Nog:     Frightening, isn't it?

pgsql-performance by date:

From: Bruce Momjian
Subject: Re: PostgreSQL as a local in-memory cache
From: Greg Smith
Subject: Re: ideal storage configuration