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 19:18:43 -0400
Message-ID: <25141.1350083923@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMHBdEoCpHfanPE_S2Lrc9KjqxvZ-N_KBnOLKn2h3XmY6Rh49A@mail.gmail.com>
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>
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
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: <25141.1350083923@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