6

I know that SQL query will use query cache to receive data instead of reprocess all of the data. Here the question I would like to ask,

I working with a server of database and I'm one of the developer that working on it and I need to do performance testing on queries that i handling

If I clear the query cache example using FLUSH QUERY CACHE; or RESET QUERY CACHE;,

will it affect others developer or it only clears away my local query cache?

If it will affect others, is there any way to clear locally or allow my query won't use the query cache for testing

Improve this question
1
  • I put SQL Server back and added SQL since this question is now not only MySQL related. Commented Dec 16, 2015 at 8:57

3 Answers 3

Reset to default
2

Two clarifications to begin with:

  • MySQL query cache is a server-side feature, there's no such thing as "local cache". You're probably confused by the LOCAL keyword in FLUSH command. As docs explain it's just an alias for NO_WRITE_TO_BINLOG (thus it's related to replication and "local" means "this server").

  • MySQL will only return cached data if you've enabled the feature and either made it default or opted-in with the SQL_CACHE hint. In my experience, most servers do not have it by default.

Let's now answer your question. At The MySQL Query Cache we can read:

The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

Which makes sense: a cache that cannot reuse stored data is not as useful.

I don't know what you want to test exactly. Your data should always be fresh:

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

However you might want to get an idea of how long the query takes to run. You can always opt out with the SQL_NO_CACHE keyword:

The server does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result.

Just take into account that a query that runs for the second time might run faster even without cache because part of the data segments might be already loaded into RAM.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try using the SQL_NO_CACHE option in your query.This will stop MySQL caching the results

SELECT SQL_NO_CACHE * FROM TABLE
Improve this answer

3 Comments

Is it work only with SELECT * ? because when I use it like SELECT SQL_NO_CACHE [value] FROM TABLE, It stated invalid column name for SQL_NO_CACHE
read more dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html @Dean
@Dean This is a MySQL extension; the syntax won't work on other DBMS.
1

With SQL Server for cached data, you can use DBCC DROPCLEANBUFFERS and force a manul CHECKPOINT.

However it works at the Server (instance) level:

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

edited*

SQL Query buffer cache is global but not local. If the buffer or query cache is drop, it drops it globally, and will affect all user using the database server.

Improve this answer

6 Comments

So... 'DBCC DROPCLEANBUFFERS' is clean up the buffer cache for entire server?.. and is there any way to clear it locally but not entire server?
CHECKPOINT records everything from memory to disk and then DROPCLEANBUFFERS clears the buffer (since everything has been recorded). But this is for SQL Server (before you mention it is MySQL). It clears the entire cache on the server. The cache is global to the SQL Server instance, for everyone and is not linked to a specific user.
ok.. thanks.. It might same as MySQL which cache are globally saved. not quite sure yet.. will do few more research if yes i tag it as solution for both MySQL and SQL Server
Most likely. This is usually the goal behind a cache
I know this isn't the answerers's fault but MySQL != SQL Server != SQL.
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.