Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx>
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@xxxxxxxxxxx>
> *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@xxxxxxxxxxx>
> *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@xxxxxxxxxxx>
> *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@xxxxxxxxxxx>
>> *Sent:* Tuesday, May 28, 2019 12:43 PM
>> *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/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@xxxxxxxxxxx
>
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux