Thread: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569of 80000 (99%)

POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569of 80000 (99%)

From

Julie Nishimura

Date:

24 May 2019, 09:57:52

Hello,

We have an issue with fsm_relations utilization reaching 99%, I was able to vacuum a handful of tables, but it wasn't enough to make a noticeable difference. I think at this point we will need to increase the number of fsm_relations from 80,000 to 100,000 which will require a restart. Because there aren't any more dead rows to delete. I confirmed this by connecting to each db and running the following query:
SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by n_dead_tup desc
daily_mail_shared_state_cddt_3588-
relname | n_live_tup | n_dead_tup
----------------+------------+------------
article_errors | 0 | 0
article_names | 3375193 | 0
indexdefs | 0 | 0
tabledefs | 0 | 0

Above output, shows n_dead_tup is zeroed out, this makes me believe that we need to increase the number of fsm relations to a number between 90k and 100k.But I might be wrong, need your advice.

PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Thanks!

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

24 May 2019, 10:00:45

Correction about version:

PostgreSQL 8.3.11

Thanks again!!!

From: Julie Nishimura <juliezain@hotmail.com>
Sent: Thursday, May 23, 2019 11:57 PM
To: pgsql-general@lists.postgresql.org; pgsql-general
Subject: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

Hello,

PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Thanks!

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Adrian Klaver

Date:

24 May 2019, 17:19:53

On 5/23/19 11:57 PM, Julie Nishimura wrote:
> Hello,
> We have an issue with fsm_relations utilization reaching 99%, I was able 

How are you arriving at the above percentage?

How many tables/indexes do you have in the database(s)?

> to vacuum a handful of tables, but it wasn't enough to make a noticeable 
> difference. I think at this point we will need to increase the number of 
> fsm_relations from 80,000 to 100,000 which will require a restart. 
> Because there aren't any more dead rows to delete. I confirmed this by 
> connecting to each db and running the following query:
> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by 
> n_dead_tup desc
> daily_mail_shared_state_cddt_3588-
> relname | n_live_tup | n_dead_tup
> ----------------+------------+------------
> article_errors | 0 | 0
> article_names | 3375193 | 0
> indexdefs | 0 | 0
> tabledefs | 0 | 0
> 
> Above output, shows n_dead_tup is zeroed out, this makes me believe that 
> we need to increase the number of fsm relations to a number between 90k 
> and 100k.But I might be wrong, need your advice.
> 
> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
> 
> Thanks!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

24 May 2019, 20:16:18

Adrian, this value was set in config file, and alerting comes from monitoring.

Would it be right query to count objects in each database (there are 75 dbs on this server totaling close to 20 tb):

SELECT

count(1) as object_count

FROM pg_catalog.pg_class c

WHERE c.relkind IN ('r','i')

Thanks!

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, May 24, 2019 7:19 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

On 5/23/19 11:57 PM, Julie Nishimura wrote:
> Hello,
> We have an issue with fsm_relations utilization reaching 99%, I was able

How are you arriving at the above percentage?

How many tables/indexes do you have in the database(s)?

> to vacuum a handful of tables, but it wasn't enough to make a noticeable
> difference. I think at this point we will need to increase the number of
> fsm_relations from 80,000 to 100,000 which will require a restart.
> Because there aren't any more dead rows to delete. I confirmed this by
> connecting to each db and running the following query:
> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by
> n_dead_tup desc
> daily_mail_shared_state_cddt_3588-
> relname | n_live_tup | n_dead_tup
> ----------------+------------+------------
> article_errors | 0 | 0
> article_names | 3375193 | 0
> indexdefs | 0 | 0
> tabledefs | 0 | 0
>
> Above output, shows n_dead_tup is zeroed out, this makes me believe that
> we need to increase the number of fsm relations to a number between 90k
> and 100k.But I might be wrong, need your advice.
>
> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>
> Thanks!

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Adrian Klaver

Date:

24 May 2019, 20:22:04

On 5/24/19 10:16 AM, Julie Nishimura wrote:
> Adrian, this value was set in config file, and alerting comes from 
> monitoring.

Yes, but what is the monitoring actually doing to get that value?

