April 16, 2014

Using flow control functions for performance monitoring queries

I’m not big fan on flow control functions like IF or CASE used in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer.

One way I find IF statement very useful is computing multiple aggregates over different set of rows in the single query sweep.

Here is how I like to use it for web site performance analyzes. As you can see in this table we have recorded “wtime” which is wallclock time it took to generate the page. We also track types of pages because they often have different performance profile.

This query scans through page generation log for some site and reports number of requests, average request time as well as classifies requests to multiple classes. From the same query we can see portion of requests which were over 300ms (12.8%) – so we call them “so so” as we set 300ms or less as performance goal for the web site. 4% of these requests get ranking “poor” being over 1 seconds and 0.1% of requests get “fatal” classification because we assume user will not wait over 5 seconds and will already go away.

On the side note I should say the average time is least usable because average does not tell you a lot. It is much better set performance goals for high percentage portion such as – 95% or 99% and see what fraction of requests matches this goal.

In this example if our 95% goal would be 1 seconds we would pass but for 95% 0.3 second response time we would fail, same as 99% requests served within 1 second.

Here is another example:

In this example I hid some rows to obfuscate some date :)

In this query we’re looking at response time for different pages and we can find “search” page responds within 1 second in about 95% while profile page in less than 60% – It is very important to do such grouping by user functions because otherwise you will not catch important but may be less used functions performance problems.

Another thing you may notice I look for performance stats not for all pages but just for pages retrieved by Google crawler. In many cases this is worth looking at (may be for all search bots rather than just google) because bots have very different site access pattern. In many cases your human visitors will visit relatively few hot pages, which will use content from the cache (or be served from the cache all together). Bots however tend to visit distinct pages which tends to have significantly lower cache hit rate.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Francesco says:

    The flow control technique to control the monitor seems to be cool. I will surely learn to operate the monitor using this technique.

  2. Lukas says:

    Actually I have not seen much of any abuse of CASE in SQL statements. Actually I tend to advocate increased use of CASE in order to fold multiple queries into one in the spirit of thinking more in sets than in procedural terms.

  3. peter says:

    Lukas,

    Then you’re lucky :) In this case you can see functions are used in SELECT list this is safe, if they are used in WHERE or JOIN clause it is a big issue

    Something like IF(a.field_1>2,a.field2,5)=b.field1 restricts possible order of joins.

    If you can fold multiple queries in one (like in this example) it is surely good idea.

  4. IF(cond, 1, 0) is the same as (cond) in MySQL so flow control functions are not necessary in this article :-).

  5. Parvesh says:

    Peter,

    On a slightly different note, the SQLs just check on the lower limit i.e., IF(wtime>0.3,1,0), instead it should be IF(wtime BETWEEN 0.3 AND 1, 1, 0). Otherwise, your “so-so” results include poor and fatal also. So, in your first query, the ‘so-so’ should be around 8%.

  6. peter says:

    Parvesh,

    Oh yes I know :)

    These inclusive times, rather than ranges are OK for me – in fact it is what I would like to know – what percent goes over 0.3 overall – is what is “bad” and when from there I can visually drill down in how bad is it :)

  7. peter says:

    Jakub,

    Indeed. Though I do not like “hackish” approach using things side affect way :)

  8. MySQL Fan says:

    Peter,

    I think this is an example a retrograde thinking:
    “I’m not big fan on flow control functions like IF or CASE used in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer.”

    Well, in this case, how about convincing MySQL AB/Sun to write a real optimizer and tackle the root of the problem? Write a Query Optimizer in such way that will handle well those if/case control functions. It’s time for MySQL RDBMS to evolve.

  9. peter says:

    This is example of practical thinking. I know MySQL optimizer will not be fixed to handle these during the next couple of years while I deal with systems which must work now.

    I am constantly providing feedback to the optimizer team of what else could be fixed and believe me they know about a lot of stuff which needs to be done themselves but it takes a lot of time to get done.

    Not to mention there are cases which simply can;t be optimized as well as equivalent set of queries without flow control functions.

  10. MySQL Fan says:

    Peter,

    I strongly believe that your efforts and contributions are more than well received by MySQL’s team. Yet, I still don’t see results. And now you’re saying that will take a couple of years to re-write the optimizer, on top of the ten years or more that have been spent to bring mysql up to this level. Hmm, isn’t that kind of a long time dedicated to build a, hmm, let’s say strong product?! I know that Rome wasn’t built in a day but still…

    Ah, and add to that list the sql layer (a bunch of if/case statements) and the lack of a consistent behaviour across the storage engines, just to start with. Add to my argument the same amount of time I mentioned earlier.

    Now that MySQL is part of Sun, they don’t have any excuse: it’s time for MySQL to REALLY shine with a REALLY strong product. Advocate for this and blog about it because you have the means. The users are waiting for maturity as change.

  11. krteQ says:

    Jakub,

    you’re right, but just in this particular case. What about:

    mysql> SELECT IF(1 = NULL, 1, 0), (1 = NULL);
    +——————–+————+
    | IF(1 = NULL, 1, 0) | (1 = NULL) |
    +——————–+————+
    | 0 | NULL |
    +——————–+————+
    1 row in set (0.00 sec)

  12. krteQ says:

    Peter,

    for more interesting results, I usually use something like:

    SELECT wtimeRounded
    , ROUND(pcnt,1) AS totalPcnt
    , ROUND(@a := @a + pcnt, 3) AS pcntSum
    , cnt
    FROM (
    SELECT COUNT(*) AS cnt
    , CEIL(wtime * 100)/100 AS wtimeRounded
    , (COUNT(*) / (SELECT COUNT(*) FROM performance_log_080523) * 100) AS pcnt
    FROM performance_log_080523
    GROUP BY CEIL(wtime * 100)/100 # HAVING COUNT(*) > 5
    ORDER BY wtime2 ASC
    ) AS tmp1;

    which shows percentages divided by specified time interval – useful for generating performance graphs.

Speak Your Mind

*