Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMqa8-0000Mk-HQ for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 01:32:00 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMqa4-0007L7-6Q for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 01:32:00 +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 q9D1VsLu027768; Fri, 12 Oct 2012 21:31:54 -0400 (EDT) From: Tom Lane To: Sergio Gabriel Rodriguez cc: pgsql-performance@postgresql.org Subject: Re: problems with large objects dump In-reply-to: <25141.1350083923@sss.pgh.pa.us> References: <16863.1348151708@sss.pgh.pa.us> <1269.1349993793@sss.pgh.pa.us> <25141.1350083923@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 12 Oct 2012 19:18:43 -0400" Date: Fri, 12 Oct 2012 21:31:54 -0400 Message-ID: <27767.1350091914@sss.pgh.pa.us> X-Pg-Spam-Score: -4.0 (----) X-Archive-Number: 201210/171 X-Sequence-Number: 48130 I wrote: > Sergio Gabriel Rodriguez 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