Re: [GENERAL] pg_dump with low priority?
On Sat, 2005-10-22 at 07:39, Bryan Field-Elliot wrote: We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. You could either set up plain old replication via slony / mammoth replicator, and backup the slave, or setup Point in Time Recovery. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump with low priority?
nice comes to mind: nice pg_dump ... On Sat, 2005-10-22 at 07:39, Bryan Field-Elliot wrote: We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump with low priority?
On 10/24/05, CSN [EMAIL PROTECTED] wrote: nice comes to mind: nice pg_dump ... as mentioned earlier...nice isn't going to do anything for I/O. PITR (point in time recovery) would be, in my opinion, the best solution to this problem. aaron.glenn ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pg_dump with low priority?
We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. Thank you for any advice, Bryan
Re: [GENERAL] pg_dump with low priority?
Bryan Field-Elliot [EMAIL PROTECTED] schrieb: Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. You can use 'nice', see see the man-page. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump with low priority?
Bryan Field-Elliot [EMAIL PROTECTED] writes: We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. You should probably use 'top' and 'vmstat' or 'iostat' to make sure the problem is what you think it is. Guessing is usually a bad idea. :) That said, I/O starvation is the most likely candidate. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. Unfortunately, changing the CPU priority with 'nice' doesn't generally affect I/O bandwidth (since an I/O bound process doesn't use much CPU). I think there has been some work on I/O priorities in the Linux kernel, but I'm not sure where that is. Are you putting the dump file on the same device as the database lives on? If so, moving it to a different device/controller would take some of the write load off your database disk. You could also send the dump file over the network to another machine rather than saving it locally, which would do the above and also (probably) slow down the whole dump process, depending on the relative speeds of your disk and your network. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
