Categories
Blog Posts

How Did You Make mrskew 20× Faster?

A couple of years ago (June 30, 2020), we released Method R Workbench version 9.0.0.66. It had 113 new features and bug fixes. One of those features was case 7800: “mrskew is now 10–20× faster than before.”

We’re prone to sneaking in performance improvements like that. It’s because we, too, use the software we sell, and we don’t like waiting around for answers any more than you do.

mrskew, in case you don’t know, creates flexible, variable-dimension profiles. It’s a skew analyzer for Oracle trace files.

…A what?

It’s a tool that can query across trace files (thousands of them, if that’s how many you have) and answer questions like these:

  1. What kinds of calls dominated the response time of your user’s experience? Imagine for the sake of this example that the answer is “read calls.” How much time did read calls take? How many read calls did your program make?
  2. Were all your read calls the same duration? Or did some take longer than the others? How much time could you save if you eliminated the slowest 10,000 read calls?
  3. How many blocks did the longest read calls read?
  4. What are the file and block IDs of the longest read calls?
  5. Are the slowest read calls associated with a particular file?
  6. Are they associated with a particular SQL statement?
  7. On what line of what trace file can you find information about your longest read call?

Our mrskew tool can answer questions like these and more.

Here are the commands to do it. Don’t let these scare you. You can summon any one of them (or any of 30+ others) with just a click, in our Method R Workbench:

  1. mrskew *.trc
  2. mrskew --name='read' --rc=p10.rc *.trc
  3. mrskew --name='read' --group='"$p3"' --gl='"BLKS/READ"' *.trc
  4. mrskew --name='read' --group='"$p1:$p2"' --gl='"FILE#:BLOCK#"' *.trc
  5. mrskew --name='read' --group='"$p1"' --gl='"FILE"' *.trc
  6. mrskew --name='read' --group='"$sqlid"' --gl='"SQLID"' *.trc
  7. mrskew --name='read' --group='"$base:$line"' --gl='"FILE:LINE"' *.trc

Now, imagine trying to ask 2GB of trace data all these questions. Without mrskew, it would probably take you a day or more to fish the answers out of your trace files (don’t bother looking in AWR or ASH; they’re not there).

A Workbench 8 mrskew execution on 2GB of input takes about 4 minutes. That’s about half an hour to run all seven commands. That’s pretty good compared to a day or two of fishing.

A Workbench 9 mrskew execution on the same input takes only about 12 seconds. That’s less than 2 minutes to answer all the questions I’ve posed here. That’s remarkable.

2019 MacBook Pro (Intel)mrskew *.trc (2GB)
Method R Workbench 8240 seconds (4 minutes)
Method R Workbench 912 seconds
mrskew execution times before and after the Method R Workbench 9 upgrade.

So, an interesting question, then, might be, “How did you do that?”

Well, that’s easy: a long time ago, I hired Jeff Holt.

How did Jeff do it?

Simple. He rewrote mrskew in C.

In Workbench 8, mrskew was a Perl program that I had written in 2009. Perl is admittedly slow, but I was interested in having a program that users could interact with using Perl’s full expression syntax.

mrskew worked really well, and we used it a lot. But it always felt weird that it was so much slower than our other utilities that do even more work (like mrprof). So Jeff, in his spare time, investigated whether he could rewrite mrskew in C. It was no small feat, given that I insisted upon keeping the full Perl expression interface.

One day he surprised me. The new C version of mrskew was passing all our automated tests and we could probably ship it now. I asked him how much faster it was. He said about 20×.

I’m used to this kind of thing with Jeff by now. But still.

The result of Jeff’s investigation is that now we have a skew analysis tool that works just as fast as our other outrageously fast tools, even when you’re battling data by the gigabyte. Today, mrskew is a standard feature of pretty much every performance improvement project we hook into, and we’re grateful that it doesn’t make us wait a long time for the answers we need.

To get a better understanding of what skew is and why it’s important, see chapter 38 of How to Make Things Faster. If you’re interested in more detail about mrskew, visit our mrskew manual page.

Categories
Blog Posts Videos

Fill the Glass

Today, Cary Millsap hosted the inaugural episode of his new weekly online session, called “Fill the Glass.” Episode 1 was an ask-me-anything session, covering topics including how to access the Method R workspace in Slack, advice about being your own publisher, and our GitHub repository (available now) for Cary’s and Jeff’s new book, “Tracing Oracle” (available soon).

Visit our “Fill the Glass” page for access to past recordings and future live sessions.

Categories
Blog Posts Videos

Insum Insider: How to Optimize a System with Cary Millsap

Today, Michelle Skamene, Monty Latiolais, and Richard Soule of Insum chatted with me for an hour on their live stream. I hope you’ll enjoy it as much as I did.