using mincore to detect page cache efficiency of Mysql’s MyIsam

We use MyIsam tables on our archive mysql database servers in an append only manner. This means that we’ve got data files that are continuously growing in size… In each mysql database archive shard there is a “hot” replica that is responsible for caching (in the Linux file system page cache) the last 4 hours worth of data. This latest 4 hours worth of data at the end of the file we call the “hot-window”. I’ve written a couple tools to help us detect if the hot-window is in page cache. I’ve added report_filechanges and pages_by_time to my python-ftools project on github.

Usage: report_filechanges [options]

Options:
-h, --help show this help message and exit
--hist-log-dir=HIST_LOG_DIR
historical log directory
--dir-to-monitor=MONITOR_DIR
director to monitor


report_filechanges is called from cron every five minutes; it produces a log file entry for each file in the Mysql MyIsam data directory. Each log entry contains the filename, the time (in Unix epoch seconds) and the file size. We later use these historical file sizes as offsets in the file. This gives us the ability to determine the boundary of the hot region.

Usage: pages_by_time [options]

Options:
-h, --help show this help message and exit
--evict Evict historical region of files
--summary Summarize page cache usage of file regions
--hist-log-dir=HIST_LOG_DIR
historical log directory
--evict-time=EVICT_TIME
eviction time


pages_by_time uses these logs files to summarize page cache usage (via the mincore system call) or to evict pages from file system page cache (via the fadvise system call).