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 1EE975340C; Mon, 4 Apr 2005 22:00:21 +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 62487-08; Mon, 4 Apr 2005 21:00:19 +0000 (GMT) Received: from cmailg3.svr.pol.co.uk (cmailg3.svr.pol.co.uk [195.92.195.173]) by svr1.postgresql.org (Postfix) with ESMTP id 065EB53461; Mon, 4 Apr 2005 22:00:18 +0100 (BST) Received: from modem-3463.lion.dialup.pol.co.uk ([217.135.173.135] helo=192.168.0.102) by cmailg3.svr.pol.co.uk with esmtp (Exim 4.41) id 1DIYg9-0008Dj-QE; Mon, 04 Apr 2005 22:00:14 +0100 Subject: Re: Sustained inserts per sec ... ? From: Simon Riggs To: Christopher Petrilli Cc: jd@commandprompt.com, Tom Lane , "Marc G. Fournier" , pgsql-performance@postgresql.org In-Reply-To: <59d991c405040413184699cfe1@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> <1112644015.16721.790.camel@localhost.localdomain> <59d991c4050404125611ad5af8@mail.gmail.com> <1112645479.16721.806.camel@localhost.localdomain> <59d991c405040413184699cfe1@mail.gmail.com> Content-Type: text/plain Organization: 2nd Quadrant Date: Mon, 04 Apr 2005 21:58:23 +0100 Message-Id: <1112648303.16721.816.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/68 X-Sequence-Number: 11465 On Mon, 2005-04-04 at 16:18 -0400, Christopher Petrilli wrote: > On Apr 4, 2005 4:11 PM, Simon Riggs wrote: > > > > I'm very interested in the graphs of elapsed time for COPY 500 rows > > > > against rows inserted. The simplistic inference from those graphs are > > > > that if you only inserted 5 million rows into each table, rather than 10 > > > > million rows then everything would be much quicker. I hope this doesn't > > > > work, but could you try that to see if it works? I'd like to rule out a > > > > function of "number of rows" as an issue, or focus in on it depending > > > > upon the results. > > > > Any chance of running a multiple load of 4 million rows per table, > > leaving the test running for at least 3 tables worth (12+ M rows)? > > As soon as I get done running a test without indexes :-) > > > > > Q: Please can you confirm that the discontinuity on the graph at around > > > > 5000 elapsed seconds matches EXACTLY with the switch from one table to > > > > another? That is an important point. > > > > > > Well, the change over happens at 51593.395205 seconds :-) Here's two > > > lines from the results with row count and time added: > > > > > > 10000000 51584.9818912 8.41331386566 > > > 10000500 51593.395205 0.416964054108 > > > > My program *SPECIFICALLY* counts to 10M then switches the COPY statement. > > OK. Please... > > cd $PGDATA/base/26488263 > > ls -l > > [root@bigbird base]# cd 26488263/ > [root@bigbird 26488263]# ls -l > total 2003740 > -rw------- 1 pgsql pgsql 1073741824 Apr 4 15:07 26488271 > -rw------- 1 pgsql pgsql 407527424 Apr 4 16:17 26488271.1 Can you do: select relname from pg_class where relfilenode = 26488271 and confirm that the name is the table you've been loading... Couldn't see all your indexes... are they still there? Thanks, Best Regards, Simon Riggs