Partition tables not created...

[SebastienPetit]

Environment
ArcGIS Monitor 2023.2
Windows Server 2019 Standard
RAM 24 GB
PostgreSQL 14

Problem description:

ArcGIS Monitor stopped writing collected metrics into PostgreSQL database and accordingly nothing shows up in monitor web application. After further analysis, we found that ArcGIS Monitor is trying to insert metrics data but the partition tables are missing. For example, monitor is trying to insert metrics data for 12 Sep 2023, unfortunately, monitor does not find metrics_data_2023_09_12 partition table. It keeps trying to insert records till the heap memory is full and it throws heap limit Allocation failed.

After creating the partition table manually, monitor started to write metrics data successfully and it shows up in the Monitor web application. Accordingly, the root cause and the effect is probably that the partition tables are not being created.

[GeoJosh]

Sebastien, this should have been fixed when you upgraded to 2023.2. On the ArcGIS Monitor machine, please open Control Panel > Uninstall a program. Find ArcGIS Monitor in the list of programs, take a screenshot of the "Version" column and post it here. I'm wondering if there was some failure during the upgrade process.

[SebastienPetit]

We were hopping that the upgrade would solve it yes.

Thank you

[GeoJosh]

Sebastien, in Monitor, click Administration > Database > Data Retention Policy > Edit. Change the value for "Metrics Data" to 181 and click Save. Click Edit again and change it back to 180. Does this trigger the partitions to be created as expected?

[SebastienPetit]

No it does not 😞

Essam just replied with more details https://community.esri.com/t5/user/viewprofilepage/user-id/547440 (we are working together)

[essamadelali]

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.   

[GeoJosh]

Hi @essamadelali and @SebastienPetit,

I am glad to hear that you have found a temporary workaround for this issue while the investigation continues. This may require a deeper look than what I can provide over the forums. Can you please open a case with Esri Support so that we can troubleshoot further?

Josh

[SebastienPetit]

Hello,

Sorry for late answer.

We already have a case open (Esri Case #03437483)

Thank you again for your support

[RanaDas]

Hi @essamadelali ,

Your observations was helpful for us. We made a little bit changes in creating the partition tables with the help of our DBA @MOHAMMEDKHAJAANSARI . Instead of creating tables one by one we created a function in which we pass the date ranges and the tables get created when a single query is fired. Other folks can utilize it if they using PosgreSQL.

Function Code:

DECLARE
current_partition_date DATE := start_date;
BEGIN
-- Create the main table if it doesn't exist

-- Loop to create partitions
WHILE current_partition_date <= end_date LOOP
-- Generate the partition name based on the current date
-- You might want to use a specific naming convention for your partitions
-- For example: 'partition_20240118'
EXECUTE 'CREATE TABLE IF NOT EXISTS metrics_data_' || to_char(current_partition_date, 'YYYY_MM_DD') || ' PARTITION OF metrics_data FOR VALUES FROM (''' || current_partition_date || ''') TO (''' || current_partition_date + 1 || ''')';

-- Increment the current date for the next iteration
current_partition_date := current_partition_date + 1;
END LOOP;
END;

Query:

SELECT public.create_partitioned_table_2d(
'2024-01-01',
'2024-12-31'
)

Note: create create_partitioned_table_2d is the name of the function and the dates specified below are the start date and end date respectively(creates partition for a year).