Re: Reduce the time required for a database recovery from archive.

[Dmitry Shulga]

Hello Stephen,

> On 19 Oct 2020, at 23:25, Stephen Frost  wrote:
> 
> Greetings,
> 
 Implementation of this approach assumes running several background 
 processes (bgworkers)
 each of which runs a shell command specified by the parameter 
 restore_command
 to deliver an archived WAL file. Number of running parallel processes is 
 limited
 by the new parameter max_restore_command_workers. If this parameter has 
 value 0
 then WAL files delivery is performed using the original algorithm, that is 
 in
 one-by-one manner. If this parameter has value greater than 0 then the 
 database
 server starts several bgworker processes up to the limit specified by
 the parameter max_restore_command_workers and passes to every process
 WAL file name to deliver. Active processes start prefetching of specified
 WAL files and store received files in the directory pg_wal/pgsql_tmp. After
 bgworker process finishes receiving a file it marks itself as a free 
 process
 and waits for a new request to receive a next WAL file. The main process
 performing database recovery still handles WAL files in one-by-one manner,
 but instead of waiting for a next required WAL file's availability it 
 checks for
 that file in the prefetched directory. If a new file is present there,
 the main process starts its processing.
>>> 
>>> I'm a bit confused about this description- surely it makes sense for the
>> OK. The description I originally provided was probably pretty misleading so 
>> I will try to clarify it a bit.
>> 
>> So, as soon as a bgworker process finishes delivering a WAL file it marks 
>> itself as a free.
>> 
>> WAL records applier working in parallel and processing the WAL files in 
>> sequential manner.
>> Once it finishes handling of the current WAL file, it checks whether it is 
>> possible to run extra bgworker processes
>> to deliver WAL files which will be required a bit later. If there are free 
>> bgworker processes then applier requests 
>> to start downloading of one or more extra WAL files. After that applier 
>> determines a name of next WAL file to handle
>> and checks whether it exist in the prefetching directory. If it does exist 
>> then applier starts handling it and
>> processing loop is repeated. 
> 
> Ok- so the idea is that each time the applying process finishes with a
> WAL file then it'll see if there's an available worker and, if so, will
> give it the next file to go get (which would presumably be some number
> in the future and the actual next file the applying process needs is
> already available).  That sounds better, at least, though I'm not sure
> why we're making it the job of the applying process to push the workers
> each time..?
Every bgwork serves as a task to deliver a WAL file. Considering a task as an 
active entity is well-known approach in software design.
So I don't see any issues with such implementation. Moreover, implementation of 
this approach is probably simpler than any other alternatives
and still providing positive performance impact in comparing with current (non 
optimized) implementation.

>  Also, I'm not sure about the interface- wouldn't it make
> more sense to have a "pre-fetch this amount of WAL" kind of parameter
> directly instead of tying that to the number of background workers?
This approach was originally considered and closely discussed.
Finally, it was decided that introducing an extra GUC parameter to control 
pre-fetch limit is not practical since it shifts responsibility for tuning 
prefetching
mechanism from postgres server to a user.
From my point of view the fewer parameters exist to set up some feature the 
better.

>  You
> might only need one or two processes doing WAL fetching to be able to
> fetch faster than the applying process is able to apply it, but you
> probably want to pre-fetch more than just one or two 16 MB WAL files.


Every time when prefetching is started a number of potentially prefetched files 
is calculated by expression
 PREFETCH_RATION * max_restore_command_workers - 'number of already 
prefetched files'
where PREFETCH_RATION is compiled-in constant and has value 16.

After that a task for delivering a next WAL file is placed to a current free 
bgworker process up until no more free bgworker processes.


