Determine the Space Used By MySQL 5 Schemas and Tables

I recently posted a script that can be used to obtain information about the space used by tables in SQL Server 2005/2008 databases.  The following scripts can be used to obtain equivalent information for MySQL 5+ databases.

Get the size of and free space of each schema

SELECT s.schema_name, 
   ROUND((SUM(t.data_length + t.index_length))/1024/1024,2) TotalSizeInMB, 
   ROUND(((SUM(t.data_length + t.index_length))-SUM(t.data_free))/1024/1024,2) DataUsedInMB, 
   ROUND(SUM(data_free)/1024/1024,2) DataFreeInMB, 
   ROUND((((SUM(t.data_length + t.index_length))-SUM(t.data_free)) /
      ((SUM(t.data_length + t.index_length)))*100),2) PctUsedInMB
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t 
   ON s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size_in_mb DESC;

 
Example output:

schema_name total_size_in_mb data_used_in_mb data_free_in_mb pct_used_in_mb
staging     961.22           932.68          28.54           97.03
production  950.34           913.37          36.97           96.11
mysql       0.50             0.50            0.00            100.00

 

Get the space used by each table in each schema

SELECT s.schema_name, table_name,
   ROUND((SUM(t.data_length + t.index_length))/1024/1024,2) TotalSizeInMB, 
   ROUND(((SUM(t.data_length + t.index_length))-SUM(t.data_free))/1024/1024,2) DataUsedInMB,
   ROUND(SUM(data_free)/1024/1024,2) DataFreeInMB,
   ROUND((((SUM(t.data_length + t.index_length))-SUM(t.data_free)) /
      
((SUM(t.data_length + t.index_length)))*100),2) PctUsedInMB
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
   ON s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
ORDER BY schema_name, total_size_in_mb DESC;

 
Example output:

                          total_size data_used data_free pct_used
schema_name table_name     in_mb      in_mb     in_mb     in_mb
production  search_index   476.66     476.66    0.00      100.00
production  search_dataset 104.57     104.57    0.00      100.00
production  biblio         96.37      93.53     2.84      97.05
production  node           41.40      41.40     0.00      100.00
staging     node_revisions 17.53      16.76     0.77      95.61
staging     search_total   17.13      17.13     0.00      100.00
staging     biblio_keyword 11.92      11.92     0.00      100.00
staging     xmlsitemap     9.65       9.65      0.00      100.00

 

Get the tables with free space within them

Use this script to find tables with more than 100KB of free space.  These tables are candidates for an OPTIMIZE TABLE operation.

SELECT s.schema_name, t.table_name,
   ROUND(SUM(data_free)/1024,2) DataFreeInKB
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
   ON s.schema_name = t.table_schema
GROUP BY s.schema_name, t.table_name
HAVING DataFreeInKB > 100
ORDER BY schema_name, DataFreeInKB DESC;

 
Example output:

schema_name table_name  data_free_in_kb
production  cache_block 12285.47
production  cache_page  11714.50
production  cache_form  4349.23
production  biblio      2911.40
production  sessions    2560.51
staging     biblio      11367.68
staging     watchdog    4858.80
staging     cache_form  3522.44
staging     sessions    1732.30
staging     node        1706.16
staging     cache_block 904.29

Advertisements

One Response to Determine the Space Used By MySQL 5 Schemas and Tables

  1. Maarten says:

    many thanks for the compact query;

    Maarten

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: