Thread: Setting max_fsm_pages

Setting max_fsm_pages

From
"Carlos Oliva"
Date:

Should I set the max_fsm_pages to the value reported (vacuum verbose) as pages stored or the value reported as total pages needed?  I ran full + analyze vacuums in my database (vacuumdb –f –z –v <database name>) a couple of times and I got the following reports:

INFO:  free space map: 454 relations, 22274 pages stored; 147328 total pages needed

DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory

 

And

 

INFO:  free space map: 454 relations, 22242 pages stored; 147328 total pages needed

DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory

 

I am not sure if I need to raise the max_fsm_pages to something larger than 22242 (e.g. 40000) or larger than 147,328.

 

Re: Setting max_fsm_pages

From
Tom Lane
Date:
"Carlos Oliva" <carlos@pbsinet.com> writes:
> Should I set the max_fsm_pages to the value reported (vacuum verbose) as
> pages stored or the value reported as total pages needed?

Total pages needed ... if not more.

            regards, tom lane

Re: Setting max_fsm_pages

From
"Carlos Oliva"
Date:
Thank you for your response Tom.

Should I set the max_fsm_pages to the "total pages needed" obtained from a
full vacuum or from a analize vacuum?  When I run a vacuum analyze (vacuumdb
-z -v), I get a smaller number of pages needed than when I run a full vacuum
with analyze (vacuumdb -f -z -v)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Saturday, November 05, 2005 10:23 PM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting max_fsm_pages

"Carlos Oliva" <carlos@pbsinet.com> writes:
> Should I set the max_fsm_pages to the value reported (vacuum verbose) as
> pages stored or the value reported as total pages needed?

Total pages needed ... if not more.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



Re: Setting max_fsm_pages

From
"Jim C. Nasby"
Date:
On Sun, Nov 06, 2005 at 08:05:29PM -0500, Carlos Oliva wrote:
> Thank you for your response Tom.
>
> Should I set the max_fsm_pages to the "total pages needed" obtained from a
> full vacuum or from a analize vacuum?  When I run a vacuum analyze (vacuumdb
> -z -v), I get a smaller number of pages needed than when I run a full vacuum
> with analyze (vacuumdb -f -z -v)

There shouldn't be any difference because of analyze. But remember that
as the tables change in size (as well as in the number of dead tuples),
total pages needed can change. For example, if you run a vacuum
immediately after a vacuum full on a system with no other activity,
you'll get:

INFO:  free space map: 0 relations, 0 pages stored; 0 total pages needed

That's because there's no dead space to be reclaimed.

Your best bet is to do a vacuum verbose (vacuumdb -v) after the database
has been running for a while using whatever vacuuming scheme you're
going to use (such as pg_autovacuum). That will give you a pretty good
estimate of how many pages you really need. Even that's not 100%
reliable though, so you still need to include extra space as a safety
margin.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Setting max_fsm_pages

From
"Carlos Oliva"
Date:
Hi Jim,
Thank you for your help.  We are going to increase the max_fxm_pages
according to the test I have been running through out the week.

If we increase the max_fsm_pages, do we need to bump up the shared_buffers
and the size of the shared memory segment of the Linux kernel(shmmax)?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim C. Nasby
Sent: Monday, November 07, 2005 2:38 PM
To: Carlos Oliva
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting max_fsm_pages

On Sun, Nov 06, 2005 at 08:05:29PM -0500, Carlos Oliva wrote:
> Thank you for your response Tom.
>
> Should I set the max_fsm_pages to the "total pages needed" obtained from a
> full vacuum or from a analize vacuum?  When I run a vacuum analyze
(vacuumdb
> -z -v), I get a smaller number of pages needed than when I run a full
vacuum
> with analyze (vacuumdb -f -z -v)

There shouldn't be any difference because of analyze. But remember that
as the tables change in size (as well as in the number of dead tuples),
total pages needed can change. For example, if you run a vacuum
immediately after a vacuum full on a system with no other activity,
you'll get:

INFO:  free space map: 0 relations, 0 pages stored; 0 total pages needed

