Thread: Queries related to checkpoints
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
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