5

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?

0

2 Answers 2

7

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

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 <database>;

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 !!!

0
-1

Posting my experience here since it is same message, and top pages in search results when searching for that message

Was seeing [ERROR] Failed to write to mysql.slow_log: 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 on Windows server With plenty of RAM available. MySQL 5.7.20-log x64 community

Running mysql_upgrade.exe --upgrade-system-tables -h 127.0.0.1 -u root -p gave mysql_upgrade: [ERROR] 13: Can't get stat of '.\mysql\slow_log.CSV' (Errcode: 2 - No such file or directory)

Since my slow_query_log_file is elsewhere this was confusing. After creating empty file at .\mysql\slow_log.CSV so far everything looks good.

I have not looked into sources if there is any chance of this message being shown for missing files, I will for now say that there is a probability of a bug that shows the wrong message in this case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.