pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
max_fsm_pages
6+ messages / 6 participants
[nested] [flat]

* max_fsm_pages
@ 2003-09-04 14:26 Jouneau Luc <Luc.Jouneau@diamant.jouy.inra.fr>
  2003-09-04 23:24 ` Re: max_fsm_pages Tom Lane <tgl@sss.pgh.pa.us>
  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 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>
  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>
  2005-06-14 18:45 ` Re: max_fsm_pages Bruce Momjian <pgman@candle.pha.pa.us>
  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: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>
  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>
  2007-04-02 09:55 ` Re: max_fsm_pages Shoaib Mir <shoaibmir@gmail.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-01 13:59 max_fsm_pages Abu Mushayeed <abumushayeed@yahoo.com>
@ 2007-04-02 09:55 ` Shoaib Mir <shoaibmir@gmail.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