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:
- 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)?
- 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)?
- 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
