On Wed, Feb 20, 2008 at 11:13 PM, bh yuan <bhyuan@xxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
