Mysql uses query cache to solve connection timeout

Mad report Error in Application log, get connection timeout:

Applying an error at the same time exceeds the maximum number of connections for the database: Max connections:

This situation is most likely to be slow SQL takes up the connection in the connection pool is not released, causing subsequent incoming requests to delay the connection to the connection pool, resulting in a request for an error, logon database troubleshooting found that the following SQL appears to perform very slow:

Mysql> SELECT * from user where MD5 (nick) = ' 3f5950f59ddf2a0d14a44166040e348f ';
Empty Set (1.32 sec)

At one glance, the MD5 function was used on Nick, causing the index in the user table to be not available, and the full table scan:

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)

Users are usually required to modify the application, the application of the MD5 function is removed, but the method of modifying the business will take some time, can not be immediately a business recovery, so look at the database there are no other methods, brainwave, because the application of the scene is read more write very little application, thought of 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 |
+ —————————— + ——————————————-+
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), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark the Oracle Corporation and/or its
Affiliates. Names may 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)

You can see that the query returns quickly, and the application is immediately restored to normal.

Ps:query Cache Open is a certain scene, because query cache is not suitable for updates, inserts, delete a very large application, so open query cache must be cautious, I have seen due to its database has a large number of inserts and updates of the database open query Cache results in the entire database waiting for query cache to fail: