Recently we were puzzled by question how query_cache works with column level privileges. The question was appeared as we discovered function query_cache_send_result_to_client is called before real parsing of query, so at the moment of execution the query_cache is not able to know which columns are accessed.
Looking into source code I found out that in the case when table has column level privileges – function query_cache_send_result_to_client just stopped and returns to usual execution. So initial assumtion was that query against table with defined column privilege is just not cached, though it is not reflected in documentation.
It was worth to test it experimentally and the reality turned out more interesting.
under root:
1 2 3 | create table xp.test1 (id int, name varchar(23)); insert into xp.test1 values (5, 'testit'); GRANT SELECT (id) ON table xp.test1 to 'vadim'@'localhost'; |
and under user ‘vadim’
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Before query execution: mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67091176 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_queries_in_cache | 0 | +-------------------------+----------+ 8 rows in set (0.01 sec) Query: mysql> select id from xp.test1 where id > 1; +------+ | id | +------+ | 5 | +------+ 1 row in set (0.00 sec) After query execution: mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67089640 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_queries_in_cache | 1 | +-------------------------+----------+ |
As you see the query was inserted into query cache with allocated memory for.
Let’s try second execution:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 8 rows in set (0.01 sec) mysql> select id from xp.test1 where id > 1; +------+ | id | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67089640 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_queries_in_cache | 1 | +-------------------------+----------+ 8 rows in set (0.02 sec) |
So query hits was not increased that proves the initial assumption – the query that includes tables with defined column level privileges can’t be cached – but actually even more – the query is stored in query cache area and eats memory, even the result cannot be used later.
You know, profiling would answer it definitively perhaps easier:
mysql> SELECT id FROM xp.test1 WHERE id> 1;
+——+
| id |
+——+
| 5 |
+——+
1 row in set (0.00 sec)
mysql> show profile;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| (initialization) | 0.000005 |
| checking query cache for query | 0.000049 |
| checking permissions | 0.00001 |
| Opening tables | 0.000011 |
| System lock | 0.000007 |
| Table lock | 0.000017 |
| init | 0.000021 |
| optimizing | 0.000009 |
| statistics | 0.000028 |
| preparing | 0.000013 |
| executing | 0.000005 |
| Sending data | 0.000036 |
| end | 0.000008 |
| query end | 0.000005 |
| storing result in query cache | 0.000005 |
| freeing items | 0.000012 |
| closing tables | 0.000007 |
| logging slow query | 0.000224 |
+——————————–+———-+
18 rows in set (0.00 sec)
mysql> SELECT id FROM xp.test1 WHERE id> 1;
+——+
| id |
+——+
| 5 |
+——+
1 row in set (0.00 sec)
mysql> show profile;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| (initialization) | 0.000005 |
| checking query cache for query | 0.000008 |
| checking privileges on cached | 0.000006 |
| checking permissions | 0.000051 |
| Opening tables | 0.000012 |
| System lock | 0.000006 |
| Table lock | 0.00001 |
| init | 0.000021 |
| optimizing | 0.00001 |
| statistics | 0.000028 |
| preparing | 0.000012 |
| executing | 0.000005 |
| Sending data | 0.000024 |
| end | 0.000007 |
| query end | 0.000004 |
| freeing items | 0.000012 |
| closing tables | 0.000007 |
| logging slow query | 0.000004 |
+——————————–+———-+
18 rows in set (0.00 sec)
Thanks Jeremy,
We’re just trying to keep things usable for people which are still stuck with MySQL Enterprise version.
Though I hope you’ve seen my post regarding potentially high overhead of SHOW PROFILE patch even when it is disabled
if there is huge query volume.
Vadim,
Please, report a bug about this waste of memory in the query cache.
Valeriy,
Done
http://bugs.mysql.com/30269