Enable query tagging with Sqlcommenter to understand application impact on database performance

Cloud SQL Insights launched earlier this year, giving developers a tool that helps detect, diagnose, and prevent query performance problems for PostgreSQL for Cloud SQL databases. Out of the box, the Query Insights dashboard provides database load graphs, detailed query performance, and built-in query plans. Users can boost the power of Insights by enabling query tagging via an open source library called Sqlcommenter. In this post, we'll share the benefits of using query tagging, what Sqlcommenter is, and two ways you can enable it: using a supported ORM or manually by following the Sqlcommenter spec.

Application-centric database monitoring

One of the most powerful features of Insights is its ability to provide application-centric database monitoring and simplify application troubleshooting. To get this added functionality, you need to enable and set up query tagging. Query tagging augments your SQL statements with information about your application; you could tag queries by business logic, microservice, route, controller, etc. For example, using payment, inventory, business analytics, or shipping tags. You can then find the queries and database load that the various services create. This may help you find unexpected events, such as spikes for a business analytics tag at certain times of day, or you might see abnormal growth for a payment service trending over the previous week. Sounds great, but how does it work?

Overview of Sqlcommenter

Insights uses an open source library called Sqlcommenter, which allows application information related to your MVC framework to be sent to the database along with queries as a SQL comment. Sqlcommenter has recently merged with OpenTelemetry to extend the vision of OpenTelemetry to databases and help grow the observability ecosystem. In addition to application information, Sqlcommenter allows OpenTelemetry trace context information to be propagated to the database, making it possible to find correlations between application traces and database query plans. Developers can view this observability information directly in database logs, the information can be integrated into other tools like Cloud SQL Insights or APM tools such as those from Datadog, Dynatrace, and Splunk.

Sqlcommenter supports several ORMs and frameworks including Django, SQLAlchemy Ruby on Rails, Knex.js, Sequelize.js, Spring, Hibernate, and more. If you are using one of these supported ORMs, the application tags are automatically created for you, with little to no change to your application code. If you are not using an ORM, you can manually add Sqlcommenter tags to your SQL queries. Let's look at how to enable Sqlcommenter for query tagging in one of the supported languages and frameworks and how to manually tag queries if you aren't using an ORM.

Using Python and SQLAlchemy with Sqlcommenter

Each supported ORM has documentation and examples to show you how to install and enable it. Here's the process you would follow to set up Sqlcommenter in a Python application that uses SQLAlchemy.

Run the following to install Sqlcommenter with the option to record OpenTelemetry trace context:

pip3 install google-cloud-sqlcommenter[opentelemetry]

Then, in your application code, attach the event listener shown below to the before_cursor_execute event of the database engine. This will ensure that all queries that are executed with that engine will have the SQL comments included. Once you've installed Sqlcommenter and added the code below, no further code changes are needed to your application.

import sqlalchemy
from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory

engine = sqlalchemy.create_engine(...)
listener = BeforeExecuteFactory(
    with_db_driver=True,
    with_db_framework=True,
    with_opentelemetry=True,
)
sqlalchemy.event.listen(engine, 'before_cursor_execute', listener, retval=True)
engine.execute(...) # comment will be added before execution

The output in the database logs would look something like this:

2021-08-28 02:33:25.287 PDT [57302] LOG:  statement: SELECT candidate, time_cast FROM
Votes ORDER BY time_cast DESC /*db_driver='pg8000',framework='sqlalchemy%3A1.4.22',controller='index',route='/',
traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',
tracestate='congo%%3Dt61rcWkgMzE%%2Crojo%%3D00f067aa0ba902b7'*/

For other ORMs and frameworks, you can find links to the instructions for setting up Sqlcommenter here.

Manually tagging queries using the Sqlcommenter spec

If you're not using one of the supported ORMs, you can add comments to your SQL statements manually by following the Sqlcommenter specification. The Sqlcommenter algorithm adds a comment containing serialized key value pairs to a SQL statement. An overview of the algorithm is below; to understand the different pieces of the algorithm in more depth, see the algorithm documentation.

sql_commenter(sql, attributes):
    if contains_sql_comment(sql):
        return sql # DO NOT mutate a statement with an already present comment.

    serialized_key_value_pairs := []

    for each attribute in attributes:
        serialized := serialize_key_value_pair(attribute)
        if serialized:
            serialized_key_value_pairs.append(serialized)

    sorted := sort(serialized_key_value_pairs)
    concatenated := concatenate(sorted)
    final := affix_comment(sql, concatenated)

    return final

The following keys are supported for use in Cloud SQL Insights: action, controller, framework, route, application, db_driver, traceparent, and tracestate. However, if you're not using Cloud SQL Insights, you can include any other custom keys as well.

The following information passed to the Sqlcommenter algorithm:

sql_commenter('SELECT * FROM POSTS, [
traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01',
     route='posts',
     action='index',
     controller='posts',
    application='SqlcommenterBlogDemo'
])

Results in this output:

SELECT * FROM POSTS/*action='index',application='SqlcommenterBlogDemo',
controller='posts',route='/posts',traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01'*/

Next steps

If you're running PostgreSQL for Cloud SQL databases, and you haven't checked out Insights yet, now is the time! If you're using one of the ORMs or frameworks that Sqlcommenter supports, you can find instructions in the documentation for how to enable it for each language and framework, along with some sample applications in the GitHub repo.