Profiler

The Profiler allows you to see the most time-consuming and frequently executed queries in your database, in addition to a large variety of other data VividCortex collects.

Profiler

By default, the Profiler ranks by “Queries,” grouping queries together by digest, and shows the total accumulated time for each digest 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.

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.

To change what category of data is being displayed, select an option from the drop down in the navigation bar:

Profiler

Here are all of the categories that can be shown and ranked by the Profiler. Some of these may not be available, depending on your database technology and installation type:

Category Description Can be ranked by…
Databases Query and size data grouped by database name Many metrics, including: Affected Rows, Errors, Data Size, Slow Queries, Warnings, etc.
Hosts Query data grouped by IP address of client Affected Rows, Count, Errors, Missing Indexes, Slow Queries, Total Time, Warnings
InnoDB Mutexes Requests, and total time waiting, for each InnoDB mutex event name. Requires instrumentation Count, Wait Time
Network Socket Ports Count of connections on each port Count
Network Socket States Count of sockets in a given connection state Count
Processes Metrics about processes running on the OS host Bytes Read/Written, Sys Calls Read/Write, CPU, etc.
Queries Query digest data captured from either TCP traffic or the MySQL/PostgreSQL query digest summary table Many metrics, including: Affected Rows, Count, Errors, Failed Rules, Missing Indexes, Poor Indexes, Slow, Total Time, etc.
Query Verbs Query data grouped by query verb, such as SELECT or INSERT Affected Rows, Count, Errors, Missing Indexes, Slow Queries, Total Time, Warnings
Query Tags Query data grouped by query tag=value combination Affected Rows, Count, Errors, Missing Indexes, Slow Queries, Total Time, Warnings
MongoDB Current Op MongoDB’s operation states, from the currentOp command Blocked, Count, Total Time
MongoDB Current Op Callers Data for IP addresses of database clients, from the currentOp command Blocked, Count, Total Time
MongoDB Current Op Queries Queries captured from the currentOp command Blocked, Count, Total Time
MongoDB Locks Lock utilization information, from db.serverStatus() Acquire Count, Acquire Wait Count, Deadlock Count, Time Acquiring
MySQL Processlist Callers Total connection count and time for client IP addresses, from the MySQL PROCESSLIST Count, Total Time
MySQL Processlist Commands Total thread command count and time, from the PROCESSLIST Count, Total Time
MySQL Processlist Queries Query digest data captured from the PROCESSLIST Count, Locks, Total Time
MySQL Processlist States Thread states from the PROCESSLIST Total Time, Count
MySQL Processlist Users Connection data grouped by username, from the PROCESSLIST Total Time, Count
PostgreSQL Activity Query digest data captured from pg_stat_activity Count, Locks, Total Time
PostgreSQL Activity Users Connection data grouped by username, from pg_stat_activity Count, Total Time
PostgreSQL Locks Data grouped by locktype from PostgreSQL’s pg.locks view Count, Wait Time
Tables / Collections Table size statistics Data Free, Data Size, Index Size, Row Count, Total Size
Users Query data grouped by database username Affected Rows, Count, Errors, Missing Indexes, Slow Queries, Total Time, Warnings

Query Searching

When ranking Queries, it is possible to search for a specific query digest (or part of a digest).

To search, just enter the search text in the Filter by Query Text box:

Profiler

Two special characters are accepted:

  • * will match any number of any characters. For example, select * customers will find any query which contains the word select followed by the word customers anywhere in the digest.
  • ^ will match the beginning of the digest. For example, ^insert into customers will find all digests which begin with insert into customers. As in this example, this is useful to find all queries with a specific query verb which affect a certain table.

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_good_index_used or No_index_used flags, 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_time threshold, 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.

Compare

Compare lets you compare data across two selected time ranges. This is useful for determining how queries (or any of the other categories) have changed over time. This view provides a quick way to answer the question “what’s different?” 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.

Profiler

To enable this mode, select the “Compare” toggle in the navigation bar:

Profiler

Then select a recent timeframe you want to compare to some time in the past, such as the past hour:

Profiler

Then select the the time in the past you wish to compare against, such as the day before, by clicking one of the seven time options underneath the slider:

Profiler


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.