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 87460537BE for ; Mon, 4 Apr 2005 20:57:02 +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 41799-03 for ; Mon, 4 Apr 2005 19:56:58 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.199]) by svr1.postgresql.org (Postfix) with ESMTP id 9F46F537C9 for ; Mon, 4 Apr 2005 20:56:57 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so1301802rng for ; Mon, 04 Apr 2005 12:56:59 -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=j78bxjrCUIsDVRWkv7QdASbcXRglJQj7dNhhZqZcpVgSm45rhzZQNykjBV5sG84bi80BstkCFz3MpG1UiB2lz4DsLJDbBGByFkSxS5HUbEczhh4ZFYoqCu0GjR2D36qxO94K4rJP4oIu9dZpAia3ZouW7O5JWLN/M3Dczz9AVvs= Received: by 10.38.161.30 with SMTP id j30mr834844rne; Mon, 04 Apr 2005 12:56:59 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 4 Apr 2005 12:56:58 -0700 (PDT) Message-ID: <59d991c4050404125611ad5af8@mail.gmail.com> Date: Mon, 4 Apr 2005 15:56:58 -0400 From: Christopher Petrilli Reply-To: Christopher Petrilli To: Simon Riggs Subject: Re: Sustained inserts per sec ... ? Cc: jd@commandprompt.com, Tom Lane , "Marc G. Fournier" , pgsql-performance@postgresql.org In-Reply-To: <1112644015.16721.790.camel@localhost.localdomain> 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> <1112644015.16721.790.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.291 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/63 X-Sequence-Number: 11460 On Apr 4, 2005 3:46 PM, Simon Riggs wrote: > On Mon, 2005-04-04 at 09:48 -0400, Christopher Petrilli wrote: > > The point, in the rough middle, is where the program begins inserting > > into a new table (inherited). The X axis is the "total" number of rows > > inserted. > > and you also mention the same data plotted with elapsed time: > http://www.amber.org/~petrilli/diagrams/pgsql_copyperf_timeline.png > > Your graphs look identical to others I've seen, so I think we're > touching on something wider than your specific situation. The big > difference is that things seem to go back to high performance when you > switch to a new inherited table. This is correct. > 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. > > 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 Note that 10M is when it swaps. I see no reason to interpret it differently, so it seems to be totally based around switching tables (and thereby indices). > Q: How many data files are there for these relations? Wouldn't be two, > by any chance, when we have 10 million rows in them? I allow PostgreSQL to manage all the data files itself, so here's the default tablespace: total 48 drwx------ 2 pgsql pgsql 4096 Jan 26 20:59 1 drwx------ 2 pgsql pgsql 4096 Dec 17 19:15 17229 drwx------ 2 pgsql pgsql 4096 Feb 16 17:55 26385357 drwx------ 2 pgsql pgsql 4096 Mar 24 23:56 26425059 drwx------ 2 pgsql pgsql 8192 Mar 28 11:31 26459063 drwx------ 2 pgsql pgsql 8192 Mar 31 23:54 26475755 drwx------ 2 pgsql pgsql 4096 Apr 4 15:07 26488263 [root@bigbird base]# du 16624 ./26425059 5028 ./26385357 5660 ./26459063 4636 ./17229 6796 ./26475755 4780 ./1 1862428 ./26488263 1905952 . > Q: What is the average row length? > About 150-160 bytes? Raw data is around 150bytes, after insertion, I'd need to do some other calculations. Chris -- | Christopher Petrilli | petrilli@gmail.com