Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E06126334DD for ; Sat, 9 Jan 2010 04:21:33 -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 26610-09 for ; Sat, 9 Jan 2010 08:21:19 +0000 (UTC) X-Greylist: delayed 00:06:39.506421 by SQLgrey-1.7.6 Received: from 63.mail-out.ovh.net (63.mail-out.ovh.net [91.121.185.56]) by mail.postgresql.org (Postfix) with SMTP id 7047C6333FE for ; Sat, 9 Jan 2010 04:21:22 -0400 (AST) Received: (qmail 26393 invoked by uid 503); 9 Jan 2010 08:15:11 -0000 Received: from b6.ovh.net (HELO mail609.ha.ovh.net) (213.186.33.56) by 63.mail-out.ovh.net with SMTP; 9 Jan 2010 08:15:11 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 9 Jan 2010 08:14:40 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO soyouz) (88.161.102.87) by ns0.ovh.net with SMTP; 9 Jan 2010 08:14:39 -0000 Date: Sat, 09 Jan 2010 09:15:11 +0100 To: "Carlo Stonebanks" , pgsql-performance@postgresql.org Subject: Re: Massive table (500M rows) update nightmare From: =?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?= Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: Content-Transfer-Encoding: 7bit Message-ID: In-Reply-To: User-Agent: Opera Mail/9.62 (Linux) X-Ovh-Tracer-Id: 4246612974907688523 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.528 tagged_above=-10 required=5 tests=AWL=0.071, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/156 X-Sequence-Number: 37036 >> crank it up more and delay the checkpoints as much as possible during >> these updates. 64 segments is already 1024M. > > We have 425M rows, total table size is 78GB, so we can imagine a worst > case UPDATE write is less than 200 bytes * number of rows specified in > the update (is that logic correct?). There is also the WAL : all these updates need to be logged, which doubles the UPDATE write throughput. Perhaps you're WAL-bound (every 16MB segment needs fsyncing), and tuning of fsync= and wal_buffers, or a faster WAL disk could help ? (I don't remember your config). > Inerestingly, the total index size is 148GB, twice that of the table, > which may be an indication of where the performance bottleneck is. Index updates can create random I/O (suppose you have a btree on a rather random column)...