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

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. Open a bash shell (bash -login)

2. Navigate to the $SPECROOT/mysql/bin/ directory

cd mysql/bin OR cd $SPECROOT/mysql/bin

3. Launch the MySQL command prompt:

./mysql.exe -uroot -proot reporting (Windows)

OR

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting -A (Linux)

 

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.

 

Download the Spectrum_10.02.03.BMP_10.2.303 from:

https://ftp.broadcom.com/user/downloads/CAproducts/CA-SPECTRUM/Updates/GA/Spectrum_10.02.03.BMP_10.2.303/