our implementation of SRM has been receiving high levels of event data recently and as a consequence the disk is now full. For info the event table files take up approx 460G of a 500G disk, which currently has approx 50k free.
I am taking steps to reduce the volume of events but in the meantime the SRM mysql has stopped storing data and I need to reclaim some disk space to get it working. (I am also investigating adding disk space but that may take some time)
I have run RpmgrInitializeLandscape.sh for most of the landscapes, removing almost all events, however this does not free up the disk space. Apparently OPTIMIZE TABLE is what I need to run to reclaim the disk space, however this needs to create a temporary copy of the table and there isn't enough disk space for that
So my next thought was to run a mysqldump and restore from that and I am wondering if that will work.
I have used the backup command from the docs (Reporting Database Management - CA Spectrum - 10.2 and 10.2.1 - CA Technologies Documentation ) however that does not appear to backup the event table. The backup file is only 57k in size and there is no mention of the event table.
Can anyone confirm that is the case? As I want to retain some of the events then that will not work for me.
So I have also run a mysqldump to backup just the event table as follows
mysqldump --defaults-file=../my-spectrum.cnf --routines -uroot -proot reporting event > /backups/backup_filename.sql
And that backup file is 32G and contains statements that look like it will recreate the event table and insert the events.
To restore I would run something like
mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting < /backups/backup_filename.sql
My question then is if I do a restore in this way will that free up the disk space?
Thanks for any help.
Backing up and restoring the event table will not reduce the size of event table file, unless you have previously purged the unwanted events.
If you run theRpmgrInitializeLandscape utility for all landscapes, it will truncate all reporting tables.
Please take a look at the following documents:
Best practices to maintain the size of reporting database (SRM -Spectrum Report Manager)
TEC593028Title: CA Spectrum Database Maintenance Guidelines and Suggestionshttp://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec593028.aspx
SAP Portal Services
I mentioned in my post that I have run RpmgrInitializeLandscape.sh for most landscapes, in particular the landscape that had the vast majority of events. As a result the number of events returned by 'select count(*) from event' is massively reduced from what it was before the RpmgrInit.
Do you think in that case the mysqldump of the event table, followed by a restore will reduce the disk space used by the database?
I think you will also need to drop and recreate event table. But I never tried it within mysqldump command line.
MySQL :: MySQL 5.7 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
Write a DROP TABLE statement before each CREATE TABLE statement.
I would suggest to take note on how to manually recreate the event table by running this MySQL command line before dropping the event table, just in case you need to recreate it manually:
mysql> show create table event;
MySQL :: MySQL 5.7 Reference Manual :: 126.96.36.199 SHOW CREATE TABLE Syntax
So you will be able to manually recreate the event table.
The start of the mysqldump output file has table drop and then create sql commands. Below are the relevant
lines. Following that there are 10s of thousands of the INSERT INTO `event` lines which I assume are repopulating the event table.
It looks to me that the restore will do the table drop followed by a table create followed by repopulating the table with events. Would you agree?
-- MySQL dump 10.13 Distrib 5.5.53, for linux2.6 (x86_64)---- Host: localhost Database: reporting-- -------------------------------------------------------- Server version 5.5.53
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
---- Table structure for table `event`--
DROP TABLE IF EXISTS `event`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `event` ( `event_key` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `event_id` binary(26) NOT NULL, `landscape_h` int(10) unsigned NOT NULL, `model_key` int(10) unsigned NOT NULL DEFAULT '0', `time` datetime NOT NULL, `type` int(10) unsigned NOT NULL, `creator_id` int(10) unsigned NOT NULL, `event_msg` text, `server_precedence` int(10) unsigned DEFAULT '0', PRIMARY KEY (`event_key`,`time`), UNIQUE KEY `event_id_model_key_idx` (`event_id`,`model_key`,`time`), KEY `landscape_idx` (`landscape_h`), KEY `model_key_idx` (`model_key`), KEY `time_idx` (`time`), KEY `landscape_and_time_idx` (`landscape_h`,`time`), KEY `type_idx` (`type`), KEY `creator_id_idx` (`creator_id`)) ENGINE=InnoDB AUTO_INCREMENT=566579481 DEFAULT CHARSET=utf8 MAX_ROWS=1000000000 AVG_ROW_LENGTH=500/*!50500 PARTITION BY RANGE COLUMNS(`time`)(PARTITION EVENT_P18 VALUES LESS THAN ('2016-05-29 00:00:00') ENGINE = InnoDB, PARTITION EVENT_P19 VALUES LESS THAN ('2016-06-05 00:00:00') ENGINE = InnoDB,.
. PARTITION EVENT_P75 VALUES LESS THAN ('2017-07-02 00:00:00') ENGINE = InnoDB, PARTITION EVENT_P76 VALUES LESS THAN ('2017-07-09 00:00:00') ENGINE = InnoDB) */;/*!40101 SET character_set_client = @saved_cs_client */;
---- Dumping data for table `event`--
LOCK TABLES `event` WRITE;/*!40000 ALTER TABLE `event` DISABLE KEYS */;INSERT INTO `event` ...
Yes, I agree with you.
I'm going to wait until tomorrow to see how soon I can get the extra disk space, but if that is going to take too long then I think I will use this procedure.
Thanks for your input.
I have the same problem, were you able to solve it by running theRpmgrInitializeLandscape and then OPTIMIZE?
My reporting database is 400gb and i have only 50gb free.
Any suggestions are welcome.
If you run the RpmgrInitializeLandscape utility for all landscapes, it will truncate all reporting tables. There is no need to run the Optimize.
Note that Optimize only works for MyISAM tables.
With CA Spectrum release 9.4, reporting data is now stored using only InnoDB storage-engine based tables.
Make sure you have selected the purge option in Data Retention Policy in the SRM Preferences and also check the Archive Expert to confirm the old data has been purged.
The purge task is started at 12:00 AM.
Yes what I originally suggested above did resolve the issue ... that is the RpmgrInitialize followed by the mysqldump and then using mysql with the dump file to restore.
Note for obvious reasons the mysqldump save needs to be to a different filesystem
Worked a treat and reclaimed the disk space
How to reclaim unused space from InnoDB reporting. - CA Knowledge
Thanks JohnO2 and Silvio!
I will try your suggestions.
If you have deleted entries from a table and want to get the disk space actually freed, you will achieve that goal using the optimize table and also the dump/load procedure you mentioned. We did that successfully a lot of times. To be way more efficient (in terms of temporary needed disk space as well as speed), you could use gzip or a similar compression tool additionally.
Since the dump is just a file, it could be easily stored on a different filesystem, even a temporary NFS mount or something similar.
# mysqldump --add-drop-table --all-databases | gzip --best - > srm-dump.sql.gz
# zcat srm-dump.sql.gz | mysql
Hope that helps.