Mysql – AWS RDS Really Odd Error… #1041 OUT OF MEMORY, Buffers OK and Memory OK

amazon-rdsawsinnodbMySQLmysql-5.6

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…

Best Answer

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.

Related Solutions

MySQL RDS Instance eating up memory and swapping

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game.

I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring):

Total memory allocated 26217103360; in additional pool allocated 0
Dictionary memory allocated 1212141
Buffer pool size   1563519

Total memory: 24.41 GiB (InnoDB needs about 5-10% more than your buffer pool for internal structures) Buffer Pool memory: ~23.85 GiB

I'm going to have to guess that the memory is consumed at the MySQL layer (not InnoDB), and two common causes are intrinsic temporary tables, and very large sort/join buffers.

Postgresql – How to seamlessly upgrade the major version of an AWS RDS postgres database

This is a good question,
working in cloud environment is tricky sometimes.

You can use pg_dumpall -f dump.sql command, that will dump your entire database to a SQL file format, In a way that you can reconstruct it from scratch pointing to other endpoint. Using psql -h endpoint-host.com.br -f dump.sql for short.

But to do that, you will need some EC2 instance with some reasonable space in disk (to fit your database dump). Also, you will need to install yum install postgresql94.x86_64 to be able to run dump and restore commands.

See examples at PG Dumpall DOC.

Remember that to keep integrity of your data, it is recommended (some cases it will be mandatory) that you shutdown the systems that connect to the database during this maintenance window.

Also, if you need speed up things, consider using pg_dump instead pg_dumpall, by taking advantage of parallelism (-j njobs) parameter, when you determine the number of CPUs involved in the process, for example -j 8 will use until 8 CPUs. By default the behavior of pg_dumpall or pg_dump is use only 1. The only advantage by using pg_dump instead pg_dumpall is that you will need to run the command for each database that you have, and also dump the ROLES (groups and users) separated.

See examples at PG Dump DOC and PG Restore DOC.

Best Answer

Related Solutions

MySQL RDS Instance eating up memory and swapping

Postgresql – How to seamlessly upgrade the major version of an AWS RDS postgres database

Related Question