Performance issues during backup

From: Dylan Luong <Dylan(dot)Luong(at)unisa(dot)edu(dot)au>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Performance issues during backup
Date: 2018-02-20 05:25:25
Message-ID: 7cf3e053324e4946a8468f4a5989fc3b@ITUPW-EXMBOX3B.UniNet.unisa.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

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?

Thanks.
Dylan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message George Neuner 2018-02-20 05:54:05 Re: Connection loosing at some places - caused by firewall
Previous Message Michael Paquier 2018-02-20 01:37:32 Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6