pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
restore time: sort_mem vs. checkpoing_segments
10+ messages / 6 participants
[nested] [flat]

* restore time: sort_mem vs. checkpoing_segments
@ 2003-09-15 19:15 Vivek Khera <khera@kcilink.com>
  2003-09-15 21:42 ` Re: restore time: sort_mem vs. checkpoing_segments Josh Berkus <josh@agliodbs.com>
  2003-09-16 05:19 ` Re: restore time: sort_mem vs. checkpoing_segments Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-17 20:15 ` Re: restore time: sort_mem vs. checkpoing_segments Robert Treat <xzilla@users.sourceforge.net>
  2003-09-23 13:59 ` Re: restore time: sort_mem vs. checkpoing_segments Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 4 replies; 10+ messages in thread

From: Vivek Khera @ 2003-09-15 19:15 UTC (permalink / raw)
  To: pgsql-performance

And the winner is... checkpoint_segments.

Restore of a significanly big database (~19.8GB restored) shows nearly
no time difference depending on sort_mem when checkpoint_segments is
large.  There are quite a number of tables and indexes.  The restore
was done from a pg_dump -Fc dump of one database.

All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG
7.4b2 on FreeBSD 4.8.

3 checkpoint_segments restore time: 14983 seconds
50 checkpoint_segments restore time: 11537 seconds
50 checkpoint_segments, sort_mem 131702 restore time: 11262 seconds

There's an initdb between each test.

For reference, the restore with 8k page size, 60k buffers, 8192
sort_mem and 3 checkpoint buffers was 14777 seconds.

It seems for restore that a larger number of checkpoint buffers is the
key, especially when dealing with large numbers of rows in a table.

I notice during the restore that the disk throughput triples during
the checkpoint.

The postgres data partition is on a 14-spindle hardware RAID5 on U320
SCSI disks.



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
@ 2003-09-15 21:42 ` Josh Berkus <josh@agliodbs.com>
  3 siblings, 0 replies; 10+ messages in thread

From: Josh Berkus @ 2003-09-15 21:42 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; pgsql-performance

Vivek,

> And the winner is... checkpoint_segments.
> 
> Restore of a significanly big database (~19.8GB restored) shows nearly
> no time difference depending on sort_mem when checkpoint_segments is
> large.  There are quite a number of tables and indexes.  The restore
> was done from a pg_dump -Fc dump of one database.

Cool!   Thank you for posting this.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
@ 2003-09-16 05:19 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-16 13:59   ` Re: restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  3 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2003-09-16 05:19 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

Vivek Khera <khera@kcilink.com> writes:
> Restore of a significanly big database (~19.8GB restored) shows nearly
> no time difference depending on sort_mem when checkpoint_segments is
> large.  There are quite a number of tables and indexes.  The restore
> was done from a pg_dump -Fc dump of one database.

I was just bugging Marc for some useful data, so I'll ask you too:
could you provide a trace of the pg_restore execution?  log_statement
plus log_duration output would do it.  I am curious to understand
exactly which steps in the restore are significant time sinks.

> I notice during the restore that the disk throughput triples during
> the checkpoint.

Hm, better make sure the log includes some indication of when
checkpoints happen.

			regards, tom lane



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-16 05:19 ` Re: restore time: sort_mem vs. checkpoing_segments Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-16 13:59   ` Vivek Khera <khera@kcilink.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Vivek Khera @ 2003-09-16 13:59 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> I was just bugging Marc for some useful data, so I'll ask you too:
TL> could you provide a trace of the pg_restore execution?  log_statement
TL> plus log_duration output would do it.  I am curious to understand
TL> exactly which steps in the restore are significant time sinks.

Sure... machine isn't gonna do much of anything until 7.4 is released
(or I hear a promise of no more dump/reload).

>> I notice during the restore that the disk throughput triples during
>> the checkpoint.

TL> Hm, better make sure the log includes some indication of when
TL> checkpoints happen.

That it does.

