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

From: Julie Nishimura <juliezain(at)hotmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)
Date: 2019-05-28 18:11:37
Message-ID: BYAPR08MB5014E359618AF1278B48FA51AC1E0@BYAPR08MB5014.namprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Julie Nishimura 2019-05-28 18:20:10 Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)
Previous Message Adrian Klaver 2019-05-28 17:26:41 Re: Alternate methods for multiple rows input/output to a function.