Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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! –  Question Overflow Mar 2 at 15:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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