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)

No comments yet, be the first.

Leave a Reply