how to Remove Reporting DB files

[Akash Ur Rehman keyani]

hello,

 

I have an issue that in my spectro server installation drive the reporting folder in MySQL folder is too large  and i want to freeup some space as reporting data is of no beneficial use for me. i am not using CABI and reporting. please guide me how to delete or remove that data from reporting folder.

event#p#event_p75.ibd

large files like this are found in it please guide how to get rid of these as these are of no use and my disk is 90% utilized.

[John OMahony]

Hi

It sounds to me that you need to free up the disk space that mysql does not release back to the system ... there is some discussion here with useful info ... Spectrum Report Manager: mysql disk full 

 

My advice would be to run a mysqldump sending the output to another disk and use the output from that to do a restore of the mysql db. That should reduce the size of the mysql/data directory considerably.  If it doesn't then you need to either reduce the amount of event data you log or increase disk space. 

After that you should implement a regular db_optimize as described in the manuals here ... Database Maintenance and Optimization - CA Spectrum - 10.3 - CA Technologies Documentation. This will free back the disk space on a regular basis, we run this once a week.

Note that inorder for this to work the amount of free disk, on the partition where mysql is installed, must be greater than the size of the mysql/data directory.

 

Hope that helps.

 

John

[Akash Ur Rehman keyani]

Dear John,

 

thanks for your advice its helpful for me but reporting DB is not of my use as i never use CABI or Spectrum reporting i just want to delete data from reporting Db to freeup disk space. event table in my reporting Db consume lot of space i want to just take 20 -30 days data..

[John OMahony]

Hi

So do you want to keep the 20-30 days of data that is currently in the db? 

 

If yes then prior to running the mysqldump you could delete all entries from the events table that are older than 20-30 days. You will still need to run through the mysqldump procedure to free the space.

After doing this you will need to tell the ArchMgr to only save 20-30 days worth of data. This is the MAX_EVENTS_RECORD field in the .configrc file and will need a restart of ArchMgr to take effect.

You will also need to implement the regular run of db_optimize.pl script to continuously free the disk space.

 

If you don't want to save the current data then it is possible to drop the events table and readd as an empty table but I have never done that. You will need to look up how to do that.

 

Note that no matter what you do you will need to implement the regular running of the db_optimize.pl script.

 

Regards, John

[Tarun Pamu]

Hi Akash,

 

The below Tech Doc should help you

 