> In other words, I would have thought we'd have:
> 
> wal_prefetch_amount = 1GB
> max_restore_command_workers = 2
> 
> and then you'd have up to 2 worker processes running and they'd be
> keeping 1GB of WAL pre-fetched at all times.  If we have just
> 'max_restore_command_workers' and you want to pre-fetch 1GB of WAL then
> you'd have to have a pretty high value there and you'd end up with
> a bunch of threads that all spike to go do work each time the applying
Sorry, I don't see how we can end up with a bunch of threads?
max_restore_command_workers has value 2 in your example meaning that no more 
than 2 bgworkers could be run c

Re: Reduce the time required for a database recovery from archive.

[Dmitry Shulga]

Hi StephenBased on our last discussion I redesigned the implementation of WAL archive recovery speed-up. The main idea of the new implementation was partly borrowed from your proposal, to be more accurate from the following one:On 9 Nov 2020, at 23:31, Stephen Frost  wrote:The relatively simple approach I was thinking was that a couple ofworkers would be started and they'd have some prefetch amount that needsto be kept out ahead of the applying process, which they couldpotentially calculate themselves without needing to be pushed forward bythe applying process.In the new implementation, several workers are spawned on server start up for delivering WAL segements from archive. The number of workers to spawn is specfied by the GUC parameter wal_prefetch_workers; the max. number of files to preload from the archive is determined by the GUC parameter wal_max_prefetch_amount. The applier of WAL records still handles WAL files one-by-one, but since several prefetching processes are loading files from the archive, there is a high probability that when the next WAL file is requested by the applier of WAL records, it has already been delivered from the archive.Every time any of the running workers is going to preload the next WAL file, it checks whether a limit imposed by the parameter wal_max_prefetch_amount was reached. If it was, then the process suspends preloading until the WAL applier process handles some of the already preloaded WAL files and the total number of already loaded but not yet processed WAL files drops below this limit.At the moment I didn't implement a mechanism for dynamic calculation of the number of workers required for loading the WAL files in time. We can consider current (simplified) implementation as a base for further discussion and turn to this matter in the next iteration if it be needed.Also I would like to ask your opinion about the issue I'm thinking about:  Parallel workers spawned for preloading WAL files from archive use the original mechanism for delivering files from archive - they run a command specified by the GUC parameter restore_command. One of the possible parameters accepted by the restore_command is %r, which specifies the filename of the last restart point. If several workers preload WAL files simultaneously with another process applying the preloaded WAL files, I’m not sure what is correct way to determine the last restart point value that WAL-preloading processes should use, because this value can be updated at any time by the process that applies WALs.Another issue that I would like to ask your opinion about regards to choosing correct value for a max size of the hash table stored in shared memory. Currently, wal_max_prefetch_amount is passed as the value for max. hash table size that I'm not sure is the best decision.Thanks in advance for feedback.Regards,Dmitry

0001-Reduce-time-required-to-recover-database-from-archiv.patch
Description: Binary data

Reduce the time required for a database recovery from archive.

[Dmitry Shulga]

Hello hackers,

Currently, database recovery from archive is performed sequentially,
by reading archived WAL files and applying their records to the database.

Overall archive file processing is done one by one, and this might
create a performance bottleneck if archived WAL files are delivered slowly,
because the database server has to wait for arrival of the next
WAL segment before applying its records.

To address this issue it is proposed to receive archived WAL files in parallel
so that when the next WAL segment file is required for processing of redo log
records it would be already available.

Implementation of this approach assumes running several background processes 
(bgworkers)
each of which runs a shell command specified by the parameter restore_command
to deliver an archived WAL file. Number of running parallel processes is limited
by the new parameter max_restore_command_workers. If this parameter has value 0
then WAL files delivery is performed using the original algorithm, that is in
one-by-one manner. If this parameter has value greater than 0 then the database
server starts several bgworker processes up to the limit specified by
the parameter max_restore_command_workers and passes to every process
WAL file name to deliver. Active processes start prefetching of specified
WAL files and store received files in the directory pg_wal/pgsql_tmp. After
bgworker process finishes receiving a file it marks itself as a free process
and waits for a new request to receive a next WAL file. The main process
performing database recovery still handles WAL files in one-by-one manner,
but instead of waiting for a next required WAL file's availability it checks for
that file in the prefetched directory. If a new file is present there,
the main process starts its processing.

