pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Question about shared_buffers and cpu usage 3+ messages / 3 participants [nested] [flat]
* Question about shared_buffers and cpu usage @ 2008-02-21 05:13 bh yuan <bhyuan@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: bh yuan @ 2008-02-21 05:13 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about shared_buffers and cpu usage @ 2008-02-21 07:15 Scott Marlowe <scott.marlowe@gmail.com> parent: bh yuan <bhyuan@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Scott Marlowe @ 2008-02-21 07:15 UTC (permalink / raw) To: bh yuan <bhyuan@gmail.com>; +Cc: pgsql-performance 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about shared_buffers and cpu usage @ 2008-02-21 12:24 Dave Cramer <pg@fastcrypt.com> parent: bh yuan <bhyuan@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Dave Cramer @ 2008-02-21 12:24 UTC (permalink / raw) To: bh yuan <bhyuan@gmail.com>; +Cc: pgsql-performance 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 > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-02-21 12:24 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-02-21 05:13 Question about shared_buffers and cpu usage bh yuan <bhyuan@gmail.com> 2008-02-21 07:15 ` Scott Marlowe <scott.marlowe@gmail.com> 2008-02-21 12:24 ` Dave Cramer <pg@fastcrypt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox