Thread: Queries related to checkpoints

Queries related to checkpoints

From
Rohan Malhotra
Date:
Hi,

I am a Noob with db tuning and trying to analyze pg_stats_brwriter data

checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc                                                                                                                                                                  
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------+------
            35241 |              58 |             699136 |        581839 |             1597 |         1663650 |    2205969940


Almost all checkpoints (99.8%) that happened are because of checkpoint_timeout passing. Is this good or should I increaase my checkpoint_segments?
During checkpoints, 699136 8K buffers were written out which is pretty low (less than 1MB).

buffers allocated (2205969940 8K), 1663650 times a database backend (probably the client itself) had to write a page in order to make space for the new allocation. Buffer allocated seems to be too high than backend buffers.
 
How to read more into the data?

Regards
Rohan

Re: [PERFORM] Queries related to checkpoints

From
"Kevin Grittner"
Date:
Rohan Malhotra <yourbuddyrohan@gmail.com> wrote:

First off, for a one-row result with too many values to fit on one
line, you might want to use this in psql:

\x on

More importantly, you seem to be misinterpreting the numbers.

You've allocated 2,205,969,940 buffers.  Of those allocations, the
allocating backend had to first write a dirty buffer to free up a
buffer to use 1,663,650 times.  That's pretty small as a percentage
of allocations, but since it's larger than the other causes of dirty
buffer writes (699,136 during checkpoints and 581,839 by the
background writer), I would be tempted to make the background writer
a little more aggressive.  Assuming you're currently at the defaults
for these, perhaps:

bgwriter_lru_maxpages = 200
bgwriter_lru_multiplier = 4

This may (or may not) increase the physical writes on your system,
so you want to closely monitor the impact of the change in terms of
whatever metrics matter most to you.  For example, in our shop, we
tend to tune our big databases which back a website such that we get
zero "write storms" which cause delays of 20 seconds or more on
queries which rormally run in less than a millisecond.  Your
concerns may be different.

For more detailed treatment of the issue look for posts by Greg
Smith; or better yet, buy his book:

http://www.postgresql.org/docs/books/

-Kevin