Getting MySQL DB size

Many a times you want to find the size of database (schema) for performance monitoring or to allocate more disk space or to take some proactive action on db size.  You can pull relevant information from MySQL INFORMATION_SCHEMA with the following query.  It sums up each table data and its index size for all tables in a single schema. 

SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length) +
SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND((SUM(t.data_length))/1024/1024,2),0.00),"Mb") data_size,
CONCAT(IFNULL(ROUND((SUM(t.index_length))/1024/1024,2),0.00),"Mb") index_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length)) -
SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length)) -
SUM(t.data_free))/((SUM(t.data_length) +
SUM(t.index_length)))*100),2),0) pct_used,
SUM(t.table_rows) total_rows,
COUNT(t.table_name) total_tables,
CONCAT(ROUND(MAX(t.data_length+t.index_length)/1024/1024, 0.00),
"Mb") biggest_table_size,
GROUP_CONCAT(distinct t.engine) engines_used,
GROUP_CONCAT(distinct t.table_collation) collations_used
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t
ON s.schema_name = t.table_schema
WHERE s.schema_name = "test_dw_advertising"
GROUP BY s.schema_name\G


*************************** 1. row ***************************
schema_name: test_dw_advertising
total_size: 212151.14Mb
data_size: 154660.83Mb
index_size: 57490.31Mb
data_used: 210039.45Mb
data_free: 2111.69Mb
pct_used: 99.00
total_rows: 309940227
total_tables: 118
biggest_table_size: 74360Mb
engines_used: InnoDB,MyISAM
collations_used: latin1_swedish_ci
1 row in set (1 min 38.26 sec)

The total size of allocated (including free data size) is nearly 212G, out of which data used ~155G and indexes used ~57G for 118 tables leaving 2G free (99% of disk allocated is used). Biggest table used 74.3G disk space and this schema has both InnoDB and MyISAM tables using single collation latin1_swedish_ci.

HTH,
Shiva

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 )

Google photo

You are commenting using your Google 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