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