Increasing PMM* Tables

[Maria Joseph Vimalan]

Dear Experts, 

We are running AE on 12.2.1 and we are planning to upgrade to 12.3.1 shortly. With 12.2.1 version , we are noticing the following tables growing on dailiy basis. 

TableName	SchemaName	RowCounts
PMMA	dbo	15246922
PMMAV	dbo	121353884

Can anyone let me know if any optimizations can be done for these two tables ? What would be impact if we truncate the entries of these two tables. 
Kindly share your expertise

Thank you in advance.

Best Regards
Vimalan

[Tatjana Radic]

Hi @Maria Joseph Vimalan, you can reduce the value of PERFORMANCE_KEEP_DAYS to lower value (5 or 10 for example) in UC_SYSTEM_SETTINGS. The default value is set to 30 (days).
This value defines how many days the data is stored in these tables, after which the data will be removed and countdown will start again.
​

------------------------------
Product Manager - Automation
CA Technologies, A Broadcom Company
------------------------------

[Maria Joseph Vimalan]

Thank you so much for your feedback. 
If we reduce the PERFORMANCE_KEEP_DAYS to 10 , does the old information will be removed automatically or should be removed manually

Kindly share your feedback

Thanks and Regards
Vimalan

[Tatjana Radic]

The tables are cleaned automatically.

------------------------------
Product Manager - Automation
CA Technologies, A Broadcom Company
------------------------------

[Tim Quakulinsky]

Hi Tatjana,

unfortunately these settings are not documented. In the PWP server log I find the following undocumented messages/parameters:
'PERFORMANCE' set to 'Y'.
'PERFORMANCE_MEASURE_INTERVAL' set to '000000001'.
'PERFORMANCE_CHECK_INTERVAL' set to '000000005'.
However, I do not find a message for PERFORMANCE_KEEP_DAYS...

I created ticket 20111943 for the missing documentation. Cross your fingers!

For your information: I already created a ticket in the last days, because I saw unknown warnings about PMM* in the JWPs.

Best regards,

Tim

------------------------------
Automation Evangelist
Fiducia & GAD IT AG
---
Mitglied des deutschsprachigen Automic-Anwendervereins FOKUS e.V.
Member of the German speaking Automic user association FOKUS e.V.
------------------------------

[Krum Ganev]

Hello All,

