How could my MySQL database on Amazon RDS have recently gone from 10.5 GB free to "storage-full" status within about 1.5 hours?

It's a 15GB MySQL 5.6.27 database running on a db.t2.micro instance. Normally only a few hundred KBytes gets added to it per day.

About a day ago the free storage space went from 10.5 GB to basically 0 GB in about 1.5 hours. The Write IOPS chart shows only my regular low-volume traffic during that time span, so apparently the data must have been generated server-side.

One potentially relevant note is that my database has about 7,000 tables and has the innodb_file_per_table set to 1.

A similar incident apparently happened 8 days ago, but was not as severe and I didn't even notice it because it didn't fill up the storage space.

Screenshot showing the incident 8 days ago plus the storage-filling incident a day ago

Screenshot showing a detailed view of the storage-filling incident

I'm not a database expert and this is for a hobby project of mine, so I'm struggling a bit to figure out how even to start troubleshooting this!

EDIT 1

I'm starting to look at at the answer provided by @RolandoMySQLDBA and I realize I left out some very useful details.

The only systems that write to the database are two EC2 instances which write to it every 30 minutes, which corresponds to the storage reduction seen in the graph.

Both of these systems are collecting the same data from the web, and then they both attempt to write that collected data at the same time to my database on the half-hour. I use two data collection systems simply for redundancy, and I have my write routines coded so that each system will try to write all of its data using INSERT IGNORE INTO so whichever system writes that particular data first wins, and the second system's insert attempt is simply ignored.

During the write that occurs every 30 minutes, one row is inserted into each of the thousands of tables in the database, except one table. Nothing is inserted into that table, but each of its (roughly) 2000 rows gets updated, one at a time.

EDIT 2

I restored an instance of the database from a point after it had about 2.5GB of data added (the event on 8/16 as seen in the first screenshot), so that I could run commands without hitting "storage full" errors.

With help from @RolandoMySQLDBA I was able to see how much InnoDB and MyISAM data was in use (How to monitor MySQL space?). Here's the output:

rudy InnoDB 761.72 MB   0.00 B  761.72 MB 

rudy Total  761.72 MB   0.00 B  761.72 MB 

sys InnoDB  16.00 KB    0.00 B  16.00 KB 

sys Total   16.00 KB    0.00 B  16.00 KB 

Database Total  761.73 MB   0.00 B  761.73 MB

I also ran the command following command to check the 'Data_Length's of all the tables in my database:

show table status from rudy;

I exported the output of that command to a CSV file, imported it as a spreadsheet, then summed all the data lengths and the total was 798,720,00.

So at this point I'm confused. If there's about 798MB in the tables, and about 761MB in the whole database according to your command's output, what else is there that could be taking up about 4.5GB (15GB instance, ~10.5GB storage free)?

Is there some other way I can see what else could be taking up space on my RDS instance?

EDIT 3

I simplified my test scenario by using only one system to write to the database, and by removing all update statements, so now all my code is doing on the database is essentially this (using python 3 with pymysql):

query = "INSERT IGNORE INTO {tn} (Timestamp, Price, Flags, Sales, Total) VALUES(%s,%s,%s,%s,%s)".format(tn=table_name)

self.cursor.execute(query, (timestamp, price, flags, sales, total))

And here is the DDL for the tables I'm inserting into:

query = "CREATE TABLE IF NOT EXISTS {tn} (Timestamp INT PRIMARY KEY, Price BIGINT, Flags INT, Sales INT, Total INT)".format(tn=table_name)

self.cursor.execute(query)

In my simplified code I'm only inserting into around 2000 tables of this type, and each table has between 1,000 and 11,000 rows.

I can reproduce the issue consistently with the above test setup.

Aurora Works Fine!

I also tried migrating a snapshot to Aurora and running the test scenario, and the issue does not occur! I'd like to stick with MySQL server since it's less expensive, but if nobody can help me solve this then I may just move on to Aurora permanently.

Here are the folders you are writing to in a MySQL RDS Server

mysql> select * from information_schema.global_variables where variable_name in

    -> ('innodb_log_group_home_dir','innodb_data_home_dir','innodb_data_file_path');

+---------------------------+------------------------+

| VARIABLE_NAME             | VARIABLE_VALUE         |

+---------------------------+------------------------+

| INNODB_LOG_GROUP_HOME_DIR | /rdsdbdata/log/innodb  |

| INNODB_DATA_FILE_PATH     | ibdata1:12M:autoextend |

| INNODB_DATA_HOME_DIR      | /rdsdbdata/db/innodb   |

+---------------------------+------------------------+

3 rows in set (0.00 sec)

Your ibdata1 file lives in /rdsdbdata/db/innodb and your redo logs live in /rdsdbdata/log/innodb.

What worries me is your ibdata1 file. Since innodb_file_per_table is enabled amd assuming you have no MyISAM tables, the only thing that could cause growth is MVCC. Lots of selects and writes can cause InnoDB to create lots of rollback info. That info can stretch the ibdata1 file. I have discussed this over the years:

• 
  Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
  


• 
  Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
  


• 
  Jun 04, 2014 : Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?
  


• 
  Jun 16, 2014 : MySQL Index creation failing on table is full
  


• 
  Aug 21, 2015 : Transactional DDL workflow for MySQL
  

You could run OPTIMIZE TABLE against all your InnoDB table to provide some shrinkage. See my 5 year old post Why does InnoDB store all databases in one file? for ideas on how to shrink your tables.

Unfortunately, you cannot do that in you present state. See this YouTube Video. As for your not being able to list your databases, please note this:

mysql>  show global variables like 'tmpdir';

+---------------+----------------+

| Variable_name | Value          |

+---------------+----------------+

| tmpdir        | /rdsdbdata/tmp |

+---------------+----------------+

1 row in set (0.00 sec)

Metacommands like SHOW create temp tables. The whole disk is just full.

BAD NEWS

Creating a read replica will not shrink anything. RDS will just take a snapshot and setup replication.

Doing the ALTER TABLE trick will shrink tables, not ibdata1.

Spinning up a new RDS instance and loading from scratch will start with a fresh ibdata1.

UPDATE 2017-08-25 12:21 EDT

Looking back on your graphs, I can see that you are sending in too much data every 30 minutes. Try updating 500 rows at a time instead of 2000. Please keep in mind that heavy updates is just as bad as heavy inserts in terms of ibdata1 growth.

Study your queries. Perhaps you have a "cross join" (JOIN without saying how the tables are related) and this generated a huge intermediate table. Can you discover the size of ibdata1? Did it become huge? If not, what other file?

Similar thing happened to us, turned out to be a funky execution plan causing a SELECT query to generate a massive intermediate table for mergesort, which got written to disk... and doesn't get cleaned up until the server restarts.

If that's what happened to you, that's probably why your Aurora replica was fine - any replica would've been fine as it would've deleted the temporary table on starting :)

If you don't mind a minute's down time, I recommend turning it off and on again.