DPM releases Query tags

Database Workload Profiling for Developers

Ever wanted to filter your workload profile by user, app, or software module? SolarWindsRegistered 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.

MarkusS_4-1588186977321.png

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.

MarkusS_5-1588186993228.png

Now imagine someone asks how many queries in total the guest_book app executed over the last hour. The Profiler has the answer.

MarkusS_6-1588187011976.png

 

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

MySQL

SELECT /* app=mobile */ ...

PostgreSQL

SELECT /* app=mobile */ ...

MongoDB

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.

MarkusS_7-1588187028872.png

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.

Anonymous