AWS RDS – Fix Unknown Database Error When Connecting to MySQL

As an AWS customer using MSSQL in RDS, I can confirm the maximum amount of databases per instance (t1.micro) using SQL Express is indeed 30.

I am testing other instance sizes, and will post further if I get different results.

It is not possible to estimate the storage required for the live database without knowing anything about the indexes in use. Each index is essentially a map, and the more "keys" to the map, the more storage space is required for that map.

Cardinality of the index (the data "shape", essentially number of unique "keys" and how they map on to rows containing that key) also becomes important if the data type for the indexed column is something larger than a bigint. An indexed column of varchar(60) with lots of unique combinations (high cardinality) will take up more storage space than one with low cardinality for the same table size because the keys in the map take up more storage space than the data pointers in the map.

UPDATE: thanks to Michael below I should have course said that my assertion about cardinality and storage size is dependent on the storage engine.

For example a database with two InnoDB tables, both with 2176 rows of 3 columns and one index on a VARCHAR(32) column. The only difference in the data for the 2 tables is that tt1 has 2176 unique values for the VARCHAR column and tt2 has an identical value for the VARCHAR column.

You will see the index size differs by around only 16kb:

mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from TABLES where TABLE_SCHEMA='t_idb1';
+------------+------------+-------------+--------------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+------------+------------+-------------+--------------+
| tt1        |       2031 |      180224 |       147456 |
| tt2        |       2031 |      180224 |       131072 |
+------------+------------+-------------+--------------+

Note that InnoDB data storage has 2 components: a data dictionary which is by default stored in the global table space file, ibdata1, in the mysql data directory, and the table data which is stored in .frm files in a subdirectory of the data directory.

That is why, Michael, you are seeing no difference in the storage size of the .frm files. If you were to restart MySQL using the innodb_file_per_table=1 directive you would see this difference reflected in the table space files:

drwx------. 2 mysql mysql   4096 Dec 19 10:52 .
drwxr-xr-x. 4 mysql mysql   4096 Dec 19 10:52 ..
-rw-rw----. 1 mysql mysql     65 Dec 19 10:52 db.opt
-rw-rw----. 1 mysql mysql   8610 Dec 19 10:52 tt1.frm
-rw-rw----. 1 mysql mysql 393216 Dec 19 10:52 tt1.ibd
-rw-rw----. 1 mysql mysql   8610 Dec 19 10:52 tt2.frm
-rw-rw----. 1 mysql mysql 376832 Dec 19 10:52 tt2.ibd

InnoDB storage is unique in that table data is effectively an index of the data dictionary, bringing some performance benefits for some operations. Therefore the effect of cardinality on the storage requirements (around 10% in this instance) is vastly different to a MyISAM:

mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from TABLES where TABLE_SCHEMA='t_msm';
+------------+------------+-------------+--------------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+------------+------------+-------------+--------------+
| tt1        |       2126 |       85040 |        87040 |
| tt2        |       2126 |       85040 |         7168 |
+------------+------------+-------------+--------------+

drwx------.  2 mysql mysql  4096 Dec 19 09:50 .
drwxr-xr-x. 13 mysql mysql  4096 Dec 19 10:29 ..
-rw-rw----.  1 mysql mysql    65 Dec 19 09:28 db.opt
-rw-rw----.  1 mysql mysql  8610 Dec 19 09:31 tt1.frm
-rw-rw----.  1 mysql mysql 85040 Dec 19 09:48 tt1.MYD
-rw-rw----.  1 mysql mysql 87040 Dec 19 09:48 tt1.MYI
-rw-rw----.  1 mysql mysql  8610 Dec 19 09:50 tt2.frm
-rw-rw----.  1 mysql mysql 85040 Dec 19 09:51 tt2.MYD
-rw-rw----.  1 mysql mysql  7168 Dec 19 09:51 tt2.MYI

Hope this explains it a bit more.