Header RSS Feed
 
If you only want to see the articles of a certain category, please click on the desired category below:
ALL Android Backup BSD Database Hacks Hardware Internet Linux Mail MySQL Monitoring Network Personal PHP Proxy Shell Solaris Unix Virtualization VMware Windows Wyse

How to measure the size of tables in MariaDB/MySQL
Wednesday - Jan 27th 2016 - by - (0 comments)

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.

Go to Homepage home
Linux Howtos how to's
Monitoring Plugins monitoring plugins
Links links

Valid HTML 4.01 Transitional
Valid CSS!
[Valid RSS]

7362 Days
until Death of Computers
Why?