Recently we experienced major issue with our Production AE, the whole system went in hung state due these PMM* tables. (reference cases {Case#20099941} ## RCA for 20099839 has been updated)

The WPs was overflowed with a delete statements against the PMM* tables which took extremly long due staled indexes.

We are doing index rebuild every sunday and the issue occur on Tuesday night. At that time there was 170m records in PMMAV.

Adhoc index rebuild and cold restart of the AE resolved the issue. The official statement is that we oversized our system which caused the abnormal record count and staled indexes.

We were advised to set PERFORMANCE to N and reduce the WP/JWP count. The other two settings were not mentoined but it looks logical to have a control on the check interval and measure interval.

Our decision is to first decreese the WP/JWP count and afterwards , if still necessary , to dissable the Performance.

Did anyone tried these three parameters? Is there a possitive outcome?

And more logical question - if you are non-PLA customer why you will need these performance metrics as they are related to Telemetry (at least we was told so).

It looks like many things are missing from the official documentation. Like the alternation of the JWP - they are now multi-thread and can maintain up to 5 threads. Previously they was single-thread. Hence the recomendation for the total count of JWPs are outdated.

Best Regards,
Krum Ganev

Automic SME

DXC.Technology 

[Carsten Schmitz]

​Very interresting.

I just checked, we have 35M records in our PMMAV and another 3.2M in PMMA, and I also find these log messages:

20191117/095350.890 - U00011847 'PERFORMANCE' set to 'Y'.
20191117/095350.891 - U00011847 'PERFORMANCE_MEASURE_INTERVAL' set to '000000001'.
20191117/095350.893 - U00011847 'GENERATE_UNDEFINED_SCRIPT_VARS' set to 'N'.
20191117/095350.895 - U00011847 'PERFORMANCE_CHECK_INTERVAL' set to '000000005'.

We have issues with the PWP of the production systems hanging on startup for some weeks now, I wonder if this has anything to do with it. I think I will set "PERFORMANCE" to "N" right away. I remember having had a conversation about this parameter, and that it lacks documentation and has a useless name many months ago, but it looks not much if anything came of it.

One question @Krum Ganev, @Tatjana Radic: Are these PMM* tables used only for the telemetry plan, and if so, did Automic issue any guidance on how to clean them out?

Thanks!
​​

[Carsten Schmitz]

Side note: I also wish Automic would start to properly normalize their tables.

select count(*) from PMMAV where PMMAV_KEY='Timer-3';​
173311

What is this, is it really neccessary to store the same string values that is apparently itself a key 170k times? And this is just one example, the whole table looks like bloat of the highest order :(

[Krum Ganev]

@Carsten Schmitz i can not confirm or deny that unfortunatelly. 

The support person wrote :

"As I mentioned previously, the PMMA tables are normally handled by the Automation Engine and from your side nothing needs to be done. During the original production down we saw long calls against the PMMA tables. The performance settings I referenced will disable this. I believe this is used for telemetry data. Turning this off should have little risk and is a simple troubleshooting step."

As you can see this is not a definative answer or solid statement from their end. 

The only advises was to set the Performance to value N , increase the index rebuild frequency and reduce the WP/JWPs count.

[Carsten Schmitz]

​Hi @Krum Ganev,

thanks for that info.

Where do you disable it, have you already discovered that? There are literally zero Google search results for these settings and no hits in the documentation. UC_SYSTEM_SETTINGS? Or the ini file?

[Krum Ganev]

Hello @Carsten Schmitz

In Client 0, the vara object UC_SYSTEM_SETTINGS create another Key called 'PERFORMANCE' and in Value put 'N'.

We did not implement this yet.​

[Maria Joseph Vimalan]

Hello Carsten,

Just thought of sharing this information so that it helps someone.

I just got the feedback from Automic Support that , even though we set PERFORMANCE to N, the collection data still will be happening.

The PM* tables are not involved in Telemetry. The maintenance is built-in within the JWP - by default 30 days are kept and all data older than 30 days are removed by the JWP automatically.within the uc_system_settings this can be changed f.ex.:

PERFORMANCE_KEEP_DAYS -> 30 (default value)
PERFORMANCE_CLEANUP_BATCH_SIZE -> 1000 (default value)

Also, there is a known issue with the settings UC_SYSTEM_SETTINGS -> PERFORMANCE.
Even this parameter is set to N, the PM data is still collected and growing.

This issue is currently planned to be fixed in AE v12.3.2 and v12.4.0

So what I did is to reduce the PERFOMANCE_KEEP_DAYS from 30 to 1 day.

Thanks and Regards
Vimalan

[Carsten Schmitz]

Where do you disable it, 

Nevermind, found it by trial-and-error.

It's UC_SYSTEM_SETTINGS.

I can confirm that at least the setting triggers:

WPsrv_log_010_00.txt:20191125/171616.316 - U00011847 'PERFORMANCE' set to 'N'.

I will likely put this on my test system tomorrow, prod on Wednesday.

------------------------------
I will not respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums.
------------------------------

[Tony Ferraro]

Is it possible to analyze the data captured by these performance parameters?

I've long wanted to be able to extend the 'Process and Utilization' GUI metrics from 24 hours to 1 month, and analyze individual CP/WP process utilization to identify bottlenecks/slowness in the system over longer time periods.

I'm using 12.3.3, with default performance parameters(30 days).
My counts are here -

PMMA;   8,056,840
PMMAV; 55,869,384