Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an RDS MySQL database on Amazon Web Services.

I want to reduce the size of the database in the control panel in order to save money.

Q: How do I make it smaller? Their site says you have to do it some magic way (from backups or such), but I don't want to mistakenly whack it!

Step by Step please... Thanks! enter image description here

This is what happened: Amazon asked what my desired max size should be. I chose 10GB. I then added data until the table exceeds 10GB. Amazon then automatically increased the size limit to 20GB. In their manual, they state "increasing" is automatic, but "decreasing" is not.

After removing some of the excess data so the data size is under 10GB again, is there a way to make a new 10GB db and copy the data to it using a snapshot perhaps?

share|improve this question
    
I just tried it. Not only is decreasing not automatic, it is also not allowed. I just tried to modify the instance with a lower number and a red error message comes up Requested storage size (5) cannot be less then the current storage size (10). –  RolandoMySQLDBA Sep 18 '12 at 21:01
    
Sorry I misunderstood the question. I thought you meant just shrink the database. –  RolandoMySQLDBA Sep 18 '12 at 21:33
add comment

1 Answer

up vote 2 down vote accepted

Write a script that will create a script

MYSQL_CONN="-hAmazonRDSDNSName -u... -p..."
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} OptimizeTableSQL FROM information_schema.tables WHERE table_schema = 'signs'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -AN -e"${SQL}" > /root/OptimizeAllTables.sql
less /root/OptimizeAllTables.sql

When you are satisfied that the script will do the job then do this:

mysql ${MYSQL_CONN} < /root/OptimizeAllTables.sql

Give it a Try !!!

CAVEAT

Since the default for innodb_file_per_table is ON, each InnoDB table being optimized will actually have its .ibd file shrunk. Any MyISAM tables (which I hope you don't have) will also have their .MYD and .MYI files shrunk as well.

UPDATE 2012-09-18 18:19 EDT

I just tried the following:

  • Created Instance1 with 5G Disk
  • Modified Instance1 to 10G Disk
  • Modified Instance1 to 5G Disk (error message : Requested storage size (5) cannot be less then the current storage size (10))
  • Create Instance2 with 5G
  • Loaded Instance1 with 2MB data
  • Created Snapshot of Instance1 (same size : 10G)

If I restore the snapshot, it creates a third Instance. DB Snapshot must be the whole Server Instance, not a dump of any kind.

You will have to do the following messy procedure:

  1. mysqldump the data from the 20GB Instance
  2. Create a New 10G Instance
  3. load the mysqldump into the new 10GB instance
  4. verify all the data you want is present
  5. destroy the old 20GB

At step 4, please run this Big Storage Engine Space Query on both the 20GB and 10GB instances

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

You will see how much wasted space disappeared when migrating the mysqldump to the new 10GB Instance. This will definitely defragment all InnoDB tables. You may need to run the above script (/root/OptimizeAllTables.sql) I gave you above to run OPTIMIZE TABLE on all the tables in the signs database once a week to keep the .ibd as small as possible. You can also run the Big Storage Engine Space Query to Monitor when it starts approaching 10GB.

share|improve this answer
1  
You code is scary, but I will try it. BTW, anyone know of cheap alternative to AWS RDS MYSQL? I have bootstrapped wikispeedia for 7 years now and its adding up ! –  aaaaaaa Sep 19 '12 at 4:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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