After we discovered that the matrics_data partition tables are not generated, and Monitor could not insert the collected data due to missing tables, which in turn leads to memory issue. The only way to get it work again is to create the partition tables. After creating the partition tables, it works fine.
Error Reference
In service log, which is located in "C:\Program Files\ArcGIS\Monitor\bin\arcgis-monitor-server-service.err.log". We found...
error: insert into "metrics_data" ("metric_id", "observed_at", "value") values ($1, $2, $3), ($4....
severity: 'ERROR',
code: '23514',
detail: 'Partition key of the failing row contains (observed_at) = (2023-0x-0x xx:00:00+00).'
After a lot of insertion failure, we found...
FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory.
To make it work
Create partition table everyday like
CREATE TABLE metrics_data_2023_09_14 PARTITION OF metrics_data
FOR VALUES FROM ('2023-09-14') TO ('2023-09-15')
this will create table for September 14th, 2023 and monitor will be able to insert the collected data in it.
In our case, we created a script to create tables for 2 months ahead (60 tables, one for each day). Moreover, scheduled task can be created to run the script to create tables every two months.
That's how we solve it for the time being till we discover and solve the root cause of not generating the partition tables automatically.