Re: bytea vs. pg_dump - Mailing list pgsql-hackers

Bernd Helmle wrote:
> --On Mittwoch, Mai 06, 2009 19:04:21 -0400 Tom Lane <tgl@sss.pgh.pa.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