Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 95267632F08 for ; Fri, 8 Jan 2010 02:22:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 36175-03 for ; Fri, 8 Jan 2010 06:22:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com [209.85.219.226]) by mail.postgresql.org (Postfix) with ESMTP id 80180633CE6 for ; Fri, 8 Jan 2010 02:21:33 -0400 (AST) Received: by ewy26 with SMTP id 26so17486272ewy.3 for ; Thu, 07 Jan 2010 22:21:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=/uBDgSUNn1gTzDVulRcQaCCPSJNfneTymvdLdg4urrE=; b=H5s0d+ag8L9JRbzF6Vp0M701BxQMPgHmGjTZaOhBTgcKgmQZQvKEDswGj2PNVxWlB6 d2VrOGyZLtAnsQrRohD3XnAaav7EhLVjVu5Jd7W9zpwx7zOHIQ/s3IhLqL+fg9Is0j6a UyhL6bpRxShvaDkuG4OIEFyYgk0YSy9QrSPIQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=C+VTBEvuP5+bySeD1KeZFeONqeliN2LIeHCHP/429oreXaWBHSVyAWCovNF069bJFo PaFxaRE2XLkAcAhj4j/8cvTj0w54mwLP3cRZLddWc2yCH7JH+dFySQFIbciY/QJ3hkoW jAr/AV4ZBMDGZfL7yx5/pIhbCCqe0+x4XZHAM= MIME-Version: 1.0 Received: by 10.216.87.81 with SMTP id x59mr571538wee.147.1262931691848; Thu, 07 Jan 2010 22:21:31 -0800 (PST) In-Reply-To: References: Date: Thu, 7 Jan 2010 23:21:31 -0700 Message-ID: Subject: Re: Massive table (500M rows) update nightmare From: Scott Marlowe To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.556 tagged_above=-10 required=5 tests=AWL=-0.816, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201001/139 X-Sequence-Number: 37019 On Thu, Jan 7, 2010 at 11:14 PM, Carlo Stonebanks wrote: >> It might well be checkpoints. =A0Have you tried cranking up checkpoint >> segments to something like 100 or more and seeing how it behaves then? > > No I haven't, althugh it certainly make sense - watching the process run, > you get this sense that the system occaisionally pauses to take a deep, l= ong > breath before returning to work frantically ;D > > Checkpoint_segments are currently set to 64. The DB is large and is on a > constant state of receiving single-row updates as multiple ETL and > refinement processes run continuously. > > Would you expect going to 100 or more to make an appreciable difference, = or > should I be more aggressive? If you're already at 64 then not much. Probably wouldn't hurt to crank it up more and delay the checkpoints as much as possible during these updates. 64 segments is already 1024M. If you're changing a lot more data than that in a single update / insert then cranking them up more might help. What you might need to do is to change your completion target to something closer to 100% since it's likely that most of the updates / inserts are not happening to the same rows over and over, but to different rows for each one, the closer you can get to 100% completed before the next checkpoint the better. This will cause some more IO to happen, but will even it out more (hopefully) so that you don't get checkpoint spikes. Look into the checkpoint logging options so you can monitor how they're affecting system performance.