Re: CopyReadLineText optimization

Heikki Linnakangas wrote:
> I still need to test the worst-case performance, with input that has a
> lot of escapes.

Ok, I've done some more performance testing with this. I tested COPY
FROM with a table with a single "text" column. There was a million rows
in the table, with a 1000 character long string:

postgres=# CREATE TABLE narrowtable2 (id text);
CREATE TABLE
postgres=# INSERT INTO narrowtable2 SELECT repeat(E'\\', 1000) FROM
generate_series(1, 1000000);
INSERT 0 1000000

After that, I dumped that to a file, and loaded it back using COPY FROM:

time ~/installations/cvshead/bin/psql postgres -c "BEGIN; TRUNCATE
narrowtable2; COPY narrowtable2 FROM
'/home/perftester/narrowtable3.tbl'; ROLLBACK;"

I repeated the test with different frequencies of backslashes in the
string, with and without the patch, and the took the smallest number of
each test case:

backslashes with without patch
all 24.9 15.6
every 4th 12.7 11.4
every 8th 10.4 10.7
every 16th 8.7 10.3
none 6.8 9.8

So the overhead of using memchr slows us down if there's a lot of escape
or quote characters. The breakeven point seems to be about 1 in 8
characters. I'm not sure if that's a good tradeoff or not...

I also tested a table with single integer column, and found no
meaningful difference (10.5 without patch vs 10.6 with patch). oprofile
shows that in this test case, only ~5% of the CPU time is spent in
CopyReadLineText, and the patch doesn't change that.

Without patch:
samples % image name app name
symbol name
7563 12.7220 no-vmlinux postgres (no
symbols)
4050 6.8127 postgres postgres DoCopy
3334 5.6083 postgres postgres
LWLockAcquire
3238 5.4468 postgres postgres
CopyReadLine
2900 4.8782 postgres postgres
LWLockRelease
2781 4.6780 libc-2.7.so postgres
__GI_____strtoll_l_internal
2778 4.6730 postgres postgres
heap_formtuple
2636 4.4341 postgres postgres hash_any
2087 3.5106 no-vmlinux no-vmlinux (no
symbols)
1748 2.9404 libc-2.7.so postgres memset
1724 2.9000 postgres postgres
PinBuffer
1670 2.8092 postgres postgres
PageAddItem
1645 2.7671 postgres postgres
heap_insert
1459 2.4542 postgres postgres
UnpinBuffer
1457 2.4509 postgres postgres
ReadBuffer_common
1321 2.2221 postgres postgres
hash_search_with_hash_value
1278 2.1498 postgres postgres
MarkBufferDirty
1219 2.0505 oprofiled oprofiled (no
symbols)
972 1.6350 postgres postgres
pg_verify_mbstr_len
756 1.2717 postgres postgres
RelationPutHeapTuple
665 1.1186 postgres postgres pg_atoi
631 1.0614 postgres postgres
RelationGetBufferForTuple
613 1.0312 postgres postgres
AllocSetReset
...

With patch:
samples % image name app name
symbol name
42720 18.1450 no-vmlinux postgres (no
symbols)
15367 6.5270 postgres postgres DoCopy
11831 5.0251 postgres postgres
LWLockAcquire
11500 4.8845 no-vmlinux no-vmlinux (no
symbols)
10182 4.3247 postgres postgres
LWLockRelease
9912 4.2100 libc-2.7.so postgres
__GI_____strtoll_l_internal
9811 4.1671 postgres postgres hash_any
8824 3.7479 postgres postgres
heap_formtuple
7459 3.1682 postgres postgres
CopyReadLine
7187 3.0526 postgres postgres
PageAddItem
6313 2.6814 libc-2.7.so postgres memset
5842 2.4813 postgres postgres
PinBuffer
5230 2.2214 postgres postgres
UnpinBuffer
5160 2.1917 postgres postgres
heap_insert
4838 2.0549 postgres postgres
ReadBuffer_common
4819 2.0468 postgres postgres
hash_search_with_hash_value
4691 1.9925 postgres postgres
MarkBufferDirty
3675 1.5609 libc-2.7.so postgres memchr
3617 1.5363 postgres postgres
AllocSetAlloc
3585 1.5227 postgres postgres
pg_verify_mbstr_len
3326 1.4127 postgres postgres
AllocSetReset
...

These tests were on a test server with a dual-core 64-bit Intel Xeons.
I'd still like to hear reports from other platforms.

Another thing that seems like an obvious win is to merge CopyReadLine
and CopyReadAttributesText/CSV so that we do just one pass over the
input. But that seems suspiciously obvious, I wonder if I'm missing
something.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com