3

We are importing a large historical database into RDS from a mysqldump

The gziped sql file was 3GB, the uncompressed sql file is 18GB.

We created a 30GB AWS RDS instance and imported the file... the RDS instance ran out of space.

We created a 50GB AWS RDS instance, imported the file... the RDS instance ran out of space.

How do I calculate the size of AWS RDS instance I need to import this dump?

To try and pre-answer any questions...

  • We don't have access to the machine that the dump came from to try and size it that way.
  • I thought it maybe RDS binary logs or slow logs that were taking the space but looking at the actual database size earlier showed it was all actually in the DB...
    mysql>  SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB"  FROM information_schema.TABLES GROUP BY table_schema ; 
    +--------------------+----------------------+
    | Database Name      | Database Size in MB  |
    +--------------------+----------------------+
    | xxxxxxxxxx         |       41658.15374756 |
    | information_schema |           0.00976563 |
    | mysql              |           5.96341228 |
    | performance_schema |           0.00000000 |
    +--------------------+----------------------+
    4 rows in set (28.39 sec)
    
4

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.

  • Curiously, your claim about index cardinality affecting the physical size of the index seems only to be true of MyISAM, and not InnoDB (I didn't realize it would be true of either one, assumed redundant copies were stored). Creating two tables with an INT primary key and an indexed CHAR(36), filling one of them with 5000 copies of exactly the same 36-character UUID, filling the other with 5000 unique UUIDs, the tablespaces are exactly the same size with InnoDB, but with MyISAM, the MYI index file for the table with identical values (cardinality = 1) is much smaller. Thoughts? – Michael - sqlbot Dec 18 '13 at 18:25
  • Hi Michael. I'm afraid I cannot replicate your results, although the affect of cardinality on storage size is much less marked for InnoDB than MyISAM due to the way the different storage engines store table and index data. – James Firth Dec 19 '13 at 10:53

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.