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

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


--
Adrian Klaver
adrian.klaver@aklaver.com