Submit a ticketCall us

WebinarWebinar: A checklist for planning your Network Performance Monitor (NPM) upgrade

Are you ready for your next upgrade? To help you plan smoothly, join this webcast to learn more about, SolarWinds® Orion® Installer, SolarWinds Upgrade Advisor, Upgrades Guides, Training Videos, and other resources available. We’ll share key upgrade planning considerations, lessons learned from customers with practical advice from SolarWinds Product Experts. We’ll also give practical tips to identify the estimated time needed and resources, how to prepare the business and IT staff for changes, ways to plan for required system changes, and more.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > MySQL repository table optimization for DPA

MySQL repository table optimization for DPA

Created by Anthony.Rinaldi_ret, last modified by Anthony.Rinaldi_ret on Sep 26, 2016

Views: 648 Votes: 0 Revisions: 2

Updated July 5, 2016

Overview

When data is deleted from InnoDB tables in MySQL, the file sizes never shrink so that the space is given back to the operating system.

You can find more information on this topic here:

Environment

  • DPA 10.2 and later

Detail

DPA uses the innodb_file_per_table setting. This means that it stores its individual tablespace information in separate IBD files, rather than just one large file for all of the tables. There are several tables that DPA will frequently add data to and later delete data from, which can lead to large files that are not utilizing all the allocated space. DPA may reuse this "wasted" space in the future, but if disk space is an issue you may want to reclaim this space using MySQL utilities.

Identify inefficient tables

You can modify the following query to identify which tables are not efficiently utilizing allocated space:

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = '[NAME_OF_DPA_DATABASE]'
    AND (100*(data_free/(data_length + index_length+data_free))) > [MINIMUM_PERCENT_WASTED]
    AND (data_length + index_length + data_free) > [MINIMUM_FILESIZE_IN_BYTES]
ORDER BY (data_length + index_length + data_free) DESC;

Example

This example finds all of the files at least 50 MB in size (52428800 bytes), with greater than 75% space wasted, in the dpa_repo database:

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = 'dpa_repo'
    AND (100*(data_free/(data_length + index_length+data_free))) > 75
    AND (data_length + index_length + data_free) > 52428800
ORDER BY (data_length + index_length + data_free) DESC;

MySQLcheck utility

If the results show that there are files with significant space that can be reclaimed, you can use the mysqlcheck command. This tells MySQL to repair and optimize all tables in the DPA repository database.

You can find more information in the MySQL documentation for mysqlcheck:

This process will take a while to run, but should not prevent the database from being used. To minimize the impact, however, SolarWinds recommends running it during a maintenance period.

From the command line, enter the following:

Windows:
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe" --user=root --password=password --databases dpa_repo --auto-repair --optimize

Linux:
mysqlcheck --user=root --password=password --databases dpa_repo --auto-repair --optimize

You can find more options, such as running optimization on one table at a time, in the MySQL documentation for OPTIMIZE TABLE:

 

 

 

 

Last modified

Tags

Classifications

Public