Re: 8.4 open item: copy performance regression?

Tom Lane wrote:
> Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
>> The following copying 3M rows(each) into a seperate table of the same 
>> database.
> 
> Is this with WAL, or bypassing WAL?  Given what we've already seen,
> a lot of contention for WALInsertLock wouldn't surprise me much here.
> It should be possible to bypass that though.

this was with WAL. here are the numbers for bypass:

processes    total time(s)    rows/s    rows/s - per core

1    15    200000    200000
2    15.1    397350.99    198675.5
4    15.2    789473.68    197368.42
6    15.3    1176470.59    196078.43
8    16.2    1481481.48    185185.19
10    21.9    1369863.01    136986.3
12    22.7    1585903.08    132158.59
14    25.2    1666666.67    119047.62
16    27.9    1720430.11    107526.88


runtimes grew very short here but the numbers still seem sane and if you 
compare them to what I did on real storage(though without the 16MB 
ringbuffer fix!):

http://www.kaltenbrunner.cc/blog/index.php?/archives/27-Benchmarking-8.4-Chapter-2bulk-loading.html

you will see that for a single core there is almost no performance 
difference between ramdisk and real disk, at 8 cores there is the 
largest gap at around 45% but on 16 cores we are down to a mere 20% 
difference.
All in all it seems that we have a big locking issue with WALInsertLock 
and even with that removed we are mostly CPU limited and not IO limited 
for COPY.



Stefan