Finding optimization opportunities in MySQL by looking at callstacks

Using callstacks to look at code is very useful. If you are not familiar with callstacks, I suggest you read my earlier blog about it. I was trying to understand the mysql code path using sysbench as the test and found something interesting. An image of the callstack is shown below. The SVG version, with much more information, is also available . The width of the block is proportional to the time it took for the function, and the height is the level (or depth) of the stack.

Mysql uses mysql_execute_command() to execute queries. Looking at the callstack you can see very clearly that mysql_execute_command() calls open_and_lock_tables which then tries to open tables via open_table(). The code path gets interesting here. As you can see in the image above, (and in the svg callstack), there is tight for loop. Looking closely (moving your mouse over the area in the svg file), you will see that the code is basically traversing a hash list! Not the best thing to do with a hashtable. Looking at the code, it is originating at line number 2781 in sql/sql_base.cc. Can it be improved? Most probably. Will it help if we improve it? Definitely for some cases, maybe not for others.

I found a few more interesting places in the code, but I will save them for my future blogs :-)

Comments:

Neel, the call graph visualizer is very cool! the svg version enables deep dives ,in a smooth fashion, to focus on interesting pieces of the timeline.

Which leads me to think - now that OpenSolaris Ruby is DTrace enabled, the visualizer could be used to help visualize Ruby call stacks.

DTrace is one of the cool things to happen to Ruby profiling, and hooking it up with a good visualization tool is, imo, the next step.

Posted by Prashant Srinivasan on April 02, 2008 at 11:10 AM PDT #

[Trackback] Neel has just posted an example of using DTrace with MySQL to examine what is going on in the system at runtime. Check out the SVG! This is very, very cool and can certainly show where we'll be able to take MySQL and other components we use in the...

Posted by Matt Ingenthron's Stream of Consciousness on April 02, 2008 at 11:50 AM PDT #

Now that I see the svg file on your blog, it looks excellent. I l look forward to using this for networking. Good work.

Posted by amitab on April 03, 2008 at 03:47 PM PDT #

Very cool!

Posted by Mikael Gueck on April 07, 2008 at 12:51 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

realneel

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today