X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EF91FD1D272 for ; Tue, 11 May 2004 19:02:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 52816-03 for ; Tue, 11 May 2004 19:01:38 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id B1F59D1D176 for ; Tue, 11 May 2004 19:01:30 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 473163D1F8; Tue, 11 May 2004 19:01:33 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 2F6F83D1C9 for ; Tue, 11 May 2004 19:01:33 -0300 (ADT) X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.3) with LMTP; Tue, 11 May 2004 18:41:21 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 63E4E3CC7B for ; Tue, 11 May 2004 18:39:20 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Tue, 11 May 2004 18:39:20 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.3) with LMTP; Tue, 11 May 2004 18:37:17 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D4C5FD1D35F for ; Tue, 11 May 2004 18:37:16 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 46454-03 for ; Tue, 11 May 2004 18:36:46 -0300 (ADT) X-Received: from neomail03.traderonline.com (email.traderonline.com [65.213.231.10]) by svr1.postgresql.org (Postfix) with ESMTP id BED6AD1BA75 for ; Tue, 11 May 2004 18:36:36 -0300 (ADT) X-Received: from dyounger-IBM (65-86-118-210.client.dsl.net [65.86.118.210]) by neomail03.traderonline.com (8.12.8/8.12.8) with ESMTP id i4BLaWAm011454 for ; Tue, 11 May 2004 17:36:32 -0400 Message-Id: <6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com> X-Sender: dylists@mail.ptd.net (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.0.1.1 Date: Tue, 11 May 2004 17:36:31 -0400 To: psql-performance@postgresql.org From: Doug Y Subject: Clarification on some settings Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Tue, 11 May 2004 19:01:11 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-performance@postgresql.org ReSent-Subject: Clarification on some settings ReSent-Message-ID: <20040511190111.J34032@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/92 X-Sequence-Number: 6923 Hello, I've been having some performance issues with a DB I use. I'm trying to come up with some performance recommendations to send to the "adminstrator". Hardware: CPU0: Pentium III (Coppermine) 1000MHz (256k cache) CPU1: Pentium III (Coppermine) 1000MHz (256k cache) Memory: 3863468 kB (4 GB) OS: Red Hat Linux release 7.2 (Enigma) Kernel: 2.4.9-31smp I/O I believe is a 3-disk raid 5. /proc/sys/kernel/shmmax and /proc/sys/kernel/shmall were set to 2G Postgres version: 7.3.4 I know its a bit dated, and upgrades are planned, but several months out. Load average seems to hover between 1.0 and 5.0-ish during peak hours. CPU seems to be the limiting factor but I'm not positive (cpu utilization seems to be 40-50%). We have 2 of those set up as the back end to 3 web-servers each... supposedly load-balanced, but one of the 2 dbs consistently has higher load. We have a home-grown replication system that keeps them in sync with each other... peer to peer (master/master). The DB schema is, well to put it nicely... not exactly normalized. No constraints to speak of except for the requisite not-nulls on the primary keys (many of which are compound). Keys are mostly varchar(256) fields. Ok for what I'm uncertain of... shared_buffers: According to http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Its more of a staging area and more isn't necessarily better. That psql relies on the OS to cache data for later use. But according to http://www.ca.postgresql.org/docs/momjian/hw_performance/node3.html its where psql caches previous data for queries because the OS cache is slower, and should be as big as possible without causing swap. Those seem to be conflicting statements. In our case, the "administrator" kept increasing this until performance seemed to increase, which means its now 250000 (x 8k is 2G). Is this just a staging area for data waiting to move to the OS cache, or is this really the area that psql caches its data? effective_cache_size: Again, according to the Varlena guide this tells psql how much system memory is available for it to do its work in. until recently, this was set at the default value of 1000. It was just recently increased to 180000 (1.5G) according to http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html it should be about 25% of memory? Finally sort_mem: Was until recently left at the default of 1000. Is now 16000. Increasing the effective cache and sort mem didn't seem to make much of a difference. I'm guessing the eff cache was probably raised a bit too much, and shared_buffers is way to high. What can I do to help determine what the proper settings should be and/or look at other possible choke points. What should I look for in iostat, mpstat, or vmstat as red flags that cpu, memory, or i/o bound? DB maintenance wise, I don't believe they were running vacuum full until I told them a few months ago that regular vacuum analyze no longer cleans out dead tuples. Now normal vac is run daily, vac full weekly (supposedly). How can I tell from the output of vacuum if the vac fulls aren't being done, or not done often enough? Or from the system tables, what can I read? Is there anywhere else I can look for possible clues? I have access to the DB super-user, but not the system root/user. Thank you for your time. Please let me know any help or suggestions you may have. Unfortunately upgrading postgres, OS, kernel, or re-writing schema is most likely not an option.