AWS RDS Really Odd Error... #1041 OUT OF MEMORY, Buffers OK and Memory OK

Question

I have an AWS RDS (MySQL 5.6.35) db.m3.medium that has given me a random error when attempting to modify a table's structure in the past two weeks:

#1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

I've never encountered this error on even a much smaller instance with much less memory. It should be noted that encryption is enabled on this instance, so that might be adding a little bit of load.

When I check the memory statistics in the console, it doesn't appear to be even using anywhere near 100% usage. It doesn't appear that the instance is swapping at all either. After a reboot, I can modify tables fine. This table is very small... < 10 columns, little overhead, and not many rows (<100). Oddly enough, we have not modified the default buffer sizes in RDS (3/4 of instance memory allocation). Not only this, after checking the buffer tables, everything seemed OK as well.

Am I missing something here?

UPDATE: It happened again. Rebooting the RDS server appears to mitigate whatever problem is occurring. Here's a screenshot of buffer statistics at the time of the incident (this is BEFORE the reboot that fixes the problem)...

UPDATE 9/1/2017: I thought that we mitigated the issue for a while after we upgraded our RDS instance to m4.large... and for a while, it did. However, while performing some migrations earlier this evening... sure enough, error #1041 - OUT OF MEMORY. I immediately checked our CloudWatch reports for anything out of the ordinary, and our freeable memory is really high, not even concerningly fluctuating. Additionally, a reboot fixed the issue. I have yet to see it happen again, but I'm sure in a few weeks after some transactions it will happen again. Is this possibly a sign of a corrupted database or something? We have NEVER had any issues reading or writing to the database, and all applications are using their databases on this server just fine. We just can't initiate ALTER TABLE changes from inside phpMyAdmin...

Answer 1

I do not believe you are alone here. AWS has push me from 5.7.11 to 5.7.17 and now I am unable to perform Alter table commands when there is any sort of memory pressure on the Database.

Have a look at https://forums.aws.amazon.com/thread.jspa?threadID=251866

At this point it looks like there may be an issue present in MySQL 5.6 < 5.6.37 and 5.7 < 5.7.19.

Unfortunately AWS RDS does not have any 5.6 images > 5.6.35 or 5.7 > 5.7.17

Before using 5.7.17 I had used 5.7.11 for some time without issue. If you are able to dump/restore your databases that might be the best option for you, otherwise you can give larger instances a shot.

Answer 2

Since this is db.m3.medium, I see two problems, maybe three

PROBLEM #1 : Available RAM

Since RDS is using 75% of the RAM for innodb_buffer_pool_size, this means you only have 960M for the OS. Not a lot of headroom.

PROBLEM #2 : DB Connections

Multiple DB Connections opening and closing can cause the OS to compete with the DB Connections for available RAM (See my old post : How costly is opening and closing of a DB connection?)

PROBLEM #3 : Too many tables in the database

Did you know memory is consumed when you have many tables and columns ?

• Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
• Apr 21, 2014 : Adding new tables -- memory usage increases

Questions to ask yourself

• Have you added tables recently ?
• Have increased the number of columns in one or more tables ?
• Are there a lot of open file tables to tables after many queries (run SHOW OPEN TABLES;) ?

SUGGESTION

Perhaps just running FLUSH TABLES; will close all open files handles to previously accessed tables just prior to doing any ALTER TABLE commands.

UPDATE 2017-08-15 08:21 EDT

I looked at the buffer statistics at the time of the incident you posted.

• Your InnoDB Buffer Pool is 98.8023% empty (168454 / 170496)
• You only have 31.59375 MB (2022 * 16384 / 1048576) of data
• Here is the part I don't understand
  • You have 23824 pages to be flushed
  • that means you have 372.25 MB (23824 * 16384 / 1048576)
  • That's like 11.78 times more the the actual data

How in the world could 372 MB need to be flushed when there is only 32 MB of data ?

PROBLEM #4 : Compression (Maybe)

I hope you are not using innodb_file_format Barracuda. This requires uncompressing pages in the InnoDB Buffer Pool. Larger Buffer Pool would be needed. I wrote about this before:

• Mar 02, 2012 : innodb_file_format Barracuda
• Jul 22, 2013 : MySQL 5.5 High CPU Usage

PROBLEM #5: CPU

db.m3.medium only 1 vCPU. That means InnoDB, Encryption, and the OS are all running on 1 vCPU. OUCH !!!

PROBLEM 1 REVISITED

According to your buffer statistics, you have 2664 MB for the InnoDB Buffer Pool

• The Buffer Pool is supposed to be 75% of the installed RAM
• db.m3.medium only has 3840 MB (3.75 GB). Remaining RAM is 960MB
• Your stats says 75% of RAM is 2664 MB. That means 100% of RAM is 3552 MB.
• Therefore, leftover RAM for the OS is not 960 MB, but 888 MB

RECOMMENDATION

Please do one or both of the following:

• Stop using Encryption
• Get more RAM (Upgrade Server Class)