search cancel

Reporting database folder is huge - SRM is not pruning old data

book

Article ID: 113241

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

NOTE: Starting from DX NetOps Spectrum 21.2.4, the default root password for MySql is "MySqlR00t". For DX NetOps Spectrum versions prior to 21.2.4, the default root password is "root". In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

The reporting directory ($SPECROOT/mysql/data/reporting) is huge. Even setting the Data Retention policy to purge, the reporting directory size does not decrease.

Based on the following MySQL queries, the first entry in the event table is from 2018-04-27 14:01:53.
The goal is to purge all data up to 2018-05-06 00:00:00.

1. Log into the system as the user that owns the Spectrum installation

2. If on Windows, start a bash shell by running "bash -login"

3. cd to the $SPECROOT/mysql/bin/ directory

4. Enter the following command to log into mysql:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting -A

5. Enter the following commands at the mysql prompt:

mysql> select (min(time)) as first, (max(time)) as last from event; 
+---------------------+---------------------+ 
| first               | last                | 
+---------------------+---------------------+ 
| 2018-04-27 14:01:53 | 2018-09-06 10:01:56 | 
+---------------------+---------------------+ 

mysql> SELECT * FROM partitionlog; 
+--------------+------------+----------------+---------------------+---------------------+---------------------+
| partition_id | table_name | partition_name | values_less_than    | creation_time       | destroy_time        |
+--------------+------------+----------------+---------------------+---------------------+---------------------+
|           52 |      event |      EVENT_P52 | 2018-04-22 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           53 |      event |      EVENT_P53 | 2018-04-29 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           54 |      event |      EVENT_P54 | 2018-05-06 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           55 |      event |      EVENT_P55 | 2018-05-13 00:00:00 | 2018-04-26 13:36:13 |                NULL | 

 

Environment

CA Spectrum 20.x

Resolution

  • With Spectrum 10.0 onwards, the event table of reporting database has been enhanced (InnoDB engine).
  • To improve the Events reports performance, the ‘events’ table in the MySQL Reporting database has been partitioned.
  • 52 partitions – each partition containing 1 week’s data – are maintained for a period of 1 year.
  • An additional 6 partitions are created to cater to the 45-day window which is the default Archive Manager data retention period.

All partitioning-related administration is handled internally no additional administration is required.

Sometimes when the Data Retention Policy is changed from "all data" to "purge", the SRM will have to purge a high amount of data which will lock the table and all other subsequent tasks will be put on hold.

1. Ensure the MySQL server is not stuck trying to purge the historical data in the reporting database. Run the following MySQL query: SHOW FULL PROCESSLIST\G

If the purge task is in progress you have to wait until it finishes.

2. Run the MySQL query to determine the range of partitioned tables that have to be truncated:

 

Note: Perform the steps with caution because once the table is truncated, you cannot recover the data.

 

mysql> SELECT * FROM partitionlog; 
+--------------+------------+----------------+---------------------+---------------------+---------------------+
| partition_id | table_name | partition_name | values_less_than    | creation_time       | destroy_time        |
+--------------+------------+----------------+---------------------+---------------------+---------------------+
|            1 | event      | EVENT_P1       | 2017-04-30 00:00:00 | 2018-04-26 13:36:13 | 2018-04-30 13:12:13 |
|            2 | event      | EVENT_P2       | 2017-05-07 00:00:00 | 2018-04-26 13:36:13 | 2018-05-07 13:12:13 |
|            3 | event      | EVENT_P3       | 2017-05-14 00:00:00 | 2018-04-26 13:36:13 | 2018-05-14 07:30:01 |
|            4 | event      | EVENT_P4       | 2017-05-21 00:00:00 | 2018-04-26 13:36:13 | 2018-05-21 22:35:41 |
|            5 | event      | EVENT_P5       | 2017-05-28 00:00:00 | 2018-04-26 13:36:13 | 2018-05-28 11:59:08 |
|            6 | event      | EVENT_P6       | 2017-06-04 00:00:00 | 2018-04-26 13:36:13 | 2018-06-04 10:44:55 |
|            7 | event      | EVENT_P7       | 2017-06-11 00:00:00 | 2018-04-26 13:36:13 | 2018-06-11 10:44:55 |
|            8 | event      | EVENT_P8       | 2017-06-18 00:00:00 | 2018-04-26 13:36:13 | 2018-06-18 10:44:55 |
|            9 | event      | EVENT_P9       | 2017-06-25 00:00:00 | 2018-04-26 13:36:13 | 2018-06-25 11:40:21 |
|           10 | event      | EVENT_P10      | 2017-07-02 00:00:00 | 2018-04-26 13:36:13 | 2018-07-02 11:40:21 |
|           11 | event      | EVENT_P11      | 2017-07-09 00:00:00 | 2018-04-26 13:36:13 | 2018-07-09 11:40:21 |
|           12 | event      | EVENT_P12      | 2017-07-16 00:00:00 | 2018-04-26 13:36:13 | 2018-08-06 10:15:14 |
|           13 | event      | EVENT_P13      | 2017-07-23 00:00:00 | 2018-04-26 13:36:13 | 2018-08-06 10:15:14 |
|           14 | event      | EVENT_P14      | 2017-07-30 00:00:00 | 2018-04-26 13:36:13 | 2018-08-06 10:15:14 |
|           15 | event      | EVENT_P15      | 2017-08-06 00:00:00 | 2018-04-26 13:36:13 | 2018-08-06 10:15:14 |
|           16 | event      | EVENT_P16      | 2017-08-13 00:00:00 | 2018-04-26 13:36:13 | 2018-08-13 10:15:14 |
|           17 | event      | EVENT_P17      | 2017-08-20 00:00:00 | 2018-04-26 13:36:13 | 2018-08-20 10:15:14 |
|           18 | event      | EVENT_P18      | 2017-08-27 00:00:00 | 2018-04-26 13:36:13 | 2018-08-27 10:15:14 |
|           19 | event      | EVENT_P19      | 2017-09-03 00:00:00 | 2018-04-26 13:36:13 | 2018-09-03 15:12:35 |
|           20 | event      | EVENT_P20      | 2017-09-10 00:00:00 | 2018-04-26 13:36:13 | NULL                |
|           21 | event      | EVENT_P21      | 2017-09-17 00:00:00 | 2018-04-26 13:36:13 | NULL                |
.
.
.
|           52 |      event |      EVENT_P52 | 2018-04-22 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           53 |      event |      EVENT_P53 | 2018-04-29 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           54 |      event |      EVENT_P54 | 2018-05-06 00:00:00 | 2018-04-26 13:36:13 |                NULL | 
|           55 |      event |      EVENT_P55 | 2018-05-13 00:00:00 | 2018-04-26 13:36:13 |                NULL | 

 

3. Run the following MySQL command to truncate the partitioned event table, from 45 to 54. The truncate command runs pretty quickly, a few seconds.

mysql> alter table event truncate partition event_p45, event_p46, event_p47, event_p48, event_p49,  event_p50, event_p51, event_p52, event_p53, event_p54;
Query OK, 0 rows affected (0.08 sec)

 

4. The event table was truncated and the old data discarded:

mysql> select (min(time)) as first, (max(time)) as last from event;
+---------------------+---------------------+
| first               | last                |
+---------------------+---------------------+
| 2018-05-06 00:00:28 | 2018-09-06 10:01:56 |
+---------------------+---------------------+
1 row in set (0.01 sec)

-rwxrwxr-x+ 1 Administrators SYSTEM  92274688 Aug 30 14:51 event#p#event_p62.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  75497472 Aug 30 14:51 event#p#event_p61.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  79691776 Aug 30 14:51 event#p#event_p60.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  62914560 Aug 30 14:51 event#p#event_p59.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  50331648 Aug 30 14:51 event#p#event_p58.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  50331648 Aug 30 14:51 event#p#event_p57.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  67108864 Aug 30 14:51 event#p#event_p56.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM  83886080 Aug 30 14:51 event#p#event_p55.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p54.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p53.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p52.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p51.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p50.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p49.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p48.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p47.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p46.ibd
-rwxrwxr-x+ 1 Administrators SYSTEM    212992 Sep  6 10:14 event#p#event_p45.ibd

Additional Information

This known issue has been fixed in 10.2.3_BMP_10.2.303 and versions from  CA Spectrum 10.3.2 onwards.

 

Please contact Spectrum support for copies of these patches/media.