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?
