Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

 

This article explains how to delete historical data from the up.time MySQL data store.  See related articles for MS SQL and Oracle data stores.  The tables listed in the article are valid for up.time 7; although the logic applies to any release of up.time, you may receive errors for tables that do not exist.

 

You may want to delete historical data historical performance data if the up.time Uptime Infrastructure Monitor Archive process has timed out and data older than the Archive Policy still exists in the data store.  You may also choose to manually delete historical data historical performance data to free up the threads for regular monitoring that the up.time Uptime Infrastructure Monitor Archive process would occupy.  And another  Another reason to manually delete historical performance data is to shrink the size of the data store DataStore which is covered in the the Shrinking your MySQL DataStore FIXLINK knowledge  knowledge base article.

 

For ease, a stored procedure that accomplishes the same as the statements below is available by contacting Support at [email protected]

 

...

  1. The first step is to verify the oldest data samples within the Datastore.  Use the DataStore Profile script for a MySQL database found on the Support Portal's Tools & Utilities page.
  2. Once you know the oldest data sample, go to the Historical Data Purge Scripts page and download the MySQL trim script you want to run i.e. Ad-hoc, Procedure or Use Archive Settings.

    Warning
    WARNING: All deleted data will be lost.  Ensure that you complete a full data store backup before proceeding with

...

 

On the command line of the up.time Monitoring Station server, enter the following to log in to the MySQL client:

 

No Format
cd <uptime_dir>/mysql/bin
mysql -uuptime -puptime -P3308 --protocol=tcp uptime

 

 

  1. a trim script.
  2. Choose a date for which all data samples collected before this day will be deleted.  It is strongly recommended that deletions are completed in small chunks (e.g. 2 weeks or 1 month at a time) rather than attempting one large delete statement, so if the oldest data in the data store is from July 1,

...

  1. 2014, choose July 15,

...

  1. 2014 as the day to delete from.

 

Replace DATE in the first statement below with your target date.  The typical MySQL Date format is YYYY-MM-DD.  Sticking with the example of July 15, 2010 above, enter "2010-07-15%" in place of "DATE%".

mysql> set @day := "DATE%"

 

The following SELECT and DELETE statements will find the corresponding id for the date entered above and delete accordingly.

 

No Format
mysql> select min(id) into @ID from performance_sample where sample_time like @day;
mysql> delete from performance_aggregate where sample_id < @ID; 
mysql> delete from performance_cpu where sample_id < @ID;
mysql> delete from performance_disk where sample_id < @ID;
mysql> delete from performance_disk_total where sample_id < @ID;
mysql> delete from performance_esx3_workload where sample_id < @ID;
mysql> delete from performance_fscap where sample_id < @ID;
mysql> delete from performance_lpar_workload where sample_id < @ID;
mysql> delete from performance_network where sample_id < @ID;
mysql> delete from performance_nrm where sample_id < @ID;
mysql> delete from performance_psinfo where sample_id < @ID;
mysql> delete from performance_vxvol where sample_id < @ID;
mysql> delete from performance_who where sample_id < @ID;
mysql> delete from performance_sample where sample_time < @day;
mysql> select min(sample_id) into @ID from vmware_perf_sample where sample_time like @day;
mysql> delete from vmware_perf_aggregate where sample_id < @ID;
mysql> delete from vmware_perf_cluster where sample_id < @ID;
mysql> delete from vmware_perf_datastore_usage where sample_id < @ID;
mysql> delete from vmware_perf_datastore_vm_usage where sample_id < @ID;
mysql> delete from vmware_perf_disk_rate where sample_id < @ID;
mysql> delete from vmware_perf_entitlement where sample_id < @ID;
mysql> delete from vmware_perf_host_cpu where sample_id < @ID;
mysql> delete from vmware_perf_host_disk_io where sample_id < @ID;
mysql> delete from vmware_perf_host_disk_io_adv where sample_id < @ID;
mysql> delete from vmware_perf_host_network where sample_id < @ID;
mysql> delete from vmware_perf_host_power_state where sample_id < @ID;
mysql> delete from vmware_perf_mem where sample_id < @ID;
mysql> delete from vmware_perf_mem_advanced where sample_id < @ID;
mysql> delete from vmware_perf_network_rate where sample_id < @ID;
mysql> delete from vmware_perf_vm_cpu where sample_id < @ID;
mysql> delete from vmware_perf_vm_disk_io where sample_id < @ID;
mysql> delete from vmware_perf_vm_network where sample_id < @ID;
mysql> delete from vmware_perf_vm_power_state where sample_id < @ID;
mysql> delete from vmware_perf_vm_storage_usage where sample_id < @ID;
mysql> delete from vmware_perf_vm_vcpu where sample_id < @ID;
mysql> delete from vmware_perf_watts where sample_id < @ID;
mysql> delete from vmware_perf_sample where sample_time < @day;
mysql> delete from erdc_decimal_data where sampletime < @day;
mysql> delete from erdc_int_data where sampletime < @day;
mysql> delete from erdc_string_data where sampletime < @day;
mysql> delete from ranged_object_value where sample_time < @day;
mysql> truncate archive_delenda;

 

 

If you need to delete a large set of data, update the @day variable with a more recent date and run the SELECT and DELETE statements again.  Continuing with our example above of July 1 and July 15, 2010 we would enter the following:

No Format
mysql> set @day := "2010-07-29%";

 

  1. You should verify the historical performance data has been deleted by running a performance graph or report in the

...

  1. Uptime Infrastructure Monitor UI or simply running the Datastore Profile script again.

If you encounter any issues or have any questions regarding this process please do not hesitate to contact [email protected] for guidance.

See related articles for DataStore running on MS SQL or Oracle