Big Statistics Server Table - Hint to improve Performance

[yogananda]

Dear All,

If you are using the embedded statistics server and you observe a general performance issue on the system when you try to open the administration console from the HANA studio, the overview page is hanging and does not return.

You can see that the following statement (or many of them) is running on the system from the threads overview:

(SELECT TOP 1 * FROM _SYS_STATISTICS.STATISTICS_LAST_CHECKS 

WHERE ALERT_ID = 0 ORDER BY ALERT_LAST_CHECK_TIMESTAMP DESC) 

UNION ALL 

SELECT * FROM _SYS_STATISTICS.STATISTICS_LAST_CHECKS 

WHERE ALERT_ID != 0

The system has high memory consumption/OOM, and you see the mentioned statement in the OOM dump or runtime dump.

You can see that the size of the alert statistics table is very big:

select round(memory_size_in_total/1024/1024/1024) as "SIZE(GB)" 

from m_cs_tables

where table_name = 'STATISTICS_ALERTS_BASE' 

and schema_name= '_SYS_STATISTICS';

In other words : 

SAP HANA database, statistics server alerts, high CPU consumption, Hana Studio administration console

Reason and Prerequisites

Reason:

The statistics server table _SYS_STATISTICS.STATISTICS_ALERTS_BASE is used to store the alert history. It is used for monitoring purposes and will be accessed by monitoring applications trying to get the alert information from HANA. If the table is too large (e.g. in GB ), queries involving this table might cause a performance impact on the system.

For example, the queries will be also issued by the studio automatically when you refresh the overview tab or open the alert tab.

Prerequisites:

• 
  
  You are using the embedded statistics server


• The statistics server table _SYS_STATISTICS.STATISTICS_ALERTS_BASE is relatively large

Solution

The configuration possibility for retention on this table will be released with SAP HANA SPS11.

On SPS11 revisions containing the feature, by default, we will check every day and delete alerts that are older than 42 days.

You can change the retention for collector 6002 by executing the following SQL adjusting RETENTION_DAYS_CURRENT:

update _SYS_STATISTICS.STATISTICS_SCHEDULE 

set RETENTION_DAYS_CURRENT = 25 

where ID = 6002

In this case, you set the retention days to 25, and the statistics server will then remove everyday alerts that are older than 25 days.

---

Workaround:

1. Manually truncate the table:
   

   TRUNCATE TABLE "_SYS_STATISTICS"."STATISTICS_ALERTS_BASE";​

   
   


2. Or delete the alert records which are older than X days (e.g. 25 days in the example):
   

   DELETE FROM "_SYS_STATISTICS"."STATISTICS_ALERTS_BASE" 
   
   WHERE "ALERT_TIMESTAMP" < add_days(CURRENT_TIMESTAMP, -25);​

   
   

 

---