MySQL Bugs: #108101: Table does not support optimize is misleading

Bug #108101	Table does not support optimize is misleading
Submitted:	9 Aug 2022 19:41	Modified:	10 Aug 2022 12:21
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
Using OPTIMIZE TABLE on any InnoDB table returns a note that the table does not support optimize, yet it also returns a status of OK. This seems to contradict itself.

InnoDB has been the default storage engine since 2010. MyISAM tables are used as the exception rather than the preferred choice for storage engine. 

OPTIMIZE TABLE actually does work on InnoDB tables and does something useful, even if it isn't exactly the same type of optimization as it was originally designed to do for MyISAM tables.

Reporting a note to users that the table does not support optimize, while in fact executing a change to the table that is something like optimize, is a source of confusion for users. At least it serves no purpose to return this note.

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

mysql> optimize table Accounts; -- or any other InnoDB table
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| test.accounts | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.accounts | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+

Suggested fix:
Continue to report a status of OK when OPTIMIZE TABLE is used on a InnoDB table, but just remove the note.

Hi Mr. Karwin,

Thank you for your bug report.

However, this is not a bug.

The error message is there, because users expect that OPTIMIZE does many things, including re-arranging pages and index trees, which it does not. It actually does less then ANALYZE statement.

This error message is there also because a table takes a metadata lock during operation and thus it can cause the slowdown in the operations.

Not a bug.

do we need to use optimize table for INNODB or we need to use analyze table in mysql for innodb tables.

Hi Mr. Patil,

With InnoDB it is much better practice to dump , remove and restore entire schema. 

It should be done once per annum or once per several years.

Hello,

Saying that OPTIMIZE does not rearrange indexes, etc is a bit disingenuous.  OPTIMIZE is an alias for ALTER TABLE...FORCE on Innodb tables (which is the subject of this report).

ALTER TABLE...FORCE causes a table rebuild to happen, which orders the physical pages in the tablespace according to the primary key index.  It condenses rows onto pages and eliminates free space, all while ensuring the data is in the most optimal order for primary key lookup.

This process takes a metadata lock, preventing other DDL from happening, but it is indicated as an Online DDL operation, which does not block DML transactions from running.

Additionally, the documentation about table rebuilds in the Online DDL page is wrong.  Table rebuilds are not an inplace operation and have never been.  A new tablespace is allocated and the records are copied from the old tablespace to the new tablespace, then the old tablespace is dropped.

Here's an example of a table rebuild:

$ stat /var/lib/mysql/test/foo.ibd
  File: /var/lib/mysql/test/foo.ibd
  Size: 114688    	Blocks: 224        IO Block: 4096   regular file
Device: 253,0	Inode: 455476851   Links: 1
Access: (0640/-rw-r-----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2022-10-03 18:19:33.043005258 -0700
Modify: 2022-10-03 18:22:33.984807754 -0700
Change: 2022-10-03 18:22:33.984807754 -0700
 Birth: 2022-10-03 18:19:33.043005258 -0700

$ mysql -e "optimize table foo" test
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

$ stat /var/lib/mysql/test/foo.ibd
  File: /var/lib/mysql/test/foo.ibd
  Size: 114688    	Blocks: 224        IO Block: 4096   regular file
Device: 253,0	Inode: 455602817   Links: 1
Access: (0640/-rw-r-----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2023-03-29 10:28:21.579067913 -0700
Modify: 2023-03-29 10:28:22.211075239 -0700
Change: 2023-03-29 10:28:22.211075239 -0700
 Birth: 2023-03-29 10:28:21.579067913 -0700

As you can see, it's a new file and new inode, hence it's not an inplace operation.

Doing a logical dump+restore is not practical for many MySQL tables, it's only done when there is innodb corruption or LSN errors.

Thanks,

--Perry