pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedmax_fsm_pages
6+ messages / 6 participants
[nested] [flat]
* max_fsm_pages
@ 2003-09-04 14:26 Jouneau Luc <Luc.Jouneau@diamant.jouy.inra.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Jouneau Luc @ 2003-09-04 14:26 UTC (permalink / raw)
To: pgsql-admin
Hi everybody,
I'm trying to understand how vacuumdb and free space map work together for retrieving free spaces after delete or update statements.
max_fsm_pages and max_fsm_relations are set to default values (respectively 10000 and 100).
I do my tests on a table containing at the beginning 1931968 tuples stocked in 11944 pages :
INFO: Pages 11944: Changed 11944, Empty 0; Tup 1931968: Vac 0, Keep 0, UnUsed 0
I delete 1931936 tuples (letting 32 tuples left).
I thought the number of pages affected by this delete will overflow fsm storage capacity, but when I run a vacuumdb verbose analyze on this table,
I have the following log information :
INFO: Removed 1397914 tuples in 8641 pages.
CPU 0.76s/0.89u sec elapsed 25.72 sec.
INFO: Removed 534022 tuples in 3303 pages.
CPU 0.37s/0.29u sec elapsed 7.62 sec.
INFO: Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, UnUsed 0.
Total CPU 1.57s/1.90u sec elapsed 42.01 sec.
It seems that FSM traced all of my deletes since it mentions removes in 8641+3303=11944 pages (>10000 which was set for max_fsm_page).
Can someone explain me, where I'm wrong ?
My first aim was to know if max_fsm_pages was set for each table or for a whole database. Per table I suppose, but Is it a good supposition ?
Where is stored (on disk) this fsm ?
Thanks for your answers.
Luc
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: max_fsm_pages
@ 2003-09-04 23:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jouneau Luc <Luc.Jouneau@diamant.jouy.inra.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-09-04 23:24 UTC (permalink / raw)
To: Jouneau Luc <Luc.Jouneau@jouy.inra.fr>; +Cc: pgsql-admin
"Jouneau Luc" <Luc.Jouneau@jouy.inra.fr> writes:
> INFO: Pages 11944: Changed 11944, Empty 0; Tup 32: Vac 1931936, Keep 0, Un=
> Used 0.
> Total CPU 1.57s/1.90u sec elapsed 42.01 sec.
> It seems that FSM traced all of my deletes since it mentions removes in 864=
> 1+3303=3D11944 pages (>10000 which was set for max_fsm_page).
The numbers output by VACUUM don't tell you anything about how many of
those pages will actually be remembered in the FSM.
In practice, max_fsm_relations=100 is too small; we've bumped the
default to 1000 for 7.4.
> My first aim was to know if max_fsm_pages was set for each table or for a w=
> hole database.
Whole database. That default setting is very small, too (enough for
a database of maybe a couple hundred meg, at most).
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* max_fsm_pages
@ 2005-06-14 18:36 Dextra - Gustavo Bartz Guedes <gustavo-bartz@dextra.com.br>
0 siblings, 1 reply; 6+ messages in thread
From: Dextra - Gustavo Bartz Guedes @ 2005-06-14 18:36 UTC (permalink / raw)
To: pgsql-admin
Is the parameter max_fsm_pages cluster-wide or per-database?
Thanks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: max_fsm_pages
@ 2005-06-14 18:45 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Dextra - Gustavo Bartz Guedes <gustavo-bartz@dextra.com.br>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2005-06-14 18:45 UTC (permalink / raw)
To: Dextra - Gustavo Bartz Guedes <gustavo-bartz@dextra.com.br>; +Cc: pgsql-admin
Dextra - Gustavo Bartz Guedes wrote:
> Is the parameter max_fsm_pages cluster-wide or per-database?
cluster-wide.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
* max_fsm_pages
@ 2007-04-01 13:59 Abu Mushayeed <abumushayeed@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Abu Mushayeed @ 2007-04-01 13:59 UTC (permalink / raw)
To: pgsql-admin
PG DB: 8.1.3
12 DB in the cluster
When I do a full vacuum of the database cluster, at the end I am getting the message as follows:
psql:dba_task.sql:1: INFO: free space map contains 8033993 pages in 1023 relations
DETAIL: A total of 8000000 page slots are in use (including overhead).
8201952 page slots are required to track all free space.
Current limits are: 8000000 page slots, 32768 relations, using 49054 KB.
psql:dba_task.sql:1: NOTICE: number of page slots needed (8201952) exceeds max_fsm_pages (8000000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 8201952.
My questions are:
1. With out increasing the max_fsm_pages, can I bring down the need for fsm_pages by deleting unnecessary tables?
2. As, the database grows the need for max_fsm_pages grows too, what is the best practice?
3. As a new postgres DBA, how should one manage this parameter?
Thanks
Abu Mushayeed
---------------------------------
It's here! Your new message!
Get new email alerts with the free Yahoo! Toolbar.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: max_fsm_pages
@ 2007-04-02 09:55 Shoaib Mir <shoaibmir@gmail.com>
parent: Abu Mushayeed <abumushayeed@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Shoaib Mir @ 2007-04-02 09:55 UTC (permalink / raw)
To: Abu Mushayeed <abumushayeed@yahoo.com>; +Cc: pgsql-admin
First of all run the VACUUM FULL ANALYZE and that should hopefully help you
get rid of this problem without changing the max_fsm_pages. Other then this
I will recommend you to have the autovacuuming process in place for proper
database maintenance.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 4/1/07, Abu Mushayeed <abumushayeed@yahoo.com> wrote:
>
> PG DB: 8.1.3
> 12 DB in the cluster
>
> When I do a full vacuum of the database cluster, at the end I am getting
> the message as follows:
>
> psql:dba_task.sql:1: INFO: free space map contains 8033993 pages in 1023
> relations
> DETAIL: A total of 8000000 page slots are in use (including overhead).
> 8201952 page slots are required to track all free space.
> Current limits are: 8000000 page slots, 32768 relations, using 49054 KB.
> psql:dba_task.sql:1: NOTICE: number of page slots needed (8201952)
> exceeds max_fsm_pages (8000000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to
> a value over 8201952.
>
> My questions are:
>
> 1. With out increasing the max_fsm_pages, can I bring down the need for
> fsm_pages by deleting unnecessary tables?
> 2. As, the database grows the need for max_fsm_pages grows too, what is
> the best practice?
> 3. As a new postgres DBA, how should one manage this parameter?
>
> Thanks
> Abu Mushayeed
>
> ------------------------------
> It's here! Your new message!
> Get new email alerts<http://us.rd.yahoo.com/evt=49938/*http://tools.search.yahoo.com/toolbar/features/mail/>with the free Yahoo!
> Toolbar.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-02 09:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-04 14:26 max_fsm_pages Jouneau Luc <Luc.Jouneau@diamant.jouy.inra.fr>
2003-09-04 23:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-14 18:36 max_fsm_pages Dextra - Gustavo Bartz Guedes <gustavo-bartz@dextra.com.br>
2005-06-14 18:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
2007-04-01 13:59 max_fsm_pages Abu Mushayeed <abumushayeed@yahoo.com>
2007-04-02 09:55 ` Shoaib Mir <shoaibmir@gmail.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