We've got a MySQL RDS instance running on AWS, with a replica. From Cloudwatch I can see that there was a sudden drop in free storage space, of the total allocated storage space of 100GB. How can I investigate what is using the storage? And remedies to attempt to free up the space?
Free Storage Space dropped suddenly from 63GB to 4GB.
I've been querying the replica and suspect running an accidental cross join, which was later cancelled, caused the issue, as the cross join query was ran around the time of the drop. Light queries still run, larger queries no longer run.
SQL client error for heavier queries now displays:
SQL Error [3] [HY000]: Error writing file '/rdsdbdata/tmp/MYSKecWe' (Errcode: 28 - No space left on device)
There is also a warning in AWS console.
The free storage capacity for DB Instance: smmainro is low at 3% of the provisioned storage [Provisioned Storage: 99.26 GB, Free Storage: 3.35 GB]
I ran below query to see how much space was in use by the database. Results show data_length as 20,612MB, index_length as 11,954MB and data_free as 614,996MB. So it look slike the total database is about 32.5GB in size, which corresponds to how much data is occupied by the master db. Does data_free represent data which could be available but is currently not available?
SELECT
sum((data_length)/1024/1024) AS data_mb,
sum((index_length)/1024/1024) AS index_mb,
sum((data_free)/1024/1024) AS free_mb
FROM
information_schema.tables
Would rebooting the master database free up the space occupied by temporary data? From some reading it doesn't seem possible to just reboot just the replica database, is that correct?
Fyi running version 5.7.19-log of MySQL. Here are some values from running SHOW GLOBAL VARIABLES:
innodb_buffer_pool_size 24,696,061,952 (24GB)
innodb_ft_cache_size 8,000,000 (8MB)
innodb_ft_result_cache_limit 2,000,000,000 (2GB)
innodb_ft_total_cache_size 640,000,000 (640MB)
innodb_log_file_size 134,217,728 (134MB)
innodb_max_undo_log_size 1,073,741,824 (1GB)
innodb_online_alter_log_max_size 134,217,728 (134MB)
max_binlog_cache_size 18,446,744,073,709,547,520 (18.5 Billion GB)
query_cache_limit 1048576
query_cache_size 1048576
