Questions tagged [query-cache]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
13 views

Is Data Warehousing the Right Solution [migrated]

I have a vendor system that I can query to get reports or raw data. The data returned are based on internal calculations, I can get also get data based on dates. I have some reports that tend to ...
0
votes
0answers
75 views

Subquery caching in join

I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell ...
1
vote
0answers
51 views

Finding which queries used an index

I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server. I ...
2
votes
2answers
40 views

DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

Kimberly Tripp's blog post here made me think that running DBCC FREESYSTEMCACHE('SQL Plans') clears just single-used ad-hoc and prepared query plans. But that seems incorrect. It clears all. Which is ...
0
votes
0answers
65 views

NHibernate second level cache collection hydration takes too long

I have enabled query caching and entity caching in NHibernate to store large collections of objects, needed for repetitive operations. While I have minimized the number of hits to db, the performance ...
7
votes
3answers
2k views

Why mysql remove query cache in 8.0 version?

Why mysql remove query cache in 8.0 version ?
-1
votes
3answers
101 views

query_cache_size is reset periodically without my will

I have a website that receives a lot of searches, so I need a cache. The VPS is under the plesk license and the OS is Centos. I have used the following command to implement a cache in mysql several ...
1
vote
2answers
4k views

Is it possible to delete cache in Mongo?

My case is that I run several queries in Mongo and measure the response time. I know that Mongo uses Memory Mapped files and cache some data in RAM. If I create an index and run a query the execution ...
3
votes
1answer
37 views

high cached_page_count - when to start to worry and what can I do about it?

Below are my top 10 cached page counts. We're having query performance issues with the top 2 tables; the queries never change but the run times are very volatile. I cant seem to work out were the ...
0
votes
1answer
144 views

Am I clearing the query cache correctly?

I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working. I ran a query the first time and it took about 3.5 ...
1
vote
3answers
553 views

I ran MySQLTuner and wondered if anyone would go over the results with me, specifically query cache and temporary tables

Here are the results of MySQLTuner: [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5....
-1
votes
1answer
44 views

SQL Server 2012 cache system? [closed]

I have an application that uses SQL Server 2012. Even if I change a value directly in the database (not via an application), it takes time until the application sees those changes, even if the ...
1
vote
0answers
167 views

why the queries run by a specific user are slow?

Here you can see how to save the contents of the default trace into a table. I have been saving the contents of the default trance into a table in my DBA database. The table is called [DBA].[dbo].[...
1
vote
1answer
164 views

Query Cache utilisation ~50% Qcache_lowmem_prunes still high

I ran two tests, each running for about a week Test 1 Query Cache Settings query_cache_limit: 50K query_cache_size: 128M query_cache_min_res_unit: 1K Results Qcache_free_blocks: 6,004 ...
2
votes
1answer
1k views

Postgres query cache analyzing table basis?

The PostgreSQL application I'm currently on is solely based on functions. Every request from the front end maps to a PostgreSQL function call. Many of these functions call other functions. Like: web ...
0
votes
1answer
303 views

Why query execute faster after first execution

I have mysql (5.7) innodb table, and run a select query and using an Id foreign key. for the first query on this Id foreign key condition, the query took a lot of time to execute, and If I run another ...
2
votes
2answers
103 views

How to optimize constant querying for a sum of many rows?

I'm making an app that is supposed to view election results (yep, homework assignment). For each of the 24681 wards and each of the 12 candidates the number of votes the said candidate has received in ...
0
votes
3answers
254 views

MySQL - Why specific query doesn't use cache?

My customer is running MySQL 5.7.16 on Ubuntu16.04 as backend for Wordpress site. A lot of heavy, bad written SQLs. I was able to improve situation significant by enabling MySQL cache, slow query log ...
0
votes
1answer
23 views

pt-query-digest slowlog output format looses query cache hit information?

I'm using pt-query-digest (v2.2.20) slowlog output format to filter out queries based on their timestamp, to run further digests on the filtered slowlog : ptqd 11-21.slow.log --output slowlog --no-...
5
votes
2answers
2k views

How does MySQL/InnoDB caching work internally when you perform two similar, but not identical, select requests?

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 ...
0
votes
0answers
2k views

All queries waiting for query cache lock

I'm managing a e-commerce site which uses a popular online shopping cart software running on MySQL 5.6. Yesterday I noticed that SHOW PROCESSLIST reports that 990 of 1000 queries are waiting for a ...
2
votes
1answer
2k views

How to get the size of the result of a SQL query?

How do you get the size (in bytes) of the result of a SQL query, in MySQL 5.6? EXPLAIN will return the number of fetched rows, but not their size. My aim is to evaluate common queries to know a ...
2
votes
1answer
319 views

mariaDB not caching queries cached by mysql

I'm migrating my database (MySQL 5.1.73) to a new server, and I am trying to replace it with mariadb (5.5.5-10). Globally mariadb seems much faster, but there is a couple of queries that it refuses to ...
0
votes
1answer
73 views

MySQL: Do killed query results get cached?

If a query is executed from the MySQL command line, then KILLed it while it is running will the query and its partial results* be in the query cache and usable to subsequent runs of the identical ...
0
votes
1answer
249 views

Freezing Centos server

My dedicated server (CENTOS 6.4) freeze every two day and I have some trouble to found te origin. What I know : This is not hardware issue as tests were made about (RAM, CPU, Discs, ...). At the ...
3
votes
1answer
159 views

Cache query that is not stored in QueryCache

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 ...
1
vote
1answer
5k views

How to change query_cache_type to 2 in AWS RDS?

I want to change my AWS RDS parameter query_cache_type value to 2 , I have done it in parameter group dashboard as in the given image. I have also restarted my mySQL server with the changed parameter,...
0
votes
1answer
725 views

How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand. But how do i do it in sqlalchemy ? Is it possible ?
0
votes
2answers
594 views

How to set query_cache_type=2 on mac?

I want to enable query_cache_type and change its value to DEMAND. When I am doing it via terminal its giving this error: ERROR 1651 (HY000): Query cache is disabled; restart the server with ...
0
votes
1answer
301 views

Does innodb_data_reads include reads from the buffer pool?

We have set up some monitoring on many of our databases that serve many different applications. It seems that they all report back more writes than reads when in reality that is very unlikely. I ...
1
vote
1answer
50 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
1
vote
0answers
1k views

memcached instead of the MySQL Query cache

I have busy mysql database with too many user and over 200G database , I see in my mysql adviser following comment : Recommendation: You are using the MySQL Query cache with a fairly high traffic ...
0
votes
2answers
764 views

MySQL query cache with time based expiration

The database we use has a read and write intensive load. Some read queries are heavy. Unfortunately the Query Cache cannot be used as it is invalidated too quickly. We wanted to avoid the classic ...
1
vote
0answers
423 views

Do you need external caching for a Postgres database?

I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
3
votes
2answers
1k views

Can I tell MySQL what to cache?

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 ...
0
votes
1answer
130 views

Sybase IQ cache database result

I'm using a query that calculates some values on a table with about 11 millions rows. And I need to display the results in real time (on my site), but this calculations need about 1min to execute. The ...
6
votes
1answer
8k views

Query profiling shows “Waiting for query cache lock” but query_cache_size is 0

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 ...
1
vote
1answer
2k views

MySQL no query cache hits

My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB. mysql> SHOW ...
0
votes
1answer
1k views

Determine if queries have small or large results

In the MySQL documentation for Query cache, specifically on the topic of query_cache_min_res_unit "If you have a lot of queries with small results, the default block size may lead to memory ...
1
vote
1answer
273 views

How to cache query for re-use in following UNION of queries?

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB). This is the query I want to save: SELECT `event`.id, event.name FROM ...
0
votes
0answers
670 views

No query cache efficiency mairaDB

I'm totally new to database tuning, and even though I've done well so far, I'm stuck with my database cache query efficiency 0%. Any help with this would much appreciated I've tried increasing ...
0
votes
2answers
324 views

Query Cache Implementation Details

I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of ...
2
votes
2answers
4k views

Optimize MySQL query_cache_min_res_unit

My query cache settings are the following: mysql> SHOW GLOBAL STATUS LIKE 'Q%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ ...
28
votes
3answers
34k views

Why query_cache_type is disabled by default start from MySQL 5.6?

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 ...
0
votes
1answer
502 views

Can queries containing deterministic user defined functions be cached in mysql query cache?

According to the mysql 5.6 docs: A query also is not cached under these conditions: ... It refers to user-defined functions (UDFs) or stored functions. However, I have seen claims that if ...
0
votes
1answer
139 views

MySQL Query Cache - what type of queue is used?

I am wondering what type of queue is MySQL Query Cache using. When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some ...
1
vote
1answer
3k views

MariaDB not caching any queries

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%"; +---...
0
votes
1answer
285 views

mysql's query cache behaviour on LIMIT

Is it accurate to assume that MySQL's query cache gets checked on subsequent execution of queries in the form SELECT ... WHERE ... LIMIT x, y where the the only varying paramaters in the query are x ...
3
votes
1answer
2k views

query cache hit value is not changing in my database

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 ...
11
votes
1answer
11k views

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

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? ...