Thread: Pgbackrest backup is too slow

Pgbackrest backup is too slow

From
Ajay Pratap
Date:
Hello,
I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest to take db backup.
Problem is backup is too slow.
My data dir size is 9.6G and full backup runtime is 22 mins
I also tried using process-max=3, full backup runtime = 21 mins
Following are the backrest configurations:
backrest.conf for pg-primary:
[data]
pg1-path=/var/lib/pgsql/10/data


[global]
log-level-file=detail
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
process-max=3

Am I doing something wrong here?
In production we have a database of size more than 500GB. It would not be feasible for me to use it as it is.pgbackrest version is 2.18-1.rhel7.x86_64.
installed from pgdg yum repo.
Also I need to take a backup from the remote machine which would also increase the overall runtime of backup/restore.

Thanks for the help in advance

Regards,
Ajay pratap

Disclaimer: The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.

Re: Pgbackrest backup is too slow

From
Stephen Frost
Date:
Greetings,

* Ajay Pratap (ajaypratap@ameyo.com) wrote:
> I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest
> to take db backup.
> Problem is backup is too slow.

Have you tried running 'top' to see what's going on?

> My data dir size is 9.6G and full backup runtime is 22 mins
> I also tried using process-max=3, full backup runtime = 21 mins

Erm, those numbers don't make any sense to me- as an example, we
regularly back up a 12GB database, from Dallas to San Fran, in 5
minutes.

I see you're requesting a checkpoint to start immediately, but how long
does the checkpoint actually take to complete?  Have you looked at your
PG logs..?

Have you tried running 'top' and then running the backup and looked to
see what's going on?  Is the CPU at 100%?  Are all the processes stuck
in 'D' state all the time (meaning you don't have enough i/o resources),
or...?

> In production we have a database of size more than 500GB. It would not be
> feasible for me to use it as it is.pgbackrest version is
> 2.18-1.rhel7.x86_64.
> installed from pgdg yum repo.

Given sufficient resources, we've seen pgbackrest push over 1TB/hour, so
there is definitely something strange going on here.

Thanks,

Stephen

Attachment

Re: Pgbackrest backup is too slow

From
Ajay Pratap
Date:
Hello Stephen,
Thanks for your prompt response.
As recommended by you, I will try to isolate the problem at system level,
Meanwhile, Is there any system recommendation by pgbackrest, (how much process-max should be configured) should I set affinity to pgbackrest process, how many core should be kept for backup /restore. Or any other configuration?

My DB size is 500GB and increasing .


Regards,
Ajay


On Fri, Oct 11, 2019 at 6:20 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,

* Ajay Pratap (ajaypratap@ameyo.com) wrote:
> I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest
> to take db backup.
> Problem is backup is too slow.

Have you tried running 'top' to see what's going on?

> My data dir size is 9.6G and full backup runtime is 22 mins
> I also tried using process-max=3, full backup runtime = 21 mins

Erm, those numbers don't make any sense to me- as an example, we
regularly back up a 12GB database, from Dallas to San Fran, in 5
minutes.

I see you're requesting a checkpoint to start immediately, but how long
does the checkpoint actually take to complete?  Have you looked at your
PG logs..?

Have you tried running 'top' and then running the backup and looked to
see what's going on?  Is the CPU at 100%?  Are all the processes stuck
in 'D' state all the time (meaning you don't have enough i/o resources),
or...?

> In production we have a database of size more than 500GB. It would not be
> feasible for me to use it as it is.pgbackrest version is
> 2.18-1.rhel7.x86_64.
> installed from pgdg yum repo.

Given sufficient resources, we've seen pgbackrest push over 1TB/hour, so
there is definitely something strange going on here.

Thanks,

Stephen

Disclaimer: The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.

Re: Pgbackrest backup is too slow

From
"Brad Nicholson"
Date:

Stephen Frost <sfrost@snowman.net> wrote on 2019/10/11 08:50:53 AM:

> From: Stephen Frost <sfrost@snowman.net>

> To: Ajay Pratap <ajaypratap@ameyo.com>
> Cc: Postgres General <pgsql-general@postgresql.org>
> Date: 2019/10/11 08:51 AM
> Subject: [EXTERNAL] Re: Pgbackrest backup is too slow
>
> Greetings,
>
> * Ajay Pratap (ajaypratap@ameyo.com) wrote:
> > I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest
> > to take db backup.
> > Problem is backup is too slow.
>
> Have you tried running 'top' to see what's going on?
>
> > My data dir size is 9.6G and full backup runtime is 22 mins
> > I also tried using process-max=3, full backup runtime = 21 mins
>
> Erm, those numbers don't make any sense to me- as an example, we
> regularly back up a 12GB database, from Dallas to San Fran, in 5
> minutes.


How many database objects do you have?  For databases with a lot of tables and/or indexes (think tens of thousands), backup performance will slow down.  This has improved a lot in newer versions, but still impacts performance of the backups.

Brad.

Re: Pgbackrest backup is too slow

From
Ajay Pratap
Date:
Thanks for your previous replies.
It turns out my machine was having kernel Issues, which made it slow.
I setup another cluster in different machine now I could able to backup 42G in 6 min(s).
<EOM>

On Fri, Oct 11, 2019 at 7:23 PM Brad Nicholson <bradn@ca.ibm.com> wrote:

Stephen Frost <sfrost@snowman.net> wrote on 2019/10/11 08:50:53 AM:

> From: Stephen Frost <sfrost@snowman.net>

> To: Ajay Pratap <ajaypratap@ameyo.com>
> Cc: Postgres General <pgsql-general@postgresql.org>
> Date: 2019/10/11 08:51 AM
> Subject: [EXTERNAL] Re: Pgbackrest backup is too slow
>
> Greetings,
>
> * Ajay Pratap (ajaypratap@ameyo.com) wrote:
> > I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest
> > to take db backup.
> > Problem is backup is too slow.
>
> Have you tried running 'top' to see what's going on?
>
> > My data dir size is 9.6G and full backup runtime is 22 mins
> > I also tried using process-max=3, full backup runtime = 21 mins
>
> Erm, those numbers don't make any sense to me- as an example, we
> regularly back up a 12GB database, from Dallas to San Fran, in 5
> minutes.


How many database objects do you have?  For databases with a lot of tables and/or indexes (think tens of thousands), backup performance will slow down.  This has improved a lot in newer versions, but still impacts performance of the backups.

Brad.


Disclaimer: The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.