Re: BUG #3801: max_fsm_pages postgresql.conf default != guc.c default

The following bug has been logged online:

Bug reference: 3801
Logged by: Reece Hart
Email address: reece(at)harts(dot)net
PostgreSQL version: 8.2.5
Operating system: Linux (SLES 10)
Description: max_fsm_pages postgresql.conf default != guc.c default
Details:

The guc.c hardwired default for max_fsm_pages is 20000. In postgresql.conf
(and .sample), the default is claimed to be 1638400. I don't know which is
correct.

On Thu, 6 Dec 2007, Reece Hart wrote:

> Bug reference: 3801
> PostgreSQL version: 8.2.5
> Description: max_fsm_pages postgresql.conf default != guc.c default
> Details:
>
> The guc.c hardwired default for max_fsm_pages is 20000. In postgresql.conf
> (and .sample), the default is claimed to be 1638400. I don't know which is
> correct.
>

You need to consider the units. guc.c is in "number of pages", while
postgresql.conf is in kB. Since the page size is 8192, these are
equivalent.

Kris Jurka

On Wed, 2007-12-05 at 23:20 -0500, Kris Jurka wrote:
> You need to consider the units. guc.c is in "number of pages", while
> postgresql.conf is in kB. Since the page size is 8192, these are
> equivalent.

I did consider that, but I'm not certain that it's that simple. Here's
why:

First, the default config for max_fsm_pages & _relations:

csb$ sudo -u postgres grep max_fsm /srv/postgresql-8.2/postgresql.conf
#max_fsm_pages = 1638400 # min max_fsm_relations*16, 6
bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each

csb$ sudo rcpostgresql restart
Shutting down PostgreSQLserver stopped
Starting PostgreSQL

csb$ psql -d csb-dev -UPUBLIC -c "select name,setting from pg_settings
where name~'max_fsm'" -UPUBLIC
name | setting
-------------------+---------
max_fsm_pages | 20000
max_fsm_relations | 1000
(2 rows)

At the very least, this seems misleading to use different implied units.
But, the 8192 factor was obvious and I guessed that there was a page
size factor -- misleading but comprehensible.

I waded into this because I was having problems with vacuuming and
getting errors of the form "consider increasing the max_fsm_pages to X".
That warning is rather specific about what I should do, and X was in the
range of 400000-600000 for various tables, far below the apparent
default in postgresql.conf. I forged ahead, manually setting it to just
above the highest recommended value (and, confusingly, lower than the
apparent default). Then:

csb$ sudo -u postgres grep max_fsm /srv/postgresql-8.2/postgresql.conf
#max_fsm_pages = 1638400 # min max_fsm_relations*16, 6
bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
max_fsm_relations = 2500
max_fsm_pages = 400000

csb$ sudo rcpostgresql restart
Shutting down PostgreSQLserver stopped
Starting PostgreSQL

csb$ psql -d csb-dev -UPUBLIC -c "select name,setting from pg_settings
where name~'max_fsm'" -UPUBLIC
name | setting
-------------------+---------
max_fsm_pages | 400000
max_fsm_relations | 2500
(2 rows)

The implied default is written in page size units, but the manually set
default is in pages, as are the values shown by vacuum hints and
pg_settings.

So, it still seems to me that the postgresql.conf default (1638400)
should be written in page units (20000) to be consistent with the other
uses. Or, am I misthinking?

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Wed, 5 Dec 2007, Reece Hart wrote:

> On Wed, 2007-12-05 at 23:20 -0500, Kris Jurka wrote:
>> You need to consider the units. guc.c is in "number of pages", while
>> postgresql.conf is in kB. Since the page size is 8192, these are
>> equivalent.
>
>
> I did consider that, but I'm not certain that it's that simple. Here's
> why:

Actually what I said earlier was completely bogus, please ignore. I just
looked at the two numbers and essentially made up an answer (even though
that answer was off by a factor of 10). Here's something hopefully more
useful...

max_fsm_pages for the initial postgresql.conf is determined at initdb time
as it depends on the shared_buffers settings it picks for the machine:

src/bin/initdb/initdb.c says:

#define FSM_FOR_BUFS(nbuffers) ((nbuffers) > 1000 ? 50 * (nbuffers) :
20000)

so the actual default at initdb time can be set as high as nbuffers * 50,
where the max shared_buffers is 4096. So the default max_fsm_pages for a
beefier machine will be 204800 which is what you will find in
postgresql.conf.sample.

The fact that you have a commented out value in your postgresql.conf does
not mean it is the default. I'd guess someone set that themself. Try
initdbing a fresh data dir and seeing what the actual default is.

Kris Jurka

Kris-

Thanks for digging into the code for that. Unfortunately, I'm now more
confused.

> the actual default at initdb time can be set as high as nbuffers *
> 50,
> where the max shared_buffers is 4096. So the default max_fsm_pages
> for a
> beefier machine will be 204800 which is what you will find in
> postgresql.conf.sample.

The initdb was done on an 8-way Opteron with 32GB of RAM. I would have
assumed that this satisfied the definition of a "beefier machine", but I
got the measly 20000 m_f_p setting (and didn't manually change the
shared_buffers setting). Initdb was done with 8.2.3 IIRC.

> The fact that you have a commented out value in your postgresql.conf
> does
> not mean it is the default.

Really? Comments in postgresql.conf file sayeth:

# The
# commented-out settings shown in this file represent the default
values.
#
# Please note that re-commenting a setting is NOT sufficient to revert
it
# to the default value, unless you restart the server.

This seems to directly say that the commented out settings are the
default values, and furthermore that one must restart to get the
indicated default back. Based on your evidence, it seems that the
postgresql.conf comment for max_fsm_pages needs revising to indicate
that the m_f_p default is determined at initdb-time.

Thank you,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Thu, 6 Dec 2007, Reece Hart wrote:

> This seems to directly say that the commented out settings are the
> default values, and furthermore that one must restart to get the
> indicated default back. Based on your evidence, it seems that the
> postgresql.conf comment for max_fsm_pages needs revising to indicate
> that the m_f_p default is determined at initdb-time.

Yes, the commented out values are the defaults, but after initdb
max_fsm_pages is not commented out, which is why I'm suggesting you or
some other admin modified your file. Try this test:

$ ./tmp/82/bin/initdb -D fsmtest > fsmlog 2>&1

$ grep max_fsm fsmlog
selecting default shared_buffers/max_fsm_pages ... 24MB/153600

$ grep max_fsm_pages fsmtest/postgresql.conf
max_fsm_pages = 153600 # min max_fsm_relations*16, 6 bytes each

So you can see max_fsm_pages is not commented out, so it is not the true
default (20000).

Kris Jurka

On Thu, 2007-12-06 at 21:40 -0500, Kris Jurka wrote:

> Yes, the commented out values are the defaults, but after initdb
> max_fsm_pages is not commented out, which is why I'm suggesting you
> or
> some other admin modified your file. Try this test:

Kris-

You're completely right, of course, and I appreciate your perseverance
in setting me straight. I don't recall making the postgresql.conf change
but I surely was the one who did so.

Thanks for your time.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0