Performance optimization, what about max_wal_size?

Product Support Self-hosting

AI-generated summary

Performance Optimization: max_wal_size

The discussion revolves around optimizing PostgreSQL performance, specifically the max_wal_size parameter. Paracelsus experienced issues during an update from beta-3 to beta-4, prompting a suggestion to increase max_wal_size due to log congestion. After researching, Paracelsus found that max_wal_size is crucial for performance, particularly for high-write loads.

Paracelsus sought opinions on adjusting max_wal_size, even though the issue only occurred during the update. The forum has 7 million posts and handles 400-600 simultaneous users, but occasionally requires optimization for finite resources.

Falco suggested running a query to investigate the issue, which Paracelsus executed, revealing 4936 timed checkpoints and 225 requested checkpoints. Falco concluded that the issue was likely caused by migrations during the update, and the forum is fine otherwise. Paracelsus trusted Falco’s judgment and acknowledged some load issues during peak hours.

1

Hi,

Latest update from beta-3 to beta-4 was more “cumbersome” than usual for us and something like the following kept coming up because of the logs:
Consider increasing the configuration parameter "max_wal_size".

Since I’m no expert on this matter, googling it showed that this parameter max_wal_size can be very important for performance (second only to shared_buffers according to some, see below):
Tuning max_wal_size in PostgreSQL | EDB (enterprisedb.com)
Tuning Your Postgres Database for High Write Loads (crunchydata.com)
PostgreSQL Performance Tuning and Optimization Guide - Sematext

Any opinions on this? Should max_wal_size parameter be changed even if this only happened during update?

For context, we have a big forum with more than 7 million posts, that consumes a lot of resources, a couple of times a week with 400-600 simultaneous users refreshing and posting and doing everything at the same time. No problems with that, :smiley: but sometimes we are forced to optimize and try to get the best possible performance for our (finite) resources.

2

What’s the value of running SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter; against the DB ?

3

Let’ see if I got it right:

SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter; 
checkpoints_timed | checkpoints_req 
-------------------+-----------------
              4936 |             225
(1 row)
4

Looks like it was caused mostly by migrations during the update, and day to day the forum is fine.

We did ship some heavy weight migrations this month, you don’t happen to have your rebuild log around do you?

5

Nope, I can try to find it somewhere later on, but I trust your judgement :+1: (the forum works ok apart from some load issues during those peak hours I mentioned).