From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergio Gabriel Rodriguez <sgrodriguez@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: problems with large objects dump
Date: Fri, 12 Oct 2012 21:31:54 -0400
Message-ID: <27767.1350091914@sss.pgh.pa.us> (raw)
In-Reply-To: <25141.1350083923@sss.pgh.pa.us>
References: <CAMHBdEoS3b7XZ2JWWG+Y71MGpnDJFLM3U0WnFMxvhOe=Oq2q_w@mail.gmail.com>
<16863.1348151708@sss.pgh.pa.us>
<CAMHBdEroekZuQZdqXyX5m=_Z6L-+-k_jWvnEi5JbkS_iy_LQ+w@mail.gmail.com>
<CAMHBdEqPt+g+aAUVfE4zV01UOLPsBuU2kpJ2jJHWs2Lr_8x1yg@mail.gmail.com>
<1269.1349993793@sss.pgh.pa.us>
<CAMHBdEoCpHfanPE_S2Lrc9KjqxvZ-N_KBnOLKn2h3XmY6Rh49A@mail.gmail.com>
<25141.1350083923@sss.pgh.pa.us>
I wrote:
> Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> writes:
>> I never use oprofile, but for a few hours into the process, I could take
>> this report:
>> 1202449 56.5535 sortDumpableObjects
> Hm. I suspect a lot of that has to do with the large objects; and it's
> really overkill to treat them as full-fledged objects since they never
> have unique dependencies. This wasn't a problem when commit
> c0d5be5d6a736d2ee8141e920bc3de8e001bf6d9 went in, but I think now it
> might be because of the additional constraints added in commit
> a1ef01fe163b304760088e3e30eb22036910a495. I wonder if it's time to try
> to optimize pg_dump's handling of blobs a bit better. But still, any
> such fix probably wouldn't make a huge difference for you. Most of the
> time is going into pushing the blob data around, I think.
For fun, I tried adding 5 million empty blobs to the standard regression
database, and then did a pg_dump. It took a bit under 9 minutes on my
workstation. oprofile showed about 32% of pg_dump's runtime going into
sortDumpableObjects, which might make you think that's worth optimizing
... until you look at the bigger picture system-wide:
samples| %|
------------------
727394 59.4098 kernel
264874 21.6336 postgres
136734 11.1677 /lib64/libc-2.14.90.so
39878 3.2570 pg_dump
37025 3.0240 libpq.so.5.6
17964 1.4672 /usr/bin/wc
354 0.0289 /usr/bin/oprofiled
So actually sortDumpableObjects took only about 1% of the CPU cycles.
And remember this is with empty objects. If we'd been shoving 200GB of
data through the dump, the data pipeline would surely have swamped all
else.
So I think the original assumption that we didn't need to optimize
pg_dump's object management infrastructure for blobs still holds good.
If there's anything that is worth fixing here, it's the number of server
roundtrips being used ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sgrodriguez@gmail.com
Subject: Re: problems with large objects dump
In-Reply-To: <27767.1350091914@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox