On a high load production server I have many SQL selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but these queries are still cached with OS cache. The server has 64G ram and used ram is still 100%, about 47G is OS cache. If I manually drop OS cache with sync; echo 3 > /proc/sys/vm/drop_caches, then everything run more smoothly. But in few seconds the OS cache grows again to about 47G and uses all free ram. And I know, most of this data will be read only once, so there is no point for caching.

Is there any way to say to OS - "I know, I will not read this data again, don't cache them." ? Would be great, if this can be set for single mysql query or process.

And one more thing, I know about innodb_flush_method=O_DIRECT, but these tables are Aria engine on MariaDB

  • 2
    The cached data will be released if any processes actually need the RAM. "Cache" is actually classed as "free" memory. You don't need to worry about it. – Philᵀᴹ Apr 10 '14 at 0:35
  • I know about this, but in my experience server doesn't manage this efficiently and free ram quickly in peak hours and if os cache fill all free ram. If I watch some php process with strace and drop os cache manually, then process run much faster. – stix Apr 11 '14 at 9:37

No. There is no possibility to pass for operating system request not to cache file reads for IO done for specific query. You can reduce sysctl parameter vm.swappiness to 1 to make cache recovery more aggressive, but that affects all IO caching in the server.


Can you check if your query_cache_type is set to 1 ?

0 or OFF - Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.

1 or ON - Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.

2 or DEMAND - Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

I'm not very familiar with Aria but I believe it follows these settings. I'd test this option to see what happen.

More information:

http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_type

  • Yes, query_cache_type is ON and I use SQL_NO_CACHE only where cache is not needed. This is not problem, mysql cache working fine. Problem is when mysql doesn't cache, then still os caching disk reads and this is useless in this case. – stix Apr 11 '14 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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