Postgres Pro
Downloads
Home   >   mailing lists

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%) - Mailing list pgsql-general

From Julie Nishimura
Subject Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)
Date
Msg-id BYAPR08MB501492C3BF27B1A71860AD08AC1E0@BYAPR08MB5014.namprd08.prod.outlook.com
Whole thread Raw
In response to Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)  (Julie Nishimura <juliezain@hotmail.com>)
Responses Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)
Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)
List pgsql-general
Tree view
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


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

pgsql-general by date:

Previous
From: Julie Nishimura
Date:
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used:79569 of 80000 (99%)
Next
From: Andreas Kretschmer
Date:
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)