Query Cache not being used even when it has memory

[Philip Gladstone]

I have a small Cloud SQL instance and I was looking at the performance of the system.

I wondered why certain repetitive queries were not being cached by the mysql query cache. Initially, I found that I had to set the query cache size to  non-zero amount. However, after that, every query shows up in the Qcache_not_cached bucket. It appears as though there is no caching being performed. 

Is there another setting that I need to tweak to get this to work?

Philip

[Ken Ashcraft]

Please provide exact steps to reproduce the problem and we'll look into it.

Thanks,

Ken

[Philip Gladstone]

This is done from the command line talking to a D1 per-use plan instance. I think that the warning is because the 1000000 is not a multiple of 4096 and it gets rounded down to 999424.

If this was made to work, I don't know how to make these settings persistent. Certainly, with a per-use plan instance, these get reset when the instance is restarted. Yes, when we move into production, this will be an always on instance, but even then, I imagine that there are circumstances when it will get restarted.

Philip

./google_sql.sh xxxxxxx.com:yyyyyyy:zzzzzz

sql> SET GLOBAL query_cache_size = 1000000;

0 row(s) affected, 1 warning(s).

sql> show warnings

-> ;

+---------+------+-------------------------------------------------------+

| Level   | Code | Message                                               |

+---------+------+-------------------------------------------------------+

| Warning | 1292 | Truncated incorrect query_cache_size value: '1000000' |

+---------+------+-------------------------------------------------------+

1 row in set (0.20 sec)

sql> show variables like 'query_cache%';

+------------------------------+---------+

| Variable_name                | Value   |

+------------------------------+---------+

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 999424  |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

+------------------------------+---------+

5 rows in set (0.12 sec)

sql> create database test;

1 row(s) affected.

sql> use test;

0 row(s) affected.

sql> create table foo (id int);

0 row(s) affected.

sql> insert into foo values (1),(2);

2 row(s) affected.

sql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Variable_name           | Value  |

+-------------------------+--------+

| Qcache_free_blocks      | 1      |

| Qcache_free_memory      | 982224 |

| Qcache_hits             | 0      |

| Qcache_inserts          | 0      |

| Qcache_lowmem_prunes    | 0      |

| Qcache_not_cached       | 0      |

| Qcache_queries_in_cache | 0      |

| Qcache_total_blocks     | 1      |

+-------------------------+--------+

8 rows in set (0.22 sec)

sql> select * from foo;             

+-------------+

| id          |

+-------------+

|           1 |

|           2 |

+-------------+

2 rows in set (0.16 sec)

sql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Variable_name           | Value  |

+-------------------------+--------+

| Qcache_free_blocks      | 1      |

| Qcache_free_memory      | 982224 |

| Qcache_hits             | 0      |

| Qcache_inserts          | 0      |

| Qcache_lowmem_prunes    | 0      |

| Qcache_not_cached       | 1      |

| Qcache_queries_in_cache | 0      |

| Qcache_total_blocks     | 1      |

+-------------------------+--------+

8 rows in set (0.09 sec)

sql> select * from foo;         

+-------------+

| id          |

+-------------+

|           1 |

|           2 |

+-------------+

2 rows in set (0.11 sec)

sql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Variable_name           | Value  |

+-------------------------+--------+

| Qcache_free_blocks      | 1      |

| Qcache_free_memory      | 982224 |

| 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.11 sec)

I would expect that the qcache_hits would be non-zero and the qcache_inserts would be non-zero.

By comparison, from a mysql instance:

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 153      |

| Qcache_free_memory      | 18662728 |

| Qcache_hits             | 14583939 |

| Qcache_inserts          | 75834540 |

| Qcache_lowmem_prunes    | 5858481  |

| Qcache_not_cached       | 34108736 |

| Qcache_queries_in_cache | 389      |

| Qcache_total_blocks     | 1016     |

+-------------------------+----------+

8 rows in set (0.00 sec)

Philip

[Ken Ashcraft]

Thanks for the detailed report.  We're looking into it.

Ken

[Rob Clevenger]

Thanks Phillip.  

I can reproduce the issue here, not immediately sure of the fix.  Do you mind if I paste your steps to reproduce into a bug in our public bug tracker so you can track progress on this?

Rob

[Philip Gladstone]

Be my guest!

Philip