X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 445F6533FF; Tue, 5 Apr 2005 03:36:32 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55929-02; Tue, 5 Apr 2005 02:36:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A6728536E1; Tue, 5 Apr 2005 03:36:28 +0100 (BST) 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 j352aTSC005218; Mon, 4 Apr 2005 22:36:29 -0400 (EDT) To: Christopher Petrilli Cc: jd@commandprompt.com, "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: Sustained inserts per sec ... ? In-reply-to: <59d991c40504041846bc39a6@mail.gmail.com> References: <20050401140421.P18194@ganymede.hub.org> <59d991c4050401113857086517@mail.gmail.com> <28353.1112388128@sss.pgh.pa.us> <59d991c4050401124638c4ab5d@mail.gmail.com> <1112388818.3487.34.camel@jd2.commandprompt.com> <59d991c4050401125956d6069d@mail.gmail.com> <59d991c40504040648457808e9@mail.gmail.com> <26454.1112629979@sss.pgh.pa.us> <59d991c405040409094e7bbd17@mail.gmail.com> <26669.1112631807@sss.pgh.pa.us> <59d991c40504041846bc39a6@mail.gmail.com> Comments: In-reply-to Christopher Petrilli message dated "Mon, 04 Apr 2005 21:46:17 -0400" Date: Mon, 04 Apr 2005 22:36:29 -0400 Message-ID: <5217.1112668589@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/75 X-Sequence-Number: 11472 Christopher Petrilli writes: > On Apr 4, 2005 12:23 PM, Tom Lane wrote: >> do a test run with *no* indexes on the table, just to see if it behaves >> any differently? Basically I was wondering if index overhead might be >> part of the problem. > http://www.amber.org/~petrilli/diagrams/pgsql_copy500_pkonly.png > I appologize, I forgot to kill the PK, but as you can see, the curve > flattened out a lot. It still begins to increase in what seems like > the same place. You can find the results themselves at: Yeah, this confirms the thought that the indexes are the source of the issue. (Which is what I'd expect, because a bare INSERT ought to be an approximately constant-time operation. But it's good to verify.) Now some amount of slowdown is to be expected as the indexes get larger, since it ought to take roughly O(log N) time to insert a new entry in an index of size N. The weird thing about your curves is the very sudden jump in the insert times. What I think might be happening is that the "working set" of pages touched during index inserts is gradually growing, and at some point it exceeds shared_buffers, and at that point performance goes in the toilet because we are suddenly doing lots of reads to pull in index pages that fell out of the shared buffer area. It would be interesting to watch the output of iostat or vmstat during this test run. If I'm correct about this, the I/O load should be basically all writes during the initial part of the test, and then suddenly develop a significant and increasing fraction of reads at the point where the slowdown occurs. The indicated fix of course is to increase shared_buffers. regards, tom lane