Re: number of relations reported by vacuumdb -av

System: MacOS XServer, 4GB RAM
PostgreSQL-8.1.9: the MCAT database 7.6GB big has 525 relations

Hi Martin

The current setting is:
> max_fsm_pages = 200000 # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 2000 # min 100, ~70 bytes each

I reset it yesterday and bounced the postmaster, but value needed for
'max_fsm_pages' continues go grow, note result from vacuumdb on Apr 18 15:15

> [arcsoft(at)dsan3 data]$ cat /tmp/dovacuumdb-pm.log
> start vacuumdb -z MCAT
> 2009-04-18 15:00:00
> NOTICE: number of page slots needed (270944) exceeds max_fsm_pages (200000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 270944.
> VACUUM
> vacuumdb completed
> 2009-04-18 15:15:07

The activity on this database is almost exclusively INSERTS averaging
between 2500-3500 INSERTS daily. I am vacuuming twice a day at 9AM and
again at 3PM, and the number of page_slots needed increase with each
vacuum.

The postmaster contains two other active databases:

JBoss db (mostly message queues) 1.5GB, 208 relations
dsmixed 82 MB 214 relations

The last vacuumdb log for Jboss also showed max_fsm_pages was exceeded:
> [arcsoft(at)dsan3 data]$ cat /tmp/dovacuumdb_jboss.log
> start vacuumdb -z jboss
> 2009-04-18 11:45:00
> NOTICE: number of page slots needed (271856) exceeds max_fsm_pages (200000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 271856.
> VACUUM
> vacuumdb completed
> 2009-04-18 11:45:35

But the vacuumdb log for 'dsmixed' was ok.

What type of statistics do I need to collect to set these two parameters
to a level I do not have to bounce the postmaster daily? Or is it
safe to just double the max_fsm_page value to 500000 or possibly 1000000?

--irene

Martin Gainty wrote:
> Good Morning Irene
>
> could you verify the requirement to set
> max_fsm_pages (integer) to 16 times new value of 'max_fsm_relations'
>
> Thanks!
> Martin Gainty
> ______________________________________________
> Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité
> This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents.
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
>
>
>
>
>
>
>> Date: Sat, 18 Apr 2009 03:23:49 -0700
>> From: ibarg(at)noao(dot)edu
>> To: pgsql-general(at)postgresql(dot)org
>> Subject: Re: [GENERAL] number of relations reported by vacuumdb -av
>>
>> never mind....I found the answer in the archives.postgresql.org. The
>> answer is 'yes' I use the sum of relations from all of the databases. So
>> I have reset 'max_fsm_relations' from 1000 to 2000.
>>
>> Irene Barg wrote:
>>> Hi,
>>>
>>> I have a PostgreSQL installation with 8 databases (counting postgres,
>>> template0, and template1). I run 'vacuumdb -z' daily on 3 of the largest
>>> user databases. The vacuumdb logs show the 'max_fsm_pages' need to be
>>> increased with almost each vacuum. So I did a 'vacuumdb -av' on all the
>>> db's:
>>>> INFO: free space map contains 81016 pages in 100 relations
>>>> DETAIL: A total of 80000 page slots are in use (including overhead).
>>>> 187792 page slots are required to track all free space.
>>>> Current limits are: 80000 page slots, 1000 relations, using 534 KB.
>>>> NOTICE: number of page slots needed (187792) exceeds max_fsm_pages
>>>> (80000)
>>>> HINT: Consider increasing the configuration parameter "max_fsm_pages"
>>>> to a value over 187792.
>>>> VACUUM
>>> I have a couple questions.
>>>
>>> 1) I can increase 'max_fsm_pages' from 80K to 200K, but why does it keep
>>> growing?
>>>
>>> The main database sees on average 2500-5000 rows inserted per day, and
>>> deletes are relatively small (although I don't have stats on deletes).
>>>
>>> 2) How is '100 relations' getting calculated?
>>>
>>> If I connect to each one of my 8 db's and do:
>>>
>>> select count(*) from pg_class;
>>>
>>> The total number of relations is 1725. So shouldn't I increase
>>> 'max_fsm_relations' from 1000 to 1725?
>>>
>>> Thank you in advance.
>>>
>>> -- irene
>>> ---------------------------------------------------------------------
>>> Irene Barg Email: ibarg(at)noao(dot)edu
>>> NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg
>>> 950 N. Cherry Ave. Voice: 520-318-8273
>>> Tucson, AZ 85726 USA FAX: 520-318-8360
>>> ---------------------------------------------------------------------
>>>
>> --
>> ---------------------------------------------------------------------
>> Irene Barg Email: ibarg(at)noao(dot)edu
>> NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg
>> 950 N. Cherry Ave. Voice: 520-318-8273
>> Tucson, AZ 85726 USA FAX: 520-318-8360
>> ---------------------------------------------------------------------
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> _________________________________________________________________
> Windows Live™: Keep your life in sync.
> http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009

--
---------------------------------------------------------------------
Irene Barg Email: ibarg(at)noao(dot)edu
NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg
950 N. Cherry Ave. Voice: 520-318-8273
Tucson, AZ 85726 USA FAX: 520-318-8360
---------------------------------------------------------------------