MySQL db optimization error on innodb tables

Question

I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32.

When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below errors are returned. I know that some of this is informational messages, and these can be ignored, however I am wondering how to get more info about these errors.

Is there a way to exclude tables from the mysqlcheck -o optimize (like the ignore option with mysqldump '--ignore-table').

mysql.innodb_index_stats note : Table does not support optimize, doing recreate + analyze instead error : The used command is not allowed with this MySQL version status : Operation failed

mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead error : The used command is not allowed with this MySQL version status : Operation failed

mysql.gtid_executed Warning : Please do not modify the gtid_executed table. This is a mysql internal system table to store GTIDs for committed transactions. Modifying it can lead to an inconsistent GTID state. note : Table does not support optimize, doing recreate + analyze instead

Answer 1

You will have to select all databases yourself and bypass what you don't want.

Back on Dec 16, 2011, I answered the post How do you mysqldump specific table(s)? where I excluded tables from a mysqldump of specific database. Also, on Feb 20, 2013, I answered the post Any option for mysqldump to ignore databases for backup?.

You can use the same techniques. If you wish to optimize all your databases and exclude those databases used specifically by mysqld, script it in the OS.

You can do something like this:

DBS_TO_EXCLUDE="information_schema performance_schema mysql sys"
NOT_IN_LIST=""
COMMA=""
for DB in ${DBS_TO_EXCLUDE} ; do
    NOT_IN_LIST="${NOT_IN_LIST}${COMMA}'${DB}'"
    COMMA=","
done
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN (${NOT_IN_LIST});"
DBLIST=$(mysql -h... -u... -p... -ANe"${SQL}")
for DB in ${DBLIST} ; do
    mysqlcheck -o ${DB}
done

If there are other DBs you wish to skip, just add it to the DBS_TO_EXCLUDE variable.

NOTE : If the InnoDB Tables are big, you should focus on optimizing the big tables that have high INSERTs, UPDATEs,and DELETEs rather than whole databases.

Answer 2

"Optimize" is usually useless; don't bother. And, yes, it takes a long time because it copies the table(s) over, plus rebuilding the INDEXes from scratch.

"Table does not support optimize, doing recreate + analyze" -- Just a warning; ignore the harsh tone of it.

ANALYZE TABLE, by itself (without the "optimize" part) is fast and usually useless.

If you regularly DELETE huge chunks of a table, don't. Instead, see http://mysql.rjweb.org/doc.php/deletebig for alternatives.