MySQL Logo

Every so often it’s a good idea to see where your server’s used hard drive space is going. You can use df to troubleshoot but it’s also nice to have a quick query to see which tables are using the most space. With this information you can determine what to do to fix it (if anything).

Please note: All examples in this article were written using MySQL 5.5 and done in a development environment. Please do not try this in a production environment unless you are sure of the ramifications.

MySQL provides a schema named INFORMATION_SCHEMA that contains a series of tables useful for finding out information about the schema(s) in your database server. Because it’s a schema you can interact with it using SQL commands:

mysql> use INFORMATION_SCHEMA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_BUFFER_PAGE                    |
| INNODB_TRX                            |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_LOCK_WAITS                     |
| INNODB_CMPMEM                         |
| INNODB_CMP                            |
| INNODB_LOCKS                          |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_RESET                      |
| INNODB_BUFFER_PAGE_LRU                |
+---------------------------------------+
40 rows in set (0.00 sec)

The table we’re interested in for our purposes today is the TABLES table. You can see the results of a select query against it below.

mysql> select * from INFORMATION_SCHEMA.TABLES limit 1\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 384
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16434816
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2018-12-02 16:13:44
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=43690
  TABLE_COMMENT: 
1 row in set (0.01 sec)

The TABLE_SCHEMA column is the schema that the table exists within and the INFORMATION_SCHEMA.TABLES table contains rows for every schema’s tables so we’re going to want to limit it to just the schema we’re interested in:

mysql> select *
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA="wordpress"limit 1\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: wordpress
     TABLE_NAME: wp_2_commentmeta
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 458
 AVG_ROW_LENGTH: 84
    DATA_LENGTH: 38608
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 28672
      DATA_FREE: 0
 AUTO_INCREMENT: 1351156
    CREATE_TIME: 2018-04-03 16:27:57
    UPDATE_TIME: 2018-04-03 16:27:57
     CHECK_TIME: 2018-04-03 16:27:57
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 row in set (0.00 sec)

Now that we’re looking at just the wordpress tables we need to look at two columns to determine the size of the database the DATA_LENGTH and INDEX_LENGTH. These hold the size of all the data in the table and the size of the indexes in bytes.

mysql> select TABLE_NAME, DATA_LENGTH, INDEX_LENGTH 
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA="wordpress"
limit 1;
+------------------+-------------+--------------+
| TABLE_NAME       | DATA_LENGTH | INDEX_LENGTH |
+------------------+-------------+--------------+
| wp_2_commentmeta |       38608 |        28672 |
+------------------+-------------+--------------+
1 row in set (0.01 sec)

Now we’re going to look at adding them together so we can get a better understanding of how much space the table is using and sort the results based on the size so we can quickly see what’s using the most space.

mysql> select TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH) as "Size"
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA="wordpress"
order by (DATA_LENGTH+INDEX_LENGTH) desc
limit 5;
+-----------------------+----------+
| TABLE_NAME            | Size     |
+-----------------------+----------+
| wp_2_redirection_logs | 43497224 |
| wp_commentmeta        |  3605916 |
| wp_comments           |  3432156 |
| wp_2_posts            |  2198264 |
| wp_5_comments         |  1671168 |
+-----------------------+----------+
5 rows in set (0.01 sec)

To make is easier to understand we can throw in some division to see the size in megabytes instead of bytes.

mysql> select TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 as "Size (MB)"
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA="wordpress"
order by (DATA_LENGTH+INDEX_LENGTH) desc
limit 5;
+-----------------------+-------------+
| TABLE_NAME            | Size (MB)   |
+-----------------------+-------------+
| wp_2_redirection_logs | 41.48218536 |
| wp_commentmeta        |  3.43886948 |
| wp_comments           |  3.27315903 |
| wp_2_posts            |  2.09642792 |
| wp_5_comments         |  1.59375000 |
+-----------------------+-------------+
5 rows in set (0.01 sec)