3

I tested several simple queries like such:

SELECT COUNT(*) FROM Users WHERE courses='a';

But none are being cached. The following are the cache related variables:

show variables like "%cache%";
+-------------------------------+----------------------+
| Variable_name                 | Value                |
+-------------------------------+----------------------+
| aria_pagecache_age_threshold  | 300                  |
| aria_pagecache_buffer_size    | 100000000            |
| aria_pagecache_division_limit | 100                  |
| binlog_cache_size             | 32768                |
| binlog_stmt_cache_size        | 32768                |
| have_query_cache              | YES                  |
| join_cache_level              | 2                    |
| key_cache_age_threshold       | 300                  |
| key_cache_block_size          | 1024                 |
| key_cache_division_limit      | 100                  |
| key_cache_segments            | 0                    |
| max_binlog_cache_size         | 18000000000000000000 |
| max_binlog_stmt_cache_size    | 18000000000000000000 |
| metadata_locks_cache_size     | 1024                 |
| query_cache_limit             | 1048576              |
| query_cache_min_res_unit      | 4096                 |
| query_cache_size              | 0                    |
| query_cache_strip_comments    | OFF                  |
| query_cache_type              | ON                   |
| query_cache_wlock_invalidate  | OFF                  |
| stored_program_cache          | 256                  |
| table_definition_cache        | 400                  |
| table_open_cache              | 400                  |
| thread_cache_size             | 0                    |
+-------------------------------+----------------------+

The cache related status shows:

show status like "%cache%";
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Aria_pagecache_blocks_not_flushed | 0     |
| Aria_pagecache_blocks_unused      | 15737 |
| Aria_pagecache_blocks_used        | 2     |
| Aria_pagecache_read_requests      | 78    |
| Aria_pagecache_reads              | 3     |
| Aria_pagecache_write_requests     | 6     |
| Aria_pagecache_writes             | 0     |
| Binlog_cache_disk_use             | 0     |
| Binlog_cache_use                  | 0     |
| Binlog_stmt_cache_disk_use        | 0     |
| Binlog_stmt_cache_use             | 0     |
| Com_assign_to_keycache            | 0     |
| Qcache_free_blocks                | 0     |
| Qcache_free_memory                | 0     |
| Qcache_hits                       | 0     |
| Qcache_inserts                    | 0     |
| Qcache_lowmem_prunes              | 0     |
| Qcache_not_cached                 | 0     |
| Qcache_queries_in_cache           | 0     |
| Qcache_total_blocks               | 0     |
| Ssl_callback_cache_hits           | 0     |
| Ssl_session_cache_hits            | 0     |
| Ssl_session_cache_misses          | 0     |
| Ssl_session_cache_mode            | NONE  |
| Ssl_session_cache_overflows       | 0     |
| Ssl_session_cache_size            | 0     |
| Ssl_session_cache_timeouts        | 0     |
| Ssl_used_session_cache_entries    | 0     |
| Subquery_cache_hit                | 0     |
| Subquery_cache_miss               | 0     |
| Threads_cached                    | 0     |
+-----------------------------------+-------+

I have read through the manual and checked that the following conditions are satisfied:

  • the query_cache_type server variable is set to 1, or ON
  • the following functions are not present in the query: BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME() DATABASE() ENCRYPT() (one parameter) FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() SLEEP() SYSDATE() UNIX_TIMESTAMP() (no parameters) USER() UUID() UUID_SHORT()
  • it is not of the form:

    SELECT SQL_NO_CACHE ... SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT ... FOR UPDATE SELECT * FROM ... WHERE autoincrement_column IS NULL SELECT ... LOCK IN SHARE MODE

  • it uses a non-TEMPORARY table
  • it does not generate a warning
  • it does not access a partitioned table
  • it does not access a table from INFORMATION_SCHEMA, mysql or the performance_schema database
  • it does not make use of user or local variables
  • it does not make use of stored functions
  • it does not make use of user-defined functions
  • it is not inside a transaction with the SERIALIZABLE isolation level

The only thing I am not sure is:

  • the user does not have a column-level privilege on any table in the query

I run SHOW FULL COLUMNS FROM Users; and all columns contain "select, insert, update". Even newly created tables contain those privileges. Can anyone explain what could be wrong?

1 Answer 1

5

You missed one thing.

query_cache_size              | 0       

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache, as does setting query_cache_type=0.

https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html

Although enabled, the query cache size is by default 0KB, which effectively disables the query cache. Enable it by setting to an amount > 40KB

https://mariadb.com/kb/en/query-cache/

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_size

1
  • You are correct. I vaguely remembered I saw a default of 8M when I was looking through the variable, which turns out to be wrong. Thanks! Mar 2, 2014 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.