How to measure the size of tables in MariaDB/MySQL

Written by - 0 comments

Published on January 27th 2016 - Listed in Database MySQL MariaDB


One cannot simply run "du" on the MySQL/MariaDB table files to measure the size. A much better and correct way is to get the information from the information_schema table:

MariaDB [(none)]> SELECT
    ->      table_schema as `Database`,
    ->      table_name AS `Table`,
    ->      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> ORDER BY (data_length + index_length) DESC LIMIT 0,10;

+----------+-----------------------------+------------+
| Database | Table                       | Size in MB |
+----------+-----------------------------+------------+
| shopware | s_core_snippets             |       5.03 |
| test1    | log_request                 |       4.63 |
| shopware | saferpaycw_transaction      |       4.53 |
| test1    | log_request_parameter       |       3.03 |
| shopware | s_core_sessions_backend     |       2.47 |
| shopware | s_articles_similar_shown_ro |       2.39 |
| test1    | object_container            |       1.59 |
| shopware | s_core_log                  |       1.52 |
| shopware | s_emarketing_lastarticles   |       1.38 |
| shopware | s_core_sessions             |       1.20 |
+----------+-----------------------------+------------+
10 rows in set (0.00 sec)

The query above lists the 10 largest tables over all databases on this MariaDB server.

And as a one-liner:

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

Found on http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database.

Back to the first sentence, where I mentioned "du". What does du think about the size of the largest table:

# du -ksh /var/lib/mysql/shopware/s_core_snippets.*
4.0K    /var/lib/mysql/shopware/s_core_snippets.frm
13M    /var/lib/mysql/shopware/s_core_snippets.ibd

Here we go.


Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.