On Wed, Feb 20, 2008 at 11:13 PM, bh yuan <bhyuan@gmail.com> wrote:
> Hi
>
> I am using Postgres8.3 on 8G memory , Xeon X5355 Quad Core x 2
> processer RH5 machine with 10G data. (with some table which have
> about 2,000,000~ 5,000,000 rows )
>
> I have two quesion.
> 1. how to set the shared_buffers and other postgresql.conf parameter
> for best performance?
> I only run the Postgres8.3 on the machine so I set the shared_buffers
> = 7168MB (7G)
> But somebody said it is too big, so confused.
OK. Shared_buffers are ONLY shared_buffers. When a pgsql process
needs memory it allocates it from the system heap. If you've given 7
out of 8 gig to pg as shared_buffers, the other 1 Gig gets split up
for programs, and for in-memory sorts by pgsql. Also, the OS is very
good at caching file access, but here it won't be able to cache
anything, because it won't have enough memory to do so. With high
swappiness settings in linux, this can result in the OS swapping
programs that it then has to swap back in. If you make your machine
swap out and back in for normal operation, you've gone backwards on
performance. Also, there's a cost associated with maintaining
shared_buffers that grows with more share_buffers. This means it's
usually not a good idea to set it larger than your working set of
data. I.e. if you have 1Gig of data and 1Gig of indexes, then 7Gig of
shared_buffers means 5gigs wasted. Lastly, there's the background
writer which writes out dirty buffer pages before a checkpoint comes
along. The bigger shared_buffers the hard it has to work, if it's
configured. For transactional systems it's usually a win to go with a
smaller (25%) shared_buffer setting and let the OS and battery backed
RAID controller help out. For certain reporting application, larger
settings of shared_buffer are often useful, but you need to reserve
some % of main memory for things like sorts. I usually stick to 25%
shared_buffers, and compute max_connects*work_mem to equal 25% and let
the OS have about 50% to work with. Then I test to see if changing
those helps.
> 2 I have 8 core cpu ,but It seems that one sql can only use 1 core.
Yep, that's normal.
> Can I use more core to execute one sql to optimize the speed ?
Only if you're willing to hack pgsql to split off sorts etc to child
processes. Note that depending on you. I/O subsystem this may or may
not be a win. If you're creating multiple indexes at once, then each
create index will use a different CPU.
