Mysql – Faster optimize table

amazon-rdsMySQLmysql-5.6

I have a 5TB+ MySQL database on RDS which I need to optimize. One particular table 1.5TB. We ran OPTIMIZE TABLE on this table in a separate RDS instance restored from a snapshot and the results were encouraging, but the command took 32 hours to run!

Is there a way to improve performance of the OPTIMIZE TABLE command? This table has 6 indexes other than the PK. I believe removing them will offer significant speedups. We plan to bump up the to the largest instance size RDS offers (r3.8xlarge) to help speed up the process. Are there any parameter groups I can tweak to improve the performance?

Best Answer

If you PARTITION the table (on anything), and have no more than, say, 30 partitions, then you could REBUILD each partition in about 3% of the total time, possibly a lot faster due to the size. (Possibly following it with a ANALYZE PARTITION would be beneficial.)

Do not use 'optimize partition', that erroneously optimizes the entire table.

So, if you get enough reclaimed space to be worth it, set up a cron or EVENT to reorg one partition each night forever.

If you provide SHOW CREATE TABLE and some important queries, I may be able to advise on how to gain some other benefit from the partitioning.

Related Solutions

Mysql – Large INSERTs performance optimization

Two hints for you:

The KEY uid is redundant, because it is covered by the PRIMARY KEY
40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000). Please try doing 40 * 1,000 and see if this works better for you than 1 * 40,000

MySQL Table Alteration – How to Add Column to Big Table in MySQL

Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
BULK LOAD the data from the old table to the new one:
```
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
```
This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.

EDIT: Read this article to get details about the commands used in the above sample query ;)

Best Answer

Related Solutions

Mysql – Large INSERTs performance optimization

MySQL Table Alteration – How to Add Column to Big Table in MySQL

Related Question