Mysql – What can cause a rapid drop in RDS MySQL Database free storage space

amazon-rdsinnodbMySQLmysql-5.6

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.

Best Answer

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.

InnoDB Architecture

enter image description here

CONJECTURE #1

Since InnoDB allocates its buffer pool contiguously, one could only imagine if there is any form of fragmentation of data and index pages among other things in the Buffer Pool. What other things? There are also updates for secondary non-unique indexes posted first inside the Buffer Pool before they are merged into the System Tablespace file (better known as ibdata1). Please notice the Insert Buffer within the InnoDB Architecture.

CONJECTURE #2

While a larger buffer pool can reduce disk I/O, a buffer pool that's too large may have issues, such as the fragmentation issue I just mentioned.

Perhaps sparse memory, though contiguous, is not a good thing either. I discussed something of that nature back on Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

I noticed the miscellaneous pages (Innodb_buffer_pool_pages_misc) within the buffer pool. That takes up 235 MB (16384 * 15022). Just some overhead for indexing and row locks. Notwithstanding, this can be victimized by a large buffer pool with lots of sizable empty gaps.

CONJECTURE #3

Data and index pages are not the only things that occupy the Buffer Pool. An annoying neighbor they must share space with is the Insert Buffer. That section of the Buffer Pool contains the changes for all non-unique indexes. Depending on the number of indexes each InnoDB table has, up to half of the Buffer Pool could be used. This should be a good reminder to look over all InnoDB table and remove redundant indexes

Here are other posts that describes how to hunt down redundant indexes and why to get rid of them:

Jan 26, 2012 : MySql - find redundant indexes
Dec 10, 2011 : Do I have duplicate key indexes?
Sep 07, 2011 : MySQL CPU Usage
May 17, 2011 : Do I need to add a new single column index to a table if a multi-column index on that field already exists?

Think about it: If a table has redundant indexes, INSERTs and UPDATEs to a table will results in rebalancing tree pages 45% of of the time (in the worst case scenario). Such tree rebalancing with happen multiple times for a table with a lot of indexes. Removing redundant indexes cuts down the time spent writing those changes to the Insert Buffer and in turn, to the Index Pages of each InnoDB table.

Increasing the Buffer Pool Size leaves room for the Insert Buffer to run amok if you have InnoDB tables with too many indexes, especially redundant indexes. Another telltale sign that you need to look for such tables is to run this query:

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables WHERE engine='InnoDB'
AND data_length/(data_length+index_length) < 0.8;

This will give you a general idea which tables need to have the indexes looked over for redundancy of leading columns.

EPILOGUE

Keep in mind that the Buffer Pool holds

Data Pages
Index Pages
Insert Buffer for Index Changes
Adaptive Hash Index
Opened tables info
Miscellaneous Overhead
Refer to the InnoDB Architecture

Wasted space in a Buffer Pool could take more time to sift through, especially with a single core that is solely responsible for all of RAM.

My advice would be to keep the innodb_buffer_pool_size at 10G.