how to reduce innodb table size?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have application generating 7-8 GB of data in to database(innodb), we delete old records from tables thinking it will reduce disk size but that is not true. even after continuous deletion tables size grow up to 300 GB and now we have disk size concern.

One option i found is to dump and restore table to compact table data, the big concern here is dump/restore took 10-15 hrs for a big table.

My questions are:

  1. Is there any way to compact table size without downtime?

  2. if there is no way to compact online tables then how I can manage such tables generating data exponentially?

  3. What is reasonable size of a single table ( we already have a table crossing 400 GB mark )?

2

If you use innodb_file_per_table, then you can simply OPTIMIZE TABLE to rebuild it.

I prefer that tables should stay small enough that you can perform these types of maintenance operations in your normal maintenance timeframe.

You might also look into partitioning in 5.1.

3

yes, i am using innodb_file_per_table, each table have separate table space however OPTIMIZE TABLE will lock the table during optimization.
Does mysql provide any automatic optimization tool/method?

4

I know it locks the tables, but that’s the best method there is unless you can do something fancy like building a “shadow table” with the same data, then swapping the two tables and deleting the old one.

5

shadowing a table of size 70-100 GB using select command would take hours of time and during that time database would keep getting inserts. we might loose data during the process.
(we are getting 5-6 k qps , 5% are inserts).