Apr 8, 2014

MySQL query profiling with Jet Profiler

During my last performance analysis I came across this really useful tool for MySQL profiling called Jet Profiler. The swedish company Polaricon AB did a great job in building an easy to use tool for spotting and optimizing slow queries in MySQL without complicated instumentation. The following image shows the UI of Jet Profiler after a profiling run:



This post consists of three parts: First a short abstract about how Jet Profiler works, followed by a conclusion of the biggest benefits in my oppinion and third a list of limitations and tips.

How Jet Profiler works

Jet Profiler simply connects to the MySQL server, quite the same way your application does. No need for special configuration of the MySQL server and no need to install any agent. Jet Profiler polls performance statistics in configurable intervals from the database server. These cover queries, tables and user information.



Benefits

  • Simple slow query detection
    The tab "Slow Queries" lists the slowest recorded queries ordered by their runtime. In this view there is no aggregation done when a query was executed multiple times. In this place you can easily spot the slowest single queries.
  • Query aggregation

    The tab "Top Queries" aggregates queries with equal normalized form. That means, the runtime of multiple executions of the same query will be added up here. In this place you can see the queries which consumed the most SQL-runtime during your measurement.
  • Different metrics on a timeline

    The timeline gives a quick impression of your measurement from a bird's eye view. You can spot network issues, concurrency effects memory shortage by switching the timelines presets and zooming to a specific period in time.
  • Filtering by timespan

    All tabs on the bottom of the application window are context sensitive, in matters of the selected time period in the upper chart. When zooming to a specific time window, tabs like 'Top Queries' or 'Top Schemas' are updated to show only information relevant to the current time window. This enables you to focus on suspicious looking spikes from different point of views.
  • Graphical explain plans

    The join dialog is a graphical representation of SQL's EXPLAIN command. It shows four criteria for a query: Join size, full table scans, full index scans, using temporary and using filesort. These are used to rate the performance on a point scala. It gives you an impression of how your query was optimized by SQL's optimizer. With these hints you can optimized queries by e.g. adjusting indizes or execution order.
  • Query rating

    The query already rating mentioned at explain plans gives you a good hint for how much potential for optimization lies in a query.
  • Simple instrumentation
    Jet Profiler attaches to the SQL server in the same way your application does. As long as you have a network connection to the server, you can simply connect Jet Profiler. No need to install an agent or reconfigure your server.
  • Non invasive
    There is no functional impact on the server when profiling with Jet Profiler, because it simply collects data using the three commands 'show processlist', 'show open tables' and 'show global status'. The performance impact is insignificant.

Limits and Tips

  • Carefully choose recording settings

    Make sure your polling interval is short enough. Jet Profiler can only see queries which are running in the moment when statistics are collected. If your polling interval is e.g. 500 ms you may not see some queries with a runtime faster than that.
    The slow query threshold lets you configure when a query should be marked as slow query in Jet Profiler.
  • Clearing output during analysis

    While optimizing queries you normally run the same query or parts of the query in different formulations or in different orders multiple times. Because Jet Profiler aggregates queries with the same normalized form, your query execution may seem to be ignored by Jet Profiler. To avoid that, you should clear your results from time to time by chossing File > New.
  • For slow queries better use Neor Profile SQL

    In some cases you may have performance problems with masses of queries which run faster than 200 ms. As that is the minimum polling interval of Jet Profiler, you may not see them with this tool. In that case, Neor Profile SQL could be your solution. It's more complex to instrument because it acts as a proxy. See here for more information.
  • Use mysql profiling mechanism to see internal execution times

    Jet Profiler does not show you the details execution profiles like MySQL does when activating the built in profiling mechanism. But that doesn't prevent you from manually inspecting execution profiles concurrently.
  • Only MySQL supported? What about Oracle?
    Binary drop in replacements for MySQL, like MariaDB, can easily be profiled by Jet Profiler. Any SQL database supporting the above mentioned three commands for statistic polling can be profiled. My tests proved that MySQL 5.6.15 can be profiled. Oracle RDBMS is not supported.
    The vendor lists official supported servers on their homepage:
    • MySQL Enterprise, MySQL Network, MySQL Pro versions: 3, 4, 5.0, 5.1 and 5.5
    • MySQL Community versions: 3, 4, 5.0, 5.1 and 5.5.
For more information about Jet Profiler, see the online documentation from the vendor.

No comments:

Post a Comment