> 
> Would it be right query to count objects in each database (there are 75 
> dbs on this server totaling close to 20 tb):
> 
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')
> 
> ?
> 
> Thanks!
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Friday, May 24, 2019 7:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
> used: 79569 of 80000 (99%)
> On 5/23/19 11:57 PM, Julie Nishimura wrote:
>> Hello,
>> We have an issue with fsm_relations utilization reaching 99%, I was able 
> 
> How are you arriving at the above percentage?
> 
> How many tables/indexes do you have in the database(s)?
> 
>> to vacuum a handful of tables, but it wasn't enough to make a noticeable 
>> difference. I think at this point we will need to increase the number of 
>> fsm_relations from 80,000 to 100,000 which will require a restart. 
>> Because there aren't any more dead rows to delete. I confirmed this by 
>> connecting to each db and running the following query:
>> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by 
>> n_dead_tup desc
>> daily_mail_shared_state_cddt_3588-
>> relname | n_live_tup | n_dead_tup
>> ----------------+------------+------------
>> article_errors | 0 | 0
>> article_names | 3375193 | 0
>> indexdefs | 0 | 0
>> tabledefs | 0 | 0
>> 
>> Above output, shows n_dead_tup is zeroed out, this makes me believe that 
>> we need to increase the number of fsm relations to a number between 90k 
>> and 100k.But I might be wrong, need your advice.
>> 
>> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>> 
>> Thanks!
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Andres Freund

Date:

27 May 2019, 16:49:20

Hi,

On 2019-05-24 06:57:52 +0000, Julie Nishimura wrote:
> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Please note that a) 8.2 has *long* been unsupported. b) greenplum,
especially 8.2 based, is quite different from normal postgres, and not
developed by the postgresql development community.

Greetings,

Andres Freund

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

27 May 2019, 20:58:57

Andres, yes, thank you!

From: Andres Freund <andres@anarazel.de>
Sent: Monday, May 27, 2019 6:49 AM
To: Julie Nishimura
Cc: pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

Hi,

On 2019-05-24 06:57:52 +0000, Julie Nishimura wrote:
> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Please note that a) 8.2 has *long* been unsupported. b) greenplum,
especially 8.2 based, is quite different from normal postgres, and not
developed by the postgresql development community.

Greetings,

Andres Freund

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

28 May 2019, 21:11:37

Adrian, the current nagios alerting does the following:

postgres=# SELECT count(*) from pg_freespacemap_relations;

count

-------

79999

(1 row)

and this is the snippet from our config:

max_fsm_pages = 6000000

# (change requires restart)

max_fsm_relations = 80000

So, we are pretty much at 100%. We have decided to increase max_fsm_relations = 90000 and restart.

To do the right settings for this value, should I run this query in all dbs on the server:

> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')

Thanks for your help

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, May 24, 2019 10:22 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

On 5/24/19 10:16 AM, Julie Nishimura wrote:
> Adrian, this value was set in config file, and alerting comes from
> monitoring.

Yes, but what is the monitoring actually doing to get that value?

>
> Would it be right query to count objects in each database (there are 75
> dbs on this server totaling close to 20 tb):
>
> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')
>
> ?
>
> Thanks!
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Friday, May 24, 2019 7:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 80000 (99%)
> On 5/23/19 11:57 PM, Julie Nishimura wrote:
>> Hello,
>> We have an issue with fsm_relations utilization reaching 99%, I was able
>
> How are you arriving at the above percentage?
>
> How many tables/indexes do you have in the database(s)?
>
>> to vacuum a handful of tables, but it wasn't enough to make a noticeable
>> difference. I think at this point we will need to increase the number of
>> fsm_relations from 80,000 to 100,000 which will require a restart.
>> Because there aren't any more dead rows to delete. I confirmed this by
>> connecting to each db and running the following query:
>> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by
>> n_dead_tup desc
>> daily_mail_shared_state_cddt_3588-
>> relname | n_live_tup | n_dead_tup
>> ----------------+------------+------------
>> article_errors | 0 | 0
>> article_names | 3375193 | 0
>> indexdefs | 0 | 0
>> tabledefs | 0 | 0
>>
>> Above output, shows n_dead_tup is zeroed out, this makes me believe that
>> we need to increase the number of fsm relations to a number between 90k
>> and 100k.But I might be wrong, need your advice.
>>
>> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>>
>> Thanks!
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

