PostgreSQL Performance - Re: Migration study, step 1: bulk write performance

Author

Re: Migration study, step 1: bulk write performance

Ron

2006-03-21, 8:33 pm

At 03:44 PM 3/21/2006, Simon Riggs wrote:
>On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
>
> compared to the raw dd result (~75.5Mb/s).
> to the table, at least one byte is also written to WAL, in effect:
> 10Gb data inserted in the table equals 20Gb written to disk)
> transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
>
>
>With those settings, you'll be checkpointing every 48 Mb, which will be
>every about once per second. Since the checkpoint will take a reasonable
>amount of time, even with fsync off, you'll be spending most of your
>time checkpointing. bgwriter will just be slowing you down too because
>you'll always have more clean buffers than you can use, since you have
>132MB of shared_buffers, yet flushing all of them every checkpoint.
IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
64 - 256 is best for checkpoint_segments as long as you have the RAM available.

I'd suggest trying values of 64, 128, and 256 and setting
checkpoint_segments to the best of those.

Ron

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Alvaro Herrera

2006-03-21, 8:33 pm

Ron wrote:

> IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
> 64 - 256 is best for checkpoint_segments as long as you have the RAM
> available.

I think you are confusing checkpoint_segments with wal_buffers.
checkpoint_segments certainly has little to do with available RAM!

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links