Reporting database folder is huge - SRM is not pruning old data
Article ID: 113241
Updated On:
Products
Issue/Introduction
NOTE: 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.
Feedback
