I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.
It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel,
apt-get install inotify-tools is all you need to get started.
# inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/* Establishing watches... Setting up watch(es) on /var/lib/mysql/mysql/user.frm OK, /var/lib/mysql/mysql/user.frm is now being watched. [...] Total of 212 watches. Finished establishing watches, now collecting statistics. Will listen for events for 60 seconds. total modify filename 2371 2371 /var/lib/mysql/relay-log.info 2148 2148 /var/lib/mysql/master.info 1157 1157 /var/lib/mysql/ib_logfile0 24 24 /var/lib/mysql/zabbix/ 24 24 /var/lib/mysql/zabbix/history.ibd 8 8 /var/lib/mysql/zabbix/trends_uint.ibd 6 6 /var/lib/mysql/zabbix/items.ibd 5 5 /var/lib/mysql/ibdata1
This is just a limited example from a dev box, but you can see the benefit. You can see which files have been accessed, in what way, and how many times over the specified period. Consequently this provides the most insight if you’re using innodb-file-per-table (or MyISAM) rather than a single InnoDB tablespace. But of course it depends a bit on what you’re looking for.