The Mysql tool I’ve been waiting for years. zt

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:

引用

PLAIN TEXTSQL:
[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.

引用

PLAIN TEXTSQL:
[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:

引用

PLAIN TEXTSQL:
[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.