pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedlow memory usage reported by 'top' indicates poor tuning?
3+ messages / 2 participants
[nested] [flat]
* low memory usage reported by 'top' indicates poor tuning?
@ 2007-02-26 16:52 Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Stosberg @ 2007-02-26 16:52 UTC (permalink / raw)
To: pgsql-performance
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:
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: low memory usage reported by 'top' indicates poor tuning?
@ 2007-02-26 17:08 Joshua D. Drake <jd@commandprompt.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joshua D. Drake @ 2007-02-26 17:08 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
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:
1. What version of PostgreSQL.
2. What operating system -- scratch , I see freebsd
3. How big is your pg_dump in comparison to the pg_database_size()
4. What type of raid do you have?
5. What is your work_mem set to?
6. What about effective_cache_size?
7. Do you analyze? How often?
>
> 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
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: low memory usage reported by 'top' indicates poor tuning?
@ 2007-02-26 17:26 Mark Stosberg <mark@summersault.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Stosberg @ 2007-02-26 17:26 UTC (permalink / raw)
To: 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
>>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-26 17:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-26 16:52 low memory usage reported by 'top' indicates poor tuning? Mark Stosberg <mark@summersault.com>
2007-02-26 17:08 ` Joshua D. Drake <jd@commandprompt.com>
2007-02-26 17:26 ` Mark Stosberg <mark@summersault.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox