Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We would like to run an optimization on our MariaDB database while still allowing it to serve queries.
I see that for MySQL this is possible since 5.7.4 (from this link)
For InnoDB tables prior to 5.7.4 and other table types, MySQL locks the table during the time OPTIMIZE TABLE is running. As of MySQL 5.7.4, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.
Does anyone know how we can do it for MariaDB as well? I see no mention at the MariaDB OPTIMIZE page.
Do you want to optimize MariaDB tables or do you want to run OPTIMIZE TABLE on MariaDB? Because those are 2 things that have nothing to do with each other.
Optimizing, in general, is not a process as easy as running a command- you need to analyze your server, profile your queries, change your schema, check your configuration; select, dimension and configure your hardware and os correctly, and use it efficiently from your application.
OPTIMIZE TABLE is a command that -to summarize-, for MyISAM and InnoDB MySQL/MariaDB engines, it defragments your table- in most cases with no real impact on performance, although it can save space under certain circunstances. Please do not execute optimize table regularly, it serves no purpose in most cases, while creating a lot of work for the servers (it basically recreates the table).
Online alter table is only possible on MySQL >=5.6+, and equivalent versions of MariaDB. OPTIMIZE TABLE basically runs ALTER TABLE <table> ENGINE=InnoDB for InnoDB tables, which is known to be able to be online since 5.6.17 for MySQL. I cannot say for sure for MariaDB 10+, as it will depend on the exact version, but that is as easy to check as forcing it to be online with the LOCK=NONE parameter, which would fail immediately otherwise.
If for any reason you cannot run a table defragmentation online, you can always use pt-online-schema change and perform a null operation, which will copy the new table without (almost) any locking.
Not yet documented but
ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE;
Is definitively working
(There is also an open bug on the topic here https://jira.mariadb.org/browse/MDEV-10453)
