On Wed, 20 Feb 2019 at 07:28, Robert Haas <robertmh...@gmail.com> wrote: > Or maybe we should just blow up the current vacuum cost delay stuff > and replace it with something that is easier to tune. For example, we > could just have one parameter that sets the maximum read rate in kB/s > and another that sets the maximum dirty-page rate in kB/s. Whichever > limit is tighter binds. If we also have the thing that is the topic > of this thread, that's a third possible upper limit.
I had similar thoughts when I saw that Peter's proposal didn't seem all that compatible with how the vacuum cost delays work today. I agree the cost limit would have to turn into something time based rather than points based. To me, it seems just too crude to have a per-backend limit. I think global "soft" limits would be better. Let's say, for example, the DBA would like to CREATE INDEX CONCURRENTLY on a 6TB table. They think this is going to take about 36 hours, so they start the operation at the start of off-peak, which is expected to last 12 hours. This means the create index is going to run for 2 off-peaks and 1 on-peak. Must they really configure the create index to run at a speed that is suitable for running at peak-load? That's pretty wasteful as surely it could run much more quickly during the off-peak. I know there's debate as to if this can rate limit WAL, but, if we can find a way to do that, then it seems to me some settings like: max_soft_global_wal_rate (MB/sec) min_hard_local_wal_rate (MB/sec) That way the rate limited process would slow down to min_hard_local_wal_rate when the WAL rate of all processes is exceeding max_soft_global_wal_rate. The min_hard_local_wal_rate is just there to ensure the process never stops completely. It can simply tick along at that rate until the global WAL rate slows down again. It's likely going to be easier to do something like that in WAL than with buffer read/write/dirties since we already can easily see how much WAL has been written by looking at the current LSN. (Of course, these GUC names are not very good. I just picked them out the air quickly to try and get their meaning across) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
