The Ultimate Guide to High Availability with MariaDB

Comments - OPTIMIZE TABLE

5 years, 6 months ago Travis Basevi
Re: OPTIMIZE TABLE

When running commands like:

SET GLOBAL innodb_optimize_fulltext_only=1; OPTIMIZE TABLE table; SET GLOBAL innodb_optimize_fulltext_only=0;

Note that the SET commands will not be replicated, but the OPTIMIZE command will be replicated. This will mean that the master will update the fulltext index and, as far as I can see, the slave(s) will run the "defragmenting" version of optimize. This will leave the slaves with non-updated versions of the fulltext index.

In any case, have confirmed the slaves have not updated the fulltext indexes by use of innodb_ft_aux_table and examining the INNODB_FT_INDEX_CACHE (and similar) tables.

If this is by design and not a bug, then NO_WRITE_TO_BINLOG should always be used when using optimize to update fulltext indexes, and the commands should then be run on each slave in turn.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.