Mysql – #1041 – Out of memory issue in thesql

amazon-rdsmemoryMySQLmysql-5.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?

Best Answer

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

Related Solutions

Mysql – Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Since you are running MySQL 5.5, have you considering tuning InnoDB for better performance?

There are several variables that have been added to the MySQL 5.1 InnoDB Plugin that are now native to MySQL 5.5. They usually help increase hyperthreading and take advantage of more IOPS if the environment can handled it. In your case, you should be able to.

I have spun RDS models before (See my post : Local database vs Amazon RDS) and can tell you that RDS is not very helpful in tuning InnoDB. You will have to take the bull by the horns on this one.

When you spun up the RDS instance, you probably used the default DB Parameter Group

You should be able to create a new DB Parameter Group for yourself. When you get to this, set the following:

innodb_read_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.
innodb_write_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.
innodb_thread_concurrency : Default is already 0. Never set this value to any other number.

I suggest these because they are default in RDS for innodb_read_io_threads and innodb_write_io_threads are just too low. In addition, your SHOW ENGINE INNODB STATUS\G

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

only shows the default of 4 for each class of io threads.

Here is the Bad News: To implement config changes, once you create your own DB Parameter Group, you must export the data from the old RDS instance, create a new instance using your new DB Parameter Group, and reload. In more detail...

Create a Custom DB Parameter Group (call it MySettings)
Download RDS CLI and setup a config file with your AWS Credentials
Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=innodb_read_io_threads,value=16,method=immediate"
Modify using DB Parameter Option List MySettings
Restart the MySQL RDS Instance

I hope this helps !!!

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

The first thing that comes to mind is the Server Model : db.m1.large

What limiting factors are placed on a MySQL RDS?

If you spin up an Amazon RDS instance of MySQL, you would subject yourself to whatever constraints are given. All models of MySQL Amazon RDS have the same major options but differ in only two aspects

max_connections
innodb_buffer_pool_size

Here is a Chart I posted Last Month

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I posted this in my other posts in the DBA StackExchange

You are using m1.large. Since the InnoDB Buffer Pool is 3/4 of the Instance. That's means you have 7.2G but only 1.8 is usable. That model is capable of having up to 623 connections. Each connection can consume memory because of

join buffers
sort buffers
read buffers
thread info

Amazon RDS is simply micromanaging resources. Since DB Connections can consume RAM, connections are probably being disallowed due to the lack of RAM needed.

SUGGESTIONS

Try reducing InnoDB log IO innodb_flush_log_at_trx_commit=0 during the mass creation
Make sure you are not doing large transactions during any automatic backups or snapshots
Try a bigger server model m1.xlarge or m2.xlarge

Best Answer

PROBLEM #1

RDS Instance was All InnoDB

RDS Instance was All MyISAM

PROBLEM #2

PROBLEM #3

PROBLEM #4

ANALYSIS

SUGGESTIONS

GIVE IT A TRY !!!

Related Solutions

Mysql – Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

SUGGESTIONS

Related Question