X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9D3A99DD6F5 for ; Thu, 1 Dec 2005 23:10:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46352-02 for ; Thu, 1 Dec 2005 23:10:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 83F299DD702 for ; Thu, 1 Dec 2005 23:10:05 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jB23A6TM021103; Thu, 1 Dec 2005 22:10:06 -0500 (EST) To: "Luke Lonergan" cc: "Rick Schumeyer" , pgsql-performance@postgresql.org Subject: Re: COPY into table too slow with index: now an I/O In-reply-to: References: Comments: In-reply-to "Luke Lonergan" message dated "Thu, 01 Dec 2005 18:26:42 -0800" Date: Thu, 01 Dec 2005 22:10:06 -0500 Message-ID: <21102.1133493006@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/48 X-Sequence-Number: 15869 "Luke Lonergan" writes: > The problem I see is nicely shown by the increase in IOWAIT between the two > patterns (with and without index). It seems likely that the pattern is: > A - insert a tuple into the table > B - insert an entry into the index > C - fsync the WAL > - repeat > This can be as bad as having a disk seek to access the table data every time > the 8KB page boundary is crossed, then again for the index, then again for > the WAL, and random disk seeks happen only as fast as about 10ms, so you can > only do those at a rate of 100/s. That analysis is far too simplistic, because only the WAL write has to happen before the transaction can commit. The table and index writes will normally happen at some later point in the bgwriter, and with any luck there will only need to be one write per page, not per tuple. It is true that having WAL and data on the same spindle is bad news, because the disk head has to divide its time between synchronous WAL writes and asynchronous writes of the rest of the files. regards, tom lane