That's because there's no dead space to be reclaimed.

Your best bet is to do a vacuum verbose (vacuumdb -v) after the database
has been running for a while using whatever vacuuming scheme you're
going to use (such as pg_autovacuum). That will give you a pretty good
estimate of how many pages you really need. Even that's not 100%
reliable though, so you still need to include extra space as a safety
margin.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings



Re: Setting max_fsm_pages

From
"Jim C. Nasby"
Date:
You might have to bump up shmmax, but fsm is completely unrelated to
shared_buffers.

On Mon, Nov 07, 2005 at 02:55:41PM -0500, Carlos Oliva wrote:
> Hi Jim,
> Thank you for your help.  We are going to increase the max_fxm_pages
> according to the test I have been running through out the week.
>
> If we increase the max_fsm_pages, do we need to bump up the shared_buffers
> and the size of the shared memory segment of the Linux kernel(shmmax)?
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim C. Nasby
> Sent: Monday, November 07, 2005 2:38 PM
> To: Carlos Oliva
> Cc: 'Tom Lane'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Setting max_fsm_pages
>
> On Sun, Nov 06, 2005 at 08:05:29PM -0500, Carlos Oliva wrote:
> > Thank you for your response Tom.
> >
> > Should I set the max_fsm_pages to the "total pages needed" obtained from a
> > full vacuum or from a analize vacuum?  When I run a vacuum analyze
> (vacuumdb
> > -z -v), I get a smaller number of pages needed than when I run a full
> vacuum
> > with analyze (vacuumdb -f -z -v)
>
> There shouldn't be any difference because of analyze. But remember that
> as the tables change in size (as well as in the number of dead tuples),
> total pages needed can change. For example, if you run a vacuum
> immediately after a vacuum full on a system with no other activity,
> you'll get:
>
> INFO:  free space map: 0 relations, 0 pages stored; 0 total pages needed
>
> That's because there's no dead space to be reclaimed.
>
> Your best bet is to do a vacuum verbose (vacuumdb -v) after the database
> has been running for a while using whatever vacuuming scheme you're
> going to use (such as pg_autovacuum). That will give you a pretty good
> estimate of how many pages you really need. Even that's not 100%
> reliable though, so you still need to include extra space as a safety
> margin.
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Setting max_fsm_pages

From
Marc Cousin
Date:
As we're talking about fsm, I've got a question I've been asking myself for
some time :
If a cluster is restarted, is the fsm forgotten ? (so does it mean one should
run a vacuum as soon as the database is restarted ?)

Le Dimanche 06 Novembre 2005 03:44, Carlos Oliva a écrit :
> Should I set the max_fsm_pages to the value reported (vacuum verbose) as
> pages stored or the value reported as total pages needed?  I ran full +
> analyze vacuums in my database (vacuumdb -f -z -v <database name>) a couple
> of times and I got the following reports:
>
> INFO:  free space map: 454 relations, 22274 pages stored; 147328 total
> pages needed
>
> DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared
> memory
>
>
>
> And
>
>
>
> INFO:  free space map: 454 relations, 22242 pages stored; 147328 total
> pages needed
>
> DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared
> memory
>
>
>
> I am not sure if I need to raise the max_fsm_pages to something larger than
> 22242 (e.g. 40000) or larger than 147,328.

Re: Setting max_fsm_pages

From
Tom Lane
Date:
Marc Cousin <cousinmarc@free.fr> writes:
> If a cluster is restarted, is the fsm forgotten ?

Given a normal postmaster shutdown, no.  In a crash-restart situation,
yes.

            regards, tom lane

Re: Setting max_fsm_pages

From
"Jim C. Nasby"
Date:
On Tue, Nov 08, 2005 at 03:36:40PM -0500, Tom Lane wrote:
> Marc Cousin <cousinmarc@free.fr> writes:
> > If a cluster is restarted, is the fsm forgotten ?
>
> Given a normal postmaster shutdown, no.  In a crash-restart situation,
> yes.

Does that include restarts due to things like failed asserts and kill
-9'ing a backend?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461