Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3818764FCEE for ; Thu, 25 Dec 2008 17:43:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32941-07 for ; Thu, 25 Dec 2008 17:43:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-1718.google.com (yx-out-1718.google.com [74.125.44.156]) by mail.postgresql.org (Postfix) with ESMTP id 9807C64FC65 for ; Thu, 25 Dec 2008 17:43:45 -0400 (AST) Received: by yx-out-1718.google.com with SMTP id 3so1231550yxi.74 for ; Thu, 25 Dec 2008 13:43:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:reply-to :sender:to:subject:in-reply-to:mime-version:content-type:references :x-google-sender-auth; bh=5CJp59+uCwDvLkaRDwIbFgq55RpFtw7EiXgfnR6Swl4=; b=W7Ppf05snMqOozUQERY691Azviy8gfiw5n09lCNbS3TEWTcnOjJP2O8UePl+J+0pnO 1Z6QASwixDlhia+fOL3V1AgLBQjUT7JdkBLPHjMHglD38KA7vG8tO0pc3tN+ddjF3wj2 E+5wVXXwyxr4H6zbGnPjRdVdusJxQHCbF/O+o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:reply-to:sender:to:subject:in-reply-to :mime-version:content-type:references:x-google-sender-auth; b=Rzv4U2cFRPFUVOzOVzGXKXQ8sFsCFBAsRTPxH86AEwoH26d73mLdDDDJli8IYuWK/d TJyw1UuvtBOR+wX2+lDwUMj2q2ekSd4BCMZ0VhOgy6hq3aLX1m0qxdiwcCPObk7yvyQ+ UYY+68T9tdBOqm6M0syuuTww8/fVHYCgTVDZw= Received: by 10.90.101.7 with SMTP id y7mr5071771agb.6.1230241424576; Thu, 25 Dec 2008 13:43:44 -0800 (PST) Received: by 10.90.87.19 with HTTP; Thu, 25 Dec 2008 13:43:44 -0800 (PST) Message-ID: Date: Fri, 26 Dec 2008 00:43:44 +0300 From: "Dmitry Koterov" Reply-To: dmitry@koterov.ru To: "Postgres General" Subject: Bgwriter and pg_stat_bgwriter.buffers_clean aspects In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_75350_31995197.1230241424575" References: X-Google-Sender-Auth: 64f04902586cd239 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/846 X-Sequence-Number: 141978 ------=_Part_75350_31995197.1230241424575 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello. I am trying to tune PostgreSQL write parameters to make writing operation fast on a heavy-loaded database (a lot of inserts/updates). After resetting the pg_stat_bgwriter statistics (I do it by deleting global/pgstat.stat file and restarting PostgreSQL) I monitor the following: # select * from pg_stat_bgwriter; checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc -------------------+-----------------+--------------------+---------------+------------------+-----------------+--------------- 8 | 0 | 19092 | 0 | 0 | 2285 | 30148 So, you see that some time after resetting the statistics we have: - a large value in buffers_backend; - a zero buffers_clean. Why? Documentation: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm says that "buffers_backend ... [is a number of] times a database backend (probably the client itself) had to write a page in order to make space for the new allocation", and "buffers_clean ... [means that] the background writer cleaned ... buffers (cleaned=wrote out dirty ones) during that time". What I am trying to achieve is that all writing operation are performed asynchronously and mostly flushed to the disk before a CHECKPOINT occurred, so CHECKPOINT is cheap thanks to bgwiter work. Could you please explain what happened and what large buffers_backend and zero buffers_clean mean? Related parameters: shared_buffers = 512MB fsync = on synchronous_commit = off wal_writer_delay = 2000ms checkpoint_segments = 20 checkpoint_timeout = 1min checkpoint_completion_target = 0.8 checkpoint_warning = 1min bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10 ------=_Part_75350_31995197.1230241424575 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hello.

I am trying to tune PostgreSQL write parameters to make writing operation fast on a heavy-loaded database (a lot of inserts/updates).
After resetting the pg_stat_bgwriter statistics (I do it by deleting global/pgstat.stat file and restarting PostgreSQL) I monitor the following:

# select * from pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
                 8 |               0 |              19092 |             0 |                0 |            2285 |         30148

So, you see that some time after resetting the statistics we have:
- a large value in buffers_backend;
- a zero buffers_clean.

Why?

Documentation: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
says that "buffers_backend ... [is a number of]  times a database backend (probably the client itself) had to write a page in order to make space for the new allocation", and "buffers_clean ... [means that] the background writer cleaned ... buffers (cleaned=wrote out dirty ones) during that time".

What I am trying to achieve is that all writing operation are performed asynchronously and mostly flushed to the disk before a CHECKPOINT occurred, so CHECKPOINT is cheap thanks to bgwiter work.

Could you please explain what happened and what large buffers_backend and zero buffers_clean mean?


Related parameters:

shared_buffers = 512MB
fsync = on             
synchronous_commit = off
wal_writer_delay = 2000ms
checkpoint_segments = 20         
checkpoint_timeout = 1min        
checkpoint_completion_target = 0.8
checkpoint_warning = 1min        
bgwriter_delay = 10ms      
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10

------=_Part_75350_31995197.1230241424575--