萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql巧用query cache解決連接超時

mysql巧用query cache解決連接超時

有朋友反應系統日志報mysql連接超時,sql執行非常慢,我們可以用mysql的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的故障:


copyright © 萬盛學電腦網 all rights reserved