|
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.
SELECT count(*) from pg_freespacemap_relations;
count
-------
Today after the change:
SELECT count(*) from pg_freespacemap_relations;
count
-------
272
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 |
