Assessing Global Database Storage

To analyze the disk footprint across all databases, querying the information_schema tables is necessary. The following script aggregates the total size, distinguishing between data and index usage, and sorts the results by the heaviest consumers first.

SELECT 
    table_schema AS 'Database Name',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Total Size (MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS 'Index Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY (data_length + index_length) DESC;

Analyzing Specific Schema Utilization

When focusing on a single database, it is useful to view the aggregate storage metrics, including allocated free space. Replace 'target_db' with the actual name of your database.

SELECT 
    CONCAT(ROUND(SUM(data_length + index_length) / 1024 / 1024, 2), ' MB') AS 'Total Consumption',
    CONCAT(ROUND(SUM(data_free) / 1024 / 1024, 2), ' MB') AS 'Allocated Free Space',
    CONCAT(ROUND(SUM(index_length) / 1024 / 1024, 2), ' MB') AS 'Index Consumption'
FROM information_schema.tables 
WHERE table_schema = 'target_db';

Granular Table Inspection

To identify which specific tables within a database are consuming the most resources, use the following query. This calculates the total volume in gigabytes for easier reading on large systems.

SELECT 
    table_name AS 'Table Identifier',
    table_rows AS 'Row Count',
    CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 4), ' GB') AS 'Data Volume',
    CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 4), ' GB') AS 'Index Volume',
    CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 4), ' GB') AS 'Total Volume'
FROM information_schema.tables 
WHERE table_schema = 'target_db'
ORDER BY (data_length + index_length) DESC;

Data and Index Segregation

Sometimes it is beneficial to view the raw data and index sizes separately for the entire database.

Total Data Size:

SELECT 
    CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 4), ' GB') AS 'Data Storage'
FROM information_schema.tables 
WHERE table_schema = 'target_db';

Total Index Size:

SELECT 
    CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 4), ' GB') AS 'Index Storage'
FROM information_schema.tables 
WHERE table_schema = 'target_db';

Table Maintenance and Optimization

Over time, tables can become fragmented due to updates and deletions. The OPTIMIZE TABLE command reorganizes the physical storage of table data and associated indexes to reduce storage footprint and improve I/O efficiency.

OPTIMIZE TABLE target_db.large_table_name;

Troubleshooting SQL Mode Errors

If you encounter aggregation errors such as Expression #2 of SELECT list is not in GROUP BY clause, it is likely due to the ONLY_FULL_GROUP_BY setting in sql_mode. This setting enforces strict SQL standards regarding aggregate queries.

Temporary Configuration

To resolve this temporarily for the current session (changes revert on restart), execute the following commands:

-- Check current mode
SELECT @@GLOBAL.sql_mode;

-- Remove ONLY_FULL_GROUP_BY and reset
SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Permanent Configuration

To apply this fix permanently, modify the MySQL configuration file.

1. Open the configuration file (e.g., /etc/mysql/conf.d/mysql.cnf or my.cnf).
2. Append the following configuration under the [mysqld] section:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

3. Save the file and restart the MySQL service to apply changes.