DX NetOps

Expand all | Collapse all

Spectrum Report Manager: mysql disk full

Silvio Okamoto

Silvio OkamotoMay 29, 2017 03:20 PM

  • 1.  Spectrum Report Manager: mysql disk full

    Posted May 29, 2017 11:01 AM

    Hi

    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.

     

    John



  • 2.  Re: Spectrum Report Manager: mysql disk full

    Broadcom Employee
    Posted May 29, 2017 12:21 PM

    Hi John,

     

    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) 

     

    TEC593028
    Title: CA Spectrum Database Maintenance Guidelines and Suggestions
    http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec593028.aspx

    SAP Portal Services 

     

    Thanks,

    Silvio



  • 3.  Re: Spectrum Report Manager: mysql disk full

    Posted May 29, 2017 01:23 PM

    Hi Silvio

    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?

     

    Regards, John



  • 4.  Re: Spectrum Report Manager: mysql disk full
    Best Answer

    Broadcom Employee
    Posted May 29, 2017 01:48 PM

    Hi John,

     

    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 

    --add-drop-table

    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 :: 13.7.5.10 SHOW CREATE TABLE Syntax 

    So you will be able to manually recreate the event table.

    Thanks,

    Silvio



  • 5.  Re: Spectrum Report Manager: mysql disk full

    Posted May 29, 2017 03:07 PM

    Hi Silvio

    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?

     

    Regards, John

    ----

     

    -- 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` ...



  • 6.  Re: Spectrum Report Manager: mysql disk full

    Broadcom Employee
    Posted May 29, 2017 03:20 PM

    Hi John,

     

    Yes, I agree with you.



  • 7.  Re: Spectrum Report Manager: mysql disk full

    Posted May 29, 2017 03:24 PM

    Hi Silvio

    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.

     

    Regards, John



  • 8.  Re: Spectrum Report Manager: mysql disk full

    Posted Jun 01, 2018 09:22 AM

    Hi JohnO2,

     

    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.

     

    Thanks!

    Facundo



  • 9.  Re: Spectrum Report Manager: mysql disk full

    Broadcom Employee
    Posted Jun 01, 2018 09:44 AM

    Hi Facundo,

     

    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.

     

    Thanks,

    Silvio



  • 10.  Re: Spectrum Report Manager: mysql disk full

    Posted Jun 01, 2018 09:57 AM

    Hi Facundo

    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  

     

    John



  • 11.  Re: Spectrum Report Manager: mysql disk full

    Broadcom Employee


  • 12.  Re: Spectrum Report Manager: mysql disk full

    Posted Jun 01, 2018 12:21 PM

    Thanks JohnO2 and Silvio!

    I will try your suggestions.



  • 13.  Re: Spectrum Report Manager: mysql disk full

    Posted Jun 06, 2017 02:15 AM

    Hi JohnO2,

     

    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.

     

    general approach:

    # mysqldump --add-drop-table --all-databases | gzip --best - > srm-dump.sql.gz

    # zcat srm-dump.sql.gz | mysql

     

    Hope that helps.

     

    Raphael