28 May 2019, 21:20:10

What is the impact of fsm_relatiosn being maxed out?

From: Julie Nishimura <juliezain@hotmail.com>
Sent: Tuesday, May 28, 2019 11:11 AM
To: Adrian Klaver; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

Adrian, the current nagios alerting does the following:

postgres=# SELECT count(*) from pg_freespacemap_relations;

count

-------

79999

(1 row)

and this is the snippet from our config:

max_fsm_pages = 6000000

# (change requires restart)

max_fsm_relations = 80000

So, we are pretty much at 100%. We have decided to increase max_fsm_relations = 90000 and restart.

To do the right settings for this value, should I run this query in all dbs on the server:

> SELECT
> count(1) as object_count
> FROM pg_catalog.pg_class c
> WHERE c.relkind IN ('r','i')

Thanks for your help

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

From

Andreas Kretschmer

Date:

28 May 2019, 21:47:39

On 28 May 2019 20:20:10 CEST, Julie Nishimura <juliezain@hotmail.com> wrote:
>What is the impact of fsm_relatiosn being maxed out?

https://www.postgresql.org/docs/8.2/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Please no top-posting with fullquote.


Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Adrian Klaver

Date:

28 May 2019, 22:43:20

On 5/28/19 11:20 AM, Julie Nishimura wrote:
> What is the impact of fsm_relatiosn being maxed out?
> 

It has been awhile since I thought about this as these settings are no 
longer relevant as of 8.4:

https://www.postgresql.org/docs/8.4/release-8-4.html

"

Track free space in separate per-relation "fork" files (Heikki)

Free space discovered by VACUUM is now recorded in *_fsm files, rather 
than in a fixed-sized shared memory area. The max_fsm_pages and 
max_fsm_relations settings have been removed, greatly simplifying 
administration of free space management.
"

To get to your question, I would take a look at:

https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Pay attention to the embedded link in the above:

https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

29 May 2019, 00:03:44

Adrian, I am trying to avoid to do any tweaking to this legacy system that nobody knows well (we inherited it recently).

Do you think it might help if we possibly drop old tables (I assume their indices will be removed too), so the overall number of objects will go down? Thanks a lot

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, May 28, 2019 12:43 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

On 5/28/19 11:20 AM, Julie Nishimura wrote:
> What is the impact of fsm_relatiosn being maxed out?
>

It has been awhile since I thought about this as these settings are no
longer relevant as of 8.4:

https://www.postgresql.org/docs/8.4/release-8-4.html

"

Track free space in separate per-relation "fork" files (Heikki)

Free space discovered by VACUUM is now recorded in *_fsm files, rather
than in a fixed-sized shared memory area. The max_fsm_pages and
max_fsm_relations settings have been removed, greatly simplifying
administration of free space management.
"

To get to your question, I would take a look at:

https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Pay attention to the embedded link in the above:

https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Alvaro Herrera

Date:

29 May 2019, 00:15:06

On 2019-May-28, Julie Nishimura wrote:

> Adrian, I am trying to avoid to do any tweaking to this legacy system that nobody knows well (we inherited it
recently).
> Do you think it might help if we possibly drop old tables (I assume their indices will be removed too), so the
overallnumber of objects will go down? Thanks a lot

Having insufficient max_fsm_relations causes severe problems, so don't
if you can avoid it.  I would certainly recommend increasing it.

Note that increasing max_fsm_relations requires that you have a large
enough shared memory allowance in the operating system; if you're too
close to the limit and try to restart with the increased setting, the
service may fail to start.  However, unless you're on something weird
that requires recompiling the kernel to update that limit, it should be
fairly simple to update it.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Adrian Klaver

Date:

29 May 2019, 03:16:27

