November 24, 2014

Query_cache and column level privileges

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:

and under user ‘vadim’

As you see the query was inserted into query cache with allocated memory for.

Let’s try second execution:

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.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. 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)

  2. peter says:

    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.

  3. Vadim,

    Please, report a bug about this waste of memory in the query cache.

  4. Vadim says:

Speak Your Mind

*