We are seeing a problem with our MariaDB setup where our queries executed as prepared statements are not being cached, however, queries executed locally through mysql command (i.e. text protocol) do get cached.
I have read MySQL query cache is enabled but not being used but it didn't really help.
I have read on some outdated blog post from 2007 and earlier, that prepared statements are not cached at all, due to not being implemented. However, the documentation of both MariaDB and MySQL mention it should work.
Here's my configuration:
MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
MariaDB [(none)]> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 268417416 |
| Qcache_hits | 0 |
| Qcache_inserts | 6 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3797 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
Additionally, this is the number of queries executed this far, which is much higher than Qcache_not_cached. I would assume they would be at least comparable, given a 0 cache usage.
MariaDB [(none)]> SHOW STATUS LIKE '%queries%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Empty_queries | 0 |
| Qcache_queries_in_cache | 0 |
| Queries | 2306776 |
| Slow_queries | 0 |
+-------------------------+---------+
I have turned on cache at the start of the [mysqld] section in my.cnf which also didn't help much.
Any other ideas?
