pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRestore db
5+ messages / 4 participants
[nested] [flat]
* Restore db
@ 2011-11-14 00:09 Alexander Burbello <burbello@yahoo.com.br>
2011-11-14 00:10 ` Restore db Alexander Burbello <contato@alexanderburbello.com.br>
2011-11-14 00:43 ` Re: Restore db Ondrej Ivanič <ondrej.ivanic@gmail.com>
2011-11-14 01:22 ` Re: Restore db Andy Colson <andy@squeakycode.net>
0 siblings, 3 replies; 5+ messages in thread
From: Alexander Burbello @ 2011-11-14 00:09 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi folks,
My server has a daily routine to import a dump file, however its taking
long time to finish it.
The original db has around 200 MB and takes 3~4 minutes to export (there
are many blob fields), however it takes 4 hours to import using pg_restore.
What can I do to tune this database to speed up this restore??
My current db parameters are:
shared_buffers = 256MB
maintenance_work_mem = 32MB
Any suggestion is very welcome.
Thank you.
Alex
^ permalink raw reply [nested|flat] 5+ messages in thread
* Restore db
2011-11-14 00:09 Restore db Alexander Burbello <burbello@yahoo.com.br>
@ 2011-11-14 00:10 ` Alexander Burbello <contato@alexanderburbello.com.br>
2011-11-14 00:35 ` Restore db Alexander Burbello <burbello@yahoo.com.br>
2 siblings, 1 reply; 5+ messages in thread
From: Alexander Burbello @ 2011-11-14 00:10 UTC (permalink / raw)
To: pgsql-admin
Hi folks,
My server has a daily routine to import a dump file, however its taking
long time to finish it.
The original db has around 200 MB and takes 3~4 minutes to export (there
are many blob fields), however it takes 4 hours to import using pg_restore.
What can I do to tune this database to speed up this restore??
My current db parameters are:
shared_buffers = 256MB
maintenance_work_mem = 32MB
Any suggestion is very welcome.
Thank you.
Alex
^ permalink raw reply [nested|flat] 5+ messages in thread
* Restore db
2011-11-14 00:09 Restore db Alexander Burbello <burbello@yahoo.com.br>
2011-11-14 00:10 ` Restore db Alexander Burbello <contato@alexanderburbello.com.br>
@ 2011-11-14 00:35 ` Alexander Burbello <burbello@yahoo.com.br>
0 siblings, 0 replies; 5+ messages in thread
From: Alexander Burbello @ 2011-11-14 00:35 UTC (permalink / raw)
To: pgsql-admin
Hi folks,
My server has a daily routine to import a dump file, however its taking
long time to finish it.
The original db has around 200 MB and takes 3~4 minutes to export (there
are many blob fields), however it takes 4 hours to import using pg_restore.
What can I do to tune this database to speed up this restore??
My current db parameters are:
shared_buffers = 256MB
maintenance_work_mem = 32MB
Any suggestion is very welcome.
Thank you.
Alex
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Restore db
2011-11-14 00:09 Restore db Alexander Burbello <burbello@yahoo.com.br>
@ 2011-11-14 00:43 ` Ondrej Ivanič <ondrej.ivanic@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ondrej Ivanič @ 2011-11-14 00:43 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi,
On 14 November 2011 11:09, Alexander Burbello <burbello@yahoo.com.br> wrote:
> What can I do to tune this database to speed up this restore??
> My current db parameters are:
> shared_buffers = 256MB
> maintenance_work_mem = 32MB
You should increase maintenance_work_mem as much as you can.
full_page_writes, archive_mode and auto_vacuum should be disable
during restore. Increase checkpoint_segments (for example to 64) and
set wal_buffers to 16MB. 8.4 introduced parallel restore (pg_restore
option -j <num jobs>).
Maybe you can't do anything mentioned above because it is not possible
to restart server (you can change maintenance_work_mem via PGOPTIONS)
or there is a single table to import (-j is not aplicable) -- try to
drop indexes and recreate them after import.
--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Restore db
2011-11-14 00:09 Restore db Alexander Burbello <burbello@yahoo.com.br>
@ 2011-11-14 01:22 ` Andy Colson <andy@squeakycode.net>
2 siblings, 0 replies; 5+ messages in thread
From: Andy Colson @ 2011-11-14 01:22 UTC (permalink / raw)
To: contato@alexanderburbello.com.br; +Cc: Alexander Burbello <burbello@yahoo.com.br>; pgsql-general@postgresql.org
On 11/13/2011 06:09 PM, Alexander Burbello wrote:
> Hi folks,
>
> My server has a daily routine to import a dump file, however its taking long time to finish it.
> The original db has around 200 MB and takes 3~4 minutes to export (there are many blob fields), however it takes 4 hours to import using pg_restore.
>
> What can I do to tune this database to speed up this restore??
>
> My current db parameters are:
> shared_buffers = 256MB
> maintenance_work_mem = 32MB
>
> Any suggestion is very welcome.
> Thank you.
> Alex
Are you doing this over a network?
If you watch it restore with vmstat (or top) are you IO bound? If so, temporarily turn off fsync, restore the db, then turn it back on.
something like:
autovacuum = off
fsync = off
synchronous_commit = off
full_page_writes = off
bgwriter_lru_maxpages = 0
On the other hand, if you are cpu bound, use the multi-core-restore-option -j.
Or use both.
> My current db parameters are:
> shared_buffers = 256MB
> maintenance_work_mem = 32MB
This is useless information without knowing anything about your computer. If you have 512 Meg of ram its a lot different than if you have 32 Gig.
-Andy
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-11-14 01:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-14 00:09 Restore db Alexander Burbello <burbello@yahoo.com.br>
2011-11-14 00:10 ` Alexander Burbello <contato@alexanderburbello.com.br>
2011-11-14 00:35 ` Alexander Burbello <burbello@yahoo.com.br>
2011-11-14 00:43 ` Ondrej Ivanič <ondrej.ivanic@gmail.com>
2011-11-14 01:22 ` Andy Colson <andy@squeakycode.net>
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