From: Ogden <lists@darkstatic.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tuning Tips for a new Server
Date: Wed, 17 Aug 2011 11:39:21 -0500
Message-ID: <D89151D3-D2E5-47E3-8B90-4A573DAB5FFA@darkstatic.com> (raw)
In-Reply-To: <f4e06a7cad34ee9217a3e57fd4eb0e67.squirrel@sq.gransy.com>
References: <959BD2FA-0312-458B-BD5F-ED358D2C47ED@darkstatic.com>
<f4e06a7cad34ee9217a3e57fd4eb0e67.squirrel@sq.gransy.com>
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 the disk 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_cost should 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@darkstatic.com, tv@fuzzy.cz
Subject: Re: Tuning Tips for a new Server
In-Reply-To: <D89151D3-D2E5-47E3-8B90-4A573DAB5FFA@darkstatic.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox