Reducing the size of a MySQL database when it is Too Large

Recently, our server ran out of storage space due to a unnecessarily huge database and I was tasked with diagnosing and remediating the issue.

I needed to:

1. Determine the remaining server disk space
2. Find the tables needing optimised
3. Remove large indexes & optimise column datatypes

Determine the remaining server disk space

The disk usage on Linux can be viewed by using the command df -h.

Find the tables needing optimised

• The following SQL can be used to view the tables using the most amount of data (sorted most to least).

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH) / 1024 / 1024) AS `data_size_mb`,
  ROUND((INDEX_LENGTH) / 1024 / 1024) AS `index_size_mb`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "your_database_name"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

• Remove keys & optimise columns where necessary (e.g. reducing field lengths from unneccesarily large values e.g. for char columns then reducing 5000 chars down to 1000, or changing bigints to either just int – or even tinyint). This can make a massive difference especially in tables with a large number of rows.
• To get a rough idea of the new length to which a varchar or char column should be set, then you can simply run:

SELECT MAX(LENGTH(`your_column`)) FROM `your_table`;

Remove large indexes & optimise column datatypes

Find large keys to drop within an InnoDB table (using the data in the “Find the tables needing optimised” step to target your efforts) by using this query. Placeholders should be changed to your own values: your_table and your_database.

SELECT
       sum(stat_value) pages,
       index_name,
       sum(stat_value) * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
           table_name = 'your_table'
       AND database_name = 'your_database'
       AND stat_description = 'Number of pages in the index'
GROUP BY
       index_name
ORDER BY
	size DESC;