Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CC87B475B99 for ; Thu, 2 Jan 2003 17:07:42 -0500 (EST) Received: from rh72.home.ee (unknown [194.204.44.121]) by postgresql.org (Postfix) with ESMTP id 5D037475B8E for ; Thu, 2 Jan 2003 17:07:41 -0500 (EST) Received: from rh72.home.ee (localhost.localdomain [127.0.0.1]) by rh72.home.ee (8.12.5/8.12.5) with ESMTP id h02M7aRX002831; Fri, 3 Jan 2003 03:07:37 +0500 Received: (from hannu@localhost) by rh72.home.ee (8.12.5/8.12.5/Submit) id h02M7Zrp002829; Fri, 3 Jan 2003 03:07:35 +0500 X-Authentication-Warning: rh72.home.ee: hannu set sender to hannu@tm.ee using -f Subject: Re: Question on hardware & server capacity From: Hannu Krosing To: Steve Wolfe Cc: pgsql-performance@postgresql.org In-Reply-To: <008701c2b286$4c9c2ae0$88693fd1@WEASEL> References: <008701c2b286$4c9c2ae0$88693fd1@WEASEL> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1041545254.2176.28.camel@rh72.home.ee> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 03 Jan 2003 03:07:34 +0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/16 X-Sequence-Number: 663 Steve Wolfe kirjutas N, 02.01.2003 kell 22:42: > Well, our current database server is getting tremendously loaded, and > right now there isn't a clear-cut choice as to an upgrade path - at least > not within the commodity hardware market. Have you optimized your queries to max ? Often one or two of the queries take most of resources and starve others. > The machine is a dual AthlonMP 2000, with 2 gigs of RAM. the loads on > the machine are getting out of hand, and performance is noticeably slowed. > 'top' shows the CPU's as being anywhere from 30% to 50% idle, with (on > average) 5-10 postmasters in the "non-idle" state. 'vmstat' shows bi/bo > pegged at zero (copious quantities of disk cache, fsync turned off), Could there be some unnecessary trashing between OS and PG caches ? How could this be detected ? > interrupts fluctuating between 200 and 1,000 per second (avg. is approx > 400), context switches between 1300 and 4500 (avg. is approx 2300). I > logged some queries, and found that in an average second, the machine > forks off 10 new backends, and responds to 50 selects and 3 updates. What are the average times for query responses ? Will running the same queries (the ones from the logs) serially run faster/slower/at the same speed ? Do you have some triggers on updates - I have occasionally found them to be real performance killers. Also - if memory bandwidth is the issue, you could tweak the parameters so that PG will prefer index scans more often - there are rumors that under heavy loads it is often better to use more index scans due to possible smaller memory/buffer use, even if they would be slower for only one or two backends. > My feelings are that the machine is being swamped by both the number of > context switches and the I/O, most likely the memory bandwidth. I'm > working on implementing some connection pooling to reduce the number of > new backends forked off, but there's not much I can do about the sheer > volume (or cost) of queries. You could try to replicate the updates (one master - multiple slaves) and distribute the selects. I guess this is what current postgreSQL state-of-the-art already lets you do with reasonable effort. > Now, if quad-Hammers were here, I'd simply throw hardware at it. > Unfortunately, they're not. Yes, it's BAD if your business grows faster than Moores law ;-p > So far, about the only commodity-level answer > I can think of would be a dual P4 Xeon, with the 533 MHz bus, and > dual-channel DDR memory. That would give each processor approximately > double the memory bandwidth over what we're currently running. > > I'm fairly sure that would at least help lower the load, but I'm not > sure by how much. If anyone has run testing under similar platforms, I'd > love to hear of the performance difference. How big is the dataset ? What kinds of queries ? I could perhaps run some quick tests on quad Xeon 1.40GHz , 2GB before this box goes to production sometime early next week. It is a RedHat AS2.1 box with rh-postgresql-7.2.3-1_as21. # hdparm -tT /dev/sda /dev/sda: Timing buffer-cache reads: 128 MB in 0.39 seconds =328.21 MB/sec Timing buffered disk reads: 64 MB in 1.97 seconds = 32.49 MB/sec > If this is going to chop the > loads in half, I'll do it. If it's only going to improve it by 10% or so, > I'm not going to waste the money. -- Hannu Krosing