Using MySQL's INFORMATION_SCHEMA to Find Large Tables
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)
Scott Keck-Warren
Scott is the Director of Technology at WeCare Connect where he strives to provide solutions for his customers needs. He's the father of two and can be found most weekends working on projects around the house with his loving partner.
Top Posts
- Working With Soft Deletes in Laravel (By Example)
- Fixing CMake was unable to find a build program corresponding to "Unix Makefiles"
- Upgrading to Laravel 8.x
- Get The Count of the Number of Users in an AD Group
- Multiple Vagrant VMs in One Vagrantfile
- Fixing the "this is larger than GitHub's recommended maximum file size of 50.00 MB" error
- Changing the Directory Vagrant Stores the VMs In
- Accepting Android SDK Licenses From The OSX Command Line
- Fixing the 'Target class [config] does not exist' Error
- Using Rectangle to Manage MacOS Windows