[check_postgres] FSM checks

[Jeff Frost]

On Fri, 26 Sep 2008, Robert Treat wrote:

> On Friday 26 September 2008 00:56:45 Robert Treat wrote:
>> On Thursday 25 September 2008 20:03:56 Jeff Frost wrote:
>>> The new FSM checks don't seem to agree with vacuum verbose:
>>>
>>> CPU 0.00s/0.00u sec elapsed 0.01 sec.
>>> INFO:  free space map contains 214803 pages in 849 relations
>>> DETAIL:  A total of 226592 page slots are in use (including overhead).
>>> 226592 page slots are required to track all free space.
>>> Current limits are:  2000000 page slots, 1000 relations, using 11823 kB.
>>>
>>> discord:~/git/check_postgres $ ./check_postgres.pl --action fsm_pages
>>> --db=postgres POSTGRES_FSM_PAGES WARNING:  fsm page slots used: 1722880
>>> of 2000000 (86%) | time=0.01
>>>
>>> POSTGRES_FSM_RELATIONS WARNING:  fsm relations used: 855 of 1000 (86%) |
>>> time=0.03
>>>
>>> The relations seems pretty close, but the pages are pretty far off:
>>>
>>> 226592
>>> vs
>>> 1722880
>>
>> What version of postgres are you on?
>>
>> Can you give me the output of the following query:
>> select sum(interestingpages), sum(storedpages),
>> sum(greatest(interestingpages,storedpages)) from pg_freespacemap_relations;
>
> ok, I think I found the issue... can you try this query and see if it gives
> you better results:
>
> select sumrequests, numrels, chunkpages, ((sumrequests+numrels)*chunkpages) as
> freepageslots from (select sum(case when avgrequest is null then
> interestingpages/32 else interestingpages/16 end) as sumrequests,
> count(relfilenode) as numrels, 16 as chunkpages from
> pg_freespacemap_relations ) "freespace.c" ;

Hey Robert, sorry for the slow response, I'm out of town this weekend with 
limited email access.

This is on 8.3.4.  Here are the results of the queries you requested:

postgres=# show server_version;
  server_version
----------------
  8.3.4
(1 row)

postgres=# select sum(interestingpages), sum(storedpages),
postgres-# sum(greatest(interestingpages,storedpages)) from 
pg_freespacemap_relations;
   sum   |  sum   |  sum
--------+--------+--------
  214803 | 214803 | 214803
(1 row)

postgres=# select sumrequests, numrels, chunkpages, ((sumrequests+numrels)*chunkpages)
postgres-# as
postgres-# freepageslots from (select sum(case when avgrequest is null then
postgres(# interestingpages/32 else interestingpages/16 end) as sumrequests,
postgres(# count(relfilenode) as numrels, 16 as chunkpages from
postgres(# pg_freespacemap_relations ) "freespace.c" ;
  sumrequests | numrels | chunkpages | freepageslots
-------------+---------+------------+---------------
        13319 |     855 |         16 |        226784
(1 row)

Let me know how else I can help.  This is an exciting addition!

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