Thread: how much mem to give postgres?
I'm trying to figure out what I need to do to get my postgres server moving faster. It's just crawling right now. It's on a p4 HT with 2 gigs of mem. I was thinking I need to increase the amount of shared buffers, but I've been told "the sweet spot for shared_buffers is usually on the order of 10000 buffers". I already have it set at 21,078. If you have, say 100 gigs of ram, are you supposed to still only give postgres 10,000? Also, do I need to up the shmmax at all? I've used the formula "250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity" at http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC but it's never quite high enough, so I just make sure it's above the amount that the postgres log says it needs. What else can I do to speed this server up? I'm running vacuum analyze on the heavily updated/inserted/deleted db's once an hour, and doing a full vacuum once a night. Should I change the vacuum mem setting at all? Are there any other settings I should be concerned with? I've heard about the effective_cache_size setting, but I haven't seen anything on what the size should be. Any help would be great. This server is very very slow at the moment. Also, I'm using a 2.6.8.1 kernel with high mem enabled, so all the ram is recognized. Thanks. -Josh
>Josh Close > I'm trying to figure out what I need to do to get my postgres server > moving faster. It's just crawling right now. It's on a p4 HT with 2 > gigs of mem. ....and using what version of PostgreSQL are you using? 8.0beta, I hope? > I was thinking I need to increase the amount of shared buffers, but > I've been told "the sweet spot for shared_buffers is usually on the > order of 10000 buffers". I already have it set at 21,078. If you have, > say 100 gigs of ram, are you supposed to still only give postgres > 10,000? Thats under test currently. My answer would be, "clearly not", others differ, for varying reasons. > Also, do I need to up the shmmax at all? I've used the formula "250 kB > + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity" > at http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html#SYSVIPC > but it's never quite high enough, so I just make sure it's above the > amount that the postgres log says it needs. shmmax isn't a tuning parameter for PostgreSQL, its just a limit. If you get no error messages, then its high enough. > Are there any other settings I should be concerned with? I've heard > about the effective_cache_size setting, but I haven't seen anything on > what the size should be. wal_buffers if the databases are heavily updated. > Any help would be great. This server is very very slow at the moment. > Try *very fast disks*, especially for the logs. Best regards, Simon Riggs
On Wed, 20 Oct 2004 01:33:16 +0100, Simon Riggs <simon@2ndquadrant.com> wrote: > ....and using what version of PostgreSQL are you using? 8.0beta, I hope? I'm using version 7.4.5. > > I was thinking I need to increase the amount of shared buffers, but > > I've been told "the sweet spot for shared_buffers is usually on the > > order of 10000 buffers". I already have it set at 21,078. If you have, > > say 100 gigs of ram, are you supposed to still only give postgres > > 10,000? > > Thats under test currently. My answer would be, "clearly not", others > differ, for varying reasons. Should I stick the rule of around 15% of mem then? I haven't found any information on why you should use certain settings at all. I read somewhere on the postgres site about using as much memory as possible, but leave a little room for other processes. Whould that be an ok theory? I'd kinda like to know why I should or shouldn't do something like this. -Josh
Josh Close <narshe@gmail.com> writes:
> I'm trying to figure out what I need to do to get my postgres server
> moving faster. It's just crawling right now.
I suspect that fooling with shared_buffers is entirely the wrong tree
for you to be barking up. My suggestion is to be looking at individual
queries that are slow, and seeing how to speed those up. This might
involve adding indexes, or tweaking the query source, or adjusting
planner parameters, or several other things. EXPLAIN ANALYZE is your
friend ...
regards, tom lane
JJosh, > I'm trying to figure out what I need to do to get my postgres server > moving faster. It's just crawling right now. It's on a p4 HT with 2 > gigs of mem. There have been issues with Postgres+HT, especially on Linux 2.4. Try turning HT off if other tuning doesn't solve things. Otherwise, see: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Josh Berkus Aglio Database Solutions San Francisco
On Wed, 20 Oct 2004 00:35:31 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I suspect that fooling with shared_buffers is entirely the wrong tree > for you to be barking up. My suggestion is to be looking at individual > queries that are slow, and seeing how to speed those up. This might > involve adding indexes, or tweaking the query source, or adjusting > planner parameters, or several other things. EXPLAIN ANALYZE is your > friend ... > > regards, tom lane Only problem is, a "select count(1)" is taking a long time. Indexes shouldn't matter with this since it's counting every row, right? The tables are fairly well indexed also, I could probably add a few more. If shared_buffers isn't the way to go ( you said 10k is the sweetspot ), then what about the effective_cache_size? I was suggested on the general list about possibly setting that to 75% of ram. Thanks. -Josh
On Tue, 19 Oct 2004 22:23:24 -0700, Josh Berkus <josh@agliodbs.com> wrote: > There have been issues with Postgres+HT, especially on Linux 2.4. Try > turning HT off if other tuning doesn't solve things. > > Otherwise, see: > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html How would I turn that off? In the kernel config? Not too familiar with that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres, so I hope HT isn't a problem. If HT is turned off, does it just not use the other "half" of the processor? Or does the processor just work as one unit? Also, I'm taking a look at that site right now :) -Josh
>How would I turn that off? In the kernel config? Not too familiar with >that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres, >so I hope HT isn't a problem. If HT is turned off, does it just not >use the other "half" of the processor? Or does the processor just work >as one unit? > > You turn it off in the BIOS. There is no 'other half', the processor is just pretending to have two cores by shuffling registers around, which gives maybe a 5-10% performance gain in certain multithreaded situations. <opinion>A hack to overcome marchitactural limitations due to the overly long pipeline in the Prescott core.</opinion>. Really of most use for desktop interactivity rather than actual throughput. M
On Wed, Oct 20, 2004 at 03:07:00PM +0100, Matt Clark wrote: > You turn it off in the BIOS. There is no 'other half', the processor is > just pretending to have two cores by shuffling registers around, which > gives maybe a 5-10% performance gain in certain multithreaded > situations. > <opinion>A hack to overcome marchitactural limitations due > to the overly long pipeline in the Prescott core.</opinion>. Really of > most use for desktop interactivity rather than actual throughput. <OT> Hyperthreading is actually an excellent architectural feature that can give significant performance gains when implemented well and used for an appropriate workload under a decently HT aware OS. IMO, typical RDBMS streams are not an obviously appropriate workload, Intel didn't implement it particularly well and I don't think there are any OSes that support it particularly well. </OT> But don't write off using it in the future, when it's been improved at both the OS and the silicon levels. Cheers, Steve
><OT> >Hyperthreading is actually an excellent architectural feature that >can give significant performance gains when implemented well and used >for an appropriate workload under a decently HT aware OS. > >IMO, typical RDBMS streams are not an obviously appropriate workload, >Intel didn't implement it particularly well and I don't think there >are any OSes that support it particularly well. ></OT> > >But don't write off using it in the future, when it's been improved >at both the OS and the silicon levels. > > > You are quite right of course - unfortunately the current Intel implementation meets nearly none of these criteria! As Rod Taylor pointed out off-list, IBM's SMT implementation on the Power5 is vastly superior. Though he's also just told me that Sun is beating IBM on price/performance for his workload, so who knows how reliable a chap he is... ;-) M
On Wed, Oct 20, 2004 at 07:16:18PM +0100, Matt Clark wrote: > ><OT> > >Hyperthreading is actually an excellent architectural feature that > >can give significant performance gains when implemented well and used > >for an appropriate workload under a decently HT aware OS. > > > >IMO, typical RDBMS streams are not an obviously appropriate workload, > >Intel didn't implement it particularly well and I don't think there > >are any OSes that support it particularly well. > ></OT> > > > >But don't write off using it in the future, when it's been improved > >at both the OS and the silicon levels. > > > > > > > You are quite right of course - unfortunately the current Intel > implementation meets nearly none of these criteria! Indeed. And when I said "no OSes support it particularly well" I meant the x86 SMT implementation, rather than SMT in general. As Rod pointed out, AIX seems to have decent support and Power has a very nice implementation, and the same is probably true for at least one other OS/architecture implementation. > As Rod Taylor pointed out off-list, IBM's SMT implementation on the > Power5 is vastly superior. Though he's also just told me that Sun > is beating IBM on price/performance for his workload, so who knows > how reliable a chap he is... ;-) :) Cheers, Steve