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 D44AFD1B297 for ; Tue, 29 Jun 2004 12:09:52 -0300 (ADT) 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 18772-09 for ; Tue, 29 Jun 2004 15:09:49 +0000 (GMT) Received: from mx.mall.cz (mx.mall.cz [81.30.232.20]) by svr1.postgresql.org (Postfix) with ESMTP id D462AD1B266 for ; Tue, 29 Jun 2004 12:09:47 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mx.mall.cz (Postfix) with ESMTP id 6119B48477; Tue, 29 Jun 2004 17:09:45 +0200 (CEST) Received: from mx.mall.cz ([127.0.0.1]) by localhost (posta [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06400-03; Tue, 29 Jun 2004 17:09:40 +0200 (CEST) Message-ID: <40E1863B.9070208@taborsky.cz> Date: Tue, 29 Jun 2004 17:09:47 +0200 From: Michal Taborsky User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Slow INSERT References: <40E160E6.4080105@taborsky.cz> <29095.1088519446@sss.pgh.pa.us> In-Reply-To: <29095.1088519446@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/295 X-Sequence-Number: 7353 Tom Lane wrote: > It's hard to see how inserting to such a simple table would be slow. Indeed. > Is the number of inserts between slowdowns perfectly repeatable? My > first thought is that the fast case is associated with inserting onto a > page that is the same one last inserted to, and the slow case is > associated with finding a new page to insert onto (which, given that you > never UPDATE or DELETE, will always mean extending the file). Given > that the table rows are fixed width, the number of rows that fit on a > page should be constant, so this theory cannot be right if the number of > inserts between slowdowns varies. I ran some tests to support this hypothesis. Every 500th insert is a tad slower, but it is insignificant (normally the INSERT lasts 1.5ms, every 500th is 9ms). During my tests (10 runs of 1000 INSERTS) I had experienced only one "slow" insert (2000ms). It is clearly caused by other processes running on this server, but such degradation of performance is highly suspicious, because the server very rarely goes over load 1.0. Just for the record, it is FreeBSD 4.9 and the system never swaps. > Also, are all the inserts being issued by the same server process, or > are they scattered across multiple processes? I'm not sure this theory > holds water unless all the inserts are done in the same process. Nope. It is a webserver, so these requests are pushed through several persistent connections (20-30, depends on current load). This insert occurs only once per pageload. -- Michal Taborsky http://www.taborsky.cz