Diagnose Table Bloating

Tables can become bloated during regular operation, meaning they consume more disk space than is required. The causes can be deletions or updating large amounts of data, resulting in the following:

• The disk of the MariaDB cluster runs out of space.

• MariaDB tables might be bloated.

Solution

To overcome the fragmentation of the tables, they have to be rewritten. This causes data to be stored in a physically optimal way. MariaDB stores the information about bloat in the data_free column of information_schema.tables.

1. Diagnose the problem by finding bloated tables. You can use the following query:

Warning: In particular, if there are numerous databases or tables in the instance, frequent querying from information_schema.tables is expensive and, hence, should be avoided. Querying invokes heavy filesystem operations and can lead to substantial performance impacts during the execution of the query.

SELECT table_name, data_free 
FROM information_schema.tables 
WHERE table_schema = '*database_name*' and data_free > 0 
ORDER BY data_free desc; 

Note: When the table statistics are not up to date, the table size computation in information_schema.tables may not work as expected, and the queries may display incorrect values for a particular table. You can use ANALYZE TABLE <tablename> instead to sort the problem and refresh the table statistics.

When you use InnoDB Full-Text Search, it creates additional files, consuming added space on the filesystem, but does not show up in the information_schema.tables. You can determine the size by querying information_schema.innodb_sys_tablespaces.

1. Reclaim free space. You can follow the steps for InnoDB and MyISAM accordingly:

• InnoDB To rewrite a InnoDB table, execute ALTER TABLE <table_name> ENGINE INNODB or ALTER TABLE <table_name> FORCE.

Warning: During the rewrite, the tables are blocked for writing access. To minimize the downtime, you can execute ALTER with ALGORITHM=COPY, LOCK=NONE.

For easy generation of all ALTER TABLE statements, we recommend using the following SQL statement:

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '.', TABLE_NAME, '` ENGINE InnoDB;') FROM information_schema.tables WHERE `TABLE_TYPE` = 'BASE TABLE' AND `ENGINE` = 'InnoDB'

• MyISAM You can use the OPTIMIZE TABLE command to rewrite a MyISAM table and reclaim all disk space from data_free. Example: OPTIMIZE TABLE <table_name>;

While the OPTIMIZE TABLE command is designed for the MyISAM storage engine to reorganize and optimize data files, it also supports InnoDB for compatibility reasons. Internally, when the command is executed but notices that the table uses InnoDB and not MyISAM, the command executes ALTER TABLE <table_name> FORCE instead of manually optimizing the table.