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
[arcs...@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:
[arcs...@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: ib...@noao.edu
To: pgsql-general@postgresql.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: ib...@noao.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: ib...@noao.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@postgresql.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: ib...@noao.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general