On 5/28/19 2:53 PM, Julie Nishimura wrote:
Please post to list also.
Ccing list
> I am sorry, did not mean to send it yet.
> #------------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
> #------------------------------------------------------------------------------
> 
> # - Memory -
> 
> shared_buffers = 16GB                   # min 128kB or max_connections*16kB
>                                          # (change requires restart)
> temp_buffers = 8MB                      # min 800kB
> #max_prepared_transactions = 5          # can be 0 or more
>                                          # (change requires restart)
> # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared 
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 256MB                                # min 64kB
> maintenance_work_mem = 512MB            # min 1MB
> max_stack_depth = 4MB                   # min 100kB
> 
> # - Free Space Map -
> 
> max_fsm_pages = 6000000
>                                          # (change requires restart)
> max_fsm_relations = 80000
> #------------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #------------------------------------------------------------------------------
> 
> # - Settings -
> wal_buffers = 4096kB
> 
> # - Free Space Map -
> 
> max_fsm_pages = 6000000
>                                          # (change requires restart)
> max_fsm_relations = 80000
> 
> Seems like the system has 98 gb, and shared_buffers set to 16 gb, so if 
> we increase max_fsm_relations to 100000, should be still enough memory 
> to start, right?
> 
> Thank you!
> 
> ------------------------------------------------------------------------
> *From:* Julie Nishimura <juliezain@hotmail.com>
> *Sent:* Tuesday, May 28, 2019 2:49 PM
> *To:* Adrian Klaver
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
> used: 79569 of 80000 (99%)
> 
> 
> Adrian,
> Thanks for your help. It seems like the system has 98 gb memory on the 
> server overall
> 
> max_connections = 300
> 
> ------------------------------------------------------------------------
> *From:* Julie Nishimura <juliezain@hotmail.com>
> *Sent:* Tuesday, May 28, 2019 2:34 PM
> *To:* Adrian Klaver
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
> used: 79569 of 80000 (99%)
> We haven't yet, still waiting for the approval from our management
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Tuesday, May 28, 2019 2:11 PM
> *To:* Julie Nishimura
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
> used: 79569 of 80000 (99%)
> On 5/28/19 2:03 PM, Julie Nishimura wrote:
>> Adrian, I am trying to avoid to do any tweaking to this legacy system 
>> that nobody knows well (we inherited it recently).
> 
> Yeah, but you already tweaked it, so it is useful to know what the
> repercussions might be.
> 
>> Do you think it might help if we possibly drop old tables (I assume 
>> their indices will be removed too), so the overall number of objects 
>> will go down? Thanks a lot
> 
> Yes. Just not sure when that will happen.
> 
>> 
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
>> *Sent:* Tuesday, May 28, 2019 12:43 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
>> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
>> used: 79569 of 80000 (99%)
>> On 5/28/19 11:20 AM, Julie Nishimura wrote:
>>> What is the impact of fsm_relatiosn being maxed out?
>>> 
>> 
>> It has been awhile since I thought about this as these settings are no
>> longer relevant as of 8.4:
>> 
>> https://www.postgresql.org/docs/8.4/release-8-4.html
>> 
>> "
>> 
>> Track free space in separate per-relation "fork" files (Heikki)
>> 
>> Free space discovered by VACUUM is now recorded in *_fsm files, rather
>> than in a fixed-sized shared memory area. The max_fsm_pages and
>> max_fsm_relations settings have been removed, greatly simplifying
>> administration of free space management.
>> "
>> 
>> To get to your question, I would take a look at:
>> 
>> https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM
>> 
>> Pay attention to the embedded link in the above:
>> 
>> https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC
>> 
>> 
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Julie Nishimura

Date:

29 May 2019, 19:41:36

Just for those who might be interested... We increased the settings for max_fsm_relations, max_fsm_pages and restarted the service. It came up with no problem. And it looks like after the restart of the psql service the utilization of fsm_relations was reset to 0.

POSTGRES_FSM_RELATIONS OK: DB control (host:10.24.33.13) fsm relations used: 84 of 160000 (0%)

I was expecting the used fsm_relations to continue its count from 80k, but instead it looks like it reset, which gives us even more room to grow.

Yesterday before the change :

SELECT count(*) from pg_freespacemap_relations;

count

-------

79999

Today after the change:

SELECT count(*) from pg_freespacemap_relations;

count

-------

272

(1 row)

Just FYI

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, May 28, 2019 5:16 PM
To: Julie Nishimura
Cc: pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

