AWS RDS mysql 5.7 Current Query Cache is by default enabled on Aurora mysql. (16GB query cache size, on 300GB of RAM). Am facing some deadlocks from Hangfire and it doesn't show anything to having Query Cache locks. There are 2 blog posts that seem contradictory on the Query Cache feature. https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/ https://aws.amazon.com/blogs/database/planning-and-optimizing-amazon-aurora-with-mysql-compatibility-for-consolidated-workloads/ One says enable Query Cache, the other says disable Query Cache. What is the official AWS stance on Query Cache? Is it better to just disable Query Cache in general RDS or not?

I have some confusion about how the Innodb buffer pool works. Let's say I have table with 15 records, if I execute: `select ... between 1 to 10` then that result will cached in buffer(?). My question is: if i execute `select .... between 1 to 15` next time, then how it will fetch the records. Is it select 10 records from the buffer and 5 records from disk?

One of our customers has an online-shop running xt-commerce 4.2. This version of the e-commerce-software has a very expensive query for the category-tree. The query adds about 3 seconds delay and is executed on every page request. Since the software is protected by IonCube I have no possibility to change the query made by the shop software. As I found out, the query is not satisfied from the built-in MySQL-Query-Cache. The contents of the resultset do not vary very often and could be cached without problems. What are the possibilities to cache this one specific query? Idea 1: Increase RAM for MySQL so that all tables and indexes fit in RAM. - result: Queries duration dropped to "only" 2.5 seconds. Unsatisfactory. Idea 2: Find out why the query is not satisfied from the query-cache and change MySQL-Settings to cache it. - result: Not found settings that change the behaviour (until now). For reference: here is the query. In the slow-log it is the same query every time (no changing typos). ``` SELECT COUNT(parent.categories_id) AS level, c.*, cd.*, su.*, cl.link_url, group_permission.*, shop.* FROM xt_categories AS c CROSS JOIN xt_categories AS parent LEFT JOIN xt_categories_description cd ON c.categories_id = cd.categories_id AND cd.categories_store_id = '1' LEFT JOIN xt_seo_url su ON (c.categories_id = su.link_id AND su.link_type = '2' AND su.store_id = '1') LEFT JOIN xt_categories_custom_link_url cl ON (cl.categories_id = c.categories_id AND cl.store_id = '1') LEFT JOIN xt_categories_permission group_permission ON (group_permission.pid = c.categories_id AND group_permission.pgroup = 'group_permission_1') LEFT JOIN xt_categories_permission shop ON (shop.pid = c.categories_id AND shop.pgroup = 'shop_1') WHERE c.categories_status = '1' AND c.categories_left BETWEEN parent.categories_left AND

Pretty straightforward question: Let's say I have a specific query that I know is regularly used, can I tell MySQL to always cache it "automatically" ... i.e. once the cached query results are flushed the `mysqld` immediately runs that query again to have the cached data ready for the next user? Or the other way round: can I tell MySQL to not cache specific queries? What about other caching options, not necessarily related to queries? I searched through the web on this but can't really find anything related.

We have a statement based replication server that has been experiencing slowdowns and during the event `SHOW FULL PROCESSLIST` showed the replication query stuck on `"Waiting for query cache lock"` which was surprising because the server `query_cache_size` is set to `0`. Profiling the offending query does show this step in every update to the table. Is this typical to see in the profile for an update even if the `query_cache_size` is `0`? Is this really just the check for query caching and not the query waiting to obtain a true lock?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the `query_cache_type` is defaulted to off start from 5.6. We enabled it again and see the loading decrease, why this value is being disabled by default start from MySQL 5.6? I cannot see the problem in enabled it.

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 | +--------------------------------

The query cache hit value is not changing in my database only for some queries.If I write ``` select * from drugs_info limit 1000; ``` the query cache hit has changed, but if I execute the following queries for two to three times the query cache hit is not changing. The queries as follows: ``` select * from drugs_info limit 10000; select * from drugs_info limit 15000; ``` like this. I don't know what is the problem. The table, which contains 64500 rows: ``` CREATE TABLE drugs_info ( did int(11) NOT NULL AUTO_INCREMENT, brand_name varchar(150) DEFAULT NULL, generic varchar(500) DEFAULT NULL, tradename varchar(150) DEFAULT NULL, manfactured varchar(100) DEFAULT NULL, unit varchar(300) DEFAULT NULL, type varchar(50) DEFAULT NULL, quantity varchar(50) DEFAULT NULL, price float DEFAULT NULL, PRIMARY KEY (did), KEY id1 (brand_name,generic) ) ENGINE=InnoDB AUTO_INCREMENT=64379 DEFAULT CHARSET=latin1; ``` and the query: ``` select * from drugs_info; ``` and my settings are ``` mysql> show status like 'qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 8958376 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ ``` Why is this?

I just started dabbling with the query cache for our CMS. Can anyone tell me (or at least give a good guess) why I get a lot of `Qcache_lowmem_prunes` when more than half of `Qcache_free_memory` is free? ``` query_cache_size=512M query_cache_limit=1M ``` This is how it looks after about 12 hours ``` show status like '%qcach%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 10338 | | Qcache_free_memory | 297348320 | | Qcache_hits | 10254104 | | Qcache_inserts | 6072945 | | Qcache_lowmem_prunes | 725279 | | Qcache_not_cached | 2237603 | | Qcache_queries_in_cache | 48119 | | Qcache_total_blocks | 111346 | +-------------------------+-----------+ ``` This is how it looked after `flush query cache`; ``` show status like '%qcach%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 443559256 | | Qcache_hits | 10307015 | | Qcache_inserts | 6115890 | | Qcache_lowmem_prunes | 725279 | | Qcache_not_cached | 2249405 | | Qcache_queries_in_cache | 26455 | | Qcache_total_blocks | 54490 | +-------------------------+-----------+ ```