Application Perf Mgmt (BAC / BSM) Practitioners Forum
cancel

rum mysql purging data

Highlighted
parkar
Acclaimed Contributor.

rum mysql purging data

Can we purge rum mysql data less than 7 days

Cheers!

Nvr Blame a day in ur life....... Good day gives "happiness" & Bad day gives "experience" ......
11 REPLIES
Siggi Gladitsch
Acclaimed Contributor.

Re: rum mysql purging data

Hi parkar,

the RUM Admin Guide has all the details on that:

 

Maintaining the MySQL Database
------------------------------
..
Purging MySQL Real User Monitor Data

 

The data stored in the MySQL database is raw data used by Real User Monitor reports to present
session clickstreams and snapshots, and to enable queries for various report filters. The data is
sent to HP Business Service Management on request.

Raw data is kept in the MySQL database for a default period of 14 days, after which it is
automatically purged from the database. The amount of time raw data is stored in the MySQL
database can be changed to any number of days between 7 and 100.

 

To change the period of time that raw data is stored in the database:
1. Edit the <RUM root directory>\conf\partitionmanager\pm_tables_config.xml file on the RUM Engine machine.
2. Change the setting <archiveDuration units="DAYS" qty="14"/> from 14 to the required number of days.
3. Save the file.

 

Note: By increasing the number of days for which session click-stream data is stored, you
may significantly increase the size of the database, which may necessitate additional disk capacity.

 

Greetings

Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
parkar
Acclaimed Contributor.

Re: rum mysql purging data

we need less than 7

Regards
Cheers!

Nvr Blame a day in ur life....... Good day gives "happiness" & Bad day gives "experience" ......
Siggi Gladitsch
Acclaimed Contributor.

Re: rum mysql purging data

Hi parkar,

 

the documentaion tells 7 - 14 days, but I've seen cases where customers increased it to 28 days,

but I haven't seen customer going lower than 7 days,

but when I look at the configuration

..

  <partitionSize units="HOURS" qty="3"/>
  <archiveDuration units="DAYS" qty="14"/>
  <ensurePartitioning units="DAYS" qty="4"/>

..

 

I don't see a reason why you shouldn't be able to go below 7 days

(but I think you know what the implications are: you will have detailed data for a smaller timerange)

 

As you can see, there is another parameter ensurePartitioning which is set to 4 days,

so I wouldn't go below 4 days.

 

Greetings

Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
parkar
Acclaimed Contributor.

Re: rum mysql purging data

customer is not willing to provide more disk space and it is getting fulle in 3 days . Pl suggest how we can reduce

 

 

Cheers!

Nvr Blame a day in ur life....... Good day gives "happiness" & Bad day gives "experience" ......
Siggi Gladitsch
Acclaimed Contributor.

Re: rum mysql purging data

Hi parkar

 

> customer is not willing to provide more disk space and it is getting fulle in 3 days . Pl suggest how we can reduce

 

The RUM 9.22 Installation Guide states the following for disk space requirements for the engine
..
2 Disks:
- 70 GB for the Real User Monitor engine installation
- 200 GB fast hard drive (Minimum) or 2 TB SCSI disks RAID 0 (RAID 0 + 1 for high availability) (Recommended)
..

 

so if the customer is running a REAL RUM installation and tries to get away with 50 GB or alike, he's on the wrong track.
My test environment with just three RUM transactions defined consumes 28 GB!

 

The top disk space consumer in RUM usually is ClickStream.
If you don't need it and it is enabled, you can disable it via
BSM > Admin > End User Management > Monitoring > .. Real User Monitor tab > Data Collection
Real User Monitor Application Data Collection Page

Per the manual
..
Due to this RUM stores all action instances data (used for the Actions Summary report, Actions Raw Data report, and the session click stream)
Click-stream data accounts for the majority of the data stored in the MySQL database.
..

You can also reduce the amount of data stored for Clickstream:
- http://[Real User Monitor engine machine name]:8180/jmx-console.
   In the JMX Agent View, scroll down to the RUM.modules section and click service=MaintenanceDBConfigurationJMX.
   In the PurgingDaysCounter parameter, change the setting to the required number of days (between 7 and 100).
   Click the "Apply Changes" button.
