Storage engine or MySQL server? Where has the time gone?

I want to answer a simple question - If a query takes X milliseconds, how much of it is spent in the storage engine, and how much in the MySQL server? Why do I think is important? Well, since I am working on MySQL performance, I want to be able to place my bets on where to spend my time optimizing MySQL. Lets take an example. If a query takes 50ms, and I am able to figure out that 40ms is in the mysql server and the remaining 10ms is in the storage engine, the first place I would want to take a look at optimizing the MySQL server. This also tells me that it does not matter what the storage engine is, I am being limited by the server. Similarly if I find the storage engine taking up most of the time, I can explore alternate storage engines. I know all you mysql experts will tell me that much of the attribution (of time) has got to do with the capability of the storage engine, what features it supports, etc. and you cannot really do the extrapolation. However, assuming falcon would have all the features of innodb, this becomes a valid question to ask. Where is my query spending its time.

I can think of 2 easy ways to answer this question. If you know of any additional methods please feel free to let me know.

Using Dtrace

I could write a Dtrace script to intercept all storage engine API calls (both the handler and handlerton) and calculate total time. I can then subtract this with the total time for the query and get the time attribution. There are a few difficulties with this method. I am able to write such a script, however, the cost of measurement is quite high. Many of the storage engine api calls take very little time, and hence the cost of intercepting them and gathering statistics distorts things quite a bit. The other problem is that there is no way for the script to work for all cases. Since the storage engine API's are mangled, the script will not work for different C++ compilers. The solution is to write a script to generate the dtrace script (which is what I have done), however, this does not really solve the question as I cannot trust the values reported because of the probe effect.

Using Sun Studio Performance Analyzer

Sun Studio Performance Analyzer has an option where you can look at the time spent inside a specific shared object. Hence, I could build the storage engine as a plugin (libinnobase.so for ex) and then do my experiment and then use the analyzer to figure out the amount of time spent in libinnobase.so and answer my question. I need to try this out. (Thanks to Richard Smith for pointing this out)

If you know of any other methods please let me know!

Comments:

I usually compare with the blackhole engine : since this engine is a lazy sod, it mostly measure the MySQL and a short substraction gives the Engine time.

Posted by Damien Seguy on July 07, 2008 at 08:54 PM PDT #

How big is the cost associated with the first method ? Did you try to measure it ?

What about a modification of the first method - you only intercept calls that may take a significant amount of time ? It could be done manually or automatically. It could be done automatically by intercepting all calls, and performing a second run without intercepting calls that came out too short in the first run. Or it could be done automatically by intercepting all functions from nm output that have a size above a certain threshold.

Doing it on a shared object level is too coarse-grained, I'm afraid.

Comparing with blackhole often means that Damien compares two completely different optimizer and execution paths, so it's not very useful in a general case.

Posted by Sergei Golubchik on July 07, 2008 at 11:04 PM PDT #

If you are using 5.0, you should look into profiling: http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

You can also do this in 5.1, although you'll have to compile from source with the --enable-community-features and --profiling flags

Posted by Gregory Haase on July 07, 2008 at 11:49 PM PDT #

Maybe Intel VTune also could complete this task on Linux or Windows platform. :-)

Posted by xx on July 08, 2008 at 06:32 PM 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