Re: Strange checkpoint behavior - checkpoints take a longtime

Hello, it’s postgres 9.6 . I know reducing wal_buffers from 32 MB to 16MB is unlikely to help, but according to the documentation values larger than 16MB are unlikely to help either, at least at the default wal segment size, so I decided to go with 16 MB.

I will probably try the compression on Monday or Tuesday, I can only experiment with a single set of changes in a day, and I plan to test something else tomorrow.

Thanks for the suggestions, and sorry for the reply style, but my mail client is not best suited for replying inline to individual points.

From: Andres Freund
Sent: Friday, October 6, 2017 04:51
To: Vladimir Nicolici
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Strange checkpoint behavior - checkpoints take a longtime

Hi,

On 2017-10-05 22:58:31 +0300, Vladimir Nicolici wrote:
> I changed some configuration parameters during the night to the values I was considering yesterday:
>
> - shared_buffers = 144GB #previously 96 GB
> - bgwriter_lru_maxpages = 100 #previously 400
> - checkpoint_timeout = 30min #previously 5min
> - checkpoint_completion_target = 0.83 #previously 0.85; 0.83 means 25 minutes writes out of 30 minutes.
> - max_wal_size = 96GB #previously 16GB

Which version of postgres is this?

> - wal_buffers = 16MB #previously 32 MB

That seems quite unlikely to help.

You might want to try also enabling wal_compression, sometimes the WAL
volume is a considerable problem.

I'd suggest reporting some "pidstat -dl 1" output, so we can see which
processes are doing how much IO.

Regards,

Andres