| Lists: | pgsql-hackers |
|---|
| From: | "Vincze, Tamas" <vincze(at)neb(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Patch to speed up pg_dump |
| Date: | 2009-04-01 14:47:51 |
| Message-ID: | 49D37E97.8050504@neb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Hi,
We have a database with tens of millions of large objects, none
of them with any comments. When running pg_dump it spends several
hours looking for BLOB comments, finding none at the end but taxing
the server so much that the simplest query takes seconds to complete.
The attached patch fixes this by fetching the description only
of those BLOBs that may have it.
For those interested, some more info:
This query takes about 2 hours to execute:
sw2=# select count(*) from pg_largeobject;
count
-----------
135807552
(1 row)
I'm throttling the transfer rate on pg_dump's stdout so that it
doesn't affect server performance a lot, but obviously it didn't
help the function saving (looking for) BLOB comments.
Regards,
Tamas
| Attachment | Content-Type | Size |
|---|---|---|
| pg_dump_blob_comment_fix.patch | text/plain | 1.3 KB |
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Vincze, Tamas" <vincze(at)neb(dot)com> |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Patch to speed up pg_dump |
| Date: | 2009-04-01 15:22:30 |
| Message-ID: | 5938.1238599350@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
"Vincze, Tamas" <vincze(at)neb(dot)com> writes:
> + * Note that it may still select BLOBs that have no comment if a pg_description row's objoid
> + * matches a BLOB's loid, but references an object contained in a different system catalog,
... seems like that would be easy to fix ...
regards, tom lane
| From: | "Vincze, Tamas" <vincze(at)neb(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Patch to speed up pg_dump |
| Date: | 2009-04-01 18:18:13 |
| Message-ID: | 49D3AFE5.2000300@neb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Tom Lane wrote:
> "Vincze, Tamas" <vincze(at)neb(dot)com> writes:
>> + * Note that it may still select BLOBs that have no comment if a pg_description row's objoid
>> + * matches a BLOB's loid, but references an object contained in a different system catalog,
>
> ... seems like that would be easy to fix ...
Yes, it wasn't that hard. The revised patch is attached.
Originally I didn't want to add more dependencies on the system
catalogs. Also, I've left the DECLARE statements untouched for
pre-v7.2 backends, so the NULL check on the comment is still
needed for those cases and if the description itself is NULL.
Regards,
Tamas
| Attachment | Content-Type | Size |
|---|---|---|
| pg_dump_blob_comment_fix.patch | text/plain | 1.2 KB |
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Vincze, Tamas" <vincze(at)neb(dot)com> |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Patch to speed up pg_dump |
| Date: | 2009-04-01 18:55:20 |
| Message-ID: | 9905.1238612120@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
"Vincze, Tamas" <vincze(at)neb(dot)com> writes:
> Tom Lane wrote:
>> "Vincze, Tamas" <vincze(at)neb(dot)com> writes:
> + * Note that it may still select BLOBs that have no comment if a pg_description row's objoid
> + * matches a BLOB's loid, but references an object contained in a different system catalog,
>>
>> ... seems like that would be easy to fix ...
> Yes, it wasn't that hard. The revised patch is attached.
Applied to HEAD and 8.3, using regclass cast to simplify ...
regards, tom lane