Re: dirty_ratio & dirty_background_ratio settings with huge memory
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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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 sharedbuffers cached > Mem: 31021113052596 49515 2088019922961185 > -/+ buffers/cache: 904183011693 > 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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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
Re: dirty_ratio & dirty_background_ratio settings with huge memory
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 sharedbuffers cached >> Mem: 31021113052596 49515 2088019922961185 >> -/+ buffers/cache: 904183011693 >> 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
Re: 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 sharedbuffers cached > Mem: 31021113052596 49515 2088019922961185 > -/+ buffers/cache: 904183011693 > 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
dirty_ratio & dirty_background_ratio settings with huge memory
Hi, I've got cutomer with really huge RAM, now it's: total used free sharedbuffers cached Mem: 31021113052596 49515 2088019922961185 -/+ buffers/cache: 904183011693 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? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
