pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum verbose relations reporting
10+ messages / 4 participants
[nested] [flat]
* vacuum verbose relations reporting
@ 2008-08-26 19:09 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2008-08-26 19:09 UTC (permalink / raw)
To: pgsql-admin
Looks like vacuum verbose doesn't report the number of relations necessary on
version 8.2.7. It just reports the max_fsm_relations value instead:
INFO: free space map contains 113311 pages in 10000 relations
DETAIL: A total of 252816 page slots are in use (including overhead).
252816 page slots are required to track all free space.
Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
NOTICE: max_fsm_relations(10000) equals the number of relations checked
HINT: You have at least 10000 relations. Consider increasing the
configuration parameter "max_fsm_relations".
VACUUM
postgres=# show server_version;
server_version
----------------
8.2.7
Does it do this in more recent versions of 8.2 or 8.3?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-26 19:20 Carol Walter <walterc@indiana.edu>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Carol Walter @ 2008-08-26 19:20 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin
If I understand your question, you might need to turn up
log_min_messages to debug1 otherwise you won't see the relations. I
had the system set to autovacuum and I couldn't see that it was doing
it until I made this change.
Carol
On Aug 26, 2008, at 3:09 PM, Jeff Frost wrote:
> Looks like vacuum verbose doesn't report the number of relations
> necessary on version 8.2.7. It just reports the max_fsm_relations
> value instead:
>
> INFO: free space map contains 113311 pages in 10000 relations
> DETAIL: A total of 252816 page slots are in use (including overhead).
> 252816 page slots are required to track all free space.
> Current limits are: 600000 page slots, 10000 relations, using 4587
> kB.
> NOTICE: max_fsm_relations(10000) equals the number of relations
> checked
> HINT: You have at least 10000 relations. Consider increasing the
> configuration parameter "max_fsm_relations".
> VACUUM
>
> postgres=# show server_version;
> server_version
> ----------------
> 8.2.7
>
> Does it do this in more recent versions of 8.2 or 8.3?
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 916-647-6411 FAX: 916-405-4032
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-26 19:32 Jeff Frost <jeff@frostconsultingllc.com>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2008-08-26 19:32 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Tue, 26 Aug 2008, Carol Walter wrote:
> On Aug 26, 2008, at 3:09 PM, Jeff Frost wrote:
>
>> Looks like vacuum verbose doesn't report the number of relations necessary
>> on version 8.2.7. It just reports the max_fsm_relations value instead:
>>
>> INFO: free space map contains 113311 pages in 10000 relations
>> DETAIL: A total of 252816 page slots are in use (including overhead).
>> 252816 page slots are required to track all free space.
>> Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
>> NOTICE: max_fsm_relations(10000) equals the number of relations checked
>> HINT: You have at least 10000 relations. Consider increasing the
>> configuration parameter "max_fsm_relations".
>> VACUUM
>>
>> postgres=# show server_version;
>> server_version
>> ----------------
>> 8.2.7
>>
>> Does it do this in more recent versions of 8.2 or 8.3?
> If I understand your question, you might need to turn up log_min_messages to
> debug1 otherwise you won't see the relations. I had the system set to
> autovacuum and I couldn't see that it was doing it until I made this change.
>
Hi Carol,
No I actually mean that this part:
>> Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
>> NOTICE: max_fsm_relations(10000) equals the number of relations checked
>> HINT: You have at least 10000 relations. Consider increasing the
Just tells me that I'm equal or greater than max_fsm_relations. But vacuum
verbose does tell me how many page_slots are in use even when it's greater
than max_fsm_pages, so you know what value to use in postgresql.conf for the
fsm settings.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-26 23:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2008-08-26 23:48 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
Jeff Frost <jeff@frostconsultingllc.com> writes:
> No I actually mean that this part:
> Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
> NOTICE: max_fsm_relations(10000) equals the number of relations checked
> HINT: You have at least 10000 relations. Consider increasing the
> Just tells me that I'm equal or greater than max_fsm_relations.
Yeah, because that's all that can be told from the contents of the
shared free space map: it's full, but we have no idea how many other
tables might have been competing for space in it.
> But vacuum verbose does tell me how many page_slots are in use even
> when it's greater than max_fsm_pages, so you know what value to use in
> postgresql.conf for the fsm settings.
Well, no :-(. What that number is is the number of page slots that the
relations that are in the FSM would have liked to have --- we have the
"request" size as well as the "allocated" size for each such relation.
We have no idea how many slots the relations that aren't in FSM might
have liked to have.
This whole design is pretty awful, of course (I can say that because it
was my design :-(). There is work in progress to make the fixed-size
FSM go away entirely in 8.4, which will certainly be a boon to DBAs.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-27 01:21 Jeff Frost <jeff@frostconsultingllc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2008-08-27 01:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
On Tue, 26 Aug 2008, Tom Lane wrote:
> Jeff Frost <jeff@frostconsultingllc.com> writes:
>> No I actually mean that this part:
>> Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
>> NOTICE: max_fsm_relations(10000) equals the number of relations checked
>> HINT: You have at least 10000 relations. Consider increasing the
>
>> Just tells me that I'm equal or greater than max_fsm_relations.
>
> Yeah, because that's all that can be told from the contents of the
> shared free space map: it's full, but we have no idea how many other
> tables might have been competing for space in it.
>
>> But vacuum verbose does tell me how many page_slots are in use even
>> when it's greater than max_fsm_pages, so you know what value to use in
>> postgresql.conf for the fsm settings.
>
> Well, no :-(. What that number is is the number of page slots that the
> relations that are in the FSM would have liked to have --- we have the
> "request" size as well as the "allocated" size for each such relation.
> We have no idea how many slots the relations that aren't in FSM might
> have liked to have.
>
> This whole design is pretty awful, of course (I can say that because it
> was my design :-(). There is work in progress to make the fixed-size
> FSM go away entirely in 8.4, which will certainly be a boon to DBAs.
Tom, is there an easy (or hard) way to count relations from all DBs by using
the system catalogs?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-27 01:36 Alvaro Herrera <alvherre@commandprompt.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2008-08-27 01:36 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Carol Walter <walterc@indiana.edu>; pgsql-admin
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).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-27 06:02 Jeff Frost <jeff@frostconsultingllc.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2008-08-27 06:02 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Carol Walter <walterc@indiana.edu>; pgsql-admin
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
Thanks folks!
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-31 16:18 Jeff Frost <jeff@frostconsultingllc.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeff Frost @ 2008-08-31 16:18 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-31 16:31 Alvaro Herrera <alvherre@commandprompt.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2008-08-31 16:31 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
Jeff Frost wrote:
> 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
Well, what this means is that not every single table has useful free
space ...
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: vacuum verbose relations reporting
@ 2008-08-31 16:46 Jeff Frost <jeff@frostconsultingllc.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jeff Frost @ 2008-08-31 16:46 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Sun, 31 Aug 2008, Alvaro Herrera wrote:
> Jeff Frost wrote:
>
>> 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
>
> Well, what this means is that not every single table has useful free
> space ...
Ohh...naturally. So, max_fsm_relations only cares about relations that have
free space to track, and not all relations. So, is there a way to compute
the number of relatinos with useful free space?
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-08-31 16:46 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-26 19:09 vacuum verbose relations reporting Jeff Frost <jeff@frostconsultingllc.com>
2008-08-26 19:20 ` Carol Walter <walterc@indiana.edu>
2008-08-26 19:32 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-08-26 23:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-27 01:21 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-08-27 01:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-08-27 06:02 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-08-31 16:18 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-08-31 16:31 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-08-31 16:46 ` Jeff Frost <jeff@frostconsultingllc.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