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 396345370B for ; Tue, 5 Apr 2005 03:55:03 +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 73199-02 for ; Tue, 5 Apr 2005 02:54:53 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.202]) by svr1.postgresql.org (Postfix) with ESMTP id D470653707 for ; Tue, 5 Apr 2005 03:54:51 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so1366815rng for ; Mon, 04 Apr 2005 19:54:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=TtX9qrctbVzf4oz5avNHU9VMqADG0MQDk2U8zcXPAGq74SbNV/Pzr/J1+96DCPlDKzMgCZEyNrlHxxofnqPSmxxkLzIQHR68HEYE4+WGDKnzPBxn4Fn6zQ6Yv4HAtZUex6okKw0tpajCGQDTAoLLAbQ0T55o8yStKluvG4Cra0U= Received: by 10.38.69.34 with SMTP id r34mr312039rna; Mon, 04 Apr 2005 19:54:57 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 4 Apr 2005 19:54:57 -0700 (PDT) Message-ID: <59d991c4050404195443fba84c@mail.gmail.com> Date: Mon, 4 Apr 2005 22:54:57 -0400 From: Christopher Petrilli Reply-To: Christopher Petrilli To: Tom Lane Subject: Re: Sustained inserts per sec ... ? Cc: jd@commandprompt.com, "Marc G. Fournier" , pgsql-performance@postgresql.org In-Reply-To: <5217.1112668589@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <20050401140421.P18194@ganymede.hub.org> <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> <5217.1112668589@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.313 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/76 X-Sequence-Number: 11473 On Apr 4, 2005 10:36 PM, Tom Lane wrote: > 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.) This seemsed to be my original idea, but I wanted to eliminate everything else as much as possible. I was also concerned that I might be hitting a bad case in the trees. I had to change some UID generation code to better hash, so... > 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. Right, I expected O(log N) behavior myself, and it seems to behave that way, if you look at the first section (although there's some interesting patterns that are visible if you exclude data outside the 90th percentile in the first section, that seems to coincide with some write activity. > 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. Well, I can track this on a run, if it would be useful, but I think you're right as it matches what I saw from looking at iostat at those points. > The indicated fix of course is to increase shared_buffers. Any idea where it should be set? Chris -- | Christopher Petrilli | petrilli@gmail.com