pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Dump/Restore performance improvement
2+ messages / 2 participants
[nested] [flat]

* Dump/Restore performance improvement
@ 2004-09-05 04:07 Adi Alurkar <adi@sf.net>
  2004-09-05 17:07 ` Re: Dump/Restore performance improvement Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Adi Alurkar @ 2004-09-05 04:07 UTC (permalink / raw)
  To: pgsql-performance

Greetings,

I have observed that in a dump/restore scenario the longest time is 
spent on index creation for larger tables, I have a suggestion of how 
the performance could be improved thus reducing the time to recover 
from a crash. Not sure if this is possible but would definitely be a 
nice addition to the TODO list.

1) Add a new config paramter  e.g work_maintanence_max_mem  this will 
the max memory postgresql *can* claim if need be.

2) During the dump phase of the DB  postgresql  estimates the 
"work_maintenance_mem" that would be required to create the index in 
memory(if possible) and add's a
SET work_maintenance_mem="the value calculated"  (IF this value is less 
than work_maintanence_max_mem. )

3) During the restore phase the appropriate memory is allocated in RAM 
and the index creation takes less time since PG does not have to sort 
on disk.

--
Adi Alurkar (DBA sf.NET) <adi@vasoftware.com>
1024D/79730470 A491 5724 74DE 956D 06CB  D844 6DF1 B972 7973 0470




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Dump/Restore performance improvement
  2004-09-05 04:07 Dump/Restore performance improvement Adi Alurkar <adi@sf.net>
@ 2004-09-05 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-09-05 17:07 UTC (permalink / raw)
  To: Adi Alurkar <adi@sf.net>; +Cc: pgsql-performance

Adi Alurkar <adi@sf.net> writes:
> 1) Add a new config paramter  e.g work_maintanence_max_mem  this will 
> the max memory postgresql *can* claim if need be.

> 2) During the dump phase of the DB  postgresql  estimates the 
> "work_maintenance_mem" that would be required to create the index in 
> memory(if possible) and add's a
> SET work_maintenance_mem="the value calculated"  (IF this value is less 
> than work_maintanence_max_mem. )

This seems fairly pointless to me.  How is this different from just
setting maintenance_work_mem as large as you can stand before importing
the dump?

Making any decisions at dump time seems wrong to me in the first place;
pg_dump should not be expected to know what conditions the restore will
be run under.  I'm not sure that's what you're proposing, but I don't
see what the point is in practice.  It's already the case that
maintenance_work_mem is treated as the maximum memory you can use,
rather than what you will use even if you don't need it all.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-09-05 17:07 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-05 04:07 Dump/Restore performance improvement Adi Alurkar <adi@sf.net>
2004-09-05 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>

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