2

I got a few questions about checkpoint process and parameters for checkpointer.

My job is to optimize a large BI system. While testing and learning, I found articles about tuning base parameters. I learned that the cache is not emptied after each commit command. There are different jobs, for example a checkpointer. While monitoring the database operation, I noticed that it starts frequently. This aroused the suspicion that this was making the performance suffer. Data of 1 trillion are expected to flow into the system in the future.

I suspect parameters like wal_buffer, checkpoint_flush_after or checkpoint_timeout are too small. Most of the values ​​are base values. The overflow Wal buffer dumps the logs to the WAL segment, and due to the small checkpoint_flush_after parameter, the shared buffer dump process to disk is called.

Does it interpret the whole process correctly and assuming that there is a large number of data, should I tweak these parameters?

Current parameters:

wal_buffers: 2048kB   
checkpoint_flush_after: 32
checkpoint_timeout: 300s 
max_wal_size: 16384 MB

Somewhere I read that wal_buffers should have value as about 3% of shared buffer. In my case shared buffer has 2621440 kB. So, can I boost up wal_buffers to 78 643kB ?

I changed a few parameters and performance appears to have improved:

wal_buffers: -1
shared_buffer: 20 GB    
checkpoint_timeout: 1h
max_wal_size: 32 GB
effective_cache_size: 50 GB

Are these values are "healthy" ? I should add that my machine has 64 GB RAM. Everywhere I can read that shared_buffer should be 40% of RAM or effective_cache_size on 50% of RAM. I know these tips are general and depend on database.

0

2 Answers 2

2

If checkpoints occur too frequently, write performance will suffer, because the same blocks get written to disk again and again.

Increase max_wal_size like the hint to the message (that you didn't show us) tells you. For old database releases (v9.4 and older), the parameter is called checkpoint_segments.

0
1

The default setting of wal_buffers is -1, and has been in all currently supported releases and for many before that. That setting causes it to scale automatically with shared_buffers, but not to be larger than wal_segment_size, which is almost always 16MB. So your setting of 2MB is quite a bit smaller than the default value would be given the size of your shared_buffers.

Way back when the default was 64kB, many people did suffer when doing bulk operations from it being too small, but since the current default was implement I think it stopped being anything to worry about. You should probably just set it back to -1 and then forget about it.

But these settings don't interact with each other the way you think. Everything written to WAL buffers needs to get written out to disk, it is not just the "overflow" that gets written. So wal_buffers and checkpoints are pretty independent of each other.

16GB for max_wal_size is a reasonable starting point. Maybe it is still too small, but it would be nice to see some evidence. Turn on log_checkpoints, and let us what stats they log and how far apart they are.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.