DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:subject:from:reply-to:to:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding;
	b=K8sHrxvxsUvuycan8wTqp+nHSP78XAdc0W6iiKez4Zy3+CspfGk+E/l5zQECDiOmStFcMjC2Fk+v+zu1PZJZF24J+nq9NMdbI9hn//nnmMf6shms1YxrX07ZDXP1PJYSJV75GHpuwIptRUqqgRKiTRB6LmN+kO6qRM8Nqobl+Is=
Subject: Re: Linux mis-reporting memory
From: Adam Tauno Williams <adamtaunowilliams@gmail.com>
Reply-To: adamtaunowilliams@gmail.com
To: pgsql-performance@postgresql.org
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>
Content-Type: text/plain
Date: Tue, 02 Oct 2007 14:37:30 -0400
Message-Id: <1191350250.5466.6.camel@aleph.wmmi.net>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

> >> 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