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@postgresql.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
