The tool I?ve been waiting for years

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 TEXT
SQL:

1. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# du -h ib_log*[/font]
2. [FONT='Courier New', Courier, monospace]257M ib_logfile0[/font]
3. [FONT='Courier New', Courier, monospace]257M ib_logfile1[/font]
4. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile0 ib_logfile1[/font]
5. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
6. [FONT='Courier New', Courier, monospace]24141 pages, 94.3 Mbytes IN core FOR 2 files; 12070.50 pages, 47.2 Mbytes per file.[/font]
7. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile0[/font]
8. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
9. [FONT='Courier New', Courier, monospace]1 page, 4.0 kbytes IN core FOR 1 file; 1.00 page, 4.0 kbytes per file.[/font]
10. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# perl /tmp/fincore --justsummarize ib_logfile1[/font]
11. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
12. [FONT='Courier New', Courier, monospace]24169 pages, 94.4 Mbytes IN core FOR 1 file; 24169.00 pages, 94.4 Mbytes per file. [/font]

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 TEXT
SQL:

1. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# du -h ibdata*[/font]
2. [FONT='Courier New', Courier, monospace]246G ibdata1[/font]
3. [FONT='Courier New', Courier, monospace][root@DB01 mysql]# perl /tmp/fincore --justsummarize ibdata1[/font]
4. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
5. [FONT='Courier New', Courier, monospace]0 pages, 0.0 bytes IN core FOR 1 file; 0.00 pages, 0.0 bytes per file. [/font]

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 TEXT
SQL:

1. [FONT='Courier New', Courier, monospace][root@DB01 logs]# du -h performance_log_080318.MYD[/font]
2. [FONT='Courier New', Courier, monospace]1.1G performance_log_080318.MYD[/font]
3. [FONT='Courier New', Courier, monospace][root@DB01 logs]# perl /tmp/fincore --justsummarize performance_log_080318.MYD[/font]
4. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
5. [FONT='Courier New', Courier, monospace]497 pages, 1.9 Mbytes IN core FOR 1 file; 497.00 pages, 1.9 Mbytes per file.[/font]
6. [FONT='Courier New', Courier, monospace] [/font]
7. [FONT='Courier New', Courier, monospace][root@DB01 logs]# du -h performance_log_080319.MYD[/font]
8. [FONT='Courier New', Courier, monospace]229M performance_log_080319.MYD[/font]
9. [FONT='Courier New', Courier, monospace][root@DB01 logs]# perl /tmp/fincore --justsummarize performance_log_080319.MYD[/font]
10. [FONT='Courier New', Courier, monospace]page size: 4096 bytes[/font]
11. [FONT='Courier New', Courier, monospace]28415 pages, 111.0 Mbytes IN core FOR 1 file; 28415.00 pages, 111.0 Mbytes per file.[/font]
12. [FONT='Courier New', Courier, monospace][root@DB01 logs]# [/font]

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.
Entry posted by peter | No comment
Add to: | | | |


The tool I?ve been waiting for years - Read More...

Distance Learning and University Effectiveness: Changing Educational Paradigms for Online Learning

Distance Learning and University Effectiveness: Changing Educational Paradigms for Online Learning

Category: Study
<< Buy This Book on Amazon >>
Read more

Microsoft Windows PowerShell Scripting Guide DEC 2007 eBook-UnderPl

Microsoft Windows PowerShell Scripting Guide DEC 2007 eBook-UnderPl

Category: Technical
Tag: Operating System
<< Buy This Book on Amazon >>
Read more

Turn $6 into $1,000s as seen on Oprah and 20/20

Make 15k in 30 Days!!!! As seen on Oprah
and 20/20!!!
That's Right 15,000 dollars in your PayPal account - That's all it takes...
FIRST, PRINT THIS OUT TO FOLLOW INSTRUCTIONS EASIER!
. PAYPAL MAGIC!!!
. TURN $6.00 INTO $15,000 IN ONLY 30 DAYS...HERES HOW!
This is Not a Scam!!!
How to Turn $6.00 into $15,000 in 30 Days with PayPal.
AS SEEN ON OPRAH!!
PAYPAL VERIFIES THAT THIS $6 INVESTMENT
IS 100% LEGAL AND IS A BIG HIT THIS YEAR
SEE THEIR NOTE BELOW OR ASK THEM DIRECTLY...
THIS MIGHT TAKE 15-30 MINUTES AND JUST $6,
BUT IT IS 100% WORTH IT TO MAKE THOUSANDS SO
QUICKLY.
From PayPal:
"Dear Member, it has come to our attention that there
is a PayPal scheme floating around at the moment you
may have heard or seen the $6 scheme. You may have
even taken part in it well we have been asked a lot of
questions about this scheme the answer is yes it does
work and yes it is safe to use providing you follow the
rules it is legal and has made a big hit on the internet
this year. If you would like to take part in this scheme
or would like a bit more information then please see the
attached file that was kindly donated to us. Thank you for
using PayPal!" This Really Works Keep in mind that everything stated below is 100% legal as stated by the U.S. Post Office (1-800-725-2161).

Now honestly,what is six Dollars, especially out of a Paypal account. In exchange
you will make thousands in the first few months. You then place your own name and email address in the bottom of the list at #6, and post the article in at least 200 newsgroups. (There are thousands of
newsgroups)

I sent out my measly $6.00 to the six people on the list. Within 7 days, I started: getting $$$$$ in my account!
I was shocked! I figured it would end soon, but the CASH just kept coming in. In my first week, I made about $25.00.By the end of the second week I had made a total of over $1,000.00! In the third week I had over $15,060.00 and it's still growing.This is now my fourth week and I have made a total of just over $26,000.00 and it's still coming in rapidly. It's certainly worth $6.00 and 6 minutes to send it out. This program WORKS. Put in message boards or send as emails.
INSTRUCTIONS
1. Login to PayPal and click the "Send Money" tab near top of
screen
In the "Recipient's Email" field type: the email address
In the "Amount" field type: "1" (your $1.00 payment)
In the "Category" field select: "Service" (Keeping it
legal)
In the "subject" field type: "EMAIL LIST",
In the "message" field type: "PLEASE PUT ME ON YOUR EMAIL LIST".
Finally, click on the "Continue" button to complete the payment.
Repeat these steps for each of the 6 email addresses. that's it!
Below are the emails that you transfer the money into:
Paypal accounts to send 1 Dollar to:
The Email List:

1. linkatam@yahoo.com
2. Jcmabrey85@aol.com
3. skgates30@yahoo.com
4. rickreineke@hotmail.com
5. hawki336@hughes.net
6. Shandriver2003@yahoo.com


2.After sending the money to the first 6 people on the list, you can
remove the name next to the #1 on the list and move the rest of the
names up one position (#2 becomes #1, #3 becomes #2, etc?) Then place
your name in the #6 position. Then save it.
3. When you have completed the instructions, take this letter and then
go to (Google, Yahoo,...) and type in (Making Money Message board, Money Message Board, Money
Forum, Free money Forum, or anything along those lines that are forums)
and start posting your copy of this message at least 200 times, to as
many unique message boards as you can, or more. The more times you post
this at a unique board, the more money you will make.
Keep a copy of this letter so you can use it a second time.
Remember, play FAIRLY and HONESTLY and this will work.
4. This program remains successful because of the honesty and integrity
of the participants. Please continue its success by carefully adhering
to the instructions.
5.This is perfectly legal! If you have any doubts, refer to 18 Sec.
1302 & 1241 of the Postal Lottery laws.
You can try this or buy another lottery ticket and see which pays first. It's up to you!
[[[ IMPORTANT NOTE: If you can not see full email addresses (this
happens on some web-based groups), just click on the link that appears
near the @ symbol of any email address above. This will take you to a
page for you to verify that you're human and then it takes you back to
this message finally revealing the full email addresses. ]]]