Search This Blog

Tuesday, November 13, 2018

First thing to try for MySQL when query is slow

In MySQL, when query is slow, most of the time is because of index. You can confirm it by using "explain query".

According to MySQL document:

MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.
So one quick solution to ask the query to use the index again, it is to run "Analyze Table" command.

According to MySQL document:

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

According to my experience, it doesn't take long time to run  "Analyze Table", and it dose solve the problem. for example:

Analyze local table user_contact;
Analyze local table contacts;
Analyze local table historyv2;
Analyze local table user;
Analyze local table sent;
Analyze local table email;
Analyze local table custom_value;
Analyze local table custom_attribute;

You can create a weekly event to do it automatically:


DELIMITER $$

CREATE EVENT weekly_analyze
    ON SCHEDULE
      EVERY 1 week
    DO
      BEGIN
        Analyze local table i88.user_contact;
        Analyze local table i88.contacts;
        Analyze local table i88.historyv2; 
        Analyze local table i88.user;
        Analyze local table i88.sent;
        Analyze local table i88.email;
        Analyze local table i88.custom_value;
        Analyze local table i88.custom_attribute;

    END $$

delimiter ;

This can save your life for some time. But eventually it will not work. When the time comes, it is for you to do:

Optimize MySQL table when query is slow


Don't run "analyze table" while your query is not slow. Or else sometimes it simply makes your query slow. You check it by running "explain your query" to get the idea. Sometimes you need to run "analyze table" several times to get the result you want. So don't give up by trying once only. And sometimes you need to switch between "analyze this table" and "analyze this another relative table" several times to get the result.

For MySQL 5.6, hopefully we could reduce this problem by using


Persistent Optimizer Statistics for InnoDB Tables

Plan stability is a desirable goal for your biggest and most important queries. InnoDB has always computed statistics for each InnoDB table to help the optimizer find the most efficient query execution plan. Now you can make these statistics persistent, so that the index usage and join order for a particular query is less likely to change.

This feature is on by default, enabled by the configuration option innodb_stats_persistent.

You control how much sampling is done to collect the statistics by setting the configuration options innodb_stats_persistent_sample_pages and innodb_stats_transient_sample_pages.

The configuration option innodb_stats_auto_recalc determines whether the statistics are calculated automatically whenever a table undergoes substantial changes (to more than 10% of the rows). If that setting is disabled, ensure the accuracy of optimizer statistics by issuing the ANALYZE TABLE statement for each applicable table after creating an index or making substantial changes to indexed columns. You might run this statement in your setup scripts after representative data has been loaded into the table, and run it periodically after DML operations significantly change the contents of indexed columns, or on a schedule at times of low activity.

Caution
To ensure statistics are gathered when a new index is created, either enable the innodb_stats_auto_recalc option, or run ANALYZE TABLE after creating each new index when the persistent statistics mode is enabled.

You can also set innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_transient_sample_pages options at the session level before creating a table, or use the STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses on the CREATE TABLE and ALTER TABLE statements, to override the system-wide setting and configure persistent statistics for individual tables.

Formerly, these statistics were cleared on each server restart and after some other operations, and recomputed when the table was next accessed. The statistics are computed using a random sampling technique that could produce different estimates the next time, leading to different choices in the execution plan and thus variations in query performance.

To revert to the previous method of collecting statistics that are periodically erased, run the command ALTER TABLE tbl_name STATS_PERSISTENT=0.

The persistent statistics feature relies on the internally managed tables in the mysql database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures.

The innodb_table_stats and innodb_index_stats tables both include a last_update column showing when index statistics were last updated, as shown in the following example:

mysql> select * from INNODB_TABLE_STATS \G
*************************** 1. row ***************************
           database_name: sakila
              table_name: actor
             last_update: 2014-05-28 16:16:44
                  n_rows: 200
    clustered_index_size: 1
sum_of_other_index_sizes: 1
...
mysql> select * from INNODB_INDEX_STATS \G
*************************** 1. row ***************************
   database_name: sakila
      table_name: actor
      index_name: PRIMARY
     last_update: 2014-05-28 16:16:44
       stat_name: n_diff_pfx01
      stat_value: 200
     sample_size: 1
     ...
If you manually update the statistics in the tables during troubleshooting or tuning, issue the command FLUSH TABLE tbl_name to make MySQL reload the updated statistics.