article Shrinking your MySQL DataStore

Article Contents

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"
    

Related Articles


Migrating Your DataStore To a New Database

RatingViews
article

This article outlines how to move your DataStore between different database types.

By: uptime Support | Date Created: 2-21-2007 | Last Modified: 6-30-2011 | Index: 142

  3909

MySQL Advanced Metrics

RatingViews
article

This article outlines the metrics collected by the up.time MySQL Advanced monitor.

By: uptime Support | Date Created: 1-26-2007 | Last Modified: 7-4-2011 | Index: 126

  3930

Default MySQL Database Users and Permissions

RatingViews
article

This article outlines the MySQL database users that are installed with up.time and their permissions

By: uptime Support | Date Created: 7-6-2007 | Last Modified: 6-28-2011 | Index: 211

  3013

How can I shrink my MySQL DataStore?

RatingViews
article

Shrinking your MySQL DataStore is a straightforward process but it will require some downtime of your up.time installation. In almost all cases it is also required that you delete some of your...

By: uptime Support | Date Created: 10-10-2009 | Last Modified: 8-13-2011 | Index: 419

  2792

Moving your MySQL DataStore to a Remote Host

RatingViews
article

The default up.time installation includes a bundled MySQL database. There are several scenarios in which it may be desirable to implement a dedicated database server. This article provides...

By: uptime Support | Date Created: 5-21-2011 | Last Modified: 8-12-2011 | Index: 515

  1958

User Comments



No comments have been posted.

Copyright © 2020 IDERA, Inc.   Legal   Privacy Statement