Skip site navigation (1) Skip section navigation (2)

Re: bytea vs. pg_dump

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Subject: Re: bytea vs. pg_dump
Date: 2009-07-21 20:20:24
Message-ID: 8FE72D48B69574341712FD55@teje (view raw or flat)
Thread:
Lists: pgsql-hackers
--On Samstag, Juli 11, 2009 13:40:44 +0300 Peter Eisentraut 
<peter_e(at)gmx(dot)net> wrote:

> OK, here is an updated patch.  It has the setting as enum, completed
> documentation, and libpq support.  I'll add it to the commit fest in the
> hope  that someone else can look it over in detail.

I've started looking at this and did some profiling with large bytea data 
again. For those interested, here are the numbers:

Dumping with bytea_output=hex (COPY to file):

real    20m38.699s
user    0m11.265s
sys     1m0.560s

Dumping with bytea_output=escape (COPY to file):

real    39m52.399s
user    0m22.085s
sys     1m50.131s

So the time needed dropped about 50%. The dump file dropped from around 48 
GB to 28 GB with the new format. I have some profiler data for this, but 
the restore seems much more interesting: the time to restore for both 
formats is quite the same:

Restore bytea_output=hex

real    32m11.028s
user    0m0.000s
sys     0m0.008s

Restore bytea_output=escape

real    31m35.378s
user    0m0.000s
sys     0m0.000s

The profile for restoring the hex format looks like this:

  %   cumulative   self              self     total
 time   seconds   seconds    calls   s/call   s/call  name
 34.54    156.79   156.79    97836     0.00     0.00  pglz_compress
 18.64    241.38    84.59   141374     0.00     0.00  CopyReadLine
 12.83    299.62    58.24 3604740854     0.00     0.00  get_hex
  8.44    337.95    38.33 14257432     0.00     0.00  XLogInsert
  7.39    371.48    33.53   141373     0.00     0.00  hex_decode
  7.23    404.31    32.83        1    32.83   436.67  DoCopy
  3.48    420.12    15.81                             esc_enc_len
  0.61    422.89     2.77 134943749     0.00     0.00  _bt_compare
  0.54    425.36     2.47 33682172     0.00     0.00  ReadBuffer_common
  0.54    427.83     2.47 52166324     0.00     0.00 
hash_search_with_hash_value
  0.45    429.89     2.06 104798203     0.00     0.00  LWLockAcquire
  0.36    431.53     1.64 105234314     0.00     0.00  LWLockRelease

I've attached a slightly edited patch which fixes a compiler warning in 
encode.c, too.

-- 
  Thanks

                    Bernd

Attachment: bytea-format_review1.patch
Description: text/x-diff (17.6 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2009-07-21 20:25:19
Subject: Re: pg_listener attribute number #defines
Previous:From: Andreas WenkDate: 2009-07-21 20:12:46
Subject: Re: psql \du and \dg is the same - pg 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group