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 A14EF53764; Tue, 5 Apr 2005 04:46: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 89680-04; Tue, 5 Apr 2005 03:46:02 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id 165D95375C; Tue, 5 Apr 2005 04:46:01 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DIf0e-0003Js-00; Mon, 04 Apr 2005 23:45:48 -0400 To: Tom Lane Cc: Christopher Petrilli , jd@commandprompt.com, "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: Sustained inserts per sec ... ? 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> <5217.1112668589@sss.pgh.pa.us> In-Reply-To: <5217.1112668589@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 Apr 2005 23:45:47 -0400 Message-ID: <87y8bxophw.fsf@stark.xeocode.com> Lines: 35 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/79 X-Sequence-Number: 11476 Tom Lane writes: > 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. All this is happening within a single transaction too, right? So there hasn't been an fsync the entire time. It's entirely up to the kernel when to decide to start writing data. It's possible it's just buffering all the writes in memory until the amount of free buffers drops below some threshold then it suddenly starts writing out buffers. > 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. I think he's right, if you see a reasonable write volume before the performance drop followed by a sudden increase in read volume (and decrease of write volume proportionate to the drop in performance) then it's just shared buffers becoming a bottleneck. If there's hardly any write volume before, then a sudden increase in write volume despite a drop in performance then I might be right. In which case you might want to look into tools to tune your kernel vm system. -- greg