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.
First, note that you may be looking at the incorrect operation -- you describe that you want to change storage size, but have quoted documentation describing storage type. This is an important distinction: RDS advises that you won't experience an outage for changing storage size, but that you will experience an outage for changing storage type.
Expect degraded performance for changing storage size, the duration and impact of which will depend on several factors:
- Your RDS instance type
- Configuration
- Will this occur during maintenance?
- Will these changes occur first on your Multi-AZ slave, and then failover?
- Current database size
- Candidate database size
- AWS capacity to handle this request at your requested time of day, at your requested availability zone, in your requested region
- Engine type (for Amazon Aurora users, storage additions are managed by RDS as-needed in 10 GB increments, so this discussion is moot)
With this in mind, you would be better served by testing this yourself, in your environment, and on your terms. Try experimenting with the following:
- Restoring a new RDS instance from a snapshot of your existing instance, and performing this operation on the new clone.
- With this clone:
- Increase the size at different times of day, when you would expect a different load on AWS.
- Increase to different sizes.
- Try it with multi-AZ. See if your real downtime changes as compared to not enabling multi-AZ.
- Try it during a maintenance window, and compare it with applying the change immediately.
This will cost a bit more (it doesn't have to... you could do most of that in 1-3 instance-hours), but you will get a much cleaner answer than peddling for our experiences in a myriad of different RDS environments.
If you're still looking for a "ballpark" answer, I would advise to plan for at least performance degradation in the scope of minutes, not seconds -- again dependent very much on your environment and configuration.
For reference, I most recently applied this exact operation to add 10GB to a 40GB db.m1.small type instance on a Saturday afternoon (in EST). The instance remained in a "modifying" state for approximately 17 minutes. Note that the modifying state does not describe real downtime, but rather the duration that the operation is being applied. You won't be able to apply additional changes to the actual instance (although you can still access the DB itself) and this is also the duration that you can expect any performance degradation to occur.
Note : If you're only planning on changing the storage size an outage is unexpected, but note that it can occur if this change is made in conjunction with other operations like changing the instance identifier/class, or storage type.
Best Answer
It looks like the RDS instance has been modified outside of Elastic Beanstalk - which means that the configuration data between what EB believes to be true, and what is actually true, no longer match up. This is a common problem when using tools like Elastic Beanstalk - it's just a wrapper for other AWS services (it's actually a nice UI for a very specific set of CloudFormation templates), and there's nothing stopping you going to the RDS console and fiddling with the settings yourself once EB has provisioned them.
gp2
has a minimum storage size of 20GB for MySQL instances, so the reason you're getting this error is because your EB environment's 5GB storage size configuration conflicts with the storage type set on the RDS instance. EB believes the storage type to be magentic, so won't enforce the limitations set out by the other storage types. It's not Elastic Beanstalk emitting that error, it's the CloudFormation stack sitting behind EB that ties everything together that the EB console ultimately controls.In order to resolve this, you'll want to set your Elastic Beanstalk's environment to match up as close as you can to what your RDS instance is configured to. I don't believe it's possible with Elastic Beanstalk to modify the RDS instance's storage type from the default magnetic configuration. So, you've got 2 options here:
Set your storage size in your EB config to be 100GB and save it, this should have EB effectively sync up with the truth. When it applies, it'll tell CloudFormation to resize the database. Now whether this will actually take the instance down for resizing or not, since the "new" storage size is the same as the current size, I'm not sure. It may also throw an error because new size == old size. You could modify it to the minimum 20GB (if you're wanting to go way lower), which will do an instance resize, and ensure that your storage size on RDS matches that of Elastic Beanstalk's configuration. You'll just have to bear in mind that you can't go any lower than 20GB because of the storage type limitation.
Resize your RDS instance manually back to magentic storage and size of 5GB - to what Elastic Beanstalk believes to be the truth. This of course will only work if your database is small enough to fit on a 5GB instance. If it doesn't, you're out of luck here, and it's probably worth exploring option #1 instead. If it will fit, this action will resize your database back to what Elastic Beanstalk thinks it currently it, and you'll then be able to resize your database again using EB's configuration tools.