I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all connections and refuses any new ones for a period of about 10-20 minutes.
The database engine is InnoDB. Each database is exactly the same and is created from a MySQL Dump file. There are 65 tables, each is empty aside from one or two tables that have a row or two, so the data size is very small. Foreign key constraints are turned off before the tables are created and turned back on afterwards for each database created.
The following pseudo code essentially describes what I am doing:
For # of databases: // around 1000-5000 usually results in a crash
CREATE DATABASE foo_num CHARACTER SET utf8 COLLATE utf8_bin; // done via Python Django connection
mysql -u USER -h HOST --password=PASSWORD DBNAME < dbcreate.sql
The dbcreate.sql looks similar to:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `FooTable1`;
CREATE TABLE `FooTable1` (
...
)
...
DROP TABLE IF EXISTS `FooTable65`;
CREATE TABLE `FooTable65` (
...
)
SET FOREIGN_KEY_CHECKS=1;
Here is a snapshot showing the connections dropping.
I noticed, however, that the CPU is not pinning or anything before, during or after the connections drop:
However, I did notice the read throughput spikes during the connection drop:
Update 1
Okay, so as Rolando suggested, it may be that RAM is the limitation. The instance I am using has ~7GB of RAM available and as I was looking through the monitoring graphs I noticed the freeable memory metric was around the 7GB mark at the same time the connections were dropped. See the attached chart:
The question is, why is there this sudden spike in RAM usage and can it be avoided without having to scale up the hardware?
Best Answer
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
Here is a Chart I posted Last Month
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
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
innodb_flush_log_at_trx_commit=0
during the mass creationm1.xlarge
orm2.xlarge