Jet Profiler is a great MySQL profiler. As the world of operating systems is getting more and more mixed, Jet Profiler offers a huge advantage: It is a Java based application and therefore able to run on Windows, Max OS X as well as on Linux.
Most of the time Java based applications tend to be very slow, however Jet Profiler shows that Java based applications can be fast and responsive as well. Even after hours of recording the UI stays responsive. To give you a working example, I had it recording on a server and real world load.
A nice feature of Jet Profiler is the setting for the polling interval. If you have a very, very busy server the polls could interfere with the performance, so you can define the polling interval, which I did. However, instead of starting with a very slow polling interval, I started with the smallest I could set. I wanted to see the impact on the server from the polling so I changed it to “Very short (0.1 sec)”. With this setting the application will collect information 600 times a minute from the database. As the server has about 200 – 300 queries a second when I was running it, that will add up (assuming 200 for the calculation) to 12,000 queries to be handled every minute. That is quite an impressive amount of data.
When I checked the load on the server I could see that the 0.1 polling interval had an effect on the overall MySQL performance. But even with that tiny interval the effect to the database was very, very small. Yes, noticeable, but still so small that it can be ignored, as long the recorded MySQL server is not at the edge of its performance.
I recorded continuously for more than 2 days and Jet Profiler was as responsive at the end as it was at the start. Only the switching between the graphs seemed to get slower when there is more recorded data. But that seems to be logical. Let’s do the math again here to establish the number of queries dealt with over 2 days i.e. 2 days = 48 hours or 2,880 minutes. And with 12,000 queries a minute we have a staggering 34,560,000 queries recorded in those 2 days.
This demonstrates that even after recording for such a long time the UI is still responsive, the switching between the different graphs does take some seconds, but considering the huge amount of data it is handling this is still fast.
As well as impressive performance, Jet Profiler gives you a number of useful features to find the most time consuming queries. MySQL can only log all the queries running longer than a configured time, whereas Jet Profiler does something a little more sophisticated. Jet Profiler is summing up all similar queries. That means all queries that are equal, regardless of their values, are summed up. Jet Profiler therefore shows you queries MySQL, even in its long running query log, would never show. Another benefit of using Jet Profiler is that in total, those MySQL queries utilize more processing time in the MySQL server, and optimizing those queries with Jet Profiler is freeing more resources on the MySQL server than optimizing one slow query that just runs once a day.
Without going through all the great features of Jet Profiler in detail, some of them are really helpful in planning your workload. When using Jet Profiler for a day or more, you can see how the number of queries distributes across the day. Another graph shows you the distribution of read and write queries or the distribution of Select, Update, Insert and Delete queries. All of that gives you a great overview of your usage of MySQL resources.
In my experience, a MySQL profiler like Jet Profiler is more and more needed to optimize queries. This is because most modern development frameworks provide easy interfaces to request data without writing even one SQL query. Not all of the resulting queries from such frameworks are highly optimized. Finding those queries can be hard when there is no MySQL Profiler.
I came upon an issue when using Jet Profiler one day when I identically closed it down – there is no pop up that asks you to save the recording first, which can be a little annoying to say the least! We can hope this will be added in later versions. As saving can only be performed after the recording is stopped, that is kind of an issue.But on the other hand should the App crash, or as in my case the computer crashes while recording, Jet Profiler is capable of recovering the crashed recording, which as far as I can see works very smoothly and stably.
Another interesting feature is the fact that Jet Profiler does not complain if the connection to the database is lost. Well, that sounds like a bad thing at first and I would prefer a small indication somewhere about it, but it is not as bad as it sounds. When the connection becomes available again, lets say after an MySQL restart or the internet connection restores, Jet Profiler reconnects automatically and continues recording. Even after I put my Mac to sleep, it reconnects and continues recording as soon as it is woken up. Apart from the missing indication, I would still call that a great feature!
It is also nice that the Jet Profiler is available in many languages. The sad part however, is that the date and time format is dictated by the language selected. E.g. when you select the English language, the time is set to show 12 hour format. To change the date and time format you would have to switch to a language that has the 24 hour format as default. It would be nice to have date/time configurable separately from the language. Apart from this, it is good to see an application that takes account of language and country specific formats.
As of version 3.0.0 Jet Profiler supports another useful function. MySQL is often hidden behind firewalls, their port is mostly not directly accessible. It is often necessary to manually create an SSH tunnel with port forwarding to reach the MySQL server. Jet Profiler 3.0.0 can now create the SSH tunnel automatically and this great feature makes it way easier to use.
Finally, the best thing about Jet Profiler is that it’s available as a limited free version. You can try it before you buy the full version. The free version has some limitations but they do not stop you from testing out most of the features. Note that the free version does not allow you to save a recording and does not show all the results of the most time consuming queries (some are masked). You have nothing to lose, lots to gain, so go try Jet Profiler!
Written by Gerhard Steinbeis