Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Database Performance Analyzer (DPA) > 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: 34 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