MyISAM table level IO on Solaris using Dtrace

I've stumbled around for awhile searching for a way to observe IO at the table or file level on a MyISAM engine. Since I'm running on Solaris 10 I decided to explore dtrace and in my travels I found some handy scripts that made it easier.

I used iosnoop which can be found here : http://brendangregg.com/dtrace.html#DTraceToolkit

iosnoop can be pointed at a specific pid, in my case the pid of the mysqld process:

iosnoop -v -p123456

Since the probe fires when IO happens, this will send out quite a bit of output to STDOUT. So I wrote a perl script to call iosnoop and summarize Read and write IO Counts and Bytes by the minute. I still have more testing and refinements but it seems to work correctly. I have probably over-scaled the bytes by converting to Mbytes.

Here is a sample output:

HR:MN FILE R_Mbyte R_count W_Mbyte W_count
00:08 /p732/cust/cust_xref_3.MYI 0 0 0 3
00:08 /p749/cust/sales_fact_6.MYI 1 152 0 0
00:08 /p744/cust/sales_fact_2.MYD 2 326 0 0
00:08 /p745/cust/sales_fact_3.MYD 27 3527 0 0
00:08 /p746/cust/sales_fact_4.MYD 12 1554 0 0
00:08 /p743/cust/sales_fact_1.MYD 4 572 0 0
00:08 /p738/cust/cust_xref_9.MYI 0 1 0 5
00:08 /p747/cust/sales_fact_4.MYI 1 214 0 0
00:08 /p741/cust/sales_fact_9.MYD 11 1546 0 0
00:08 /p740/cust/sales_fact_7.MYI 1 175 0 0
00:08 /p747/cust/sales_fact_5.MYD 4 596 0 0
00:08 /p743/cust/sales_fact_0.MYI 0 130 0 0
00:09 /p745/cust/sales_fact_2.MYI 1 166 0 0
00:09 /p737/cust/cust_xref_8.MYI 0 0 0 7
00:09 /p742/cust/sales_fact_0.MYD 15 1963 0 0
00:09 /p749/cust/sales_fact_7.MYD 4 653 0 0
00:09 /p742/cust/sales_fact_9.MYI 0 114 0 0
00:09 /p748/cust/sales_fact_6.MYD 11 1514 0 0
00:09 /p744/cust/sales_fact_1.MYI 1 220 0 0