From: Jeff Frost <jeff@frostconsultingllc.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuum verbose relations reporting
Date: Sun, 31 Aug 2008 09:18:23 -0700
Message-ID: <48BAC44F.6090909@frostconsultingllc.com> (raw)
In-Reply-To: <48B4EDF7.9080505@frostconsultingllc.com>
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>
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
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-admin@postgresql.org
Cc: jeff@frostconsultingllc.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us
Subject: Re: vacuum verbose relations reporting
In-Reply-To: <48BAC44F.6090909@frostconsultingllc.com>
* 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