MariaDB Table Maintenance

Database tables require periodic maintenance. If not done, database performance will gradually drop off.  In extreme cases, it can completely stop under heavy load.

Table maintenance can be done by clients as required. But in case of large databases (tens of GBs or more), we would advise you, to agree a maintenance windows with our technical support, who will perform the necessary steps.

MyISAM tables - these are very simple and do not support transactions and other advanced features. We recommend to  run OPTIMIZE TABLE and ANALYZE TABLE on tables, which see a lot if writes. The first one reorganizes the data in the table. The second analyzes them - MySQL uses the gathered information to create an optimal query plan for executing queries. Take note, that like any write operation in MyISAM, even these lock the whole table while they are runnning. Therefore, the best time to run these commands, is during a period of minimal activity.

InnoDB tables- advanced tables with transactions. Write operations lock only the changed data, not the whole tables. It is possible to run OPTIMIZE TABLE on InnoDB table. However, this command is not natively implemented - instead, MySQL internally runs a different set of commands, which result in the same outcome:

Table does not support optimize, doing recreate + analyze instead 

InnoDB tables need to be defragmented from time to time. You can achieve that by running ALTER TABLE,  which re-sets engine to InnoDB.

ALTER TABLE table_name ENGINE=InnoDb;