Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
Your major problem is having at least 10000 tables
Here is the issue I see
How many file handles are open if you have 10000 InnoDB tables ?
.frm file.ibd fileThis would be a maximum of 20000 open file handles
How many file handles are open if you have 10000 MyISAM tables ?
.frm file.MYD file.MYI fileThis would be a maximum of 30000 open file handles
You are using m1.medium. That only has 3.75 GB RAM.
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 increasesYour 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?)
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.
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.
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.
