Re: effective_io_concurrency's steampunk spindle maths

On Sat, Mar 7, 2020 at 9:52 AM Michael Banck <michael(dot)banck(at)credativ(dot)de> wrote:
> On Mon, Mar 02, 2020 at 06:28:41PM +1300, Thomas Munro wrote:
> > * "maintenance_" prefix is like other GUCs that establish (presumably
> > larger) limits for processes working on behalf of many user sessions
>
> I'm a bit skeptical about this - at least in V12 there's only two GUCs
> with 'maintenance' in the name: maintenance_work_mem and
> max_parallel_maintenance_workers. Both are used for utility commands and
> do not apply to regular user queries while (AFAICT) your proposal is not
> limited to utility commands. So I think if you name it
> 'maintenance'-something, people will assume it only involves VACUUM or
> so.

No, the proposal is not for the "maintenance" GUC to affect user
queries. The idea is that the "maintenance" GUC would be used for WAL
prefetching during recovery[1], index prefetch during VACUUM[2] and
probably some other proposed things that are in development relating
to background "undo" processing. What these things have in common, as
Andres first articulated on thread [2] is that they all deal with a
workload that is correlated with the activities of multiple user
backends running concurrently. That's the basic idea of the WAL
prefetching patch: even though all backends suffer from I/O stalls due
to cache misses, usually that's happening concurrently in many
backends. A streaming replica that is trying to follow along
replaying the write-workload of the primary has to suffer all those
stalls sequentially, so I'm trying to recreate some I/O parallelism by
looking ahead in the WAL. The theory with the two GUCs is that a user
backend should be able to use some I/O parallelism, but a
"maintenance" job like the WAL prefetcher should be allowed to use a
lot more. That's why the existing VACUUM code mentioned in thread [2]
already does "+ 10".

Maybe "maintenance" isn't the best word for this, but that's the idea.

[1] https://www.postgresql.org/message-id/flat/CA%2BhUKGJ4VJN8ttxScUFM8dOKX0BrBiboo5uz1cq%3DAovOddfHpA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CA%2BTgmoZP-CTmEPZdmqEOb%2B6t_Tts2nuF7eoqxxvXEHaUoBDmsw%40mail.gmail.com