Skip site navigation (1) Skip section navigation (2)

Re: low memory usage reported by 'top' indicates poor tuning?

From: Mark Stosberg <mark(at)summersault(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: low memory usage reported by 'top' indicates poor tuning?
Date: 2007-02-26 17:26:23
Message-ID: erv58u$2i9j$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
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
>>
> 
> 

In response to

pgsql-performance by date

Next:From: Dave CramerDate: 2007-02-26 17:28:37
Subject: Re: does prepareThreshold work? forced to use old driver
Previous:From: Joshua D. DrakeDate: 2007-02-26 17:08:03
Subject: Re: low memory usage reported by 'top' indicates poor tuning?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group