Correlate Database Monitoring and Traces

Database Monitoring is not supported for this site.

This guide assumes that you have configured Database Monitoring and are using APM. Connecting APM and DBM injects APM trace identifiers into DBM data collection, which allows for correlation of these two data sources. This enables product features showing database information in the APM product, and APM data in the DBM product.

Before you begin

Supported databases
Postgres, MySQL, SQL Server
Supported Agent versions
7.46+
Data privacy
Enabling SQL comment propagation results in potentially confidential data (service names) being stored in the databases which can then be accessed by other third parties that have been granted access to the database.

APM tracer integrations support a Propagation Mode, which controls the amount of information passed from applications to the database.

  • full mode sends full trace information to the database, allowing you to investigate individual traces within DBM. This is the recommended solution for most integrations.
  • service mode sends the service name, allowing you to understand which services are the contributors to database load. This is the only supported mode for SQL Server applications.
  • none mode disables propagation and does not send any information from applications.

SQL Server does not support full propagation mode due to statement caching behavior which could cause performance issues when including full trace context.

DD_DBM_PROPAGATION_MODEPostgresMySQLSQL Server
full
service

Supported application tracers and drivers

LanguageLibrary or FrameworkPostgresMySQLSQL Server
Go: dd-trace-go >= 1.44.0
database/sqlservice mode only
sqlxservice mode only
Java dd-trace-java >= 1.11.0
jdbcservice mode only
Ruby: dd-trace-rb >= 1.8.0
pg
mysql2
Python: dd-trace-py >= 1.9.0
psycopg2
.NET dd-trace-dotnet >= 2.35.0
Npgsql *
MySql.Data *
MySqlConnector *
ADO.NET *service mode only
PHP dd-trace-php >= 0.86.0
pdo
MySQLi
Node.js: dd-trace-js >= 3.17.0
postgres
mysql
mysql2

* CommandType.StoredProcedure not supported

Setup

For the best user experience, ensure the following environment variables are set in your application:

DD_SERVICE=(application name)
DD_ENV=(application environment)
DD_VERSION=(application version)

Update your app dependencies to include dd-trace-go@v1.44.0 or greater:

go get gopkg.in/DataDog/dd-trace-go.v1@v1.44.0

Update your code to import the contrib/database/sql package:

import (
   "database/sql"
   "gopkg.in/DataDog/dd-trace-go.v1/ddtrace/tracer"
   sqltrace "gopkg.in/DataDog/dd-trace-go.v1/contrib/database/sql"
)

Enable the database monitoring propagation feature using one of the following methods:

  1. Env variable: DD_DBM_PROPAGATION_MODE=full

  2. Using code during the driver registration:

    sqltrace.Register("postgres", &pq.Driver{}, sqltrace.WithDBMPropagation(tracer.DBMPropagationModeFull), sqltrace.WithServiceName("my-db-service"))
    
  3. Using code on sqltrace.Open:

    sqltrace.Register("postgres", &pq.Driver{}, sqltrace.WithServiceName("my-db-service"))
    
    db, err := sqltrace.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=disable", sqltrace.WithDBMPropagation(tracer.DBMPropagationModeFull))
    if err != nil {
        log.Fatal(err)
    }
    

Full example:

import (
	"database/sql"
	"gopkg.in/DataDog/dd-trace-go.v1/ddtrace/tracer"
	sqltrace "gopkg.in/DataDog/dd-trace-go.v1/contrib/database/sql"
)

func main() {
	// The first step is to set the dbm propagation mode when registering the driver. Note that this can also
	// be done on sqltrace.Open for more granular control over the feature.
	sqltrace.Register("postgres", &pq.Driver{}, sqltrace.WithDBMPropagation(tracer.DBMPropagationModeFull))

	// Followed by a call to Open.
	db, err := sqltrace.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	// Then, we continue using the database/sql package as we normally would, with tracing.
	rows, err := db.Query("SELECT name FROM users WHERE age=?", 27)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
}

Follow the Java tracing instrumentation instructions and install the 1.11.0 version, or greater, of the Agent.

You must also enable the jdbc-datastore instrumentation.

Enable the database monitoring propagation feature using one of the following methods:

  • Set the system property dd.dbm.propagation.mode=full
  • Set the environment variable DD_DBM_PROPAGATION_MODE=full

Full example:

# Start the Java Agent with the required system properties
java -javaagent:/path/to/dd-java-agent.jar -Ddd.dbm.propagation.mode=full -Ddd.integration.jdbc-datasource.enabled=true -Ddd.service=my-app -Ddd.env=staging -Ddd.version=1.0 -jar path/to/your/app.jar

Test the feature in your application:

public class Application {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager
                    .getConnection("jdbc:postgresql://127.0.0.1/foobar?preferQueryMode=simple", "user", "password");
            Statement stmt = connection.createStatement();
            String sql = "SELECT * FROM foo";
            stmt.execute(sql);
            stmt.close();
            connection.close();
        } catch (SQLException exception) {
            //  exception logic
        }
    }
}

Note: Prepared statements are not supported in full mode, and all JDBC API calls that use prepared statements are automatically downgraded to service mode. Since most Java SQL libraries use prepared statements by default, this means that most Java applications are only able to use service mode.

In your Gemfile, install or update dd-trace-rb to version 1.8.0 or greater:

source 'https://rubygems.org'
gem 'ddtrace', '>= 1.8.0'

