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(dot)klaver(at)aklaver(dot)com>
Sent: Friday, May 24, 2019 10:22 AM
To: Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com>
> *Sent:* Friday, May 24, 2019 7:19 AM
> *To:* Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com