Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 7BCE01337C27 for ; Tue, 1 Feb 2011 21:18:04 -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 04282-01-9 for ; Wed, 2 Feb 2011 01:17:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9]) by mail.postgresql.org (Postfix) with ESMTP id E1DF41337C4F for ; Tue, 1 Feb 2011 21:16:30 -0400 (AST) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0LznVv-1QFwi707ed-014ZDB; Wed, 02 Feb 2011 02:16:28 +0100 Message-ID: <4D48B06A.6020100@2ndquadrant.com> Date: Tue, 01 Feb 2011 20:16:26 -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: <4D47F44D.2000305@2ndquadrant.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010104010803080706060000" X-Provags-ID: V02:K0:IKSfFIEdmzAT31vE5jqE808AfughdCVNAPjrhOjcIY1 CqDZqd5dtBrLzcevVly12el76/MUbaNCtmCo/hszKxSrOcNc1L 2GkmxyklYevS83ga6vdyXGR96ijZ+EuGvgllm2aNbm0uLOVjJw Kkp4SOcz82VKfUlAZoMq9Tpnbfj3erV4rGPyXrKbbztHrJxoZD C+LeuaCF/JnA5K5AEEFG5dCFq6QIbGkk6ldzjWhFbg= 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/16 X-Sequence-Number: 42154 This is a multi-part message in MIME format. --------------010104010803080706060000 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Benjamin Krajmalnik wrote: > > 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. > > What in particular should I be looking at to help me decide? > The information reported that's related to vacuuming. If you don't have enough workers, you can watch the dead row counts pop upwards without enough "last autovacuum time" updates on enough tables to suggest it's keeping up. If you see >20% dead rows on lots of tables and they're not being processed by AV and having their timestamps, that's your sign that you don't have enough workers. > 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. > > checkpoints_timed = 261 > > checkpoints_req = 0 > > buffers_checkpoint = 49,058,438 > > buffers_clean = 3,562,421 > > maxwritten_clean = 243 > > buffers_backend = 11,774,254 > > buffers_alloc = 42,816,578 > See how buffers_backend is much larger than buffers_clean, even though maxwritten_clean is low? That means the background writer isn't running often enough to keep up with cleaning things, even though it does a lot of work when it does kick in. In your situation I'd normally do a first pass by cutting bgwriter_lru_maxpages to 1/4 of what it is now, cut bgwriter_delay to 1/4 as well (to 50ms), and then see how the proportions change. You can probably cut the multiplier, too, yet still see more pages written by the cleaner. I recommend saving a snapsot of this data with a timestamp, i.e.: select now(),* from pg_stat_bgwriter; Anytime you make a change to one of the background writer or checkpoint timing parameters. That way you have a new baseline to compare against. These numbers aren't very useful with a single value, but once you get two of them with timestamps you can compute all sorts of fun statistics from the pair. -- 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 --------------010104010803080706060000 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Benjamin Krajmalnik wrote:

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.

What in particular should I be looking at to help me decide?



The information reported that's related to vacuuming.  If you don't have enough workers, you can watch the dead row counts pop upwards without enough "last autovacuum time" updates on enough tables to suggest it's keeping up.  If you see >20% dead rows on lots of tables and they're not being processed by AV and having their timestamps, that's your sign that you don't have enough workers.


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.

checkpoints_timed = 261

checkpoints_req = 0

buffers_checkpoint = 49,058,438

buffers_clean = 3,562,421

maxwritten_clean = 243

buffers_backend = 11,774,254

buffers_alloc = 42,816,578


See how buffers_backend is much larger than buffers_clean, even though maxwritten_clean is low?  That means the background writer isn't running often enough to keep up with cleaning things, even though it does a lot of work when it does kick in.  In your situation I'd normally do a first pass by cutting bgwriter_lru_maxpages to 1/4 of what it is now, cut bgwriter_delay to 1/4 as well (to 50ms), and then see how the proportions change.  You can probably cut the multiplier, too, yet still see more pages written by the cleaner.

I recommend saving a snapsot of this data with a timestamp, i.e.:

select now(),* from pg_stat_bgwriter;

Anytime you make a change to one of the background writer or checkpoint timing parameters.  That way you have a new baseline to compare against.  These numbers aren't very useful with a single value, but once you get two of them with timestamps you can compute all sorts of fun statistics from the pair.


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