Re: bytea vs. pg_dump

Bernd Helmle wrote:
> --On Mittwoch, Mai 06, 2009 19:04:21 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>
>> So I'm now persuaded that a better textual representation for bytea
>> should indeed make things noticeably better here. It would be
>> useful though to cross-check this thought by profiling a case that
>> dumps a comparable volume of text data that contains no backslashes...
>
> This is a profiling result of the same data converted into a printable
> text format without any backslashes. The data amount is quite the same
> and as you already guessed, calls to appendBinaryStringInfo() and
> friends gives the expected numbers:
>
>
> time seconds seconds calls s/call s/call name
> 35.13 24.67 24.67 134488 0.00 0.00 byteaout
> 32.61 47.57 22.90 134488 0.00 0.00 CopyOneRowTo
> 28.92 67.88 20.31 85967 0.00 0.00 pglz_decompress
> 0.67 68.35 0.47 4955300 0.00 0.00
> hash_search_with_hash_value
> 0.28 68.55 0.20 11643046 0.00 0.00 LWLockRelease
> 0.28 68.75 0.20 4828896 0.00 0.00 index_getnext
> 0.24 68.92 0.17 1208577 0.00 0.00 StrategyGetBuffer
> 0.23 69.08 0.16 11643046 0.00 0.00 LWLockAcquire
> ...
> 0.00 70.23 0.00 134498 0.00 0.00 enlargeStringInfo
> 0.00 70.23 0.00 134497 0.00 0.00 appendBinaryStringInfo
> 0.00 70.23 0.00 134490 0.00 0.00 AllocSetReset
> 0.00 70.23 0.00 134490 0.00 0.00 resetStringInfo
> 0.00 70.23 0.00 134488 0.00 0.00 CopySendChar
> 0.00 70.23 0.00 134488 0.00 0.00 CopySendEndOfRow

while doing some pg_migrator testing I noticed that dumping a database
seems to be much slower than IO-system is capable off. ie i get 100% CPU
usage with no IO-wait at all with between 15-30MB/s read rate if i say
do a pg_dumpall > /dev/null.

The profile for that looks like:

samples % image name symbol name
1333764 29.3986 postgres CopyOneRowTo
463205 10.2099 postgres enlargeStringInfo
237117 5.2265 postgres AllocSetAlloc
231017 5.0920 postgres appendBinaryStringInfo
224792 4.9548 postgres heap_deform_tuple
172154 3.7946 postgres AllocSetReset
162434 3.5803 postgres DoCopyTo
149948 3.3051 postgres internal_putbytes
137548 3.0318 postgres OutputFunctionCall
129480 2.8540 postgres heapgettup_pagemode
101017 2.2266 postgres FunctionCall1
93584 2.0628 postgres pq_putmessage
86553 1.9078 postgres timesub
81400 1.7942 postgres CopySendChar
81230 1.7905 postgres int4out
78374 1.7275 postgres localsub
52003 1.1462 postgres MemoryContextAlloc
51265 1.1300 postgres CopySendEndOfRow
49849 1.0988 postgres SPI_push_conditional
48157 1.0615 postgres pg_server_to_client
47670 1.0507 postgres timestamptz_out
42762 0.9426 postgres timestamp2tm

Stefan