How to initialize the Spectrum Report Manager (SRM - CA Knowledge 

 

Regards,

Tarun

[John OMahony]

Hi Tarun

I notice that Tech Doc is dated Feb 2018... when I tried using RpmgrInitializeLandscape last year (May 2017), it did not free up the disk space (see post referenced above). I will be interested to know if this has changed? Have you tried this and does it free up the disk space?

 

@Akash, if you can try this and let us know how it works that would be great.

 

Regards, John

[Akash Ur Rehman keyani]

Dear John/Tarun,

 

First i want to know that changes in reporting DB can effect my spectrum events db or not or any performance issue.

If it will not effect there is no issue with completely removing data or left 30 days data .. i want easy way whatever is easy and better i will go for it.

Secondly  yesterday i run some commands on my system and its continue till now see the screenshot i have attached now tell me what to do because it takes much time still command is running. 

 

i don't know how to run RpmgrInitializeLandscape i never find it.

 

 

..

[John OMahony]

Hi Akash

Firstly changes in the reporting db will not effect the Spectrum ddm db events. The reporting db in fact gets events from the ddm db.

It looks to me that you have (or maybe had) over 7 months of event data in your reporting db so depending on how many events your system generates, and the spec of your system, that can take some time to delete. 

Once you finally sort the mysql db out you will need to configure the SRM db to only retain 20-30 days of event data, as it looks like it is currently set to a lot more than that. That can be done in the Report Manager admin pages, under Administration on the OneClick web page.

Finally if you read the tech doc re RpmgrInitializeLandscape you would know where to find it ... "in the $SPECROOT/bin directory on the OneClick/Report Manager server"

 

Regards, John

[Akash Ur Rehman keyani]

Thanks John,

 

I wait for the deletion process to end and after that i will run this OPTIMIZE TABLE event; and then i will run   ./RpmgrInitializeLandscape.bat root root -initHist 20 -all as you said but it requires a downtime for spectro server as tomcat service need to be stop before its done. screenshot of preferences in administration is attached i changed this few days ago. 

[John OMahony]

Hi Akash

OPTIMIZE TABLE needs to create a temporary copy of the table, so you will need to have enough free space on the disk to do that. If you don't have free space greater than the size of the $SPECROOT/mysql/data/reporting directory then OPTIMIZE will not work.

The tech note Tarun sent claims that the RpmgrInitializeLandscape will free the disk space but that is not what I experienced over a year ago. Maybe that has changed since.

If neither of those work then you will need to run through mysqldump and restore or else work out how to drop and readd the events table.

 

Good luck, John

[Akash Ur Rehman keyani]

Its really a mess now how difficult it is to remove data from db. CA should arrange some easy methords for this. i am waiting for delete query to end up. update you after trying upper suggestion.

thanks for your help and support. 

if these not work i will shut spectro server and manually delete files from reporting directory. i am fed up from this.

[Matthew_Gay]

We have the archiver to actively delete data based on retention period set, and the initialize script to remove all data.

Is there something more you would be looking for from CA?

 

An optimize is required, unless you initialize.

[Akash Ur Rehman keyani]

JohnO2 please find the delete query results its haulted with an error. now whats the next way i move towards ths plan is flop now.

 

secondly if i use ./RpmgrInitializeLandscape.bat root root -initHist 20 -all i will not effect DDMD database?? if it will not then i should try this and need downtime for it.

 

[John OMahony]

Hi Akash

running RpmgrInitializeLandscape will not affec the ddm so yes go ahead and do that. I am interested to see if it does release the disk space back.

 

Actually before doing that, just check the reporting mysql db to make sure it isn't corrupt. I don't think it should be but maybe just login and run a select count(*) from event command ...

 

Regards, John

[Matthew_Gay]

The initialize script truncates the tables so it will give back disk space to the OS.

If it is corrupt you can just delete the entire database, create the reporting directory and restart Tomcat.

We will rebuilt the reporting database on startup in that scenario.

 

[Akash Ur Rehman keyani]

Dear matthew/Jhon,

Today morning i run the ./RpmgrInitializeLandscape.bat root root -initHist 30 -all  script and screenshot of results is attached and after duration of 6 hours i terminated it because i have to perform a urgent task as per customer requirement.

 After sometime i start this again and wait for it results for 1 day if it ends then good if not then i think i will shut spectro server and manually delete files from reporting folder in spectrum/MySQL/data directory.

[Jason Volpini]

Hi Akosh

 

I would not recommend deleting files from "/Mysql/Data” directory - that can really mess up mysql table structure, and possibly have to reinstall.

 

If the rpmgrinitializelandscape command cannot run due to lack of sufficient disk space headroom, or you do not have enough "time” to allow it to run to completion, then you can manually clear tables from mysql without deleting table files.

 

Log into mysql and run

 

TRUNCATE TABLE <tablename>;

 

This will completely WIPE the table, without needing disk space headroom. Caution: this is irreversible! Once a truncate command is run, that data is gone forever. But, this is a "hail mary” option to clear out data quickly.

 

Then you can run ./RpmgrInitializeLandscape.bat root root -initHist 30 -all to bring data back into SRM if you needed.

 

HTH

~Jay V

[Akash Ur Rehman keyani]

Dear,

 

Truncate script will remove all data from table but customer says that i need 30 days data.

 

As you mentioned above ./RpmgrInitializeLandscape.bat root root -initHist 30 -all … is this can get back 30 days data after deletion?? then it will solve my problem.

[Jason Volpini]

Correct. ./RpmgrInitializeLandscape.bat root root -initHist 30 -all = "initialize SRM, then poll all landscape DDM databases for last 30 days of data”

 

Assuming DDM is config to store 30 days or more of data. (default is 45 days)

[Akash Ur Rehman keyani]

Dear Volja01,

 

PF screenshot of truncate of max size of table i have in my reporting db. 

[Akash Ur Rehman keyani]

what the optimize command is saying about plz guide me.

[Akash Ur Rehman keyani]

Dear All thanks,

 

volja01  your truncate solution will sort out my issue free up disk space but i never reinitialize the landscape so is there its compulsory or not if table will store new data as it comes then its ok??

[Jason Volpini]

Akash,

 

If I understand your question correctly are you asking if you do not run repmgrinitializeLandscape, will SRM still populate new data by polling DDM? If that is your question then yes, it will populate new data (assuming SRM polling remains enabled). If you want "old” data (last 30 days, last 45 days, etc) then you must run repmgrinitializeLandscape command.

 

~Jay V

[Akash Ur Rehman keyani]

Volja,

 

yes i truncate only event table never change any other secondly there is no impact of removing data to customer so i just want to know that new data will populate in event table properly so the new data will be available if customer awakes and ask for new data in future.

 

Once again thanks a lot for your help.

[Jason Volpini]

I believe that technically OPTIMIZE is MyISAM engine command and in InnoDB engine is converted to ANALYZE command. It is the same function.