Re: Sustained inserts per sec ... ? - Mailing list pgsql-performance
| From | Christopher Petrilli |
|---|---|
| Subject | Re: Sustained inserts per sec ... ? |
| Date | |
| Msg-id | 59d991c405040413184699cfe1@mail.gmail.com Whole thread Raw |
| In response to | Re: Sustained inserts per sec ... ? (Simon Riggs <simon@2ndquadrant.com>) |
| Responses |
Re: Sustained inserts per sec ... ?
Re: Sustained inserts per sec ... ? |
| List | pgsql-performance |
On Apr 4, 2005 4:11 PM, Simon Riggs <simon@2ndquadrant.com> 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
pgsql-performance by date: