Mysql – Is it safe to kill ANALYZE TABLE query on a MyISAM table

myisamMySQLmysql-5.1

I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an index is being used, I'm not sure.

So I'm thinking to run a OPTIMIZE TABLE query on the table to speed things up. But there are currently multiple ANALAYZE TABLE queries locking the table, so is it safe to kill such queries ? I couldn't find enough information on mysql's documentation for that matter..

Thanks.

Best Answer

First thing to know is what ANALYZE TABLE does.

For a MyISAM table mydb.mytable, ANALYZE TABLE traverses all the indexes in the mytable.MYI file, reads all BTREE pages, computes statistics, and stores in them in the information_schema, specifically STATISTICS. You can see it when you run one of the following:

  • SHOW INDEXES FROM mydb.mytable;
  • SELECT * FROM information_schema.statistics WHERE table_schema='mydb' AND table_name='mytable';

ANALYZE TABLE is a read-only operation and does issue a table-wide read lock.

ANSWER TO YOUR QUESTION

Yes, you can kill ANALYZE TABLE and wait briefly for the read lock to be released. However, there is a significant penalty. Because the index statistics will not be completely computed, the MySQL Query Optimizer will take rather bad guesses at generating EXPLAIN plans of queries against that table.

SUGGESTIONS

SUGGESTION #1 : Delay ANALYZE TABLE

If you want to run OPTIMIZE TABLE mydb.mytable without doing the ANALYZE TABLE operation, do the following:

ALTER TABLE mydb.mytable RENAME mydb.mytable_opt;
ALTER TABLE mydb.mytable_opt ENGINE=MyISAM;
ALTER TABLE mydb.mytable_opt RENAME mydb.mytable;

This will rename the table, perform the defragmentation, and rename it back.

You could do it in one line as well

ALTER TABLE mydb.mytable ENGINE=MyISAM;

If you want to run ANALYZE TABLE later on, set up a cron job to run it in every midnight or perhaps once a week. You will have to do this inevitably if you expect queries against your table to perform well after an optimization.

SUGGESTION #2 : Change the MyISAM table to InnoDB

During the generation of an EXPLAIN plan, InnoDB will perform reads against the index pages of a table by traversing 2-3 levels of nonleaf nodes and literally approximate (or really guess) the cardinality of all column levels. You can tell this is happening to an InnoDB table when you run SHOW INDEXES FROM mydb.mytable; because the cardinality changes with each run.

In light of this, you can simply run

ALTER TABLE mydb.mytable ENGINE=InnoDB;

and that's it. Even if you ran ANALYZE TABLE on an InnoDB table, the statistics can get rather stale. By default, innodb_stats_on_metadata is already disabled in MySQL 5.6. If you have MySQL 5.5 and prior, you can disable the update of the statistics by doing this:

SET GLOBAL innodb_stats_on_metadata = 0;

You will still have to bite the bullet and run ANALYZE TABLE sometime downstream if the InnoDB table experiences high INSERTs, UPDATEs, and DELETEs. Otherwise, the MySQL Query Optimizer will take bad guesses at EXPLAIN plans.

Related Question