巧用query cache

   收到一用户反馈其应用日志中狂报错误,获取连接超时:

同时应用报错超出了数据库的最大连接数:max connections:

这种情况很有可能是有慢sql占用了连接池中的连接没有释放,导致后续进来的请求迟迟获取不到连接池中的连接,导致请求报错,登录数据库排查发现如下sql出现执行非常的慢:

   mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f’;

   Empty set (1.32 sec)

   一眼可以看出在nick上使用了md5函数,导致user表中的索引不能使用,而全表扫描:

   mysql> explain select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?

   +—-+————-+——-+——+—————+——+———+——+——-+————-+

   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

   +—-+————-+——-+——+—————+——+———+——+——-+————-+

   | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 256608 | Using where |

   +—-+————-+——-+——+—————+——+———+——+——-+————-+

   1 row in set (0.00 sec)

   通常情况下需要用户修改应用,将应用中的md5函数去掉,但是修改业务的方法还需要一段时间,不能够立刻是业务恢复,所以看看数据库还有没有其他的方法,灵机一动,由于该应用的场景是读多写非常少的应用,想到了query cache:

   mysql> show variables like ‘%query%’;

   +——————————+——————————————-+

   | Variable_name | Value |

   +——————————+——————————————-+

   | ft_query_expansion_limit | 20 |

   | have_query_cache | YES |

   | long_query_time | 1.000000 |

   | query_alloc_block_size | 8192 |

   | query_cache_limit | 1048576 |

   | query_cache_min_res_unit | 4096 |

   | query_cache_size | 0 |

   | query_cache_type | ON |

   | query_cache_wlock_invalidate | OFF |

   | query_prealloc_size | 8192 |

   | slow_query_log | ON |

   | slow_query_log_file | /home/mysql/data3003/mysql/slow_query.log |

   +——————————+——————————————-+

   12 rows in set (0.00 sec)

   mysql> set global query_cache_size=1024*1024*32;

   Query OK, 0 rows affected (0.02 sec)

   mysql> Ctrl-C — exit!

   Aborted

   root@xxxx ~

   # my 3003

   Entry Port ==== 3003

   Welcome to the MySQL monitor. Commands end with ; or \g.

   Your MySQL connection id is 4601147

   Server version: 5.1.61-Alibaba-3930-log Source distribution

   Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

   Oracle is a registered trademark of Oracle Corporation and/or its

   affiliates. Other names may be trademarks of their respective

   owners.

   Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

   mysql> use cuxiaowang_db

   Database changed

   mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?’;

   Empty set (1.32 sec)

   mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?

   Empty set (0.00 sec)

   可以看到查询能够很快的返回,应用立刻恢复正常。

   PS:query cache的打开是有一定场景的,由于query cache并不适合更新,插入,删除非常大的应用,所以打开query cache一定要慎重,笔者曾经看到由于其数据库有大量的插入和更新的数据库打开query cache而导致整个数据库都在等待query cache的故障:

   

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>