A user's identity in MariaDB is determined by their username and host combination. If you have numerous users from different source hosts, you will need to reset the password for each user. To reset your database password, connect to the MariaDB cluster and run either of the following commands using the user whose password should be changed:
SET PASSWORD = PASSWORD('<new password>');
SET PASSWORD = '<passwordhash>';
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.
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
.
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.
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
.
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.
Get help troubleshooting common issues with MariaDB.
Learn how to troubleshoot the cause of table bloating.
Learn how to reset your database password.