PostgreSQL 9.6 understanding wal files

Question

I am trying to understand the behaviour of wal files. The wal related settings of the database are as follows:

"min_wal_size"  "2GB"   
"max_wal_size"  "20GB"
"wal_segment_size"  "16MB"
"wal_keep_segments" "0"
"checkpoint_completion_target"  "0.8"
"checkpoint_timeout"    "15min"

The number of wal files is always 1281 or higher:

SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';
-- count 1281

As I understand it this means wal files currently never fall below max_wal_size (1281 * 16 MB = 20496 MB = max_wal_size) ??

I would expect the number of wal files to decrease below maximum right after a checkpoint is reached and data is synced to disk. But this is clearly not the case. What am I missing?

richyen · Accepted Answer · 2019-12-20 17:16:12Z

3

As per the documentation (emphasis added):

The number of WAL segment files in pg_xlog directory depends on min_wal_size, max_wal_size and the amount of WAL generated in previous checkpoint cycles. When old log segment files are no longer needed, they are removed or recycled (that is, renamed to become future segments in the numbered sequence). If, due to a short-term peak of log output rate, max_wal_size is exceeded, the unneeded segment files will be removed until the system gets back under this limit. Below that limit, the system recycles enough WAL files to cover the estimated need until the next checkpoint, and removes the rest

So, as per your observation, you are probably observing the "recycle" effect -- the old WAL files are getting renamed instead of getting removed. This saves the disk some I/O, especially on busy systems.

Bear in mind that once a particular file has been recycled, it will not be reconsidered for removal/recycle again until it has been used (i.e., the relevant LSN is reached and checkpointed). That may take a long time if your system suddenly becomes less active.

edited Dec 20, 2019 at 17:16

answered Dec 20, 2019 at 16:55

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SteZe Over a year ago

Thank you for the explanation. What does this mean for my system? Should I increase max_wal_size or reduce checkpoint_timeout? Or is this totally normal and I shouldn't do anything?

richyen Over a year ago

How big is your pg_xlog partition? Personally, I would leave it alone if you don’t expect more of these activity spikes on a regular basis. Postgres will not prevent WAL activity based on the config—it might give you warnings, but you shouldn’t see any interruption in service. Increasing max_wal_size could work to your detriment—as mentioned in the docs: it could increase time for crash recovery.

SteZe Over a year ago

What if those spikes are happening regularly? I was seeing this behavior when investigating a regular drop in query performance which is probably connected to a disk i/o problem or bottleneck.

richyen Over a year ago

I think the answer to this is not necessarily straightforward, as there are a few additional data points needed--do you have any information that you can provide from your logs (i.e., messages saying checkpoints are happening too frequently)? Do you have any graphs indicating that the performance dips coincide with checkpoint activity? You could even be suffering from shared_buffers being set too high, causing a massive flushes of cache to disk. Trying to diagnose all that could be beyond the scope of this SO question (though I'm willing to open up a chat here and discuss further)

jjanes · Accepted Answer · 2019-12-20 17:07:55Z

0

If your server is very busy and then abruptly becomes mostly idle, you can get into a situation where the log fails remain at max_wal_size for a very long time. At the time it was deciding whether to remove or recycle the files, it was using them up quickly and so decided to recycle up to max_wal_size for predicted future use, rather than remove them. Once recycled, they will never get removed until they have been used (you could argue that that is a bug), and if the server is now mostly idle it will take a very long time for them to be used and thus removed.

answered Dec 20, 2019 at 17:07

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

PostgreSQL 9.6 understanding wal files

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related