From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | David Griffiths <dgriffiths(at)boats(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Any issues with my tuning... |
Date: | 2003-10-13 21:32:17 |
Message-ID: | 200310131432.17735.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
David,
> shared_buffers = 96000 # min max_connections*2 or 16, 8KB each
This seems a little high to me, even for 2gb RAM. What % of your available
RAM does it work out to?
> effective_cache_size = 6000 # typically 8KB each
This is very, very low. Given your hardware, I'd set it to 1.5GB.
> Note that I've played with all these values; shared_buffers has been as
> low as 5000, and effective_cache_size has been as high as 50000. Sort
> mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been
> between 16 and 128.
If large updates are slow, increasing checkpoint_segments has the largest
effect on this.
> Tied up in all this is my inability to grasp what shared_buffers do
>
> From " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html> ":
>
> "shbufShared buffers defines a block of memory that PostgreSQL will use
> to hold requests that are awaiting attention from the kernel buffer and
> CPU." and "The shared buffers parameter assumes that OS is going to
> cache a lot of files and hence it is generally very low compared with
> system RAM."
This is correct. Optimal levels among the people on this list who have
bothered to do profiling have ranged btw. 6% and 12% of available RAM, but
never higher.
> From " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
> <http://www.lyris.com/lm_help/6.0/tuning_postgresql.html> "
>
> "Increase the buffer size. Postgres uses a shared memory segment among
> its subthreads to buffer data in memory. The default is 512k, which is
> inadequate. On many of our installs, we've bumped it to ~16M, which is
> still small. If you can spare enough memory to fit your whole database
> in memory, do so."
This is absolutely incorrect. They are confusing shared_buffers with the
kernel cache, or perhaps confusing PostgreSQL configuration with Oracle
configuration.
I have contacted Lyris and advised them to update the manual.
> Our database (in Oracle) is just over 4 gig in size; obviously, this
> won't comfortably fit in memory (though we do have an Opteron machine
> inbound for next week with 4-gig of RAM and SCSI hard-drives). The more
> of it we can fit in memory the better.
This is done through increasing the effective_cache_size, which encourages the
planner to use data kept in the kernel cache.
> What about changing these costs - the doc at
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.htm
> l> doesn't go into a lot of detail. I was thinking that maybe the
> optimizer decided it was faster to do a sequential scan rather than an
> index scan based on an analysis of the cost using these values.
>
> #random_page_cost = 4 # units are one sequential page fetch
> cost
> #cpu_tuple_cost = 0.01 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #cpu_operator_cost = 0.0025 # (same)
That's because nobody to date has done tests on the effect of tinkering with
these values on different machines and setups. We would welcome your
results.
On high-end machines, random_page_cost almost inevatibly needs to be lowered
to 2 or even 1.5 to encourage the use of indexes.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-10-13 22:25:04 | Re: [HACKERS] Sun performance - Major discovery! |
Previous Message | Ron Johnson | 2003-10-13 20:53:04 | Re: Any issues with my tuning... |