SET STATEMENT queries are put into query cache but never hit it

Bug #1387273 reported by Elena Stepanova
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Invalid
Undecided
Unassigned
5.5
Invalid
Undecided
Unassigned
5.6
Triaged
Medium
Unassigned
5.7
Triaged
Medium
Unassigned

Bug Description

# Start server with query-cache-type = 1

MySQL [test]> create table t1 (i int);
Query OK, 0 rows affected (0.96 sec)

MySQL [test]> select @@query_cache_size, @@query_cache_type;
+--------------------+--------------------+
| @@query_cache_size | @@query_cache_type |
+--------------------+--------------------+
| 1048576 | ON |
+--------------------+--------------------+
1 row in set (0.00 sec)

MySQL [test]> # Make sure that query cache works

MySQL [test]> show status like 'QCache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031200 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL [test]> select * from t1;
Empty set (0.00 sec)

MySQL [test]> show status like 'QCache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1029664 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL [test]> select * from t1;
Empty set (0.00 sec)

MySQL [test]> show status like 'QCache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1029664 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL [test]> # Query cache works

MySQL [test]> set statement lock_wait_timeout=1 for select * from t1;
Empty set (0.00 sec)

MySQL [test]> show status like 'QCache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1028640 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL [test]> # The query was put into the query cache

MySQL [test]> set statement lock_wait_timeout=1 for select * from t1;
Empty set (0.00 sec)

MySQL [test]> show status like 'QCache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1028640 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 6 |
+-------------------------+---------+
8 rows in set (0.00 sec)

MySQL [test]> # ... but didn't hit it.

MySQL [test]> # Why put the query into cache if can't use it?

tags: added: set-statement
Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :
Download full text (4.0 KiB)

Able to reproduce the same with PS 5.6.21

nilnandan@Dell-XPS:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.21-69.0 Percona Server (GPL), Release 69.0, Revision 675

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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>
mysql> select @@query_cache_size, @@query_cache_type;
+--------------------+--------------------+
| @@query_cache_size | @@query_cache_type |
+--------------------+--------------------+
| 16777216 | ON |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t1 (i int);
Query OK, 0 rows affected (0.04 sec)

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16759648 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 70 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.01 sec)

mysql>
mysql> select * from t1;
Empty set (0.00 sec)

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16758112 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 70 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16758112 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 70 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> set statement lock_wait_timeout=1 for select * from t1;
Empty set (0.00 sec)

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 16757088 |
| Qcache_hits | 1 ...

Read more...

Changed in percona-server:
status: New → Confirmed
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1573

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.