Re: Performance issues during backup

On Fri, 23 Feb 2018 01:46:22 +0000
Dylan Luong <Dylan(dot)Luong(at)unisa(dot)edu(dot)au> wrote:

> 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.

If "ionice -c 2 -n 7" helped some, maybe try "ionice -c 3" to set IO priority
to the lowest possible level.

However, I've used this technique many times on heavily loaded systems without
issue. If you're having to squirm this much to avoid problems, you probably
need to get better hardware or investigate the possibility that your
hardware is faulty or some other IO related issue.

> The affected queries are more of the update/delete/insert queries. Could pg_start_backup be causing locking of some sort.

Not in my experience. And the fact that they are write queries having trouble
makes me theorize that you're saturating the write capacity of your disks.

> 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/
>

--
Bill Moran