Shrinking your MySQL DataStore

Article Contents

• Overview
• Step 1 - Trim out Performance Data that You No Longer Need
• Step 2 - Export your Configuration and Performance Data
• Step 3 - Rebuild your DataStore

Overview

At times it may be necessary to reduce the disk space that your MySQL DataStore consumes on your monitoring station. The process of reducing your DataStore size follows the general steps that are outlined in this article.

Step 1 - Trim out Performance Data that You No Longer Need

In order to shrink your DataStore you will need to remove some portions of your historical performance data. Removing performance data will not shrink the DataStore but it will free up space within the DataStore tablespace, this will stop the DataStore from growing until all of the space used for the trimmed data has been filled with new performance data.

There are a few different ways to trim out historical performance data from your DataStore. The most simplistic method is to change your Uptime Infrastructure Monitor archive policy to the number of months that you would like to maintain but you can also run manual SQL commands to remove very specific sets of data. For trimming specific performance data please refer to the following articles: Manually trim performance data from a MySQL DataStore, Manually trim performance data from a MS SQL DataStore, or Manually trim performance data from an Oracle DataStore.

To trim out performance data using the archive policy use the steps below.

1. Change the archive policy to the number of months that you want to retain -- for example one month. To reduce the amount of data that Uptime Infrastructure Monitor keeps in the DataStore, make the the number of months smaller than the current number.
2. Stop and then restart the Uptime Infrastructure Monitor Data Collector using the following commands: Starting (or restarting) and Stopping Uptime Infrastructure Monitor
   
   

When Uptime Infrastructure Monitor restarts, it should start archiving data. The archived files will be placed in the directory <uptime_dir>/archives. Once the archiving process is completed, the entry Finished Archiving should appear in the file <uptime_dir>/logs/uptime.log.

Step 2 - Export your Configuration and Performance Data

Trimming out performance data from your DataStore does not shrink the size of your DataStore on disk alone. You must also export your configuration and performance data after you have trimmed it down. Then, you can rebuild the DataStore and reduce its size.

To export your configuration and performance information, first stop the uptime_core or Data Collector service, so that Uptime Infrastructure Monitor isn't actively writing to the database: Starting (or restarting) and Stopping Uptime Infrastructure Monitor

Now run the following command to export the database contents:

> cd <uptime_dir>mysql\bin
> mysqldump -uroot -puptimerocks -P3308 --protocol=tcp uptime > export.txt

The command above dumps your Uptime Infrastructure Monitor DataStore information into a simple text file named export.txt. This file will be very large, so plan for this command to take some time to complete. The settings used below are based on a default Uptime Infrastructure Monitor installation. Check your uptime.conf file for the settings that are specific to your installation. 

Step 3 - Rebuild your DataStore

After exporting your configuration and performance data, you must delete and rebuild your DataStore. When the DataStore is rebuilt based on your trimmed performance data it will consume only the smaller amount of space, successfully shrinking the size of the DataStore on disk.

To rebuild your DataStore, perform the following steps. This process will remove all Uptime Infrastructure Monitor data from the DataStore. Be sure that you have completed step 2 above to backup your configuration before performing these steps.

1. Run the following commands to delete the Uptime Infrastructure Monitor DataStore:

   On Linux systems:

   > mysql -uuptime -puptime -P3308 --protocol=tcp 
   mysql> use uptime; 
   mysql> drop database uptime; 
   mysql> exit;
   > /etc/init.d/uptime_datastore stop

   On Windows systems:

   > mysql -uuptime -puptime -P3308 --protocol=tcp 
   mysql> use uptime; 
   mysql> drop database uptime; 
   mysql> exit;
   > net stop "Uptime Data Store"

2. Delete the following files from your DataStore directory:

   ibdata1
   ib_logfile0
   ib_logfile1

3. Run the following commands to restart your database and rebuild your DataStore: 

   On Linux systems: 

   > /etc/init.d/uptime_datastore start
   > mysql -uuptime -puptime -P3308 --protocol=tcp
   mysql> create database uptime;
   mysql> exit;
   > mysql -uuptime -puptime -P3308 --protocol=tcp -A -N -f uptime < export.txt

   On Windows systems:

   > net start "Uptime Data Store"
   > mysql -uuptime -puptime -P3308 --protocol=tcp
   mysql> create database uptime;
   mysql> exit;
   > mysql -uuptime -puptime -P3308 --protocol=tcp -A -N -f uptime < export.txt

4. Run the following commands to restart the remaining Uptime Infrastructure Monitor processes: 

   On Linux systems:

   /etc/init.d/uptime_core start
   /etc/init.d/uptime_httpd start
   /etc/init.d/uptime_controller start

   On Windows systems:

   > net start "Uptime Data Collector"
   > net start "Uptime Web Server"
   > net start "Uptime Controller"