Note that 7 is a hardcoded limit, JMX fails if you try to go below 7 days

 

If you definitely need to go below 7 days, you can perform the following steps:

- stop RUM engine

- modify E:\HPRUM\conf\partitionmanager\pm_tables_config.xml
   <archiveDuration units="DAYS" qty="3"/>
   <ensurePartitioning units="DAYS" qty="1"/>

- start RUM engine

 

This will reduce the amount of data kept in the DB to 3 days.
You will see a lot of activity with RUM Engine's partition manager until it is done.

 

You can verify the settings RUM's PM uses via
 http://<RUM_engine>:8180/jmx-console/
  RUM.modules
   service=PartitionManager
    showStatus

 

Please also note that it is not possible to decrease the size of disk space allocated by RUM MySQL without recreating the database schema. Basically the MySQL database is growing as long as it needs to, and once data has been freed up, it remains free inside the table space, meaning no more data can be written into. It will not increase more than that size but the disk space will not be released. The only option here is to recreate the database:

 

Recreate database schema (RUM raw data for the past (14) days will be lost):

1. Stop your MySQL DB (via start menu)
2. Physically delete the following (from HPRUM_DATA folder):
    • Schema folder
    • ib_logfile0
    • ib_logfile1
    • ibdata1
3. Start your MySQL DB (via start menu)
4. create new RUM DB
5. Make sure purging policy is set to 7 days from RUM engine JMX console to reduce data to keep.

 

Just to repeat:
we support going down to 7 days only!
While my recommendatios seem to work, it would not be supported.

 

Greetings
Siggi

Customer Support
Micro Focus

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
blselenium
Super Contributor.

Re: rum mysql purging data

Dear Team,

 

Lets say if MYSQL purging policy is of 14 days then can anyone resolves the below mentioned queries

what is the Purging frequency? i.e at what intervals does it take place

How deletion of data takes place

 

Regards,

Blselenium

 

AlexPereverzev
Outstanding Contributor.

Re: rum mysql purging data

Hi Blselenium,

It is not part of MySQL. RUM Engine has it's own module called "Partition Manager", this functionality is not a standard RDBMS functionality. PM creates new partitions for storing new raw data (incoming), manage them and then purge when they are old.

As you know RUM is storing data in 2 places: RUM Engine DB for raw data and BSM Profile DB for aggregated data. After raw data being purged, some reports seamlessly query data from profile DB and some raw data became not available.

Best regards, Alexey

parkar
Acclaimed Contributor.

Re: rum mysql purging data

At what interval raw data gets purged ?

Consider I have defined 45 days by changing default 14 days interval period..now suppose if I want to make it 30 than how it take place..it deletes immediately beyond 30 days or at defined time interval..where it is defined ?
Cheers!

Nvr Blame a day in ur life....... Good day gives "happiness" & Bad day gives "experience" ......
AlexPereverzev
Outstanding Contributor.

Re: rum mysql purging data

Hi Parkar,

As you can see from config files, default partition size is 3 hours + there is a scheduler running by default hourly looking for old partitions to delete. I do not think that change of those default values is supported.

BTW, we recommend 2T of disk space to store raw data (that is calculated max value to highly loaded Engine). Extending purging period to 45 days will require to allocate aprox. 6T for DB. I think that can make all you queries to DB slower. There should be a serious reason to have all that. IMHO, better way is to configure properly aggregated data and store it properly in BSM profile DB.

Best regards, Alexey 

 

parkar
Acclaimed Contributor.

Re: rum mysql purging data

Thanks alexey for response

Are you saying scheduler runs every one hour and check For partition to be deleted in LIFO (last in first out ) manner
And PM creates new partitions for new raw data and default partition size 3 hrs.

Also is there any flag which is made before deletion or direct delete operation is performed on database rows basis on time stamp ?

Regards
Cheers!

Nvr Blame a day in ur life....... Good day gives "happiness" & Bad day gives "experience" ......
AlexPereverzev
Outstanding Contributor.

Re: rum mysql purging data

Hi Parkar,

I did not say anything about Scheduler work - just that it is running by default hourly. About purge - i think it is just dropping old partitions, it is much more effective.

Alexey