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.romer...@gmail.com] Sent: Wednesday, 21 February 2018 1:37 AM To: Laurenz Albe <laurenz.a...@cybertec.at> Cc: Dylan Luong <dylan.lu...@unisa.edu.au>; pgsql-general@lists.postgresql.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.a...@cybertec.at<mailto:laurenz.a...@cybertec.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/
