RE: Performance issues during backup

Hi
Thanks for the rely.
I have trialed the ionice -c 2 -n 7 tar…. change to our backup script and it appears to have helped but not by much.
The affected queries are more of the update/delete/insert queries. Could pg_start_backup be causing locking of some sort.
Regards
Dylan

From: Rene Romero Benavides [mailto:rene(dot)romero(dot)b(at)gmail(dot)com]
Sent: Wednesday, 21 February 2018 1:37 AM
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: Dylan Luong <Dylan(dot)Luong(at)unisa(dot)edu(dot)au>; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Performance issues during backup

What about sending the backup to a different server? through ssh / rsync or something, that would save lots of IO activity

2018-02-20 2:02 GMT-06:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)at>>:
Dylan Luong wrote:
> We perform nighty base backup of our production PostgreSQL instance. We have a script that basically puts the instance
> into back mode and then backs up (tar) the /Data directory and then takes it out of backup mode.
> Ie,
> psql -c "SELECT pg_start_backup('${DATE}');"
> tar -cvf - ${DATA_DIR} --exclude ${DATA_DIR}/pg_log | split -d -b $TAR_SPLIT_SIZE - ${BACKUP_DIR}/${BACKUP_NAME}
> psql -c "SELECT pg_stop_backup();"
>
> The size of our database is about 250GB and it usually takes about 1 hour to backup.
> During this time, we have performance issue where queries can take up to 15secs to return where normally it takes 2 to 3 seconds.
> During this time (1:30am) usage is low (less than 10 users) on the system.
>
> Has anyone experience the same problem and any suggestions where to look at to resolve the problem?

The "tar" is probably taking up too much I/O bandwidth.

Assuming this is Linux, you could run it with

ionice -c 2 -n 7 tar ...

or

ionice -c 3 tar ...

Of course then you can expect the backup to take more time.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/