I'll post the results in the next couple of days, as each run takes
about 4 hours ;-)



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
@ 2003-09-17 20:15 ` Robert Treat <xzilla@users.sourceforge.net>
  2003-09-17 20:21   ` Re: restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-22 20:17   ` Re: restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  3 siblings, 2 replies; 10+ messages in thread

From: Robert Treat @ 2003-09-17 20:15 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

On Mon, 2003-09-15 at 15:15, Vivek Khera wrote:
> And the winner is... checkpoint_segments.
> 
> Restore of a significanly big database (~19.8GB restored) shows nearly
> no time difference depending on sort_mem when checkpoint_segments is
> large.  There are quite a number of tables and indexes.  The restore
> was done from a pg_dump -Fc dump of one database.
> 
> All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG
> 7.4b2 on FreeBSD 4.8.

hmm... i wonder what would happen if you pushed your sort_mem higher...
on some of our development boxes and upgrade scripts, i push the
sort_mem to 102400 and sometimes even higher depending on the box. this
really speeds up my restores quit a bit (and is generally safe as i make
sure there isn't any other activity going on at the time)

another thing i like to do is turn of fsync, as if the system crashes in
the middle of reload i'm pretty sure i'd be starting all over anyway...

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL




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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-17 20:15 ` Re: restore time: sort_mem vs. checkpoing_segments Robert Treat <xzilla@users.sourceforge.net>
@ 2003-09-17 20:21   ` Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Vivek Khera @ 2003-09-17 20:21 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-performance

>>>>> "RT" == Robert Treat <xzilla@users.sourceforge.net> writes:

RT> hmm... i wonder what would happen if you pushed your sort_mem higher...
RT> on some of our development boxes and upgrade scripts, i push the
RT> sort_mem to 102400 and sometimes even higher depending on the box. this
RT> really speeds up my restores quit a bit (and is generally safe as i make
RT> sure there isn't any other activity going on at the time)

Ok... just two more tests to run, no big deal ;-)


RT> another thing i like to do is turn of fsync, as if the system crashes in
RT> the middle of reload i'm pretty sure i'd be starting all over anyway...

I'll test it and see what happens.  I suspect not a big improvement on
a hardware RAID card with 128Mb backed up cache, though.  But never
say never!



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-17 20:15 ` Re: restore time: sort_mem vs. checkpoing_segments Robert Treat <xzilla@users.sourceforge.net>
@ 2003-09-22 20:17   ` Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Vivek Khera @ 2003-09-22 20:17 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-performance

>>>>> "RT" == Robert Treat <xzilla@users.sourceforge.net> writes:

RT> hmm... i wonder what would happen if you pushed your sort_mem higher...
RT> on some of our development boxes and upgrade scripts, i push the
RT> sort_mem to 102400 and sometimes even higher depending on the box. this
RT> really speeds up my restores quit a bit (and is generally safe as i make
RT> sure there isn't any other activity going on at the time)

I was just checking, and I already ran test with larger sort_mem.  the
checkpoint segments made more of a difference...



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
@ 2003-09-23 13:59 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-09-23 14:27   ` Re: restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-24 05:24   ` Re: restore time: sort_mem vs. checkpoing_segments Dennis Bjorklund <db@zigo.dhs.org>
  3 siblings, 2 replies; 10+ messages in thread

From: Bruce Momjian @ 2003-09-23 13:59 UTC (permalink / raw)
  To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance

Vivek Khera wrote:
> And the winner is... checkpoint_segments.
> 
> Restore of a significanly big database (~19.8GB restored) shows nearly
> no time difference depending on sort_mem when checkpoint_segments is
> large.  There are quite a number of tables and indexes.  The restore
> was done from a pg_dump -Fc dump of one database.
> 
> All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG
> 7.4b2 on FreeBSD 4.8.
> 
> 3 checkpoint_segments restore time: 14983 seconds
> 50 checkpoint_segments restore time: 11537 seconds
> 50 checkpoint_segments, sort_mem 131702 restore time: 11262 seconds

With the new warning about too-frequent checkpoints, people have actual
feedback to encourage them to increase checkpoint_segments.  One issue
is that it is likely to recommend increasing checkpoint_segments during
restore, even if there is no value to it being large during normal
server operation.  Should that be decumented?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-23 13:59 ` Re: restore time: sort_mem vs. checkpoing_segments Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-09-23 14:27   ` Vivek Khera <khera@kcilink.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Vivek Khera @ 2003-09-23 14:27 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-performance

>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

BM> restore, even if there is no value to it being large during normal
BM> server operation.  Should that be decumented?


Yes, right alongside the recommendation to bump sort_mem, even though
in my tests sort_mem made no significant difference in restore time
going from 8m to 128m.



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

* Re: restore time: sort_mem vs. checkpoing_segments
  2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
  2003-09-23 13:59 ` Re: restore time: sort_mem vs. checkpoing_segments Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-09-24 05:24   ` Dennis Bjorklund <db@zigo.dhs.org>
  1 sibling, 0 replies; 10+ messages in thread

From: Dennis Bjorklund @ 2003-09-24 05:24 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance

On Tue, 23 Sep 2003, Bruce Momjian wrote:

> With the new warning about too-frequent checkpoints, people have actual
> feedback to encourage them to increase checkpoint_segments.  One issue
> is that it is likely to recommend increasing checkpoint_segments during
> restore, even if there is no value to it being large during normal
> server operation.  Should that be decumented?

One could have a variable that turns off that warning, and have pg_dump
insert a statement to turn it off. That is, if one never want these
warnings from a restore (from a new dump).

In any case, documentation is good and still needed.

-- 
/Dennis




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


end of thread, other threads:[~2003-09-24 05:24 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-15 19:15 restore time: sort_mem vs. checkpoing_segments Vivek Khera <khera@kcilink.com>
2003-09-15 21:42 ` Josh Berkus <josh@agliodbs.com>
2003-09-16 05:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-16 13:59   ` Vivek Khera <khera@kcilink.com>
2003-09-17 20:15 ` Robert Treat <xzilla@users.sourceforge.net>
2003-09-17 20:21   ` Vivek Khera <khera@kcilink.com>
2003-09-22 20:17   ` Vivek Khera <khera@kcilink.com>
2003-09-23 13:59 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-23 14:27   ` Vivek Khera <khera@kcilink.com>
2003-09-24 05:24   ` Dennis Bjorklund <db@zigo.dhs.org>

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