Message-ID: <48BAC44F.6090909@frostconsultingllc.com>
Date: Sun, 31 Aug 2008 09:18:23 -0700
From: Jeff Frost <jeff@frostconsultingllc.com>
Organization: Frost Consulting, LLC
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: Alvaro Herrera <alvherre@commandprompt.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-admin@postgresql.org
Subject: Re: vacuum verbose relations reporting
References: 
 <Pine.LNX.4.64.0808261207140.5906@discord.home.frostconsultingllc.com>
	<E1DC2F12-02E8-4D82-9821-B9C7D01657D3@indiana.edu>
	<Pine.LNX.4.64.0808261230460.3638@discord.home.frostconsultingllc.com>
	<11312.1219794535@sss.pgh.pa.us>
	<Pine.LNX.4.64.0808261820360.3638@discord.home.frostconsultingllc.com>
	<20080827013612.GT4920@alvh.no-ip.org>
	<48B4EDF7.9080505@frostconsultingllc.com>
In-Reply-To: <48B4EDF7.9080505@frostconsultingllc.com>
Content-Type: multipart/alternative;
	boundary="------------000505040600050601020909"

This is a multi-part message in MIME format.
--------------000505040600050601020909
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


Jeff Frost wrote:
> Alvaro Herrera wrote:
>> Jeff Frost wrote:
>>
>>   
>>> Tom, is there an easy (or hard) way to count relations from all DBs by 
>>> using the system catalogs?
>>>     
>>
>> Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and
>> sum across all databases (you need to connect to each one).
>>
>> (Actually you only need to count indexes that are btrees, if you need
>> such a distinction.  Other indexes do not use the FSM as far as I know).
>>   
> Perfect, so here's a little script that does the trick then:
>
> #!/bin/sh
>
> PSQL=/usr/bin/psql
> DATABASES=$($PSQL -lt |  awk {'print $1'} | grep -v template0 )
> RELATIONS=0
>
> for DB in $DATABASES; do
>     RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select
> count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
> done
>
> echo $RELATIONS
>
I guess this isn't entirely accurate, as the above script returns 35883,
but vacuum verbose returns:

INFO:  free space map contains 111435 pages in 10005 relations

If I take out the toast tables and indexes, I get a result much closer
to what vacuum verbose returns: 10626  which might just be because the
vacuum verbose ran a few hours ago.

So, the question is, do the FSM settings take into account toast tables
and indexes as Alvaro suggested and vacuum verbose isn't properly
reporting on it?


-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032


--------------000505040600050601020909
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Jeff Frost wrote:
<blockquote cite="mid:48B4EDF7.9080505@frostconsultingllc.com"
 type="cite">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
Alvaro Herrera wrote:
  <blockquote cite="mid:20080827013612.GT4920@alvh.no-ip.org"
 type="cite">
    <pre wrap="">Jeff Frost wrote:

  </pre>
    <blockquote type="cite">
      <pre wrap="">Tom, is there an easy (or hard) way to count relations from all DBs by 
using the system catalogs?
    </pre>
    </blockquote>
    <pre wrap=""><!---->
Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and
sum across all databases (you need to connect to each one).

(Actually you only need to count indexes that are btrees, if you need
such a distinction.  Other indexes do not use the FSM as far as I know).
  </pre>
  </blockquote>
Perfect, so here's a little script that does the trick then:<br>
  <br>
#!/bin/sh<br>
  <br>
PSQL=/usr/bin/psql<br>
DATABASES=$($PSQL -lt |&nbsp; awk {'print $1'} | grep -v template0 )<br>
RELATIONS=0<br>
  <br>
for DB in $DATABASES; do<br>
&nbsp;&nbsp;&nbsp; RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select
count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))<br>
done<br>
  <br>
echo $RELATIONS<br>
  <br>
</blockquote>
I guess this isn't entirely accurate, as the above script returns
35883, but vacuum verbose returns: <br>
<br>
INFO:&nbsp; free space map contains 111435 pages in 10005 relations<br>
<br>
If I take out the toast tables and indexes, I get a result much closer
to what vacuum verbose returns: 10626&nbsp; which might just be because the
vacuum verbose ran a few hours ago. <br>
<br>
So, the question is, do the FSM settings take into account toast tables
and indexes as Alvaro suggested and vacuum verbose isn't properly
reporting on it?<br>
<br>
<br>
<pre class="moz-signature" cols="72">-- 
Jeff Frost, Owner 	<a class="moz-txt-link-rfc2396E" href="mailto:jeff@frostconsultingllc.com">&lt;jeff@frostconsultingllc.com&gt;</a>
Frost Consulting, LLC 	<a class="moz-txt-link-freetext" href="http://www.frostconsultingllc.com/">http://www.frostconsultingllc.com/</a>
Phone: 916-647-6411	FAX: 916-405-4032
</pre>
</body>
</html>

--------------000505040600050601020909--