Re: Tuning Tips for a new Server

On 17 Srpen 2011, 3:35, Ogden wrote:
> Hope all is well. I have received tremendous help from this list prior and
> therefore wanted some more advice.
>
> I bought some new servers and instead of RAID 5 (which I think greatly
> hindered our writing performance), I configured 6 SCSI 15K drives with
> RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K
> drives on a different virtual disk and also Raid 10, a total of 146Gb. I
> was thinking of putting Postgres' xlog directory on the OS virtual drive.
> Does this even make sense to do?

Yes, but it greatly depends on the amount of WAL and your workload. If you
need to write a lot of WAL data (e.g. during bulk loading), this may
significantly improve performance. It may also help when you have a
write-heavy workload (a lot of clients updating records, background writer
etc.) as that usually means a lot of seeking (while WAL is written
sequentially).

> The system memory is 64GB and the CPUs are dual Intel E5645 chips (they
> are 6-core each).
>
> It is a dedicated PostgreSQL box and needs to support heavy read and
> moderately heavy writes.

What is the size of the database? So those are the new servers? What's the
difference compared to the old ones? What is the RAID controller, how much
write cache is there?

> Currently, I have this for the current system which as 16Gb Ram:
>
> max_connections = 350
>
> work_mem = 32MB
> maintenance_work_mem = 512MB
> wal_buffers = 640kB

Are you really using 350 connections? Something like "#cpus + #drives" is
usually recommended as a sane number, unless the connections are idle most
of the time. And even in that case a pooling is recommended usually.

Anyway if this worked fine for your workload, I don't think you need to
change those settings. I'd probably bump up the wal_buffers to 16MB - it
might help a bit, definitely won't hurt and it's so little memory it's not
worth the effort I guess.

>
> # This is what I was helped with before and made reporting queries blaze
> by
> seq_page_cost = 1.0
> random_page_cost = 3.0
> cpu_tuple_cost = 0.5
> effective_cache_size = 8192MB

Are you sure the cpu_tuple_cost = 0.5 is correct? That seems a bit crazy
to me, as it says reading a page sequentially is just twice as expensive
as processing it. This value should be abou 100x lower or something like
that.

What are the checkpoint settings (segments, completion target). What about
shared buffers?

Tomas