I’ve just been pointed to the nice tool which I was waiting for years to see. It is fincore – little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.
When it comes to MySQL it is very useful with MyISAM tables which has their data file cached by OS cache only so you do not have any good information from MySQL side on what data is cached. You can also use it with Innodb to see how much memory are you wasting with double buffering by not using of O_DIRECT.
Besides general clues such as 50% of my file is cached you should watch for dynamics – for example check it during backup process and compare it due to normal load – this can give you a clue if slow down happens because of extra IO pressure or just because pages were washed out. You can also check how pages are cached. For example every second page cached may be helpful for point queries but does not save a lot of IO for doing table scans.
One thing I’m still missing is looking it from another side – so I have say 10GB of OS cache used on the server but how can I tell what is using it ? This look from another side would help me dramatically to find out what is causing cache pressure and what needs to be worked on. Scanning all files on filesystem and checking which are cached obviously does not work.
The fincore looks more like proof of concept tool – it is a bit simplistic, however being written in Perl it is easily hackable – if you want to make it to print percentage of file cached or “graph” showing how cached pages are distributed among file is very easy.
The great thing about this tool it is very fast and it does not disturbs OS file cache by using mincore function to get pages which are currently in cache.
This function is actually the real meat here – the tool is simplistic but it shows how to use the function so you can write real stuff. For example using this tool MySQL can easily add amount of cached data per table for MyISAM and Archive tables to INFORMATION_SCHEMA (or other system tables) which would be really cool Of course than one would need to implement cache content tracking for storage engines which cache everything in their own cache memory – Innodb, Falcon, Maria.
Having information about how large portion of table is cached would allow optimizer to take much smarter decisions in many cases.
Lets now see some examples:
1 2 3 4 5 6 7 8 9 10 11 12 | [root@DB01 mysql]# du -h ib_log* 257M ib_logfile0 257M ib_logfile1 [root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile0 ib_logfile1 page size: 4096 bytes 24141 pages, 94.3 Mbytes in core for 2 files; 12070.50 pages, 47.2 Mbytes per file. [root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile0 page size: 4096 bytes 1 page, 4.0 kbytes in core for 1 file; 1.00 page, 4.0 kbytes per file. [root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile1 page size: 4096 bytes 24169 pages, 94.4 Mbytes in core for 1 file; 24169.00 pages, 94.4 Mbytes per file. |
So we can see one of Innodb log files is practically uncached while other has about 1/3rd cached – this makes sense, perhaps second log file is being written now and there is a “tail” of pages which just were not removed from the cache yet. As Innodb does not read logfile unless in recovery these are waste and Innodb could use fadvice to give instruction to kernel not to cache these as long as it can’t perform direct IO to log files on Linux because it is not aligned.
1 2 3 4 5 | [root@DB01 mysql]# du -h ibdata* 246G ibdata1 [root@DB01 mysql]# perl /tmp/fincore --justsummarize ibdata1 page size: 4096 bytes 0 pages, 0.0 bytes in core for 1 file; 0.00 pages, 0.0 bytes per file. |
Out of 250GB innodb data file none of pages are in cache – this is because this instance is using O_DIRECT flag to bypass data buffering and we can well see it works.
Lets now see stats for MyISAM tables:
1 2 3 4 5 6 7 8 9 10 11 12 | [root@DB01 logs]# du -h performance_log_080318.MYD 1.1G performance_log_080318.MYD [root@DB01 logs]# perl /tmp/fincore --justsummarize performance_log_080318.MYD page size: 4096 bytes 497 pages, 1.9 Mbytes in core for 1 file; 497.00 pages, 1.9 Mbytes per file. [root@DB01 logs]# du -h performance_log_080319.MYD 229M performance_log_080319.MYD [root@DB01 logs]# perl /tmp/fincore --justsummarize performance_log_080319.MYD page size: 4096 bytes 28415 pages, 111.0 Mbytes in core for 1 file; 28415.00 pages, 111.0 Mbytes per file. [root@DB01 logs]# |
The performance log for yesterday is almost out of cache. It is about 0:50 by server clock this is why we still can see some pages remaining. Today log file is 50% in cache. Knowing access pattern to the file you can draw some conclusions about how much IO pressure we have on this server.
P.S If you would hack this tool or know any similar tools please let me know.
Perl used in this tool just for wrapping C-code, in which mincore called.
P.S. Sorry for broken english 🙂
Yes sure. But the fact the wrapper is in Perl makes it easier to extend for people familiar with scripting language.
Hardcore C guys just need to know such call exists 🙂
СпаÑибо большое за интереÑную утилитку. Узнал много интереÑного. Ðапример то, что .MYD-файлы кÑшируютÑÑ Ñильнее .MYI-файлов. Ð¥Ð¾Ñ‚Ñ Ð¼Ð¾Ð¶ÐµÑ‚ Ñто только у Ð¼ÐµÐ½Ñ Ñ‚Ð°Ðº.
Иногда fincore падает почему-то. Ðо в целом даёт довольно интереÑную картину. Ещё раз ÑпаÑибо.
MYD files are expected to be cached more by OS than MYI files because later are cached in key_buffer so OS should get less requests for them and less motivation to have them in the cache.
Wow I always wanted something like this. I had no idea that this even existed. Thank you for letting us know!
Hello,
there is an util which looks like what you want:
http://oss.oracle.com/projects/codefragments/src/trunk/pagecache-info/pagecache-info
_but_ (as usual) there is a problem — it worked only for 2 old versions of kernel 🙂
And it doesn’t work on lastest kernels…
Need to update it for supporting new kernels.
I was trying but i have small knowledge of kernel 🙁
Thanks.
Anyone knows if there is updated version for this too anywhere ?
Пётр, ÑпаÑибо за разъÑÑнение.
Я раньше вÑегда Ñчитал, что что двойное кÑширование (в MySQL и в OS) – Ñто зло и приводит к тому, что памÑÑ‚ÑŒ тратиÑÑ Ð²Ð¿ÑƒÑтую. Рна практике выходит, что в Ñтих двух кÑшах ÑодержатьÑÑ Ñ€Ð°Ð·Ð½Ñ‹Ðµ данные и памÑÑ‚ÑŒ напраÑно почти не тратитÑÑ, ибо данные попавшие в кÑш MySQL-Ñ Ð·Ð°Ð¿Ñ€Ð°ÑˆÐ¸Ð²Ð°ÑŽÑ‚ÑÑ Ñ Ð´Ð¸Ñка лишь единожды и потом Ñ Ð±Ð¾Ð»ÑŒÑˆÐ¾Ð¹ вероÑтноÑтью вытеÑнÑÑŽÑ‚ÑÑ Ð¸Ð· кÑша OS, Ñ‚.к. к ним нет запроÑов на чтение.
Michael,
The double buffering has many down sides. Depending on workload and OS it can be more or less. You’re right data which is cached is typically removed from MySQL quickly but still. Consider full table scan for example – you get a lot of data which is being read very quickly – consuming a lot of cache quickly it can well remove data which you need.
Another issue is battle for virtual memory – a lot of cached IO makes OS to try to make more memory for the cache which often causes it to try swap out something… like some of huge caches you allocated to MySQL.
Anyway – this tool is great because now you can actually see how much double buffering you have and what gets cached.