mysql database .ibd file size

Question

MySQL .ibd file has grown to more than 3 TB and my RedHat box can no longer support adding more disk spaces.

Questions

• How can I scale database storage ?
• What best practices can resolve this issue ?

Note:I use one .ibdfile per table and have a couple of huge tables.

Answer 1

If you are just storing data, your data mount for /var/lib/mysql should be 5TB SATA drives. If your application is write heavy, your data mount for /var/lib/mysql should be 5TB SAS drives RAID10.

If upgrading disks is out of the question, you need to do periodic table defragmentation in one of three(3) ways:

1. ALTER TABLE myinnodbtable ENGINE=InnoDB;
2. OPTIMIZE TABLE myinnodbtable;
3. Customize the defragmentation the table

You can perform this defragmentation manually as follows:

CREATE TABLE myinnodbtemptable LIKE myinnodbtable;
INSERT INTO myinnodbtemptable SELECT * FROM myinnodbtable;
ALTER TABLE myinnodbtable RENAME myinnodbtableold;
ALTER TABLE myinnodbtemptable RENAME myinnodbtable;
DROP TABLE myinnodbtableold;

Option 3 does not work if the InnoDB table has constraints because constraints will disappear.