Database Workload Profiling for Developers
Ever wanted to filter your workload profile by user, app, or software module? SolarWinds® Database Performance Monitor (DPM) is happy to announce the new Query tags feature.
Query tags are key-value pairs embedded in query comments. This gives added visibility into your query workload in two ways:
● See overall performance by tag, e.g., the total number of query executions and total query time
● Filter by tag, seeing only query families where an execution had the specified tag
WHAT’S THE BENEFIT?
● Troubleshooting: Quickly identify bad actors such as app hosts or users
● Code Deploy: More easily isolate changes after a code deploy
● Microservices: Drill in to the queries affecting your service only
Suppose your databases receive requests from multiple applications or microservices and you want to profile the workload from a single source. Using the Profiler to Rank Queries by Total Time, the results can be filtered to see just those queries with the tag app=guest_book. Note that if multiple applications run similar queries, the displayed metrics are not specific to the filtered tag.
How can you find which tags are associated with a query? The query detail page shows a complete list of tags just above the performance overview.
Now imagine someone asks how many queries in total the guest_book app executed over the last hour. The Profiler has the answer.
How Do You Implement Query Tagging?
The exact syntax for query comments is database specific: and implement nested block comments, while employs a $comment meta-operator.
The table below illustrates our example for each of those databases:
|
Database |
Example |
|
SELECT /* app=mobile */ ... |
|
|
SELECT /* app=mobile */ ... |
|
|
db.collection.find( { <query> } ).comment(‘app=mobile’ ) |
The default delimiters for query tags are = and a space, but this is configurable through the tag-delimiters agent option or via the Settings UI.
Using Rails? Try Marginalia to attach comments to your ActiveRecord queries. By default, it adds the application, controller, and action names as a comment at the end of each query.
One More Thing...
What if you can’t use comments, such as off-host installs where query samples are taken from the performance_schema or pg_stat_statements? DPM can automatically generate some key=value pairs from the connection data provided by those extensions, so look out for db=database_name, origin=ip_address and user=username in those cases. The availability of these auto-generated tags differs depending on your technology and installation method.
Dig deeper into the technical details via our documentation here.
