dirty_ratio & dirty_background_ratio settings with huge memory

pinker wrote:

I've got cutomer with really huge RAM, now it's:
total used free shared buffers cached
Mem: 3102111 3052596 49515 208801 992 2961185
-/+ buffers/cache: 90418 3011693
Swap: 8191 1 8190

(free -m)

and before it was twice more (6TB).

and trying to figure out how to set dirty_ratio & dirty_background_ratio
parameters. Even for normal sized server those default sizes are to high,
but now would be ridiculously huge, respectively 1,24TB and 300GB.
I'm thinking about 1 percent for dirty_ratio and not using
dirty_background_ratio because it's not possible to set it below 1% but to
set dirty_background_bytes instead to about 1-2GB.
Maybe someone has got other experience with RAM of this size and those
settings?

Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
and not use the *_ratio settings.

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On 03/06/2018 01:16 PM, Laurenz Albe wrote:

pinker wrote:

I've got cutomer with really huge RAM, now it's:
total used free shared buffers cached
Mem: 3102111 3052596 49515 208801 992 2961185
-/+ buffers/cache: 90418 3011693
Swap: 8191 1 8190

(free -m)

and before it was twice more (6TB).

and trying to figure out how to set dirty_ratio & dirty_background_ratio
parameters. Even for normal sized server those default sizes are to high,
but now would be ridiculously huge, respectively 1,24TB and 300GB.
I'm thinking about 1 percent for dirty_ratio and not using
dirty_background_ratio because it's not possible to set it below 1% but to
set dirty_background_bytes instead to about 1-2GB.
Maybe someone has got other experience with RAM of this size and those
settings?

Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
and not use the *_ratio settings.

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.

It should be the other way around: dirty_background_bytes < dirty_bytes

regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Tomas Vondra wrote:

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.

It should be the other way around: dirty_background_bytes < dirty_bytes

Of course, thanks for the correction.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On 2018-03-06 03:23:29 -0700, pinker wrote:

Hi,
I've got cutomer with really huge RAM, now it's:
total used free shared buffers cached
Mem: 3102111 3052596 49515 208801 992 2961185
-/+ buffers/cache: 90418 3011693
Swap: 8191 1 8190

(free -m)

and before it was twice more (6TB).

and trying to figure out how to set dirty_ratio & dirty_background_ratio
parameters. Even for normal sized server those default sizes are to high,
but now would be ridiculously huge, respectively 1,24TB and 300GB.
I'm thinking about 1 percent for dirty_ratio and not using
dirty_background_ratio because it's not possible to set it below 1% but to
set dirty_background_bytes instead to about 1-2GB.
Maybe someone has got other experience with RAM of this size and those
settings?

With a halfway modern PG I'd suggest to rather tune postgres settings
that control flushing. That leaves files like temp sorting in memory for
longer, while flushing things controlledly for other sources of
writes. See *_flush_after settings.

Greetings,

Andres Freund

Andres Freund wrote

With a halfway modern PG I'd suggest to rather tune postgres settings
that control flushing. That leaves files like temp sorting in memory for
longer, while flushing things controlledly for other sources of
writes. See *_flush_after settings.

Greetings,

Andres Freund

Thank you Andres, I missed those parameters appearing. It's not the case for
this customer, because they have an ancient version 9.0, but it's
interesting for others :)
The maximal value - 2MB - is it not to small? Regarding the fact that for
instance the same buffer will be updated 10 times between checkpoints and
bgwriter well be flushing it directly to disk 10 times instead to allow the
buffer to stay in the cache and do the update faster?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Laurenz Albe wrote

Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
and not use the *_ratio settings.

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds
fine.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Thank you Laurenz. I was always wondering why not to set bigger window
between those 2. Because setting dirty_background_bytes seems quite natural
for me, i.e. start to write asynchronously faster, but why to provoke stalls
faster? is it not better to stretch the window much wider, like 1GB for
dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach
3: Both Ways from
https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2018-03-06 18:59:01 -0700, pinker wrote:

Andres Freund wrote

With a halfway modern PG I'd suggest to rather tune postgres settings
that control flushing. That leaves files like temp sorting in memory for
longer, while flushing things controlledly for other sources of
writes. See *_flush_after settings.

Greetings,

Andres Freund

Thank you Andres, I missed those parameters appearing. It's not the case for
this customer, because they have an ancient version 9.0, but it's
interesting for others :)
The maximal value - 2MB - is it not to small? Regarding the fact that for
instance the same buffer will be updated 10 times between checkpoints and
bgwriter well be flushing it directly to disk 10 times instead to allow the
buffer to stay in the cache and do the update faster?

In most cases where the same buffer is updated frequently it should be
stored in postgres' buffer cache. Note you can tune those.

Greetings,

Andres Freund

pinker wrote:

Laurenz Albe wrote

Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
and not use the *_ratio settings.

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds
fine.

To repeat (for the archive): it should be the other way round.

Thank you Laurenz. I was always wondering why not to set bigger window
between those 2. Because setting dirty_background_bytes seems quite natural
for me, i.e. start to write asynchronously faster, but why to provoke stalls
faster? is it not better to stretch the window much wider, like 1GB for
dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach
3: Both Ways from
https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/

I'd say that setting vm.dirty_background_bytes low and vm.dirty_bytes
very high is not a good idea, at least for databases:

- If flushing data to disk in the background can keep up with the write
activity, you're fine. But then you'd also be fine with a lower setting
for vm.dirty_bytes, right?

- If flushing to disk cannot keep up with the write activity, you'll get
an I/O spike when everything is flushed to disk at checkpoint time and
the database may become unresponsive for a while.

The idea behind a low vm.dirty_bytes setting is to avoid one big I/O spike
and have several smaller ones instead.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com