Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 5A0811337B58 for ; Tue, 1 Feb 2011 07:54:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 05831-03 for ; Tue, 1 Feb 2011 11:53:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by mail.postgresql.org (Postfix) with ESMTP id BCF991337B34 for ; Tue, 1 Feb 2011 07:53:52 -0400 (AST) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis) id 0MYcc6-1PfAaq1YSg-00Ut7Z; Tue, 01 Feb 2011 12:53:51 +0100 Message-ID: <4D47F44D.2000305@2ndquadrant.com> Date: Tue, 01 Feb 2011 06:53:49 -0500 From: Greg Smith User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: Benjamin Krajmalnik CC: pgsql-performance@postgresql.org Subject: Re: Configuration for a new server. References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------040908020400020101060101" X-Provags-ID: V02:K0:Xml6AKhadXeldXVjsNrb7c1+cz1OFeLReijnfutngUV iGh9BKWfRqIqk/5/3HKdvmLV+a5uGYrPRQf67zruMngz9j22d1 eSgAo9rQllnJ+obfcG3ow4VTpvf2H1QlYbSZ8lY0reWA/rtvrE uf15nyey3XXoKvX0WbyIJJp6VMh8h2dDfvI/9JQf5cG4F+MfbH optPxrRXBYEPeE+Bt41/FwN4eVR5lAswifIHg7fSwE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201102/1 X-Sequence-Number: 42139 This is a multi-part message in MIME format. --------------040908020400020101060101 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Benjamin Krajmalnik wrote: > > have a new set of servers coming in -- Dual Xeon E5620's, 96GB RAM, > 18 spindles (1 RAID1 for OS -- SATA, 12 disk RAID10 for data -- SAS, > RAID-1 for logs -- SAS, 2 hot spares SAS). > You didn't mention the RAID controller and its cache setup. That's a critical piece to get write, err, right. Presumably you've got a battery-backed RAID cache on your SAS controller. Knowing that and what model it is (to make sure it's one of the ones that performs well) would be good info to pass along here. > > Is the 25% RAM for shared memory still a good number to go with for > this size server? > Several people have reported to me they see drop-offs in performance between 8GB and 10GB for that setting. I currently recommend limiting shared_buffers to 8GB until we have more data on why that is. You suggested already having checkpoint issues, too; if that's true, you don't want to dedicate too much RAM to the database for that reason, too. > There are approximately 50 tables which get updated with almost 100% > records updated every 5 minutes -- what is a good number of autovacuum > processes to have on these? The current server I am replacing only > has 3 of them but I think I may gain a benefit from having more. > Watch pg_stat_user_tables and you can figure this out for your workload. There are no generic answers in this area. > Currently I have what I believe to be an aggressive bgwriter setting > as follows: > > > > bgwriter_delay = 200ms # 10-10000ms between rounds > > bgwriter_lru_maxpages = 1000 # 0-1000 max buffers > written/round > > bgwriter_lru_multiplier = 10 # 0-10.0 multipler on buffers > scanned/round > > > > Does this look right? > You'd probably be better off decreasing the delay rather than pushing up the other two parameters. It's easy to tell if you did it right or not; just look at pg_stat_bgwriter. If buffers_backend is high relative to the others, that means the multiplier or delay is wrong. Or if maxwritten_clean is increasing fast, that means bgwriter_lru_maxpages is too low. > These are values which I arrived to by playing with them to make sure > that the end user performance did not suffer. The checkpoints are > taking about 8 minutes to complete, but between checkpoints the disk > i/o on the data partition is very minimal -- when I had lower segments > running a 15 minute timeout with a .9 completion target, the platform > was fairly slow vis-à-vis the end user. > The completion target isn't the main driver here, the number of segments/timeout is. When you space checkpoints out further, the actual amount of total I/O the server does decreases, both to the WAL and to the main database. So I suspect your tweaking the target had little impact, and it's possible you might even get smoother performance if you put it back to a higher value again. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books --------------040908020400020101060101 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Benjamin Krajmalnik wrote:

 have a new set of servers coming in – Dual Xeon E5620’s, 96GB RAM, 18 spindles (1 RAID1 for OS – SATA, 12 disk RAID10 for data – SAS, RAID-1 for logs – SAS, 2 hot spares SAS).


You didn't mention the RAID controller and its cache setup.  That's a critical piece to get write, err, right.  Presumably you've got a battery-backed RAID cache on your SAS controller.  Knowing that and what model it is (to make sure it's one of the ones that performs well) would be good info to pass along here.
 

Is the 25% RAM for shared memory still a good number to go with for this size server?


Several people have reported to me they see drop-offs in performance between 8GB and 10GB for that setting.  I currently recommend limiting shared_buffers to 8GB until we have more data on why that is.  You suggested already having checkpoint issues, too; if that's true, you don't want to dedicate too much RAM to the database for that reason, too.

There are approximately 50 tables which get updated with almost 100% records updated every 5 minutes – what is a good number of autovacuum processes to have on these?  The current server I am replacing only has 3 of them but I think I may gain a benefit from having more.


Watch pg_stat_user_tables and you can figure this out for your workload.  There are no generic answers in this area.

Currently I have what I believe to be an aggressive bgwriter setting as follows:

 

bgwriter_delay = 200ms                  # 10-10000ms between rounds

bgwriter_lru_maxpages = 1000            # 0-1000 max buffers written/round    

bgwriter_lru_multiplier = 10            # 0-10.0 multipler on buffers scanned/round

 

Does this look right?


You'd probably be better off decreasing the delay rather than pushing up the other two parameters.  It's easy to tell if you did it right or not; just look at pg_stat_bgwriter.  If buffers_backend is high relative to the others, that means the multiplier or delay is wrong.  Or if maxwritten_clean is increasing fast, that means bgwriter_lru_maxpages is too low.


These are values which I arrived to by playing with them to make sure that the end user performance did not suffer.  The checkpoints are taking about 8 minutes to complete, but between checkpoints the disk i/o on the data partition is very minimal – when I had lower segments running a 15 minute timeout with a .9 completion target, the platform was fairly slow vis-à-vis the end user.


The completion target isn't the main driver here, the number of segments/timeout is.  When you space checkpoints out further, the actual amount of total I/O the server does decreases, both to the WAL and to the main database.  So I suspect your tweaking the target had little impact, and it's possible you might even get smoother performance if you put it back to a higher value again.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--------------040908020400020101060101--