MySQL – prevent OS cache for query

linuxmariadbMySQLPHP

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

Best Answer

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.