Run OPTIMIZE TABLE to defragment tables for better performance

Question

I read articles related to OPTIMIZE TABLE which needs further clarification.

I ran a mysqltuner.pl against my MariaDBv10.6.7 where I got a few recommendations and one of them was to run optimize table.

Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `DB`.`TableA`; -- can free 426 MB
    Total freed space after theses OPTIMIZE TABLE : 426 Mb

Questions:

1. Is it ok to run OPTIMIZE TABLE 'TableA' in InnoDB tables to get better performance (with my understanding it clears up unused space in the disk but will it contribute to performance)?
2. Since I am using InnoDB it says "Table does not support optimize, doing recreate + analyze instead". Do I need to run Alter Table ... OPTIMIZE instead of OPTIMIZE TABLE (Guess both are linked)?
3. Even after I run the OPTIMIZE TABLE as suggested still I see that table 426Mb is not freed up by it completely (It was reduced to 384MB). Can't we free up the complete size?

> select * from information_schema.TABLES where TABLE_NAME = "TableA"\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: DB
     TABLE_NAME: TableA
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1600474
 AVG_ROW_LENGTH: 207
    DATA_LENGTH: 332136448
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 446693376 (426MB)
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-08-09 16:01:05
    UPDATE_TIME: 2022-08-09 16:04:47
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: partitioned
  TABLE_COMMENT:
1 row in set (0.01 sec)

ERROR: No query specified


> optimize table TableA;
+-----------+----------+----------+--------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                           |
+-----------+----------+----------+--------------------------------------------------------------------+
| DB.TableA | optimize | note     | Table does not support optimize, doing recreate + analyze instead  |
| DB.TableA | optimize | status   | OK                                                                 |
+-----------+----------+----------+--------------------------------------------------------------------+
2 rows in set (8.25 sec)

127.0.0.1:3307> select * from information_schema.TABLES where TABLE_NAME = "TableA"\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: DB
     TABLE_NAME: TableA
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1600474
 AVG_ROW_LENGTH: 193
    DATA_LENGTH: 310116352
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 402653184 (384MB)
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-08-09 16:47:00
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: partitioned
  TABLE_COMMENT:
1 row in set (0.27 sec)

Used the same logic as in mysqltuner.pl to find the free size. Not sure about the logic behind the query.

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),cast(DATA_FREE as signed) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND cast(DATA_FREE as signed)*100/(DATA_LENGTH+INDEX_LENGTH+cast(DATA_FREE as signed)) > 10 AND NOT ENGINE='MEMORY' $not_innodb

Update 1:

As requested, added the output of tableA

> SHOW TABLE STATUS WHERE name LIKE "TableA"\G;
*************************** 1. row ***************************
            Name: TableA
          Engine: InnoDB
         Version: 10
      Row_format: Dynamic
            Rows: 1875385
  Avg_row_length: 3
     Data_length: 5685248
 Max_data_length: 0
    Index_length: 0
       Data_free: 1991245824
  Auto_increment: NULL
     Create_time: 2022-10-25 10:53:40
     Update_time: 2022-10-25 11:34:32
      Check_time: NULL
       Collation: utf8mb3_general_ci
        Checksum: NULL
  Create_options: partitioned
         Comment:
Max_index_length: 0
       Temporary: N
1 row in set (0.002 sec)

> Show create table TableA;

