You can determine the current size of your database schemas in MySQL using the INFORMATION_SCHEMA. The following SQL query enables you to see the size of all schemas in your selected MySQL database instance.
Code Example
## Determine MySQL Database Schema Size
## http://effectiveMySQL.com/sql/#determining-schema-size
## Last Modified: 2010-12-24
SELECT table_schema,
SUM(data_length+index_length)/1024/1024 AS total_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY 2 DESC;
+--------------------------+---------------+ | table_schema | total_mb | +--------------------------+---------------+ | example_data | 2346.42683983 | | example_xcart | 729.59970284 | | example_dev | 405.09923077 | | mysql | 0.64243031 | | information_schema | 0.00390625 | +--------------------------+---------------+
It is a recommended Best Practice that you record the size of all database schemas on a regular basis for determining capacity planning and database growth. The following query provides the necessary output you would consider for recording sizing information. This data can be managed in the Effective MySQL Meta Schema.
Code Example
## Recording MySQL Database Schema Size
## http://effectiveMySQL.com/sql/#record-schema-size
## Last Modified: 2010-12-24
--SET @instance='example';
--INSERT INTO em_meta.schema_size(instance, table_schema, total_mb, date_mb,
-- index_mb, table_count, log_date)
SELECT @instance, table_schema,
SUM(data_length+index_length)/1024/1024 AS total_mb,
SUM(data_length)/1024/1024 AS data_mb,
SUM(index_length)/1024/1024 AS index_mb,
COUNT(*) AS tables,
CURDATE() AS today
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema')
GROUP BY table_schema
ORDER BY 2 DESC;
+--------------------------+---------------+---------------+--------------+--------+------------+ | table_schema | total_mb | data_mb | index_mb | tables | today | +--------------------------+---------------+---------------+--------------+--------+------------+ | example_data | 2346.42683983 | 1423.32820702 | 923.09863281 | 62 | 2010-05-08 | | example_xcart | 729.59970284 | 526.09970284 | 203.50000000 | 256 | 2010-05-08 | | example_dev | 405.09923077 | 233.75548077 | 171.34375000 | 238 | 2010-05-08 | +--------------------------+---------------+---------------+--------------+--------+------------+
A current performance implication is the quering of information for an InnoDB table as this results in random index of pages.
