1

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. Sudden free storage drop

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
  • So, investigate individual table. Look up which one got an out burst. Check your network activity. Enable logs and look up which particular web service is requesting heavy writing. If there is even web server. look up your logging log for mysql percona.com/blog/2012/12/28/auditing-login-attempts-in-mysql Asking investigation point on forum seems pointless though. It is easier to help if you are more specific – Popeye Jan 7 at 1:51
  • A possibility: A very bad query was run. And it created a huge table. Even it if were later dropped, it might have permanently increased ibdata1. Can you get SHOW TABLE STATUS for your database? It might give a clue. – Rick James Jan 7 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.