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:
- mysqldump the data from the 20GB Instance
- Create a New 10G Instance
- load the mysqldump into the new 10GB instance
- verify all the data you want is present
- 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.
One of my customers is running their entire production site up on EC2. They've had pretty good luck with it, except for when there is some sort of Amazon caused outage (which there have been a couple of over the last couple of years).
The biggest thing that you need to worry about is planning for an EC2 site failure with your application. If EC2 has a major failure are you ok with the database being offline for a couple of days, or possibly loosing all the data in the database? If not then you'll need to handle that via traditional SQL Server DR planning by having another server running as a hot standby server in another Amazon data center. The same goes for the web servers and anything else that hits the database.
The slowest part of using EC2 is the storage. There are ways around this by getting lots of volumes from Amazon and striping across them within the VM.
As for the dev machine, that's pretty easy. Just toss it up there and it should be fine as long as you've got backups going somewhere.
Best Answer
Since you mentioned your client wanted a comma separated file as a report, you could always do something like:
psql -U myuser -h my.rds.external.address -c "\copy (SELECT * FROM mytable WHERE foo = 'bar') TO 'my_report.csv' WITH CSV HEADER NULL AS ''"
If you run that from a command line or a cron job from another machine (once you've allowed that IP address in your RDS instance configuration), you can just compress it and make it available to your client based on their needs.
A
view
would also work if you wanted to give them realtime access to the data instead of a comma separated report.