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 up.time 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 assistance trimming specific performance data please contact support, 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 up.time keeps in the DataStore, make the the number of months smaller than the current number.
2. Stop and then restart the up.time Data Collector using the following commands:

   On Linux systems:

   > /etc/init.d/uptime_core stop
   > /etc/init.d/uptime_core start

   On Windows systems:

   > net stop "up.time Data Collector"
   > net start "up.time Data Collector"

When up.time 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, run the following command:

> cd <uptime_dir>mysqlbin
> mysqldump -uuptime -puptime -P3308 --protocol=tcp uptime > export.txt

The command above dumps your up.time 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 up.time 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 up.time 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 up.time 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 "up.time 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 "up.time 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 up.time processes:

   On Linux systems:

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

   On Windows systems:

   > net start "up.time Data Collector"
   > net start "up.time Web Server"