Home > database > 注意table_open_cache过小也会导致性能问题

注意table_open_cache过小也会导致性能问题

December 23rd, 2016

周一的时候有一个客户反馈自从上次rds重启后,慢查询特别多,有大量响应时间在1~3秒的请求,后端的工程师介入调查,发现随便建一个最简单的表,插入数据都需要300ms。一开始的时候怀疑可能是网络延迟导致的,客户测试了从ecs到rds的网络延迟,测试结果网络延时不到1ms,那问题到底出现在那里?在MySQL中可以使用profile去查看SQL的执行时间主要消耗在哪里,所以我们来看一下profile:

mysql> show profiles;
+———-+————+—————–+
| Query_ID | Duration   | Query           |
+———-+————+—————–+
|        1 | 0.09211525 | select * from d |
|        2 | 0.03659925 | select * from d |
|        3 | 0.22665400 | select * from d |
|        4 | 0.11063350 | select * from d |
|        5 | 0.06929725 | select * from d |
|        6 | 0.09054975 | select * from d |
|        7 | 0.15971375 | select * from d |
|        8 | 0.12960625 | select * from d |
|        9 | 0.22713975 | select * from d |
|       10 | 0.00124025 | select * from d |
+———-+————+—————–+

mysql> show profile cpu for query 4;
+———————-+———-+———-+————+
| Status               | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting             | 0.000198 | 0.001000 |   0.000000 |
| checking permissions | 0.000053 | 0.000000 |   0.000000 |
| Opening tables       | 0.000454 | 0.000999 |   0.001000 |
| init                 | 0.000059 | 0.000000 |   0.000999 |
| System lock          | 0.000055 | 0.000000 |   0.000000 |
| optimizing           | 0.000053 | 0.000000 |   0.000000 |
| statistics           | 0.000056 | 0.000000 |   0.000000 |
| preparing            | 0.000056 | 0.000000 |   0.000000 |
| executing            | 0.000052 | 0.001000 |   0.000000 |
| Sending data         | 0.000072 | 0.000000 |   0.000000 |
| end                  | 0.000053 | 0.000000 |   0.000000 |
| query end            | 0.000056 | 0.000000 |   0.000000 |
| closing tables       | 0.000056 | 0.000000 |   0.000000 |
| freeing items        | 0.000076 | 0.000000 |   0.000000 |
| cleaning up          | 0.000056 | 0.000000 |   0.000000 |
+———————-+———-+———-+————+

mysql> show profile cpu for query 5;
+———————-+———-+———-+————+
| Status               | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting             | 0.000201 | 0.000000 |   0.000000 |
| checking permissions | 0.000054 | 0.000000 |   0.000000 |
| Opening tables       | 0.127694 | 0.116982 |   0.009999 |
| init                 | 0.000101 | 0.000000 |   0.000999 |
| System lock          | 0.000054 | 0.000000 |   0.000000 |
| optimizing           | 0.000052 | 0.000000 |   0.000000 |
| statistics           | 0.000056 | 0.000000 |   0.000000 |
| preparing            | 0.000056 | 0.001000 |   0.000000 |
| executing            | 0.000052 | 0.000000 |   0.000000 |
| Sending data         | 0.000072 | 0.000000 |   0.000000 |
| end                  | 0.000053 | 0.000000 |   0.000000 |
| query end            | 0.000053 | 0.000000 |   0.000000 |
| closing tables       | 0.009045 | 0.008998 |   0.002000 |
| freeing items        | 0.000076 | 0.001000 |   0.000000 |
| cleaning up          | 0.000058 | 0.000000 |   0.000000 |
+———————-+———-+———-+————+
15 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu for query 6;
+———————-+———-+———-+————+
| Status               | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting             | 0.000197 | 0.001000 |   0.000000 |
| checking permissions | 0.000053 | 0.000000 |   0.000000 |
| Opening tables       | 0.013475 | 0.014998 |   0.000000 |
| init                 | 0.000060 | 0.000000 |   0.000000 |
| System lock          | 0.000067 | 0.000000 |   0.000000 |
| optimizing           | 0.000059 | 0.000000 |   0.002000 |
| statistics           | 0.000061 | 0.000000 |   0.000000 |
| preparing            | 0.000059 | 0.000000 |   0.000000 |
| executing            | 0.000053 | 0.000000 |   0.000000 |
| Sending data         | 0.000073 | 0.000000 |   0.000000 |
| end                  | 0.000054 | 0.000000 |   0.000000 |
| query end            | 0.000055 | 0.000000 |   0.000000 |
| closing tables       | 0.000967 | 0.000000 |   0.001000 |
| freeing items        | 0.000077 | 0.000000 |   0.000000 |
| cleaning up          | 0.000053 | 0.001000 |   0.000000 |
+———————-+———-+———-+————+
15 rows in set, 1 warning (0.00 sec)

通过这个profile可以看到执行时间主要花费在Opening tables,这个时候问题就比较清楚了,我们看一下table_open_cache这个参数的值是否较小,结果这个RDS的table_open_cache只有100,而这个RDS却创建了上万张的表,进而导致了每次访问新的表的时候不得不重新打开,所以只需把table_open_cache调大即可解决目前的问题:table_open_cache1tips:

通常在设置table_open_cache参数的时候,在业务的高峰时期,检查open_Tables的值,如果open_Tables的值与table_open_cache的值相等,并且opened_tales的值在不断的增加,这个时候就需要对table_open_cache的值增加了,这个时候线程的状态:Opening tables。目前rds table_open_cache的初始值已经调大至2000。

 

Categories: database Tags:
Comments are closed.