X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 49D4D14B2881 for ; Tue, 10 Aug 2004 22:06:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48137-02 for ; Wed, 11 Aug 2004 01:06:53 +0000 (GMT) Received: from mail.gotfrag.com (unknown [66.208.110.11]) by svr1.postgresql.org (Postfix) with ESMTP id A4BDC14B2878 for ; Tue, 10 Aug 2004 22:06:44 -0300 (ADT) Received: from jcoene2 (roc-66-66-149-175.rochester.rr.com [66.66.149.175]) by mail.gotfrag.com (8.12.10/8.12.8) with ESMTP id i7B16b0E072661; Tue, 10 Aug 2004 21:06:37 -0400 (EDT) (envelope-from jcoene@gotfrag.com) Message-Id: <200408110106.i7B16b0E072661@mail.gotfrag.com> From: "Jason Coene" To: "'Rod Taylor'" Cc: "'Postgresql Performance'" Subject: Re: Hardware upgrade for a high-traffic database Date: Tue, 10 Aug 2004 21:06:37 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2096 In-Reply-To: <1092179211.11635.30.camel@jester> Thread-Index: AcR/Lr47tKJaxYJUST64s429yuXv/gADnnNw X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/136 X-Sequence-Number: 7772 Hi Rod, Actually, we're already using a substantial caching system in code for nearly all pages delivered - we've exhausted that option. Our system uses a login/session table for about 1/8 of our page views (those visitors who are logged in), and has tracking features. While I'd love to scrap them and give the database server a vacation, it's a requirement for us. You're correct about the query caching (stored in memory) being used - most of our queries are run once and then come from memory (or, based on speed of consecutive executions, that seems to be the case). Once a user hits a page for the first time in an hour or so, it seems to cache their session query. The issue that I think we're seeing is that the performance on the 3Ware RAID is quite bad, watching FreeBSD systat will show it at "100% busy" at around "3.5 MB/s". When it needs to seek across a table (for, say, an aggregate function - typically a COUNT()), it slows the entire server down while working on the disk. Additionally, VACUUM's make the server practically useless. We have indexes on everything that's used in queries, and the planner is using them. The server has 2GB of physical memory, however it's only uses between 130MB and 200MB of it. Postgres is the only application running on the server. Our pertinent settings look like this: max_connections = 512 shared_buffers = 20000 sort_mem = 2000 vacuum_mem = 20000 effective_cache_size = 300000 fsync = false wal_sync_method = fsync wal_buffers = 32 checkpoint_segments = 2 checkpoint_timeout = 30 commit_delay = 10000 Typically, we don't use anywhere near the 512 connections - however there are peak hours where we come close, and other times that we eclipse it and run out (should some connections become serialized due to a slowdown). It's not something that we can comfortably lower. The non-standard checkpoint settings have helped making it less likely that a large (in disk time) query will conflict with a checkpoint write. I'm a programmer - definitely not a DBA by any stretch - though I am forced into the role. From reading this list, it seems to me that our settings are reasonable given our usage, and that a disk upgrade is likely in order. I'd love to hear any suggestions. Thanks, Jason -----Original Message----- From: Rod Taylor [mailto:pg@rbt.ca] Sent: Tuesday, August 10, 2004 7:07 PM To: Jason Coene Cc: Postgresql Performance Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database > Our database is about 20GB on disk, we have some quite large tables - 2M > rows with TEXT fields in a sample table, accessed constantly. We average > about 4,000 - 5,000 queries per second - all from web traffic. As you can 99% is reads? and probably the same data over and over again? You might want to think about a small code change to cache sections of page output in memory for the most commonly generated pages (there are usually 3 or 4 that account for 25% to 50% of web traffic -- starting pages). The fact you're getting 5k queries/second off IDE drives tells me most of the active data is in memory -- so your actual working data set is probably quite small (less than 10% of the 20GB). If the above is all true (mostly reads, smallish dataset, etc.) and the database is not growing very quickly, you might want to look into RAM and RAM bandwidth over disk. An Opteron with 8GB ram using the same old IDE drives. Get a mobo with a SCSI raid controller in it, so the disk component can be upgraded in the future (when necessary).