guessing unused junk space in table. (innodb mysql/mariadb )

Question

We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am able to reclaim space from big table with a ALTER TABLE mydb.mytable ENGINE=InnoDB.

Hopefully that works, but I don't know table will return useful unused space a with table will not. I mean some big table have sometimes NO useless space, and some have a lot, depending how much DELETE have been done (I guess). Is there a way to know in advance how much space is allowed for deleted record so I can run ALTER TABLE mydb.mytable ENGINE=InnoDB only on table with lot of useless space.

Answer 1

After digging the question, I went with a SELECT like this one:

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb' AND DATA_FREE > 0 ORDER by DATA_FREE DESC;

So I have a list of table to process.

a scripted form would be :

#! /bin/bash
[ -z "$1" ] && echo usage "$0 [database_name]"
[ -z "$1" ] && exit 1
for T in $(mysql -ss -e "SELECT table_name from information_schema.tables WHERE table_schema = \"$1\" AND DATA_FREE > 0 ORDER by DATA_FREE DESC;");do echo \"$T\";time mysql -e "SET sql_log_bin=0;alter table $1.$T ENGINE=InnoDB";done 2>&1

Answer 2

That ALTER will copy the table over, requiring briefly enough space for a copy of the table.

InnoDB does a pretty good job of keeping its BTrees somewhat free of wasted space. Hence the action won't do much good.

Be sure that innodb_file_per_table is still on; otherwise you will be moving the table into ibdata1, which is quite hard to shrink.

Certain tables will always have Data_free > 0, so you are generally wasting effort on them. I suggest you add LIMIT 10 to your select.