The Profiler page shows queries that execute on the selected systems during the selected time range. It is a powerful feature for ranking and filtering query activity on your servers, and can instantly show you queries that need attention.
By default, the Profiler groups similar queries together, and shows the total accumulated time for each family of queries. This helps you find out which queries consume the most execution time, which is a useful way to find queries that add load to the servers or make your application respond more slowly to requests. There are several items of interest in the image below, highlighted with red (click to magnify):
- The top navigation bar has filters and menus for changing what is displayed in the Profiler table.
- You can choose to rank queries, query verbs (e.g.
UPDATE), query tags, databases, users and client hosts.
- You can rank queries by total time, execution count, number of warnings raised by the query, and many more criteria. We continually add new options to this menu control.
- You can choose the number of queries to show, and filter queries by text contained within them.
- You can choose to rank queries, query verbs (e.g.
- Compare lets you compare queries across two selected time ranges. This is useful for determining how queries have changed over time.
- The main table of results is sortable by clicking on the column headers. The main column displays a sparkline of the metric by which you ranked (VividCortex captures 1-second resolution for query metrics, as usual). In the example, the sparkline shows how the query’s total accumulated time varies during the selected time range. You can hover over the sparklines to reveal the values at the desired point in time. Additional columns are shown to the right. Columns contain values and blue shaded backgrounds. The shaded area represents the proportion of that column’s sum; hover over a cell for details.
- Hovering over a query highlights its row in the table. Queries may have notifications of important information. Clicking on any query will navigate to the query details page.
Items in the Profiler
The main area of the UI is very information-dense, so here is a deeper explanation of each item you might see, depending on what you’ve selected in the options panel at the top:
- Notifications (the bell icon) is the number of errors and/or warnings detected for this family of queries. Navigate to the query details for more information.
- Total Time is the sum of response times for the family of queries over the selected timespan.
- Count is the number of times the query executed.
- Average Latency is the mean response time for the query over the selected timespan. It is derived as the Total Time divided by the count.
- Poor Indexes and Missing Indexes are the number of times a query executed with the
No_index_usedflags, respectively. For details, please see the MySQL docs. These imply that the server couldn’t find a suitable index to optimize the query, or the one available was sub-optimal.
- Slow Queries is the number of times the query’s execution time was longer than the
long_query_timethreshold, which defaults to 10 seconds. It usually does not correspond exactly to queries in the slow query log, because queries may be logged to the slow query log for a number of other reasons as well as just exceeding the threshold.
- CPU Time, IO Ops, and other columns are computed estimates, not exact measures. See Computed Columns at the bottom of this page.
This view provides a quick way to answer the question “what changed?” when a server starts to behave differently, or to look at before-and-after behavior of your database workload after a change to an application or an event such as a product launch. The image below shows a one-hour view of the profiler compared to an hour prior.
You may see more rows than you expected in the results. For example, if you compare the top five queries from today with a week ago, you may get up to ten queries returned. This is because we show the top queries from each time period; if the top queries from each time period are the same, you’ll get those five returned. However, if some queries which were in the top five are no longer in the top five, we’ll show those as well in order for you to get a complete view of anything that has changed in your database.
Clicking on a query in the profiler will display the query details page, which includes key performance metrics, all notifications and sample executions that have been captured.
You can click the “Choose Columns” control at the top right of the Profiler to choose which columns you see in the main table. Some of the columns are quantities that are not directly measurable, such as CPU or I/O consumed by each query. VividCortex uses a unique patent-pending regression technique to compute the relationship between query metrics and arbitrary metrics of interest on the database or operating system. This computation does not execute in our agents; it is performed on our systems or in-browser, depending on the scenario.
Because the columns are computed through statistical methods, there is some amount of uncertainty associated with them. The exact level of accuracy depends on factors such as the number of samples, the strength of the discovered relationship between metrics, and how mixed your workload is (or how many and different servers you’re looking at in a single view).