AHMED ZBYR

AHMED ZBYR

Biker, EV Enthusiast

When a MySQL or MariaDB server runs low on disk space, identifying which schemas and tables consume the most storage is the first step in capacity troubleshooting. MySQL exposes table metadata—including data size (data_length), index size (index_length), and unallocated space (data_free)—through SHOW TABLE STATUS and the information_schema.TABLES view.

This guide shows how to inspect table and database sizes in MySQL and interpret the results.

Inspect Table Metadata with SHOW TABLE STATUS

To view low-level storage metrics (storage engine, row count estimate, data length, index length, and free space) for all tables in a specific database (such as zabbix):

SHOW TABLE STATUS FROM zabbix;

For large schemas, querying information_schema.TABLES is much easier to sort and filter.

Check the Size of a Single Table

Use the query below to calculate the combined data and index size (in MB) for a specific table. Replace zabbix and history with your target schema and table names:

SELECT
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = 'zabbix'
  AND table_name = 'history';

Example Output

mysql> SELECT
    ->     table_name AS `Table`,
    ->     ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = 'zabbix'
    ->   AND table_name = 'history';
+---------+------------+
| Table   | Size in MB |
+---------+------------+
| history |       0.03 |
+---------+------------+
1 row in set (0.00 sec)

List All Tables Across All Databases by Size

To rank every table on the MySQL instance from largest to smallest:

SELECT
    table_schema AS `Database`,
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

Example Output

+--------------------+---------------------------------------+------------+
| Database           | Table                                 | Size in MB |
+--------------------+---------------------------------------+------------+
| zabbix             | items                                 |       3.41 |
| zabbix             | auditlog                              |       3.30 |
| zabbix             | functions                             |       3.17 |
| zabbix             | triggers                              |       2.67 |
| zabbix             | images                                |       1.53 |
| zabbix             | items_applications                    |       0.64 |
| zabbix             | events                                |       0.56 |
| mysql              | help_topic                            |       0.46 |
| zabbix             | housekeeper                           |       0.19 |
| zabbix             | history_uint                          |       0.16 |
| zabbix             | auditlog_details                      |       0.13 |
| zabbix             | opconditions                          |       0.03 |
| zabbix             | trigger_discovery                     |       0.03 |
| zabbix             | sysmap_url                            |       0.03 |
| zabbix             | proxy_autoreg_host                    |       0.03 |
| zabbix             | dbversion                             |       0.02 |
| zabbix             | zbxe_translation                      |       0.01 |
...
| information_schema | TABLES                                |       0.00 |
| information_schema | STATISTICS                            |       0.00 |
| information_schema | ENGINES                               |       0.00 |
+--------------------+---------------------------------------+------------+
157 rows in set (0.16 sec)

Summarize Total Size per Database Schema

To aggregate total disk usage (data, indexes, and total MB) per database schema:

SELECT
    table_schema AS `Database`,
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Data MB`,
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Index MB`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Total MB`,
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS `Free Space MB`
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Operational Notes on MySQL Disk Usage

  • InnoDB Fragmentation (data_free): Deleting rows with DELETE frees pages inside the InnoDB tablespace (data_free), but does not shrink the .ibd file on disk unless the table is rebuilt (for example, with OPTIMIZE TABLE when innodb_file_per_table=ON) or truncated (TRUNCATE TABLE).
  • Shared System Tablespace (ibdata1): If innodb_file_per_table was disabled when tables were created, table data resides inside the global ibdata1 file, which cannot be shrunk in-place without a logical dump and reload.
  • Binary Logs & Relay Logs: If filesystem usage (df -h /var/lib/mysql) is much higher than the sum reported by information_schema.TABLES, check binary logs (SHOW BINARY LOGS;), slow query logs, or temporary files in datadir.
  • Metadata Freshness: In MySQL 8.0+, cached table statistics in information_schema may lag behind recent writes; run ANALYZE TABLE <table_name>; if you need updated statistics.

References

You May Also Enjoy