Re: Tuning Tips for a new Server - Mailing list pgsql-performance

On Aug 17, 2011, at 9:44 AM, Tomas Vondra wrote:

> 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 database is about 200Gb so using /usr/local/pgsql/pg_xlog on a virtual disk with 100Gb should not be a problem with
thedisk space should it? 

>> 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?
>

I am sorry I overlooked specifying this. The database is about 200Gb and yes these are new servers which bring more
power(RAM, CPU) over the last one. The RAID Controller is a Perc H700 and there is 512Mb write cache. The servers are
Dells. 

>> 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.

So just increasing the wal_buffers is okay? I thought there would be more as the memory in the system is now 4 times as
much.Perhaps shared_buffers too (down below).  

>>
>> # 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.

These settings are for the old server, keep in mind. It's a 16GB machine (the new one is 64Gb). The value for
cpu_tuple_costshould be 0.005? How are the other ones? 


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


#checkpoint_segments = 3                # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min              # range 30s-1h
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0 - was 0.5
#checkpoint_warning = 30s               # 0 disables

And

shared_buffers = 4096MB


Thank you very much

Ogden