|
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@xxxxxxxxxxx>
Sent: Friday, May 24, 2019 10:22 AM To: Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxxx> > *Sent:* Friday, May 24, 2019 7:19 AM > *To:* Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxxx -- Adrian Klaver adrian.klaver@xxxxxxxxxxx |
