Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id B9A352E003B for ; Mon, 5 May 2008 16:44:20 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 35086-01 for ; Mon, 5 May 2008 16:44:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by developer.postgresql.org (Postfix) with ESMTP id 6683F2E003E for ; Mon, 5 May 2008 16:44:17 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d37so2695627pye.29 for ; Mon, 05 May 2008 12:44:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=bJSe41Xtrch8gK94s0dqyRaf1s0EguaJLKn8bvz9NYM=; b=RWtTEF60WX9H1US9YbmT83ulDXqrpC3Bj8Mol//JP/kQXEegvNguJO2rFC5YXJWsUGD23tnkIGwLzcId+F3yV1XQTp7vspzxD0O6ZE+kcnrxW4b+NTkayJAd2K8BbX+/4tQAgzKvpmYGTLOznJKLlSOgVuTUlZYSBf+7DcKkPzM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=gPNFo5IeJV06KxFIlRHKyp7ixM24Ek/WUaZoxqhw9r6ljbYFr3aa7R+sPWUPMLuZ1bQ0uf2TKqkn61149mVYEkdONYcta5vGoz8bjz7POUCC2chQxww+/aXMzrkru9pF8yvFQpBUNyJcuzjRJf9iB1AIr0GolTlOdFM4eglA3xw= Received: by 10.114.52.1 with SMTP id z1mr6101961waz.192.1210016656139; Mon, 05 May 2008 12:44:16 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Mon, 5 May 2008 12:44:16 -0700 (PDT) Message-ID: Date: Mon, 5 May 2008 13:44:16 -0600 From: "Scott Marlowe" To: "Campbell, Lance" Subject: Re: Backup causing poor performance - suggestions Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/58 X-Sequence-Number: 30026 On Mon, May 5, 2008 at 11:19 AM, Campbell, Lance wrote: > Scott, > When I do a df -h I see that the database takes up a total of 8Gig of > disk space. This is not the size of the backup file of the database. Ok. Just wanted to make sure. Looking at vmstat, with little or no blocks read in, it would appear your database fits in memory. > I have attached the vmstats as a text file. Are there any properties I > could adjust that would help with writing data during a backup? Are > there any properties that might help with improving pg_dump performance? With as much writing as you have going on, it might help to crank up your setting for checkpoint segments to something like 100 or more. With as much disk space as you've got you can afford it. Other than that, you might wanna look at a faster / better RAID controller in the future. One with lots of battery backed cache set to write back. > Ideally I want to keep the backups as simple a process as possible > before we have to go to the next level of backup. My next strategy is > to put some of the tables in tablespaces on a couple different disks. I > will also backup to a dedicated set of disks. If there are a few > performance tweaks I can make I will. I was hoping to wait a few years > before we have to go to a more involved backup process. It may well be cheaper to go the route of a faster RAID controller with battery backed cache, since it's a one time investment, not an ongoing maintenance issue like running PITR or slony can become. Plus it helps overall performance quite a bit.