Optimize, check and repair tables in MySQL, howto
improve work with tables in MySQL
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 :)