Thread: problems with large objects dump

Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> writes:
> Our production database, postgres 8.4 has an approximate size of 200 GB,
> most of the data are large objects (174 GB), until a few months ago we used
> pg_dump to perform backups, took about 3-4 hours to perform all the
> process. Some time ago the process became interminable, take one or two
> days to process, we noticed that the decay process considerably to startup
> backup of large object, so we had to opt for physical backups.

Hm ... there's been some recent work to reduce O(N^2) behaviors in
pg_dump when there are many objects to dump, but I'm not sure that's
relevant to your situation, because before 9.0 pg_dump didn't treat
blobs as full-fledged database objects.  You wouldn't happen to be
trying to use a 9.0 or later pg_dump would you?  Exactly what 8.4.x
release is this, anyway?

            regards, tom lane

Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> writes:
> On Thu, Sep 20, 2012 at 11:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You wouldn't happen to be
>> trying to use a 9.0 or later pg_dump would you?  Exactly what 8.4.x
>> release is this, anyway?

> Tom, thanks for replying, yes, we tried it with postgres postgres 9.1 and
> 9.2 and the behavior is exactly the same. The production version is 8.4.9

Well, I see three different fixes for O(N^2) pg_dump performance
problems in the 8.4.x change logs since 8.4.9, so you're a bit behind
the times there.  However, all of those fixes would have been in 9.2.0,
so if you saw no improvement with a 9.2.0 pg_dump then the problem is
something else.  Can you put together a test case for somebody else to
try, or try to locate the bottleneck yourself using oprofile or perf?

            regards, tom lane

Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> writes:
>     I tried with Postgresql 9.2 and the process used to take almost a day
> and a half, was significantly reduced to 6 hours, before failing even used
> to take four hours. My question now is, how long should it take the backup
> for a 200GB database with 80% of large objects?

It's pretty hard to say without knowing a lot more info about your system
than you provided.  One thing that would shed some light is if you spent
some time finding out where the time is going --- is the system
constantly I/O busy, or is it CPU-bound, and if so in which process,
pg_dump or the connected backend?

Also, how many large objects is that?  (If you don't know already,
"select count(*) from pg_largeobject_metadata" would tell you.)

            regards, tom lane

Hi, 

    I tried with Postgresql 9.2 and the process used to take almost a day and a half, was significantly reduced to 6 hours, before failing even used to take four hours. My question now is, how long should it take the backup for a 200GB database with 80% of large objects?

Hp proliant Xeon G5

32 GB RAM

OS SLES 10 + logs --> raid 6

data-->raid 6

thanks!

On Thu, Sep 20, 2012 at 12:53 PM, Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> wrote:

On Thu, Sep 20, 2012 at 11:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

 You wouldn't happen to be

trying to use a 9.0 or later pg_dump would you?  Exactly what 8.4.x
release is this, anyway?

Tom, thanks for replying, yes, we tried it with postgres postgres 9.1 and 9.2 and the behavior is exactly the same. The production version is 8.4.9

Greetings, 

sergio. 

Sergio Gabriel Rodriguez <sgrodriguez@gmail.com> writes:
> On Thu, Oct 11, 2012 at 7:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's pretty hard to say without knowing a lot more info about your system
>> than you provided.  One thing that would shed some light is if you spent
>> some time finding out where the time is going --- is the system
>> constantly I/O busy, or is it CPU-bound, and if so in which process,
>> pg_dump or the connected backend?

>  the greatest amount of time is lost in I/O busy.

In that case there's not going to be a whole lot you can do about it,
probably.  Or at least not that's very practical --- I assume "buy
faster disks" isn't a helpful answer.

If the blobs are relatively static, it's conceivable that clustering
pg_largeobject would help, but you're probably not going to want to take
down your database for as long as that would take --- and the potential
gains are unclear anyway.

> 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.

            regards, tom lane

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