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 7A0DE5344A for ; Mon, 4 Apr 2005 21:18:49 +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 47436-09 for ; Mon, 4 Apr 2005 20:18:42 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.200]) by svr1.postgresql.org (Postfix) with ESMTP id B8A01537E0 for ; Mon, 4 Apr 2005 21:18:40 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so1307304rng for ; Mon, 04 Apr 2005 13:18:43 -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=cFxdZTh/lzh40YR42etErxiuinjYqX29z6m2DT17/I+eOqWrYZ2HRaQgRp6RJfgeGXjasOYd3Y49NvV7VZkKeFRm39d98iunGc15HkPCJQBxmONBhSp3FShq3Xx14xWxa/yUOoFyGd02OfL93YKkdc2ssp5hdSLSbQgnlwHd1MM= Received: by 10.38.86.68 with SMTP id j68mr5685934rnb; Mon, 04 Apr 2005 13:18:42 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 4 Apr 2005 13:18:42 -0700 (PDT) Message-ID: <59d991c405040413184699cfe1@mail.gmail.com> Date: Mon, 4 Apr 2005 16:18:42 -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: <1112645479.16721.806.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> <59d991c4050404125611ad5af8@mail.gmail.com> <1112645479.16721.806.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.299 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/65 X-Sequence-Number: 11462 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 > > > > 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). > > OK, but do you have some other external knowledge that it is definitely > happening at that time? Your argument above seems slightly circular to > me. My program *SPECIFICALLY* counts to 10M then switches the COPY statement. > This is really important because we need to know whether it ties in with > that event, or some other. Unless basic integer math is failing, it's definately happening at 10M rows. > Have you run this for more than 2 files, say 3 or more? You mean, 3 or more tables? I'm not sure which type of files you are reffering to here. > You COMMIT after each 500 rows? This is done using COPY syntax, not INSERT syntax. So I suppose "yes" I do. The file that is being used for COPY is kept on a ramdisk. > OK. Please... > cd $PGDATA/base/26488263 > ls -l [root@bigbird base]# cd 26488263/ [root@bigbird 26488263]# ls -l total 2003740 -rw------- 1 pgsql pgsql 49152 Apr 4 12:26 1247 -rw------- 1 pgsql pgsql 245760 Apr 4 12:27 1249 -rw------- 1 pgsql pgsql 573440 Apr 4 12:24 1255 -rw------- 1 pgsql pgsql 57344 Apr 4 14:44 1259 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16384 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 16386 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 16388 -rw------- 1 pgsql pgsql 24576 Apr 4 12:29 16390 -rw------- 1 pgsql pgsql 106496 Apr 4 12:24 16392 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16394 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16396 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16398 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16400 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16402 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16404 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16406 -rw------- 1 pgsql pgsql 212992 Apr 4 14:44 16408 -rw------- 1 pgsql pgsql 49152 Apr 4 12:24 16410 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16412 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16414 -rw------- 1 pgsql pgsql 114688 Apr 4 12:24 16416 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16418 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16672 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16674 -rw------- 1 pgsql pgsql 237568 Apr 4 12:26 16676 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16678 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16679 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16680 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16681 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16682 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16683 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16684 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16685 -rw------- 1 pgsql pgsql 245760 Apr 4 12:26 16686 -rw------- 1 pgsql pgsql 73728 Apr 4 12:26 16687 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16688 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16689 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16690 -rw------- 1 pgsql pgsql 65536 Apr 4 12:26 16691 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16692 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16693 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16694 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16695 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16696 -rw------- 1 pgsql pgsql 32768 Apr 4 12:24 16697 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16698 -rw------- 1 pgsql pgsql 163840 Apr 4 12:26 16701 -rw------- 1 pgsql pgsql 196608 Apr 4 12:26 16702 -rw------- 1 pgsql pgsql 73728 Apr 4 12:24 16703 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16706 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16707 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16708 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16709 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16710 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16711 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16712 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16713 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16714 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16715 -rw------- 1 pgsql pgsql 32768 Apr 4 12:24 16716 -rw------- 1 pgsql pgsql 106496 Apr 4 12:24 16717 -rw------- 1 pgsql pgsql 106496 Apr 4 12:24 16718 -rw------- 1 pgsql pgsql 1212416 Apr 4 12:24 16719 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16720 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16721 -rw------- 1 pgsql pgsql 40960 Apr 4 14:44 16724 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16727 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16728 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16729 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16730 -rw------- 1 pgsql pgsql 16384 Apr 4 12:26 16731 -rw------- 1 pgsql pgsql 49152 Apr 4 12:26 16732 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16735 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16737 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16738 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16740 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16744 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16746 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16750 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16752 -rw------- 1 pgsql pgsql 122880 Apr 4 12:24 16753 -rw------- 1 pgsql pgsql 16384 Apr 4 12:24 16755 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 16759 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 16761 -rw------- 1 pgsql pgsql 40960 Apr 4 12:24 17158 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17160 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17162 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17163 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17165 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17167 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17168 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17170 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17172 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17173 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17175 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17177 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17178 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17180 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17182 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17183 -rw------- 1 pgsql pgsql 0 Apr 4 12:24 17185 -rw------- 1 pgsql pgsql 8192 Apr 4 12:24 17187 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488264 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488266 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488268 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488269 -rw------- 1 pgsql pgsql 1073741824 Apr 4 15:07 26488271 -rw------- 1 pgsql pgsql 407527424 Apr 4 16:17 26488271.1 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488273 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488275 -rw------- 1 pgsql pgsql 565067776 Apr 4 16:17 26488276 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488278 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488280 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488282 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488283 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488285 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488287 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488289 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488290 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488292 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488294 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488296 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488297 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488299 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488301 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488303 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488304 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488306 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488308 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488310 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488311 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488313 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488315 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488317 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488318 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488320 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488322 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488324 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488325 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488327 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488329 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488331 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488332 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488334 -rw------- 1 pgsql pgsql 0 Apr 4 12:26 26488336 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488338 -rw------- 1 pgsql pgsql 8192 Apr 4 12:26 26488339 -rw------- 1 pgsql pgsql 60045 Apr 4 12:24 pg_internal.init -rw------- 1 pgsql pgsql 4 Apr 4 12:24 PG_VERSION Hopefully this helps. Chris -- | Christopher Petrilli | petrilli@gmail.com