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 C000353425 for ; Mon, 4 Apr 2005 17:51:20 +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 87445-07 for ; Mon, 4 Apr 2005 16:51:18 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.198]) by svr1.postgresql.org (Postfix) with ESMTP id 61EBA5341F for ; Mon, 4 Apr 2005 17:51:17 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so1253381rng for ; Mon, 04 Apr 2005 09:51:18 -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=hjelBWIadqy5DoqyWufv8UTv8dGSlvk9+XE2/SQMwd7dkjxrQf1BLTBdPwRoTqEmoCLjZhy/1YVEujXxgFl9uhd78cIc96rpkZpOiBWH5stwQS7DeumeUvA6vXKh9/+RzgwUMm3HyEFHxjI0KeV0EKWimRmBTpUM80nFxvzxPE4= Received: by 10.38.160.52 with SMTP id i52mr5389329rne; Mon, 04 Apr 2005 09:51:17 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 4 Apr 2005 09:51:17 -0700 (PDT) Message-ID: <59d991c4050404095114ff953c@mail.gmail.com> Date: Mon, 4 Apr 2005 12:51:17 -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: <26669.1112631807@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> <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> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.286 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/57 X-Sequence-Number: 11454 On Apr 4, 2005 12:23 PM, Tom Lane wrote: > Christopher Petrilli writes: > > On Apr 4, 2005 11:52 AM, Tom Lane wrote: > >> Could we see the *exact* SQL definitions of the table and indexes? > > > Of course, this is a bit cleansed, since it's an internal project, but > > only the column names are changed: > > Thanks. No smoking gun in sight there. But out of curiosity, can you > 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. Running now, but it'll take a while since I have a 3/4 second pause after each COPY to better reflect "real world" ... the application does 1 COPY per second, or whenever it hits 1000 entries. This seemed to be a sane way to deal with it, and not burden the system with needless index balancing, etc. > Also, the X-axis on your graphs seems to be total number of rows > inserted ... can you relate that to elapsed real time for us? Sure, like I said, there's a 3/4 second sleep between each COPY, regardless of how long it took (which well, isn't quite right, but close enough for this test). I've created a PNG with the X axies reflecting "elapsed time": http://www.amber.org/~petrilli/diagrams/pgsql_copyperf_timeline.png In addition, I've put up the raw data I used: http://www.amber.org/~petrilli/diagrams/results_timeline.txt The columns are rowcount, elapsed time, instance time. Hopefully this might help some? This machine has nothing else running on it other than the normal stripped down background processes (like sshd). -- | Christopher Petrilli | petrilli@gmail.com