0

I have one master RDS instance of 50 GB storage and created a read replica of the master DB with the same configuration.

I use this read replica only for SELECT operations nothing else. But suddenly I got the storage full problem with the read replica DB. Master Db is working properly, how is it possible if the size is same but replica DB's size is full?

Getting below error when executing the complex query(inner, join, group). ERROR 3 (HY000): Error writing file '/rdsdbdata/tmp/MY7U2XRf' (Errcode: 28 - No space left on device)

In AWS Console, The slave DB status is "Storage-full" and event log message is : The free storage capacity for DB Instance: example-slave is low at 1% of the provisioned storage [Provisioned Storage: 49.07 GB, Free Storage: 527.80 MB]. You may want to increase the provisioned storage to address this issue.

But, I checked the free size with below command of both Master and replica Db instances, it's almost the same.

SELECT table_schema, 
       ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" 
FROM information_schema.tables 
GROUP BY 1 
ORDER BY 2 DESC;

Master DB's space is used 23.86 GB out of 50 GB and Slave DB's space is used 24 GB out of 50 GB.

Please help me.

3
  • Please edit the question and add the exact error message you received. Do you have a console view of the RDS instance? What does the console list as issues? Add as much details as possible. Thanks and welcome to DBA.SE.
    – John K. N.
    May 15, 2020 at 8:52
  • @Johnakahot2use - Updated the issue.
    – Rajeev
    May 15, 2020 at 10:21
  • When the system tells you it is full, it is full, besides your query is missing something see dba.stackexchange.com/questions/265744/…
    – nbk
    May 15, 2020 at 11:10

1 Answer 1

1

Possibly what happened was that a complex SELECT was run. It was so complex that it built one or more giant temporary tables that overran the disk tmpdir filesystem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.