Thread: Question about shared_buffers and cpu usage

Question about shared_buffers and cpu usage

From
"bh yuan"
Date:
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.
The memory info is that
-----------------------------------------------------
MemTotal:      8177484 kB
MemFree:        313336 kB
Buffers:        112700 kB
Cached:        7008160 kB
SwapCached:     210832 kB
Active:        7303660 kB
Inactive:       402088 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      8177484 kB
LowFree:        313336 kB
SwapTotal:     8385920 kB
SwapFree:      7415768 kB
Dirty:             908 kB
Writeback:           0 kB
AnonPages:       28312 kB
Mapped:        2163912 kB
Slab:            99396 kB
PageTables:      13004 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  12474660 kB
Committed_AS:  8169440 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    267136 kB
VmallocChunk: 34359470587 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
-----------------------------------------------

2  I have 8 core cpu ,but It seems that one sql can only use 1 core.
Can I use more core to execute one sql to optimize the speed ?

Thanks

Re: Question about shared_buffers and cpu usage

From
"Scott Marlowe"
Date:
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.

Re: Question about shared_buffers and cpu usage

From
Dave Cramer
Date:
On 21-Feb-08, at 12:13 AM, bh yuan 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.

Yes, it is too big! make it 2G to start
>