SQL query cache

Question

I know that an SQL query will use the query cache to retrieve data instead of processing all the data. Here is a question I would like to ask,

I work with a database server, and I am one of the developers who works on it, and I need to perform performance testing on the requests that I process

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

will it affect another developer or will it only clear my local query cache?

If this affects others, is there a way to clear locally or allow my request will not use the request cache for testing

Answer 1

Two explanations to get you started:

• MySQL query cache is a server function, there is no such thing as a "local cache". You are probably confused by the LOCAL keyword in the FLUSH . Since docs explains this simply an alias for NO_WRITE_TO_BINLOG (thus, it is associated with replication, and “local” means “this server”).

• MySQL will only return cached data if you enable this function and either do it by default or select it using the SQL_CACHE hint. In my experience, most servers do not have it by default.

Now let's answer your question. In the MySQL cache, you can read:

The request cache is distributed between sessions, so a result set created by one client can be sent in response to the same request issued by another client.

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

I do not know what you want to check for sure. Your data should always be fresh:

The query cache does not return stale data. When the tables are changed, any corresponding entries in the query cache are cleared.

However, you can get an idea of ​​how long the request will take. You can always opt out of the SQL_NO_CACHE :

The server does not use the request cache. It does not check the cache request to make sure that the result is already cached, and it does not cache the result of the request.

Just keep in mind that a query that is executed a second time can run faster even without a cache, as part of the data segments may already be loaded into RAM.

Answer 2

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

 SELECT SQL_NO_CACHE * FROM TABLE 

Answer 3

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

However, it works at the Server (instance) level:

Use DBCC DROPCLEANBUFFERS to check queries with a cold buffer cache without stopping and restarting the server.

To remove clean buffers from the buffer pool, first use CHECKPOINT to create a cold buffer cache. This causes all dirty pages for the current database to be written to disk and flushes the buffers. After that, you can run the DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

edited *

The SQL query buffer cache is global, but not local. If the cache of the buffer or query falls, it deletes it all over the world and will affect all users using the database server.