The patch implemeting the described approach is attached to this email.
The patch contains a test in the file src/test/recovery/t/021_xlogrestore.pl
Although the test result depends on real execution time and hardly could be
approved for including to the repository it was added in order to show
a positive effect from applying the new algorithm. In my environment restoring
from archive with parallel prefetching is twice as faster than in original
mode.

Regards,
Dmitry.



archive_recovery_speedup.patch
Description: Binary data

Re: Reduce the time required for a database recovery from archive.

[Dmitry Shulga]

Hello Stephen

> On 9 Sep 2020, at 21:26, Stephen Frost  wrote:
> 
> Greetings,
> 
> * Dmitry Shulga (d.shu...@postgrespro.ru) wrote:
>> Overall archive file processing is done one by one, and this might
>> create a performance bottleneck if archived WAL files are delivered slowly,
>> because the database server has to wait for arrival of the next
>> WAL segment before applying its records.
>> 
>> To address this issue it is proposed to receive archived WAL files in 
>> parallel
>> so that when the next WAL segment file is required for processing of redo log
>> records it would be already available.
> 
> Yes, pgbackrest already does exactly this (if configured)- uses parallel
> processes to fetch the WAL and have it be available ahead of time.

pgbackrest is a third-party software that should be additionally installed on 
customer's premises.

On the other hand, built-in support of this optimization in PostgresSQL is a 
good argument to add
this feature  and provide it to customers just out of the box.
 
> 
>> Implementation of this approach assumes running several background processes 
>> (bgworkers)
>> each of which runs a shell command specified by the parameter restore_command
>> to deliver an archived WAL file. Number of running parallel processes is 
>> limited
>> by the new parameter max_restore_command_workers. If this parameter has 
>> value 0
>> then WAL files delivery is performed using the original algorithm, that is in
>> one-by-one manner. If this parameter has value greater than 0 then the 
>> database
>> server starts several bgworker processes up to the limit specified by
>> the parameter max_restore_command_workers and passes to every process
>> WAL file name to deliver. Active processes start prefetching of specified
>> WAL files and store received files in the directory pg_wal/pgsql_tmp. After
>> bgworker process finishes receiving a file it marks itself as a free process
>> and waits for a new request to receive a next WAL file. The main process
>> performing database recovery still handles WAL files in one-by-one manner,
>> but instead of waiting for a next required WAL file's availability it checks 
>> for
>> that file in the prefetched directory. If a new file is present there,
>> the main process starts its processing.
> 
> I'm a bit confused about this description- surely it makes sense for the
OK. The description I originally provided was probably pretty misleading so I 
will try to clarify it a bit.

So, as soon as a bgworker process finishes delivering a WAL file it marks 
itself as a free.

WAL records applier working in parallel and processing the WAL files in 
sequential manner.
Once it finishes handling of the current WAL file, it checks whether it is 
possible to run extra bgworker processes
to deliver WAL files which will be required a bit later. If there are free 
bgworker processes then applier requests 
to start downloading of one or more extra WAL files. After that applier 
determines a name of next WAL file to handle
and checks whether it exist in the prefetching directory. If it does exist then 
applier starts handling it and
processing loop is repeated. 

> parallel workers to continue to loop and fetch up to some specified
> max..?  Then to monitor and to fetch more when the amount pre-fetched so
> far drops before that level?  The description above makes it sound like
> X WAL will be fetched ahead of time, and then the recovery process will
> go through those until it runs out and then it'll have to wait for the
> next X WAL to be fetched, which means it's still going to end up being
> delayed even with these parallel processes, which isn't good.
> 

> Does this also properly handle timeline switches..?
> 
> Thanks,
> 
> Stephen

Regards,
Dmitry