How to check database and table sizes in MySQL and MariaDB

When a VPS starts running low on disk, the database is often the first suspect. MySQL and MariaDB keep size statistics for every table in information_schema, so a few queries tell you which database, and which table inside it, is using the space. This guide gives you those queries and shows how to compare them with what is really on disk.

Before you start

• A Linux VPS running MySQL 8 or MariaDB 10.x/11.x. The queries below work on both.
• SSH access as root or a sudo user. See Connect to your Linux VPS with SSH if you have not logged in yet.
• A database account that can read the tables you want to measure. The administrative account is simplest.

Open the MySQL client. On Ubuntu and Debian the root account usually logs in through the system socket, so sudo is enough:

sudo mysql

If your root account uses a password instead, run mysql -u root -p and type it at the prompt.

Size of every database

This query adds up the data and index size of all tables in each database and shows the result in megabytes, largest first:

SELECT table_schema AS db_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

You get output similar to this:

+--------------------+---------+
| db_name            | size_mb |
+--------------------+---------+
| shop               |  2841.6 |
| blog               |   312.4 |
| mysql              |     2.7 |
| sys                |     0.0 |
+--------------------+---------+

data_length is the size of the rows. index_length is the size of the indexes. Together they are a close estimate of the space a table uses.

Size of each table in one database

Once you know which database is large, list its tables. Replace shop with your database name:

SELECT table_name,
       table_rows,
       ROUND(data_length / 1024 / 1024, 1)  AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'shop'
ORDER BY total_mb DESC;

Find the biggest tables on the server

To see the top 10 tables across every database at once, leave out the WHERE filter on a single database and exclude the system schemas:

SELECT CONCAT(table_schema, '.', table_name) AS full_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY total_mb DESC
LIMIT 10;

The free_mb column shows space that is allocated to the table file but not in use, often left behind after large deletes. Typical big tables are logs, sessions, cache tables and order histories. A web app's log or session table can often be trimmed safely, but check the application's documentation first.

Refresh stale numbers

The numbers in information_schema are statistics, not live measurements.

• On MySQL 8, the server caches these statistics. The cache lifetime is set by information_schema_stats_expiry, which defaults to one day. Set it to 0 for your session to read fresh values:

  SET SESSION information_schema_stats_expiry = 0;

• On both MySQL and MariaDB, ANALYZE TABLE updates the statistics for a table:

  ANALYZE TABLE shop.orders;

ANALYZE TABLE is quick and safe on most tables, but run it outside busy hours on very large ones.

Compare with disk use

The database's own figures do not include binary logs, undo and redo logs, or temporary files. To see what is really on disk, first find the data directory:

SELECT @@datadir;

It is usually /var/lib/mysql/. Exit the client with exit, then measure the directory from the shell:

sudo du -sh /var/lib/mysql
sudo du -sh /var/lib/mysql/* | sort -h | tail -n 15

Each database has its own folder, and with innodb_file_per_table (the default) each InnoDB table has its own .ibd file. Files named binlog.000123 or mysql-bin.000123 are binary logs. They can grow large on busy servers. To see them from inside MySQL:

SHOW BINARY LOGS;

Check free space on the whole disk with:

df -h /

Check it works

• The database query lists every database with a size, and the totals look plausible for your sites.
• The sum of the database sizes is close to, but lower than, the du result for /var/lib/mysql. The difference is mostly logs and system files.
• After ANALYZE TABLE, a table you recently cleaned up shows a smaller size.

Troubleshooting

• A database is missing from the list: your account cannot see its tables. Run the query as the administrative user, or check grants with SHOW GRANTS;.
• Sizes have not changed after deleting rows: InnoDB keeps the freed space inside the table file. The space shows in data_free and is reused for new rows. OPTIMIZE TABLE rebuilds the table and can return space to the disk, but it locks and copies the table, so back up first and run it in a quiet period.
• du is far larger than the query totals: look for binary logs, a large ibdata1 file, or #innodb_temp and #innodb_redo folders. Old binary logs are the most common cause.
• The query is slow: information_schema scans can take a while on servers with thousands of tables. Filter by table_schema to measure one database at a time.

Next steps

• Take a copy before any cleanup: Back up and restore MySQL with mysqldump.
• Find the config file where log expiry and other settings live: MySQL and MariaDB config file locations.
• Keep the common admin commands at hand: MySQL command-line cheat sheet.