| TableA | CREATE TABLE `TableA` (
  `Col1` mediumint(8) unsigned NOT NULL,
  `Col2` tinyint(4) NOT NULL,
  `Col3` tinyint(4) NOT NULL,
  `Col4` tinyint(4) NOT NULL,
  `Col5` tinyint(4) NOT NULL,
  `Col6` smallint(4) NOT NULL,
  `timestamp` int(11) NOT NULL,
  `Col7` bigint(20) DEFAULT NULL,
  `Col8` bigint(20) DEFAULT NULL,
  `Col9` tinyint(4) DEFAULT NULL,
  :::
  `Col40` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`Col1` ,`Col2` ,`Col3` ,`Col4` ,`Col5` ,`Col6`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
 PARTITION BY RANGE (`timestamp`)
(PARTITION `p2022_10_11_02_00_00` VALUES LESS THAN (1665437400) ENGINE = InnoDB,
 PARTITION `p2022_10_11_03_00_00` VALUES LESS THAN (1665441000) ENGINE = InnoDB,
 PARTITION `p2022_10_11_04_00_00` VALUES LESS THAN (1665444600) ENGINE = InnoDB,
 ....
 PARTITION `p2022_10_25_12_00_00` VALUES LESS THAN (1666683000) ENGINE = InnoDB)
 //partitioned by timestamp. Partitioned more than 360

Answer 1

All is well on your database, from the information in your question.

1. Yes, it is OK to use OPTIMIZE on production tables. It, and its InnoDB-era replacement, use online data definition language statements.

2. The server does the right thing when you say OPTIMIZE TABLE for an InnoDB table.

3. Tuning tools like sqltuner.pl provide estimates, not hard numbers, about things like saved space.

4. It is difficult to measure performance improvements resulting from OPTIMIZE TABLE in many cases. Busy tables with FULLTEXT indexes are an exception.

Some background.

When applications change tables (using INSERT, UPDATE, and DELETE) they sometimes leave unused space in the tables' data structures. An obvious case: UPDATEing a row to change a longer VARCHAR() value to a shorter one.

FULLTEXT indexes also leave empty space when changed.

Diagnostic programs. and information_schema.TABLES.DATA_FREE, estimate the space that will be available after you reorganize (optimize) the tables. Those estimates are more accurate for the legacy MyISAM storage engine than they are for newer storage engines like InnoDB. So it is not a surprise that your table reorg didn't yield as much free space as your tool estimated.

Some references:

• MariaDB Defragmenting InnoDB Tablespaces
• MySQL Optimize Table statement.

Answer 2

Using OPTIMIZE TABLE defragments InnoDB tables, which might reduce the size, especially right after major data changes like bulk deletes.

But in practice, it won't improve performance in a significant way. Performance is not the reason to use OPTIMIZE TABLE.

The error message about "Table does not support optimize" is because it doesn't do exactly the same thing that OPTIMIZE TABLE does on the old MyISAM storage engine. For InnoDB, it does support the statement, and it does do something useful: copies rows to a new tablespace, and rebuilds secondary indexes in the process. The message that it doesn't support optimize is misleading.

For InnoDB, OPTIMIZE TABLE <name> does the same work as ALTER TABLE <name> FORCE or ALTER TABLE <name> ENGINE=InnoDB. There is no syntax for ALTER TABLE <name> OPTIMIZE (except for a specified partition).

As for your mysteriously high "data_free", I wonder if you are storing this table in the shared system tablespace. That is, the configuration option innodb_file_per_table is disabled, which causes tables to be stored together in the system tablespace, which is conventionally in a file ibdata1 in the data directory.

If the table is in the system tablespace, then OPTIMIZE TABLE doesn't copy the table to a new tablespace, it just moves the data to a different area of the system tablespace, and does not recover any free space. Further, the "data_free" is reported for the whole tablespace, not just for the respective table. The "data_free" probably won't change much after you optimize a table in that tablespace.

You can check what tablespace your table occupies this way:

SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME = '<schemaname>.<tablename>';

The space id for the system tablespace is 0. If it's not 0, then the table is in its own separate tablespace.

---

I wish people didn't use mysqltuner.

mysqltuner is known to offer unreliable advice. I've seen cases where its advice indicates the author doesn't know enough about MySQL internals to be giving advice. I think mysqltuner is the cause of a lot of needless work.

Here are some past examples where I tried to correct misunderstandings of people who were confused by mysqltuner reports:

• InnoDB Write Log efficiency is too high more than 100% (1953.15%)?
• MySQL tmp_table_size for a small database
• Mysql per thread memory, variables that lead to thread memory usage?

Answer 3

Short Answer: 'Never' use OPTIMIZE TABLE on InnoDB tables. (It's mostly a waste of time. Even if you see an improvement, it is gobbled up soon afterward.)

Why no savings in your case? Each PARTITION pre-allocates 4MB-7MB of "free" space. The 'advice' failed to notice that; hence you got no savings.

If you ever need to DELETE lots of rows, let's talk. There are usually better ways to do such, and they don't lead to the temptation to do OPTIMIZE.

The table have about 70 partitions? And only 1.6M rows? Do you get any benefit from PARTITIONing? (I doubt it.) Show us the main queries; I will take you into some better indexes and no partitioning.