Mysql – When should run Analyze table statement in thesql

MySQL

Hello DBA's i have also some question

  1. Why should we never run ANALYZE TABLE?
  2. How do I know for sure this table/db needs to be analyzed ? It seems that MySQL doesn't store when the last time "stat" was updated ?

Just for Example :-
Assume i have queried 

mysql> show index from goods;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| goods |          0 |  PRIMARY |            1 |          id |         A |     7765796 |     NULL |   NULL |      |      BTREE |         |               |
| goods |          1 |  shop_id |            1 |     shop_id |         A |       14523 |     NULL |   NULL |      |      BTREE |         |               |
| goods |          1 |  shop_id |            2 | create_date |         A |      168168 |     NULL |   NULL |  YES |      BTREE |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

So to see this output,how do i get to know for this table need to run analyze table command ?

Best Answer

I just implemented a service at my company to run ANALYZE TABLE regularly on InnoDB tables.

The reason I did this is that we were affected by this bug: InnoDB persistent statistics not persistent enough .

When using InnoDB persistent statistics, automatic table statistics are refreshed after about 10% of the rows are modified. There's a counter that keeps track of this, but the counter itself is not persistent; it starts over at 0 if the MySQL Server restarts.

We have some tables that are very large, with hundreds of millions of rows. We also restart MySQL Server regularly, because we stay up to date faithfully on MySQL updates and OS updates. The combination of these two things causes us to do restarts before the 10% of rows are modified, and therefore the InnoDB table stats never update.

So I created a service that visits every database replicaset every day. It checks the table mysql.innodb_table_stats and specifically the last_update column. This will tell me how long it has been since the table stats were updated.

If that value is more than 90 days old for a given table, I definitely need to run ANALYZE TABLE.

Otherwise I do a random sampling and pick 10% of the tables to analyze. This should result in every table getting analyzed on average every ten days.

Naturally by checking mysql.innodb_table_stats, only InnoDB tables are considered, but we made a policy that we only support InnoDB tables in our company.

Related Question