[GENERAL] checkpoints are occurring too frequently
I have a lot of entries like this in the log file 2011-11-17 02:02:46 PYST LOG: checkpoints are occurring too frequently (13 seconds apart) 2011-11-17 02:02:46 PYST HINT: Consider increasing the configuration parameter checkpoint_segments. No, checkpoint parameters in postgres.conf are: checkpoint_segments = 32 # in logfile segments, min 1, 16MB each checkpoint_timeout = 10min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 What should be a correct value for checkpoint_segments to avoid excessive checkpoint events?
Re: [GENERAL] checkpoints are occurring too frequently
increase your checkpoint segments -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] checkpoints are occurring too frequently
Hi Anibal, On Thu, 17 Nov 2011 09:48:10 -0300, Anibal David Acosta a...@devshock.com wrote: What should be a correct value for checkpoint_segments to avoid excessive checkpoint events? There is no golden rule or value that fits all scenarios. Usually 32 is a good value to start with, however it might not be perfectly tailored for your environment. To give you an idea, currently you are issuing a checkpoint every 32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us better your workload, if it is subject to usage spikes or regularly distributed throughout the day mainly in terms of inserts/updates/deletes. I would gradually try and increase checkpoint_segments by 32 and monitor the effects. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] checkpoints are occurring too frequently
Thanks! -Mensaje original- De: Gabriele Bartolini [mailto:gabriele.bartol...@2ndquadrant.it] Enviado el: jueves, 17 de noviembre de 2011 10:14 a.m. Para: Anibal David Acosta CC: pgsql-general@postgresql.org Asunto: Re: [GENERAL] checkpoints are occurring too frequently Hi Anibal, On Thu, 17 Nov 2011 09:48:10 -0300, Anibal David Acosta a...@devshock.com wrote: What should be a correct value for checkpoint_segments to avoid excessive checkpoint events? There is no golden rule or value that fits all scenarios. Usually 32 is a good value to start with, however it might not be perfectly tailored for your environment. To give you an idea, currently you are issuing a checkpoint every 32*16MB of WAL traffic (or every 10 minutes). Maybe you can describe us better your workload, if it is subject to usage spikes or regularly distributed throughout the day mainly in terms of inserts/updates/deletes. I would gradually try and increase checkpoint_segments by 32 and monitor the effects. Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Checkpoints are occurring too frequently...
LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checkpoints are occurring too frequently...
On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Checkpoints are occurring too frequently...
Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. G. Patnude ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checkpoints are occurring too frequently...
On Tue, 2005-07-12 at 13:29, Greg Patnude wrote: Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. So, how often is this running? Once a second, once a minute, once and hour? If it's only running once an hour, then something else is wrong. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Checkpoints are occurring too frequently...
LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? This generally happens as a result of having LOTS of updates going into the database. If you're doing bulk loads of data, this is absolutely normal. It probably is a good idea to increase the specified config parameter; that can improve performance for most of the cases where the message comes up. -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://cbbrowne.com/info/rdbms.html Rules of the Evil Overlord #153. My Legions of Terror will be an equal-opportunity employer. Conversely, when it is prophesied that no man can defeat me, I will keep in mind the increasing number of non-traditional gender roles. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checkpoints are occurring too frequently...
-Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 11:40 AM To: Greg Patnude Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Checkpoints are occurring too frequently... On Tue, 2005-07-12 at 13:29, Greg Patnude wrote: Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. So, how often is this running? Once a second, once a minute, once and hour? If it's only running once an hour, then something else is wrong. [GP-] I've been running it about 2 or 3 times a minute on average... I increased the checkpoint_segments param in postgreSQL.conf from the factory default' of 1 to 5... this seems to clear up most of the noise Greg Patnude ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checkpoints are occurring too frequently...
Greg Patnude [EMAIL PROTECTED] writes: From: Scott Marlowe [mailto:[EMAIL PROTECTED] So, how often is this running? Once a second, once a minute, once and hour? If it's only running once an hour, then something else is wrong. I've been running it about 2 or 3 times a minute on average... I increased the checkpoint_segments param in postgreSQL.conf from the factory default' of 1 to 5... this seems to clear up most of the noise The factory default has never been 1; AFAIR it's always been 3, and like many of the other defaults that's aimed for small-and-slow machines. If you're not short of disk space, something like 30 is reasonable. (Note this can cost you 32MB per increment, so a setting of 30 means you're willing to invest up to a gigabyte of disk space in WAL. Time was when that was an astonishing amount of disk, but no more...) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Checkpoints are occurring too frequently...
On Tue, 2005-07-12 at 15:55, Greg Patnude wrote: -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 11:40 AM To: Greg Patnude Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Checkpoints are occurring too frequently... On Tue, 2005-07-12 at 13:29, Greg Patnude wrote: Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. So, how often is this running? Once a second, once a minute, once and hour? If it's only running once an hour, then something else is wrong. [GP-] I've been running it about 2 or 3 times a minute on average... I increased the checkpoint_segments param in postgreSQL.conf from the factory default' of 1 to 5... this seems to clear up most of the noise Well, if you've got that many updates happening all the time, you'll want to read up on the fsm settings and probably either use the autovacuum daemon or schedule a regularly running cron job to vacuum the database or those tables. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Checkpoints are occurring too frequently...
The factory default has never been 1; AFAIR it's always been 3, and like many of the other defaults that's aimed for small-and-slow machines. If you're not short of disk space, something like 30 is reasonable. (Note this can cost you 32MB per increment, so a setting of 30 means you're willing to invest up to a gigabyte of disk space in WAL. Time was when that was an astonishing amount of disk, but no more...) As this is a fairly common problem, should we consider increasing the default to 10? J regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Checkpoints are occurring too frequently...
It's been suggested in the past that we ought to document multiple sets of parameter choices from small test platform to big fast machine; MySQL have done something of the sort for a long time. That is probably a good idea. regards, tom lane -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Checkpoints are occurring too frequently...
Joshua D. Drake [EMAIL PROTECTED] writes: The factory default has never been 1; AFAIR it's always been 3, and like many of the other defaults that's aimed for small-and-slow machines. If you're not short of disk space, something like 30 is reasonable. (Note this can cost you 32MB per increment, so a setting of 30 means you're willing to invest up to a gigabyte of disk space in WAL. Time was when that was an astonishing amount of disk, but no more...) As this is a fairly common problem, should we consider increasing the default to 10? Well, 3 is a 100Mb commitment, 10 would be about 300Mb, which is a lot for some people. I'm more inclined to leave it where it is and remind people to read the tuning documents. It's been suggested in the past that we ought to document multiple sets of parameter choices from small test platform to big fast machine; MySQL have done something of the sort for a long time. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
