ANS-0557 · OTHER
How to Determine the Size of All MySQL Databases on a Server?
Database administrators can efficiently monitor storage usage by executing specific SQL queries to retrieve the total size and free space for all MySQL databases.
Short answer
To find the size of all MySQL databases on a server, execute a SQL query against the information_schema.TABLES table. This allows for calculating the total data and index length, as well as free space, for each database. Results can be displayed in megabytes or bytes, providing a clear overview of storage consumption.
Scenario
Database administrators often need to monitor the storage consumption of their MySQL servers. Understanding the size of individual databases and the total free space available is crucial for capacity planning, performance optimization, and ensuring efficient resource allocation. This information helps in identifying large databases that may require optimization or additional storage.
Solution
To determine the size of all MySQL databases on a server, use one of the following SQL commands. These commands query the information_schema.TABLES to aggregate data and index lengths, as well as free space, for each schema.To see the size in Megabytes (MB):
SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024"Free Space in MB" FROM information_schema.TABLES GROUP BYtable_schema;To see the size in Bytes (B):
SELECT table_schema "Data Base Name", sum( data_length + index_length) "Data Base Size in Bytes", sum( data_free ) "Free Space in Bytes"FROM information_schema.TABLES GROUP BY table_schema;Expert NetSuite Support
Need help with this NetSuite issue?
Other consulting and configuration support