#1041 - Out of memory issue in mysql

I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I am using InnoDB format.

When I create a table or alter a table I get this error:


#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

How do I resolve this?

PROBLEM #1

Your major problem is having at least 10000 tables

Here is the issue I see
RDS Instance was All InnoDB

How many file handles are open if you have 10000 InnoDB tables ?

• One file handle for the .frm file

• One file handle for the .ibd file

This would be a maximum of 20000 open file handles
RDS Instance was All MyISAM

How many file handles are open if you have 10000 MyISAM tables ?

• One file handle for the .frm file

• One file handle for the .MYD file

• One file handle for the .MYI file

This would be a maximum of 30000 open file handles
PROBLEM #2

You are using m1.medium. That only has 3.75 GB RAM.
PROBLEM #3

Having 10000 tables can be very memory intensive because of the amount of metadata to maintain

See my old posts about INFORMATION_SCHEMA's memory consumption

• Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?

• Apr 21, 2014 : Adding new tables -- memory usage increases

PROBLEM #4

Your Production Server probably has too many open DB Connections that are dormant but consuming memory (See my post How costly is opening and closing of a DB connection?)
ANALYSIS

Your problem could simply be having too many open tables at the moment you are trying to create a table (which needs 2-3 file handles) or an alter table (which needs 2-3 file handles). I have seen this happen to me trying to create a partitioned table and running out of file handles. I wrote about this in my old post What are the possible risks involved in partitioning large database tables? where I raised the ULIMIT on the OS (bare metal server) to address it. I know changing ULIMIT does not help you because you are in RDS.
SUGGESTIONS

Simply run

mysql> FLUSH TABLES;

and it will close all tables that have open files handles.

If you prefer to close specific open tables, you can list the open tables with

mysql> SHOW OPEN TABLES FROM ;

Then, you must close the tables explicitly like this

mysql> FLUSH TABLES db1.tb1,db2,tb2,db3.tb3;

After running FLUSH TABLES;, then you can run CREATE TABLE or ALTER TABLE.

You may also need upgrade to another server model with much more RAM.

You could reduce the number of tables but archiving the data and removing inactive databases. This will reduce RAM consumed for the INFORMATION_SCHEMA.

You should also close all your DB Connections that are not being pooled.
GIVE IT A TRY !!!

StackExchange Database Administrators Q#120999, answer score: 7