X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id B18F8CC0AB for ; Mon, 14 Jul 2003 17:52:48 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 84687-10 for ; Mon, 14 Jul 2003 17:53:12 +0000 (GMT) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id A192FCC0FA for ; Mon, 14 Jul 2003 14:52:46 -0300 (ADT) Received: from nick (bilbo.ontko.com [199.164.165.101]) by shire.ontko.com (8.12.3/8.12.3/Debian-6.4) with SMTP id h6EHrC0P012779 for ; Mon, 14 Jul 2003 12:53:13 -0500 Reply-To: From: "Nick Fankhauser" To: "Pgsql-Performance@Postgresql. Org" Subject: Sanity check requested Date: Mon, 14 Jul 2003 12:51:44 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/225 X-Sequence-Number: 2518 Hi folks- For some time, we've been running Postgres with the default configuration & getting adequate performance, but the time has come to tune a bit, so I've been lurking on this list & gathering notes. Now I'm about ready to make a change & would appreciate it if a few more experienced folks could comment on whether I appear to be heading in the right direction- Here's what I'm planning: Increase SHMMAX and SHMALL in my kernel to 134217728 (128MB) Increase shared_buffers to 8192 (64MB) Increase sort_mem to 16384 (16MB) Increase effective_cache_size to 65536 (1/2 GB) Here's the environment: The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the DB on SCSI RAID drives. The server runs only PostgreSQL The database size is about 8GB, with the largest table 2.5 GB, and the two most commonly queried tables at 1 GB each. The two most commonly queried tables are usually queried based on a non-unique indexed varchar field typically 20 chars long. The query is a "like" on people's names with trailing %, so this often gets pushed to seq scan or returns several thousand records. (As when someone searches on 'Jones%'. Records from the largest table are always accessed via unique index in groups of 20 or less. The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual processor support) The PostgreSQL version is 7.3.2 We typically have about 30 interactive users on the DB, but they're using a shared connection pool of 16. Our main problem appears to be when one of the users fires up a large query and creates a log-jam with resources. My reasoning is that I'll increase shared_buffers based on anecdotal recommendations I've seen on this list to 64MB. I'll boost the OS SHMMAX to twice that value to allow adequate room for other shared memory needs, thus reserving 128MB. Of the remaining memory, 256MB goes to 16 connections * 16MB sort space, if I leave about 128 MB for headroom, then 1/2 GB should be left available for the effective cache size. Any thoughts? Is this a sane plan? Are there other parameters I should consider changing first? Thanks! -Nick --------------------------------------------------------------------- Nick Fankhauser nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ --------------------------------------------------------------------- Nick Fankhauser nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/