Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3DCDA9FB341 for ; Mon, 26 Feb 2007 13:27:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 78572-10 for ; Mon, 26 Feb 2007 13:26:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 67DB29FB31E for ; Mon, 26 Feb 2007 13:26:56 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id AEAEA30AF3; Mon, 26 Feb 2007 18:26:54 +0100 (MET) From: Mark Stosberg X-Newsgroups: pgsql.performance Subject: Re: low memory usage reported by 'top' indicates poor tuning? Date: Mon, 26 Feb 2007 12:26:23 -0500 Organization: Hub.Org Networking Services Lines: 91 Message-ID: References: <45E313F3.9070605@commandprompt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5.0.8 (X11/20061109) In-Reply-To: <45E313F3.9070605@commandprompt.com> To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.265 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/300 X-Sequence-Number: 23354 Joshua D. Drake wrote: > Mark Stosberg wrote: >> Hello, >> >> I'm trying to make sense of the memory usage reported by 'top', compared >> to what "pg_database_size" shows. Here's one result:' > > > You are missing the most important parts of the equation: Thanks for your patience, Joshua. I'm new at performance tuning. > 1. What version of PostgreSQL. Now, 8.1. We are evaluating 8.2 currently and could potentially upgrade soon. > 2. What operating system -- scratch , I see freebsd > 3. How big is your pg_dump in comparison to the pg_database_size() Using the compressed, custom format: 360M. It was recently 1.2G due to logging tables that were pruned recently. These tables are only inserted into and are not otherwise accessed by the application. > 4. What type of raid do you have? RAID-1. > 5. What is your work_mem set to? 1024 (left at the default) > 6. What about effective_cache_size? 1000 (default) For any other settings, it's probably the defaults, too. > 7. Do you analyze? How often? Once, nightly. I'm currently learning and experience with autovacuuming to see if there is a more optimal arrangement of autovacuuming + nightly cron vacuuming. A test on Friday was failure: Autovacuuming brought the application to a crawl, and with 8.1, I couldn't see what table it was stuck on. I had autovacuum_vacuum_cost_delay set to "10". Thanks again for your experienced help. Mark >> select pg_size_pretty(pg_database_size('production')); >> pg_size_pretty >> ---------------- >> 6573 MB >> >> Now, looking at memory use with "top", there is a lot memory that isn't >> being used on the system: >> >> Mem: 470M Active, 2064M Inact >> >> ( 3 Gigs RAM, total ). >> >> Overall performance is decent, so maybe there's no >> problem. However, I wonder if we've under-allocated memory to >> PostgreSQL. (This is a dedicated FreeBSD DB server). >> >> Some memory settings include: >> >> shared_buffers = 8192 (we have 450 connections) >> max_fsm_pages = 1250000 (we kept getting HINTs to bump it, so we did) >> >> Maybe we should be bumping up the "sort_mem" and "vacuum_mem" as well? >> >> I do sometimes see sorting and vacuuming as showing up as things I'd >> like to run faster. >> >> This list has been a great resource for performance tuning help, and I >> continue to appreciate your help. We've used PostgreSQL on every project >> we've had a choice on for the last 10 years. (Has it been that long?!) >> We've never regretted it once. >> >> Mark >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> > >