Optimize, check and repair tables in MySQL, howto improve work with tables in MySQL

Monday, 12th April 2010

There are few quick tips that helps if some unexpected downtime of your SQL server occurs. Even though nowdays this won’t happen too often with servers running with a good ups, sometimes even administrator errors can cause problems with your mysql tables. If your MySQL server refuses to start, it’s quite probable that you’re experiencing a problem with a broken table or tables in MySQL. Therefore you need to go through all your mysql databases and check the consistency of your MyISAM or Innodb tables, ofcourse accordingly to your MySQL database types. To check a certain table for consistency with MySQL after you select the database, you have to execute: mysql$ CHECK TABLE your_table_name; If the above command after presumably executed with all your databases and there consequent tables reports, everytime OK then your MySQL crashes are not caused by table incosistencies. However if instead of OK the CHECK TABLE reports Corruptthen you have a broken table and you have to fix it as soon as possible, in order to be able to bring up to life the MySQL server once again. Here is an example of a broken table after a CHECK REPAIR searchindex; : +------------------+-------+----------+------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+------------------------------------+ | test.searchindex | check | error | Key in wrong position at page 4096 | | test.searchindex | check | error | Corrupt | +------------------+-------+----------+------------------------------------+ To fix the CORRUPTED or BROKEN table as also known you have to issue the command: mysql$ REPAIR TABLE yourtable_name; Depending on your table size after a while, if everything is going fine you should see something like: +------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+--------+----------+----------+ | test.searchindex | repair | status | OK | +------------------+--------+----------+----------+ 1 row in set (0.08 sec) Be aware that sometimes in order to fix a broken table you have to use the MySQL repair extended function. Expect The EXTENDED REPAIR function option to take a much more time, even sometimes with large databases with million of records it could take hours, especially if the MySQL server is serving other client requests as well. This terrible siutation sometimes occurs because of mysql locks, though I believe locks are probably a topic of another post. Hopefully after issuing that the table in MySQL would properly repair and your MySQL will begin starting up with the rc script once again. Apart from crashes and table repairs there are few nice things concerning MySQL that are doing me good every now and then. I’m talking about the MySQL functions: ANALYZE TABLE and OPTIMIZE TABLE ANALYZE TABLE does synchronization of the information concerning the variables within tables that has a INDEX key settled according to the database to which they belong. In other simply words, executing ANALYZE TABLE to your database tables every now and then and that would probably help in speeding up the code executed in the SQL that has JOINS involved. The second one OPTIMIZE TABLE is natively supported with MyISAM SQL database types, and secondary supported with Innodb, where the Optimize with Innodb is done in a non-traditional way. When invoked to process an Innodb table OPTIMIZE TABLE does use ALTER TABLE to achieve an Innodb table optimization. In practice what the optimize table does is defragmentation of the table unto which it’s executed. A quick example of the optimize table is for instance: OPTIMIZE TABLE your_table_name; In order to find out which tables need to be defragmented or in other words needs optimize table you have to issue the cmd: show table status where Data_free!=0; Note that you have to issue this command on each of your databases; Just because this is so boring you can of course use my script check_optimize_sql.sh which will quickly loop through all the databases and show you which tables need to be optimized. I’ve written also a second shell script that loops through all MySQL databases and lists all databases and sub tables that requires optimize and further on proceeds optimizing to download the script check_and_optimize_sql_tables.sh click here Happy optimizing 🙂

Share this on:

Download PDFDownload PDF

Tags: , , , , , , , , , , , , ,

3 Responses to “Optimize, check and repair tables in MySQL, howto improve work with tables in MySQL”

  1. HgAlexx says:
    Firefox 6.0 Firefox 6.0 Windows 7 x64 Edition Windows 7 x64 Edition
    Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0) Gecko/20100101 Firefox/6.0

    Hi,

    I am using a simple .sh in a daily cron:

    mysqlcheck –defaults-extra-file=/etc/mysql/debian.cnf –check –analyze –auto-repair –optimize –all-databases –silent >> /var/log/mysql/cronwork.log

    Where /etc/mysql/debian.cnf contains login info of the special system account debian use to perform any task on mysql

    This will do everything on all tables of all databases, if required.
    –silent will only output error in /var/log/mysql/cronwork.log

    Bye.

    View CommentView Comment
    • admin says:
      Epiphany 2.30.6 Epiphany 2.30.6 Debian GNU/Linux x64 Debian GNU/Linux x64
      Mozilla/5.0 (X11; U; Linux x86_64; en-us) AppleWebKit/531.2+ (KHTML, like Gecko) Version/5.0 Safari/531.2+ Debian/squeeze (2.30.6-1) Epiphany/2.30.6

      Heya Alex,

      Thanks for the tip, I’ll try how this works on some mysql servers 🙂
      Great info thanks again!

      Best!
      Georgi

      View CommentView Comment
  2. HgAlexx says:
    Firefox 6.0 Firefox 6.0 Windows 7 x64 Edition Windows 7 x64 Edition
    Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0) Gecko/20100101 Firefox/6.0

    Be carefull, the comment form remove all double – before all arg.
    Check mysqlcheck manpage for more info.

    View CommentView Comment

Leave a Reply

CommentLuv badge