Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMoVE-00024m-Fq for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 23:18:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMoVB-0004xr-Hp for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 23:18:47 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q9CNIhgF025142; Fri, 12 Oct 2012 19:18:43 -0400 (EDT) From: Tom Lane To: Sergio Gabriel Rodriguez cc: pgsql-performance@postgresql.org Subject: Re: problems with large objects dump In-reply-to: References: <16863.1348151708@sss.pgh.pa.us> <1269.1349993793@sss.pgh.pa.us> Comments: In-reply-to Sergio Gabriel Rodriguez message dated "Fri, 12 Oct 2012 19:35:06 -0300" Date: Fri, 12 Oct 2012 19:18:43 -0400 Message-ID: <25141.1350083923@sss.pgh.pa.us> X-Pg-Spam-Score: -4.0 (----) X-Archive-Number: 201210/169 X-Sequence-Number: 48128 Sergio Gabriel Rodriguez writes: > On Thu, Oct 11, 2012 at 7:16 PM, Tom Lane 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