Search code examples
optimizationmariadbdatabase-performance

What happens if I restart a MariaDB server while it is Repairing or Optimizing a very large table?

What happens if I restart a MariaDB server while it is Repairing or Optimizing a very large table (like at least 20GB)? Probably because I need to use the table for other stuff and I'm just getting plain bored.

Solution

  • REPAIR and OPTIMIZE are designed to be crash-safe. (Or at least to a large extent.)

    OPTIMIZE, for example, copies the table over to a tmp table name. When finished, it internally does a RENAME TABLE, which is fast.

    OPTIMIZE is needed in only very rare cases for MyISAM. It is even less needed for InnoDB. What is your use case? I will probably counter that it is 'futile' or 'not worth the effort'.

    Repair is needed only for MyISAM. I hope you are not using that antiquated engine.

    More

    • Consider switching to InnoDB; we can discuss this further. REPAIR is much less often needed, plus is automated.
    • What is the schema? What is the data flow like? I may have tips on avoiding the indexes getting out of date. (The way the .MYD file is laid out is problematic for certain data flows.)
    • Use ANALYZE TABLE (instead) when there is no complaint about index corruption yet queries are suddenly slow.