On 5/28/19 2:53 PM, Julie Nishimura wrote:
Please post to list also.
Ccing list
> I am sorry, did not mean to send it yet.
> #------------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
> #------------------------------------------------------------------------------
>
> # - Memory -
>
> shared_buffers = 16GB # min 128kB or max_connections*16kB
> # (change requires restart)
> temp_buffers = 8MB # min 800kB
> #max_prepared_transactions = 5 # can be 0 or more
> # (change requires restart)
> # Note: Increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 256MB # min 64kB
> maintenance_work_mem = 512MB # min 1MB
> max_stack_depth = 4MB # min 100kB
>
> # - Free Space Map -
>
> max_fsm_pages = 6000000
> # (change requires restart)
> max_fsm_relations = 80000
> #------------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #------------------------------------------------------------------------------
>
> # - Settings -
> wal_buffers = 4096kB
>
> # - Free Space Map -
>
> max_fsm_pages = 6000000
> # (change requires restart)
> max_fsm_relations = 80000
>
> Seems like the system has 98 gb, and shared_buffers set to 16 gb, so if
> we increase max_fsm_relations to 100000, should be still enough memory
> to start, right?
>
> Thank you!
>
> ------------------------------------------------------------------------
> *From:* Julie Nishimura <juliezain@hotmail.com>
> *Sent:* Tuesday, May 28, 2019 2:49 PM
> *To:* Adrian Klaver
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 80000 (99%)
>
>
> Adrian,
> Thanks for your help. It seems like the system has 98 gb memory on the
> server overall
>
> max_connections = 300
>
> ------------------------------------------------------------------------
> *From:* Julie Nishimura <juliezain@hotmail.com>
> *Sent:* Tuesday, May 28, 2019 2:34 PM
> *To:* Adrian Klaver
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 80000 (99%)
> We haven't yet, still waiting for the approval from our management
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Tuesday, May 28, 2019 2:11 PM
> *To:* Julie Nishimura
> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
> used: 79569 of 80000 (99%)
> On 5/28/19 2:03 PM, Julie Nishimura wrote:
>> Adrian, I am trying to avoid to do any tweaking to this legacy system
>> that nobody knows well (we inherited it recently).
>
> Yeah, but you already tweaked it, so it is useful to know what the
> repercussions might be.
>
>> Do you think it might help if we possibly drop old tables (I assume
>> their indices will be removed too), so the overall number of objects
>> will go down? Thanks a lot
>
> Yes. Just not sure when that will happen.
>
>>
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
>> *Sent:* Tuesday, May 28, 2019 12:43 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
>> *Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations
>> used: 79569 of 80000 (99%)
>> On 5/28/19 11:20 AM, Julie Nishimura wrote:
>>> What is the impact of fsm_relatiosn being maxed out?
>>>
>>
>> It has been awhile since I thought about this as these settings are no
>> longer relevant as of 8.4:
>>
>> https://www.postgresql.org/docs/8.4/release-8-4.html
>>
>> "
>>
>> Track free space in separate per-relation "fork" files (Heikki)
>>
>> Free space discovered by VACUUM is now recorded in *_fsm files, rather
>> than in a fixed-sized shared memory area. The max_fsm_pages and
>> max_fsm_relations settings have been removed, greatly simplifying
>> administration of free space management.
>> "
>>
>> To get to your question, I would take a look at:
>>
>> https://www.postgresql.org/docs/8.3/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM
>>
>> Pay attention to the embedded link in the above:
>>
>> https://www.postgresql.org/docs/8.3/kernel-resources.html#SYSVIPC
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)

From

Adrian Klaver

Date:

29 May 2019, 21:32:28

On 5/29/19 9:41 AM, Julie Nishimura wrote:
> Just for those who might be interested... We increased the settings for 
> max_fsm_relations, max_fsm_pages and restarted the service. It came up 
> with no problem. And it looks like after the restart of the psql service 
> the utilization of fsm_relations was reset to 0.
> POSTGRES_FSM_RELATIONS OK: DB control (host:10.24.33.13) fsm relations 
> used: 84 of 160000 (0%)
> 
> I was expecting the used fsm_relations to continue its count from 80k, 
> but instead it looks like it reset, which gives us even more room to grow.
> 

FSM is tied to VACUUM, so as VACUUM is run over tables the count will 
increase.


-- 
Adrian Klaver
adrian.klaver@aklaver.com