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

Started by Julie Nishimuraalmost 7 years ago16 messagesgeneral

juliezain@hotmail.com

almost 7 years ago

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!

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Julie Nishimura (#1)

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

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%)

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

almost 7 years ago

In reply to: Julie Nishimura (#1)

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

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Adrian Klaver (#3)

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

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 7 years ago

In reply to: Julie Nishimura (#4)

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

Andres Freund

andres@anarazel.de

almost 7 years ago

In reply to: Julie Nishimura (#1)

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

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Andres Freund (#6)

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

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

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Adrian Klaver (#5)

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

________________________________
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

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Julie Nishimura (#8)

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

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

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

#10

Andreas Kretschmer

andreas@a-kretschmer.de

almost 7 years ago

In reply to: Julie Nishimura (#9)

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

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

#11

Adrian Klaver

adrian.klaver@aklaver.com

almost 7 years ago

In reply to: Julie Nishimura (#9)

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

#12

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Adrian Klaver (#11)

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

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)

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

#13

Alvaro Herrera

alvherre@2ndquadrant.com

almost 7 years ago

In reply to: Julie Nishimura (#12)

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

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 overall number 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

#14

Adrian Klaver

adrian.klaver@aklaver.com

almost 7 years ago

In reply to: Julie Nishimura (#1)

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

Import Notes

Reply to msg id not found: BYAPR08MB50143145425663DDE772E79FAC1E0@BYAPR08MB5014.namprd08.prod.outlook.com

#15

Julie Nishimura

juliezain@hotmail.com

almost 7 years ago

In reply to: Adrian Klaver (#14)

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

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

#16

Adrian Klaver

adrian.klaver@aklaver.com

almost 7 years ago

In reply to: Julie Nishimura (#15)

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

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