Re: Sustained inserts per sec ... ?

On Apr 4, 2005 4:11 PM, Simon Riggs <simon(at)2ndquadrant(dot)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(at)bigbird base]# cd 26488263/
[root(at)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(at)gmail(dot)com