It was few days back I came to know about Jet Profiler an excellent tool for profiling MySQL queries. Thanks Mr. Bjorn Melinder for offering me a professional version for free. Jet Profiler is a tool for monitoring and analyzing performance of MySQL server. It will give a graphical representation of database activities in real time. In a MySQL server usually there will be a number of users and databases. While doing performance optimization it will be useful to know the databases or tables that have more load, so that they can be given more attention or move to another server. Using Jet Profiler we can record the server activities for a duration of time with a particular polling interval. It will show these details in different columns and also in a real time graph. Some of the interesting results in Jet Profiler are
1 - Top Queries
It will list heaviest queries during the selected time frame. This table will show which queries are mostly observed. Often the performance bottlenecks in server will be due to bad quries. Once we got that queries we can analize for them to improve them. We can explain each queries from this table and check the optimized version from there itself (this is really cool !!). It will also show a rating good to very bad/ very slow.
2 - Top Users
Usually a database server will have a number of users connected to it. So it’s important to understand the users who are consuming more resources. This table will show a comparison of different users of the server.
3 - Top Tables
List of heaviest tables in a given time frame. Identifying tables with high table will help us to decide on table level optimization like partitioning.
4 - Top Schemas
Lists schemas that have heavy load.The graph can be customized to select the metrics we are interested in. There are some presets like Tables, Threads, MyISAM and InnoDB cache, etc. as well.
It's a very useful tool for database administrators as well as developers. Administrators can make use of it for analyzing and monitoring database server where as developers can use it for optimizing Queries.To know more about Jet Profiler visit their site http://www.jetprofiler.com/