4

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?

closed as unclear what you're asking by Max Vernon, Mark Sinkinson, Tom V, mustaccio, RLF Nov 28 '15 at 20:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why downvote...tell me answer if u know.. – Bhupendra Pandey Nov 13 '15 at 15:24
  • I assume the downvote is because the error message tells you how to solve the problem. Have you tried what it suggests? – Mark Sinkinson Nov 13 '15 at 15:26
  • 2
    What is 'everything'? Show us the steps you've tried, otherwise people will end up suggesting things you've already done, wasting your time and ours – Mark Sinkinson Nov 13 '15 at 15:30
  • 1
    You've likely received a downvote because your question is very broad, and lacking details. You need to describe in much greater detail everything you are doing. For instance, what is the table design you are trying to create/alter? What size is your instance? etc. By the way, it wasn't me that downvoted your question, even though I have voted to close it as "too broad". – Max Vernon Nov 13 '15 at 15:31
  • 1
    Get more RAM and/or reduce the 10K tables. Then start a new thread if you still have more problems. And include SHOW VARIABLES; and RAM size. – Rick James Dec 3 '15 at 5:13
5

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

  • i did- FLUSH TABLES; it took 2 min..after that i run alter table, same error is coming. Freeable memory increses from 200 mb to 600 mb. – Bhupendra Pandey Nov 13 '15 at 18:23
  • How can we clear all memory .is there any way?? – Bhupendra Pandey Nov 13 '15 at 18:40
  • I have staging and live server..in live same instance type but more table present..available memory showing 250 MB..In staging available memory is 490 MB..alter is working in live but not in staging..I think memory is not the issue... – Bhupendra Pandey Nov 13 '15 at 19:09
  • Updated my answer ... – RolandoMySQLDBA Nov 13 '15 at 19:13
  • FLUSH TABLES- tried ..does not work Production server working fine on same cofuguration but development/testing server not working(having less table than production).- i dont think this is the issue(may be) You could reduce the number of tables but archiving the data and removing inactive databases- i removed atleast 4-5 database but nothing happend You should also close all your DB Connections that are not being pooled.- all connection are closed..restarted the server so many time. – Bhupendra Pandey Nov 13 '15 at 19:18

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