pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore >10million large objects
2+ messages / 2 participants
[nested] [flat]
* pg_restore >10million large objects
@ 2013-12-23 15:19 Mike Williams <mike.williams@comodo.com>
2013-12-23 15:54 ` Re: pg_restore >10million large objects bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Mike Williams @ 2013-12-23 15:19 UTC (permalink / raw)
To: pgsql-admin
Hi all,
There have been some questions about pg_dump and huge numbers of large objects
recently. I have a query about the opposite.
How can restoring a database with a lot of large objects run faster?
My database has a relatively piddling 13 million large objects, so dumping it
isn't a problem.
Restoring it is a problem though.
This is for a migration from 8.4 to 9.3. The dump is taken using pg_dump from
9.3.
I've run a test on a significantly smaller test system.
~4GB overall, and 1.1 million large objects. It took 2 hours, give or take.
The server it's on isn't especially fast though.
It seems that each "SELECT pg_catalog.lo_create('xxxxx');" is run
independently and sequentially, despite having --jobs=8 specified.
Is there any magic incantation, or animal sacrifice, I can make to get those
lo_create() calls run in parallel?
Our 9.3 production servers have 12 cores (plus HT) and SSDs, so can do many
queries at the same time.
Thanks
--
Mike Williams
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_restore >10million large objects
2013-12-23 15:19 pg_restore >10million large objects Mike Williams <mike.williams@comodo.com>
@ 2013-12-23 15:54 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: bricklen @ 2013-12-23 15:54 UTC (permalink / raw)
To: Mike Williams <mike.williams@comodo.com>; +Cc: pgsql-admin
On Mon, Dec 23, 2013 at 7:19 AM, Mike Williams <mike.williams@comodo.com>wrote:
>
> How can restoring a database with a lot of large objects run faster?
>
> It seems that each "SELECT pg_catalog.lo_create('xxxxx');" is run
> independently and sequentially, despite having --jobs=8 specified.
>
>
I don't have an answer for why the restore seems to be serialized, but have
you considered creating your pg_dump (-Fc) but exclude all the lobs, then
dump or COPY the large objects out separately which you can them import
with a manually-specified number of processes? By "manually specified", I
mean execute a number of COPY FROM commands using separate threads.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-12-23 15:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-23 15:19 pg_restore >10million large objects Mike Williams <mike.williams@comodo.com>
2013-12-23 15:54 ` bricklen <bricklen@gmail.com>
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