From: Adam Tauno Williams <adamtaunowilliams@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Linux mis-reporting memory
Date: Tue, 02 Oct 2007 14:37:30 -0400
Message-ID: <1191350250.5466.6.camel@aleph.wmmi.net> (raw)
In-Reply-To: <094E11EB-8F17-4C96-993A-4AD637404F91@decibel.org>
References: <20070920230400.GM95343@decibel.org>
<16213.1190329934@sss.pgh.pa.us>
<87r6ksa1jb.fsf@oxford.xeocode.com>
<1190363401.4661.116.camel@PCD12478>
<87r6ksuzf8.fsf@oxford.xeocode.com>
<094E11EB-8F17-4C96-993A-4AD637404F91@decibel.org>
> >> But then why is it not reporting that in the "Swap: used"
> >> section ? It
> >> only reports 42308k used swap.
> > Hm, good point.
> > The other possibility is that Postgres just hasn't even touched a
> > large part
> > of its shared buffers.
> Sorry for the late reply...
> No, this is on a very active database server; the working set is
> almost certainly larger than memory (probably by a fair margin :( ),
"almost certainly"
> and all of the shared buffers should be in use.
"should be"
It would be better to just check! :) The catalogs and informational
views will give you definitive answers to these quests.
> I'm leaning towards "top on linux == dumb".
I disagree, it just isn't the appropriate tool for the job. What top
tells you is lots of correct information, it just isn't the right
information.
For starters try -
SELECT
'HEAP:' || relname AS table_name,
(heap_blks_read + heap_blks_hit) AS heap_hits,
ROUND(((heap_blks_hit)::NUMERIC / (heap_blks_read + heap_blks_hit) *
100), 2) AS heap_buffer_percentage
FROM pg_statio_user_tables
WHERE (heap_blks_read + heap_blks_hit) > 0
UNION
SELECT
'TOAST:' || relname,
(toast_blks_read + toast_blks_hit),
ROUND(((toast_blks_hit)::NUMERIC / (toast_blks_read + toast_blks_hit) *
100), 2)
FROM pg_statio_user_tables
WHERE (toast_blks_read + toast_blks_hit) > 0
UNION
SELECT
'INDEX:' || relname,
(idx_blks_read + idx_blks_hit) AS heap_hits,
ROUND(((idx_blks_hit)::NUMERIC / (idx_blks_read + idx_blks_hit) * 100),
2)
FROM pg_statio_user_tables
WHERE (idx_blks_read + idx_blks_hit) > 0
--
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adamtaunowilliams@gmail.com
Subject: Re: Linux mis-reporting memory
In-Reply-To: <1191350250.5466.6.camel@aleph.wmmi.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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