# Depends on your usage
gem 'mysql2'
gem 'pg'

Enable the database monitoring propagation feature using one of the following methods:

  1. Env variable: DD_DBM_PROPAGATION_MODE=full

  2. Option comment_propagation (default: ENV['DD_DBM_PROPAGATION_MODE']), for mysql2 or pg:

     Datadog.configure do |c|
     	c.tracing.instrument :mysql2, comment_propagation: 'full'
     	c.tracing.instrument :pg, comment_propagation: 'full'
     end
    

Full example:

require 'mysql2'
require 'ddtrace'

Datadog.configure do |c|
	c.service = 'billing-api'
	c.env = 'production'
	c.version = '1.3-alpha'

	c.tracing.instrument :mysql2, comment_propagation: ENV['DD_DBM_PROPAGATION_MODE']
end

client = Mysql2::Client.new(:host => "localhost", :username => "root")
client.query("SELECT 1;")

Update your app dependencies to include dd-trace-py>=1.9.0:

pip install "ddtrace>=1.9.0"

Install psycopg2 (Note: Connecting DBM and APM is not supported for MySQL clients):

pip install psycopg2

Enable the database monitoring propagation feature by setting the following environment variable:

  • DD_DBM_PROPAGATION_MODE=full

Full example:


import psycopg2

POSTGRES_CONFIG = {
    "host": "127.0.0.1",
    "port": 5432,
    "user": "postgres_user",
    "password": "postgres_password",
    "dbname": "postgres_db_name",
}

# connect to postgres db
conn = psycopg2.connect(**POSTGRES_CONFIG)
cursor = conn.cursor()
# execute sql queries
cursor.execute("select 'blah'")
cursor.executemany("select %s", (("foo",), ("bar",)))
This feature requires automatic instrumentation to be enabled for your .NET service.

Follow the .NET Framework tracing instructions or the .NET Core tracing instructions to install the automatic instrumentation package and enable tracing for your service.

Ensure that you are using a supported client library. For example, Npgsql.

Enable the database monitoring propagation feature by setting the following environment variable:

  • For Postgres and MySQL: DD_DBM_PROPAGATION_MODE=full
  • For SQL Server: DD_DBM_PROPAGATION_MODE=service
This feature requires the tracer extension to be enabled for your PHP service.

Follow the PHP tracing instructions to install the automatic instrumentation package and enable tracing for your service.

Ensure that you are using a supported client library. For example, PDO.

Enable the database monitoring propagation feature by setting the following environment variable:

  • DD_DBM_PROPAGATION_MODE=full

Install or update dd-trace-js to a version greater than 3.17.0 (or 2.30.0 if using end-of-life Node.js version 12):

npm install dd-trace@^3.17.0

Update your code to import and initialize the tracer:

// This line must come before importing any instrumented module.
const tracer = require('dd-trace').init();

Enable the database monitoring propagation feature using one of the following methods:

  1. Env variable: DD_DBM_PROPAGATION_MODE=full

  2. Option dbmPropagationMode (default: ENV['DD_DBM_PROPAGATION_MODE']):

    tracer.use('pg', { dbmPropagationMode: 'full', service: 'my-db-service' })
    

Full example:

const pg = require('pg')
const tracer = require('dd-trace').init()

tracer.use('pg', { dbmPropagationMode: 'full', service: 'my-db-service' })

const client = new pg.Client({
	user: 'postgres',
	password: 'postgres',
	database: 'postgres'
})

client.connect(err => {
	console.error(err);
	process.exit(1);
});

client.query('SELECT $1::text as message', ['Hello world!'], (err, result) => {
	// handle result
})

Explore the APM Connection in DBM

Attribute active database connections to the calling APM services

View active connections to a database broken down by the APM Service they originate from.

Break down active connections for a given host by the upstream APM services making the requests. You can attribute load on a database to individual services to understand which services are most active on the database. Pivot to the most active upstream service’s service page to continue the investigation.

Filter your database hosts by the APM services that call them

Filter your database hosts by the APM services that call them.

Quickly filter the Database List to display only the database hosts that your specific APM services depend on. Easily identify if any of your downstream dependencies have blocking activity that may impact service performance.

View the associated trace for a query sample

Preview the sampled APM trace that the query sample being inspected was generated from.

When viewing a Query Sample in Database Monitoring, if the associated trace has been sampled by APM, you can view the DBM Sample in the context of the APM Trace. This allows you to combine DBM telemetry, including the explain plan and historical performance of the query, alongside the lineage of the span within your infrastructure to understand if a change on the database is responsible for poor application performance.

Explore the DBM Connection in APM

Visualize the downstream database hosts of APM services

Visualize the downstream database hosts that your APM Services depend on from the Service Page.

On the APM page for a given service, view the direct downstream database dependencies of the service as identified by Database Monitoring. Quickly determine if any hosts have disproportionate load that may be caused by noisy neighbors. To view a service’s page, click on the service in the Service Catalog to open a details panel, then click View Service Page in the panel.

Identify potential optimizations using explain plans for database queries in traces

Identify inefficiencies using explain plans for database queries within traces.

View historical performance of similar queries to those executed in your trace, including sampled wait events, average latency, and recently captured explain plans, to contextualize how a query is expected to perform. Determine if the behavior is abnormal and continue the investigation by pivoting to Database Monitoring for additional context about the underlying database hosts.

Further Reading