Is there a proper order to run mysqlcheck commands in for periodic maintenance?



  • Is there a proper order in whcih to run the various https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html#option_mysqlcheck_analyze commands?

    e.g. CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE and also FLUSH TABLES and FLUSH LOGS.

    I wrote this script that I plan to run periodically via cron and just want to make sure I'm not misunderstanding the docs and running them in the right order, if any.

    echo -n 'Checking MySQL tables...'
    OUTPUT=$(mysqlcheck --defaults-file=/etc/mysql/sys.cnf --all-databases --check 2>&1)
    if [ $? -eq 0 ]; then
        echo ' SUCCESS.'
    else
        echo ' FAIL!'
    fi
    

    echo -n 'Analyzing MySQL tables...'
    mysqlcheck --defaults-file=/etc/mysql/sys.cnf --all-databases --analyze >/dev/null 2>&1
    if [ $? -eq 0 ]; then
    echo ' SUCCESS.'
    else
    echo ' FAIL!'
    fi

    echo -n 'Optimizing MySQL tables...'
    mysqlcheck --defaults-file=/etc/mysql/sys.cnf --all-databases --optimize >/dev/null 2>&1
    if [ $? -eq 0 ]; then
    echo ' SUCCESS.'
    else
    echo ' FAIL!'
    fi

    echo -n 'Flushing MySQL tables...'
    mysqladmin --defaults-file=/etc/mysql/sys.cnf refresh >/dev/null 2>&1
    if [ $? -eq 0 ]; then
    echo ' SUCCESS.'
    echo 'Flushing MySQL logs... SUCCESS.'
    else
    echo ' FAIL!'
    echo 'Flushing MySQL logs... FAIL!'
    fi

    if [ -z "$OUTPUT" ]; then
    :
    else
    echo 'Raw mysqlcheck results...'
    echo "${OUTPUT}"
    echo ' ...DONE.'
    fi

    EDIT

    I updated the script, removed the REPAIR TABLE part because I think that type of stuff should be done manually.



  • Check and repair and flush tables are not relevant unless you use MyISAM tables. I never use MyISAM tables. I never run mysqlcheck directly.

    FLUSH LOGS is not necessary. It does nothing to help tables.

    OPTIMIZE TABLE is not usually needed in the default storage engine, InnoDB. InnoDB will re-use empty extents automatically. Eventually, an InnoDB tablespace might become fragmented, but this doesn't necessarily have any negative effect except for taking extra space. OPTIMIZE TABLE rebuilds a tablespace in a defragmented manner, so occasionally I would run it, but not on a schedule. Do it as needed, typically after a bulk data import or bulk delete.

    ANALYZE TABLE is worthwhile to run periodically. I set up a scheduled job to run this about every 10 days per table. Actually my scheduled job would run every day and randomly choose 10% of tables to analyze. That was probably overly complex, because there's no downside to just running ANALYZE TABLE every day on every table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2