pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFastest pq_restore?
5+ messages / 4 participants
[nested] [flat]
* Fastest pq_restore?
@ 2011-03-17 14:25 Michael Andreasen <michael@dunlops.com>
2011-03-18 01:08 ` Re: Fastest pq_restore? Andy Colson <andy@squeakycode.net>
2011-03-19 16:58 ` Re: Fastest pq_restore? bricklen <bricklen@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Andreasen @ 2011-03-17 14:25 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've been looking around for information on doing a pg_restore as fast as
possible. It is for a backup machine so I am not interested in anything like
crash recovery or anything else that would impact speed of load. I just want
to go from no database to database there as fast as possible. The server is
for postgresql only and this is the only database, sp both system at
postgres can be set however is required for the fast load.
Currently I am using a twin processor box with 2GB of memory and raid 5
disk.
I start postgres before my load with these settings, which have been
suggested.
shared_buffers = 496MB
maintenance_work_mem = 160MB
checkpoint_segments = 30
autovacuum = false
full_page_writes=false
maintenance_work_mem and checkpoint_segments were advised to be increased,
which I have done, but these are just guess values as I couldn't see any
advise for values, other than "bigger".
I restore like this;
pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz
Even as this, it is still slower than I would like.
Can someone suggest some optimal settings (for postgresql 9) that will get
this as quick as it can be?
Thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fastest pq_restore?
2011-03-17 14:25 Fastest pq_restore? Michael Andreasen <michael@dunlops.com>
@ 2011-03-18 01:08 ` Andy Colson <andy@squeakycode.net>
2011-03-18 14:38 ` Re: Fastest pq_restore? Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 5+ messages in thread
From: Andy Colson @ 2011-03-18 01:08 UTC (permalink / raw)
To: Michael Andreasen <michael@dunlops.com>; +Cc: pgsql-performance
On 03/17/2011 09:25 AM, Michael Andreasen wrote:
> Hi,
>
> I've been looking around for information on doing a pg_restore as fast as possible. It is for a backup machine so I am not interested in anything like crash recovery or anything else that would impact speed of load. I just want to go from no database to database there as fast as possible. The server is for postgresql only and this is the only database, sp both system at postgres can be set however is required for the fast load.
>
> Currently I am using a twin processor box with 2GB of memory and raid 5 disk.
>
> I start postgres before my load with these settings, which have been suggested.
>
>
> shared_buffers = 496MB
> maintenance_work_mem = 160MB
> checkpoint_segments = 30
> autovacuum = false
> full_page_writes=false
>
> maintenance_work_mem and checkpoint_segments were advised to be increased, which I have done, but these are just guess values as I couldn't see any advise for values, other than "bigger".
>
>
> I restore like this;
>
> pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz
>
>
> Even as this, it is still slower than I would like.
>
> Can someone suggest some optimal settings (for postgresql 9) that will get this as quick as it can be?
>
> Thanks.
>
>
>
>
>
autovacuum = off
fsync = off
synchronous_commit = off
full_page_writes = off
bgwriter_lru_maxpages = 0
-Andy
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fastest pq_restore?
2011-03-17 14:25 Fastest pq_restore? Michael Andreasen <michael@dunlops.com>
2011-03-18 01:08 ` Re: Fastest pq_restore? Andy Colson <andy@squeakycode.net>
@ 2011-03-18 14:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-18 19:10 ` Re: Fastest pq_restore? Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2011-03-18 14:38 UTC (permalink / raw)
To: Michael Andreasen <michael@dunlops.com>; Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
Andy Colson <andy@squeakycode.net> wrote:
> On 03/17/2011 09:25 AM, Michael Andreasen wrote:
>> I've been looking around for information on doing a pg_restore as
>> fast as possible.
>> I am using a twin processor box with 2GB of memory
>> shared_buffers = 496MB
Probably about right.
>> maintenance_work_mem = 160MB
You might get a benefit from a bit more there; hard to say what's
best with so little RAM.
>> checkpoint_segments = 30
This one is hard to call without testing. Oddly, some machines do
better with the default of 3. Nobody knows why.
>> autovacuum = false
>> full_page_writes=false
Good.
> fsync = off
> synchronous_commit = off
Absolutely.
> bgwriter_lru_maxpages = 0
I hadn't thought much about that last one -- do you have benchmarks
to confirm that it helped with a bulk load?
You might want to set max_connections to something lower to free up
more RAM for caching, especially considering that you have so little
RAM.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fastest pq_restore?
2011-03-17 14:25 Fastest pq_restore? Michael Andreasen <michael@dunlops.com>
2011-03-18 01:08 ` Re: Fastest pq_restore? Andy Colson <andy@squeakycode.net>
2011-03-18 14:38 ` Re: Fastest pq_restore? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-03-18 19:10 ` Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 5+ messages in thread
From: Andy Colson @ 2011-03-18 19:10 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Michael Andreasen <michael@dunlops.com>; pgsql-performance
On 3/18/2011 9:38 AM, Kevin Grittner wrote:
> Andy Colson<andy@squeakycode.net> wrote:
>> On 03/17/2011 09:25 AM, Michael Andreasen wrote:
>
>>> I've been looking around for information on doing a pg_restore as
>>> fast as possible.
>
>> bgwriter_lru_maxpages = 0
>
> I hadn't thought much about that last one -- do you have benchmarks
> to confirm that it helped with a bulk load?
>
Nope, I got it from the "running with scissors" thread (I think), (maybe
from Greg Smith)
or here:
http://rhaas.blogspot.com/2010/06/postgresql-as-in-memory-only-database_24.html
I dont recall exactly. I saw it, add added a comment to my .conf just
incase I ever needed it.
-Andy
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fastest pq_restore?
2011-03-17 14:25 Fastest pq_restore? Michael Andreasen <michael@dunlops.com>
@ 2011-03-19 16:58 ` bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: bricklen @ 2011-03-19 16:58 UTC (permalink / raw)
To: Michael Andreasen <michael@dunlops.com>; +Cc: pgsql-performance
On Thu, Mar 17, 2011 at 7:25 AM, Michael Andreasen <michael@dunlops.com> wrote:
> Currently I am using a twin processor box with 2GB of memory and raid 5
> disk.
> I start postgres before my load with these settings, which have been
> suggested.
>
> I restore like this;
> pg_restore -Fc -j 4 -i -O -d my_db my_db_dump.tbz
>
Just throwing this out there, but you have 4 parallel jobs running the
restore (-j 4), with two processors? They are multi-core? You might be
seeing some contention there if they aren't.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-03-19 16:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-17 14:25 Fastest pq_restore? Michael Andreasen <michael@dunlops.com>
2011-03-18 01:08 ` Andy Colson <andy@squeakycode.net>
2011-03-18 14:38 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-03-18 19:10 ` Andy Colson <andy@squeakycode.net>
2011-03-19 16:58 ` 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