Re: WIP/PoC for parallel backup - Mailing list pgsql-hackers

On Mon, Apr 27, 2020 at 10:23 PM David Zhang <david.zhang@highgo.ca> wrote:
>
> Hi,
>
> Here is the parallel backup performance test results with and without
> the patch "parallel_backup_v15" on AWS cloud environment. Two
> "t2.xlarge" machines were used: one for Postgres server and the other
> one for pg_basebackup with the same machine configuration showing below.
>
> Machine configuration:
>      Instance Type        :t2.xlarge
>      Volume type          :io1
>      Memory (MiB)         :16GB
>      vCPU #               :4
>      Architecture         :x86_64
>      IOP                  :6000
>      Database Size (GB)   :108
>
> Performance test results:
> without patch:
>      real 18m49.346s
>      user 1m24.178s
>      sys 7m2.966s
>
> 1 worker with patch:
>      real 18m43.201s
>      user 1m55.787s
>      sys 7m24.724s
>
> 2 worker with patch:
>      real 18m47.373s
>      user 2m22.970s
>      sys 11m23.891s
>
> 4 worker with patch:
>      real 18m46.878s
>      user 2m26.791s
>      sys 13m14.716s
>
> As required, I didn't have the pgbench running in parallel like we did
> in the previous benchmark.
>

So, there doesn't seem to be any significant improvement in this
scenario.  Now, it is not clear why there was a significant
improvement in the previous run where pgbench was also running
simultaneously.  I am not sure but maybe it is because when a lot of
other backends were running (performing read-only workload) the
backend that was responsible for doing backup was getting frequently
scheduled out and it slowed down the overall backup process.  And when
we start using multiple backends for backup one or other backup
process is always running making the overall backup faster.  One idea
to find this out is to check how much time backup takes when we run it
with and without pgbench workload on HEAD (aka unpatched code).  Even
if what I am saying is true or there is some other reason due to which
we are seeing speedup in some cases (where there is a concurrent
workload), it might not make the case for using multiple backends for
backup but still, it is good to find that information as it might help
in designing this feature better.

> The perf report files for both Postgres server and pg_basebackup sides
> are attached.
>

It is not clear which functions are taking more time or for which
functions time is reduced as function symbols are not present in the
reports.  I think you can refer
"https://wiki.postgresql.org/wiki/Profiling_with_perf" to see how to
take profiles and additionally use -fno-omit-frame-pointer during
configure (you can use CFLAGS="-fno-omit-frame-pointer during
configure).


--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com