Profiler
If you are monitoring databases with SolarWinds Observability, you can use the Profiler to display useful information like the most time-consuming and frequently executed queries in your database. The Profiler allows you to organize queries for your monitored database through a variety of options.
What is a profile?
A profile consists of collecting key performance metrics from your host and database with SolarWinds Observability such as Response Time, Throughput, and Top Queries and grouping that data to draw meaningful conclusions about your system's performance. Create data profiles to ensure that the structure of your data is consistent and formatted correctly across your platform.
With the SolarWinds Observability Profiler, you can rank the top 10 to 200 queries through a variety of grouping options. Group the top 200 queries across the last month to see the queries that are having the most load on your database. Create database profiles to assess your data quality, and to see if any new queries are impacting your performance. Create profiles for content discovery, to look into collected data records to see if there are any systemic problems, such as table rows with incorrect formatting. Create profiles to discover how the interconnectedness or relationships of your data, such as the relationships between database tables, or relationships between queries.
Create a Profile
Click Databases > Profiler to open the Profiler. See Databases area overview for more information.
By default, the Profiler displays the Top 10 queries by Total Time for the last hour of monitored data. This helps you find the queries that are consuming the most execution time, which is a useful way to identify the queries that are adding load to your server.
To change the data that is being displayed, click the category drop-down menu(s) at the top of the page and make a selection. Click Apply to display your selection.
Click the Hide Internal Queries checkbox to hide queries executed by SolarWinds Observability for database monitoring.
Profiler chart
The Profiler chart is set to display the Top 10 Queries by Total time for the last hour by default. You can modify the Profiler chart to display varying degrees of collected data from your host and database instance(s) monitored with SolarWinds Observability. For example, if your profiler selection is Queries ranked by Total Time, the chart and accompanying table will display all of the recorded queries by their total execution time across the selected time period, helping you to see which queries were consuming most of your server load within that time period. The top 5 ranked items are displayed in the chart by default, with the remaining items listed in the table below (unchecked).
Click an option from within the chart to display only that selection.
Profiler table
The top 10 ranking items are displayed in the Profiler table by default. Click to clear the checkbox and remove that item from the chart. Click an empty box to add that item to the chart.
Clicking a new box while 5 boxes are checked will remove one checked box and add your selection to the chart.
Click Choose Columns to open the Choose Columns window. Click the columns you want to add to the Profiler display and then select Apply to save your changes. Click any added column in the Profiler table to sort by that column.
Other category options
Use the Profiler to rank other categories such as Query Verbs, Databases, or MySQL Processlist Queries by a variety of available ranking options. Click the Queries and Total Time drop-down menus, select the desired ranking options, and then click Apply to display your ranked selection. See the table below for the full list of ranking categories. For example, you could set the Profiler chart to display the Top 20 Databases by Count over the last week to see which of your databases is executing the most queries. This could help you to identify the databases that need more attention or refinement. See the table below for a full list of ranking categories.
| Category | Description |
|---|---|
| Clients | Query data grouped by the Client IP address. |
| Databases | Query and size data grouped by database name. |
| InnoDB Mutexes | Requests, and total time waiting, for each InnoDB mutex event name. |
| MongoDB Current Op | The number of in-progress operations for the MongoDB database instance from the currentOp command. |
| MongoDB Current Op Callers | Data for IP addresses of database clients, from the currentOp command. |
| MongoDB Current Op Queries | The number of queries observed from the currentOp command. |
| MongoDB Current Op Users | The number of users observed from the currentOp command. |
| MongoDB Locks | The number of observed locks for the MongoDB database from db.serverStatus(). |
| MySQL Processlist Callers | Total connection count and time for client IP addresses, from the MySQL PROCESSLIST. |
| MySQL Processlist Commands | Total thread command count and time, from the PROCESSLIST. |
| MYSQL Processlist Queries | Total connection count and time for client IP addresses from the MySQL PROCESSLIST. |
|
MySQL Processlist States |
Thread states from the PROCESSLIST. |
| MySQL Processlist Users | Connection data grouped by username, from the PROCESSLIST. |
| PostgreSQL Locks | Data grouped by locktype from PostgreSQL’s pg.locks view. |
| PostgreSQL Stat Activity | Query digest data captured from pg_stat_activity. |
| PostgreSQL Stat Activity Users | Connection data grouped by username, from pg_stat_activity. |
| Queries | Query digest data captured by SolarWinds Observability. The profiler ranks by the Queries category by default. |
| Query Tags | Query data grouped by query tags. |
| Query Verbs | Query data grouped by query verb, such as SELECT or INSERT. |
| SQL Server Processlist Callers | Total connection count and time for client IP addresses, from the SQL Server PROCESSLIST. |
| SQL Server Processlist Queries | Total connection count and time for SQL Server PROCESSLISTqueries. |
| SQL Server Processlist States | Thread states for SQL Server PROCESSLIST queries. |
| SQL Server Processlist Users | Users associated with SQL Server PROCESSLISTquery execution. |
| Tables / Collections | Table size statistics. |
| Users | Query data grouped by database username. |
Rank by query tags
Use the Profiler to rank your recorded queries by query tag. Click Query Tags from the drop-down menu and click Apply to display your selection by query tags. You can get even more granular results by entering a Tag Name, or Tag Value, to search for specific results.
Ranking by query tags can help you to identify the queries that are affecting the load across your database. For example, consider the following scenario. You have tagged your queries with their app name, and have recorded those queries with SolarWinds Observability. You then use the Profiler to rank the queries across your environment by the top 10 query tags by total time, and filter by that tag name. In this scenario, you can see all the queries with the given app name and how they rank by total time to determine which queries are consuming more of your database's resources.
Compare profiler data
Click the Compare to toggle to compare the currently displayed profiling query data and the query data that was recorded before the selected time range. You can compare currently recorded query data to recorded data from 30 minutes ago, 1 hour ago, 3 hours ago, 6 hours ago, 1 day ago, 3 days ago, 1 week ago, or 1 month ago.
For example, if you are profiling the top 10 queries by total time for the last day, clicking Compare to with the default selection of 1 week ago will also display the top 10 queries by total time for the previous 7 days, allowing you to easily compare the data, and any changes over time. The numbered query represents the query from the currently selected time range, and the query beneath the numbered query represents the query 7 days before the selected time range.
Clicking the Compare to toggle will always display the currently selected time range data, and data for the selected time ago.
Export profiler data
Once you have set up your desired ranking, you can download a .csv file to share your Profiler results with other stakeholders. Click Export to CSV to download your profiler ranking.
Rank options
The Profiler allows you to rank the top 10 to 200 of a selected category by a variety of ranking options. The following ranking options are available:
| Option | Description |
|---|---|
| Affected Rows | Ranks the selected number of queries by the number of rows that were affected during query execution. |
| Block Read Time | Ranks the selected number of queries by the amount of time it took to read a block during query execution. |
| Block Write Time | Ranks the selected number of queries by the amount of time it took to write a block during query execution. |
| Created Temp Disk Tables | Ranks the selected number of queries by the number of created temp disk tables during query execution. |
| Created Temp Tables | Ranks the selected number of queries by the number of created temp tables during query execution. |
| Count | Ranks the selected number of queries by their total execution count for the selected time period. |
| Data Free | The amount of available data across the selected time period and the change over time. |
| Data Size | The amount of consumed data across the selected time period and the change over time. |
| Errors | Ranks the selected number of queries by their total errors for the selected time period. |
| Index Size | Ranks the selected number of queries by the amount of data consumed by the index across the selected time period. |
| Local Blocks Dirtied | Ranks the selected number of queries by the number of unmodified blocks that were changed during query execution. |
| Local Blocks Hit | Ranks the selected number of queries by the number of local blocks hit during query execution. |
|
Local Blocks Read |
Ranks the selected number of queries by the number of local blocks read during query execution. |
| Local Blocks Written | Ranks the selected number of queries by the number of local blocks written during query execution. |
| Lock Time | Ranks the selected number of queries by the time each query waited to get the lock that was required to execute. |
| Missing Indexes | Ranks the selected number of queries by the number of missing indexes during query execution. |
| Rows Examined | Ranks the selected number of queries by the total rows examined for the selected time period. |
| Rows Sent | Ranks the selected number of queries by the total rows sent during their execution for the selected time period. |
| Select Full Join | Ranks the selected number of queries by the number of Full_Joins during query execution. |
| Select Scan | Ranks the selected number of queries by the number of table scans or index scans that took place during query execution. |
| Slow Queries | Ranks the selected number of queries by their execution time. |
| Shared Blocks Dirtied | Ranks the selected number of queries by the number of blocks from tables and indexes that took place during query execution. |
| Shared Blocks Read | Ranks the selected number of queries by the number of blocks from tables and indexes read from the disk during query execution. |
| Shared Blocks Written | Ranks the selected number of queries by the number of blocks that were removed from the cache during query execution. |
| Sort Rows | Ranks the selected number of queries by the number of rows sorted during query execution. |
| Sort Scan | Ranks the selected number of queries by the number of rows scanned during query execution. |
| Temp Blocks Read | Ranks the selected number of queries by the number of temp block reads during query execution. |
| Temp Blocks Written | Ranks the selected number of queries by the number of temp block writes during query execution. |
| Total Time | Ranks the selected number of queries by their total execution time for the selected time period. Total execution is represented as a percentage of the selected time period. |
| Total Size | Ranks the selected number of queries by the amount of memory that the database consumed across the selected time period. |
| Warnings | Ranks the selected number of queries by their total warnings during execution for the selected time period. |
Additional columns
The following columns are available through the Column Chooser:
| Column | Description |
|---|---|
| Action | Displays the number of actions executed for each of the top selected queries. |
| Affected Rows | Displays the number of rows that were affected during query execution for each of the top selected queries. |
| Avg. Latency | Displays the average query latency for each of the top selected queries. |
| Block Read Time | Displays the amount of time it took to read a block during query execution for each of the top selected queries. |
| Block Write Time | Displays the amount of time it took to write a block during query execution for each of the top selected queries. |
| Count | Displays the amount of times the query executed for each of the top selected queries. |
| Created Temp Disk Tables | Displays the number of created temp disk tables during query execution for each of the top selected queries. |
| Created Temp Tables | Displays the number of created temp tables during query execution for each of the top selected queries. |
| Data Free | Displays the amount of free data for each of the top selected queries. |
| Data Size | Displays the amount of memory consumed by each of the top selected queries. |
| Errors | Displays the number of errors for each of the top selected queries. |
| First Seen | Displays the time when the query was first recorded by the SolarWinds Observability Agent. |
| Index Size | Displays the amount of data consumed by the index. |
| Locks | Displays the number of locks in the MySQL Processlist queries. |
| Locked | Displays the number of locked queries in the MySQL Processlist queries. |
| Local Blocks Dirtied | Displays the number of unmodified blocks that were changed during query execution for each of the top selected queries. |
| Local Blocks Hit | Displays the number of local blocks hit during query execution for each of the top selected queries. |
| Local Blocks Read | Displays the number of local blocks read during query execution for each of the top selected queries. |
| Local Blocks Written | Displays the number of local blocks written during query execution for each of the top selected queries. |
| Lock Time | Displays the time each query waited to get the lock that was required to execute for each of the top selected queries. |
| Latency p99 | Displays the amount of query latency in the 99th percentile value for each of the top selected queries. |
| Missing Indexes | Displays the number of missing indexes during query execution for each of the top selected queries. |
| Notifications | Displays the number of associated notifications for each of the top selected queries. |
| Rows Examined | Displays the number of rows examined for each of the top selected queries. |
| Rows Sent | Displays the number of rows sent for each of the top selected queries. |
| Shared Blocks Dirtied | Displays the number of shared dirtied blocks that took place during query execution for each of the top selected queries. |
| Shared Blocks Hit | Displays the number of blocks from tables and indexes for each of the top selected queries. |
| Shared Blocks Read | Displays the number of blocks from tables and indexes that took place during query execution for each of the top selected queries. |
| Shared Blocks Written | Displays the selected number of queries by the number of blocks that were removed from the cache during query execution. |
| Select Full Join | Displays the selected number of queries by the number of Full_Joins during query execution. |
| Select Scan | Displays the selected number of queries by the number of table scans or index scans that took place during query execution. |
| Sort Rows | Displays the number of rows sorted during query execution for each of the top selected queries. |
| Sort Scan | Displays the number of rows scanned during query execution for each of the top selected queries. |
| Temp Blocks Read | Displays the number of temp block reads during query execution for each of the top selected queries. |
| Temp Blocks Written | Displays the number of temp block writes during query execution for each of the top selected queries. |
| Total Time | Displays the total execution time for the selected time period for each of the top selected queries. |
| Warnings | Displays the number of associated warnings for each of the top selected queries. |