Check fragmented space in MariaDB or MySQL

Check Fragmented space of databases in MariaDB or MySQL


SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

DB_NAME      |DB_SIZE|APPROXIMATED_FRAGMENTED_SPACE_GB|
-------------+-------+--------------------------------+
test         |   0.25|                            0.00|
sys          |   0.00|                            0.00|
classicmodels|   0.00|                            0.00|

Check fragmented space in tables of MariaDB or MySQL

SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

DB_NAME      |TABLE_NAME  |SIZE_GB|APPROXIMATED_FRAGMENTED_SPACE_GB|
-------------+------------+-------+--------------------------------+
classicmodels|employees   |   0.00|                            0.00|
test         |sales       |   0.00|                            0.00|
sys          |test        |   0.00|                            0.00|
test         |mytest      |   0.25|                            0.00|
classicmodels|productlines|   0.00|                            0.00|
classicmodels|orders      |   0.00|                            0.00|
test         |test1       |   0.00|                            0.00|
classicmodels|offices     |   0.00|                            0.00|

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.