Re: dbt-2 tuning results with postgresql-8.3.5

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Dec 22, 2008 at 12:59 AM, Greg Smith <gsmith@xxxxxxxxxxxxx> wrote:
> On Sat, 20 Dec 2008, Mark Wong wrote:
>
>> Here are links to how the throughput changes when increasing
>> shared_buffers: http://pugs.postgresql.org/node/505 My first glance takes
>> tells me that the system performance is quite erratic when increasing the
>> shared_buffers.
>
> If you smooth that curve out a bit, you have to throw out the 22528MB figure
> as meaningless--particularly since it's way too close to the cliff where
> performance dives hard.  The sweet spot looks to me like 11264MB to 17408MB.
>  I'd say 14336MB is the best performing setting that's in the middle of a
> stable area.
>
>> And another series of tests to show how throughput changes when
>> checkpoint_segments are increased: http://pugs.postgresql.org/node/503 I'm
>> also not what to gather from increasing the checkpoint_segments.
>
> What was shared_buffers set to here?  Those two settings are not completely
> independent, for example at a tiny buffer size it's not as obvious there's a
> win in spreading the checkpoints out more.  It's actually a 3-D graph, with
> shared_buffers and checkpoint_segments as two axes and the throughput as the
> Z value.

The shared_buffers are the default, 24MB.  The database parameters are
saved, probably unclearly, here's an example link:

http://207.173.203.223/~markwkm/community6/dbt2/baseline.1000.1/db/param.out

> Since that's quite time consuming to map out in its entirety, the way I'd
> suggest navigating the territory more efficiently is to ignore the defaults
> altogether.  Start with a configuration that someone familiar with tuning
> the database would pick for this hardware:  8192MB for shared_buffers and
> 100 checkpoint segments would be a reasonable base point.  Run the same
> tests you did here, but with the value you're not changing set to those much
> larger values rather than the database defaults, and then I think you'd end
> with something more interesting. Also, I think the checkpoint_segments
> values >500 are a bit much, given what level of recovery time would come
> with a crash at that setting. Smaller steps from a smaller range would be
> better there I think.

I should probably run your pgtune script, huh?

Regards,
Mark

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux