Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 850E19FA162 for ; Tue, 12 Dec 2006 13:17:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 72584-03 for ; Tue, 12 Dec 2006 13:17:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web57105.mail.re3.yahoo.com (web57105.mail.re3.yahoo.com [216.252.111.118]) by postgresql.org (Postfix) with SMTP id 904BD9F9FDA for ; Tue, 12 Dec 2006 13:17:18 -0400 (AST) Received: (qmail 8584 invoked by uid 60001); 12 Dec 2006 17:17:17 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=xs/PRiy1/HuIw/HAhWKBYBpCa5cXARzRW1P3lBUdVPAUs6mG/v8EvVaAtuRGqhDHaqP0UEHTxvZZ+sR5vLoew6E7D9h+4bARut7t34MzKd+dMsOqvTsUI/Gf2tSXKlDPw5NJqbuKN7Dg+56/2fnYdWY+IVwI4d4knj++0rfYtNE=; X-YMail-OSG: u9MLA6sVM1n3.vApYzCEQ3gTgm_TgsHQQEclUlk7tDRg32qtxlEFlDO.OCEy_aFJadgN9AF5P4gjnoMTAq7gvBPqBQmZ8zyF9nu19zFHeKYYmFDHQhUdfw-- Received: from [64.237.4.140] by web57105.mail.re3.yahoo.com via HTTP; Tue, 12 Dec 2006 09:17:17 PST Date: Tue, 12 Dec 2006 09:17:17 -0800 (PST) From: Abu Mushayeed Subject: Increase checkpoint segments ? To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1958115311-1165943837=:8108" Content-Transfer-Encoding: 8bit Message-ID: <604882.8108.qm@web57105.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/161 X-Sequence-Number: 23953 --0-1958115311-1165943837=:8108 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello, I have many updates happening in the DB. All of them works with millions of rows. When this happens I get the following message: checkpoints are occurring too frequently (294 seconds apart) Consider increasing the configuration parameter "checkpoint_segments". Some values from my postgresql.conf files are as follows. My question is do I really increase the checkpoint segments or should I increase the WAL buffers or do an create table as and then insert instead of update statement? shared_buffers = 20000 #60000 # min 16 or max_connections*2, 8KB each -- Bizgres work_mem = 65536 #131072 #65536 # min 64, size in KB -- Bizgres Database change from 1024 to 65536 maintenance_work_mem = 524288 #131072 # min 1024, size in KB max_fsm_pages = 8000000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 32768 # min 100, ~70 bytes each fsync = on # turns forced synchronization on or off wal_buffers = 128 # min 4, 8KB each checkpoint_segments = 256 # in logfile segments, min 1, 16MB each checkpoint_timeout = 3600 #300 # range 30-3600, in seconds checkpoint_warning = 300 # in seconds, 0 is off default_statistics_target = 250 # range 1-1000 Thanks Abu --------------------------------- Have a burning question? Go to Yahoo! Answers and get answers from real people who know. --0-1958115311-1165943837=:8108 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello,

I have many updates happening in the DB. All of them works with millions of rows. When this happens I get the following message:

checkpoints are occurring too frequently (294 seconds apart)
Consider increasing the configuration parameter "checkpoint_segments".

Some values from my postgresql.conf files are as follows. My question is do I really increase the checkpoint segments or should I increase the WAL buffers or do an create table as and then insert instead of update statement?

shared_buffers = 20000 #60000                   # min 16 or max_connections*2, 8KB each -- Bizgres work_mem = 65536 #131072 #65536                 # min 64, size in KB -- Bizgres Database change from 1024 to 65536
maintenance_work_mem = 524288 #131072           # min 1024, size in KB
max_fsm_pages = 8000000                 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 32768               # min 100, ~70 bytes each

fsync = on                              # turns forced synchronization on or off
wal_buffers = 128                       # min 4, 8KB each
checkpoint_segments = 256               # in logfile segments, min 1, 16MB each
checkpoint_timeout = 3600  #300         # range 30-3600, in seconds
checkpoint_warning = 300                # in seconds, 0 is off
default_statistics_target = 250         # range 1-1000

Thanks
Abu


Have a burning question? Go to Yahoo! Answers and get answers from real people who know. --0-1958115311-1165943837=:8108--