5
Sep 12
Speed up slow queries on information_schema table
Set innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema.
mysql> select count(*),sum(data_length) from information_schema.tables; +----------+------------------+ | count(*) | sum(data_length) | +----------+------------------+ | 5581 | 3051148872493 | +----------+------------------+ 1 row in set (3 min 21.82 sec) mysql> show variables like '%metadata' +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_on_metadata | ON | +--------------------------+-------+ mysql> set global innodb_stats_on_metadata=0; mysql> show variables like '%metadata' +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_on_metadata | OFF | +--------------------------+-------+ mysql> select count(*),sum(data_length) from information_schema.tables; +----------+------------------+ | count(*) | sum(data_length) | +----------+------------------+ | 5581 | 3051148872493 | +----------+------------------+ 1 row in set (0.49 sec)
Leave a Reply
You must be logged in to post a comment.