Instrumenting the Database client with Elastic APM

After we set up Elastic APM to log our request transaction, the following thing we wish to instrument are the SQL queries going to our database endpoint. Elastic APM provides instrumentation that wraps the database/sql driver, which produces an *sql.DB.

Extending DB connection

We already planned to produce a *sqlx.DB for this eventuality with the Connector field function in db.ConnectionOptions:

// Connector is an optional parameter to produce our
// own *sql.DB, which is then wrapped in *sqlx.DB
Connector func(context.Context, Credentials) (*sql.DB, error)

We can now just modify the Connect() function in the DB package, to extend it with an APM connector.

// Connect connects to a database and produces the handle for injection
func Connect(ctx context.Context) (*sqlx.DB, error) {
	options := ConnectionOptions{
		Connector: func(ctx context.Context, credentials Credentials) (*sql.DB, error) {
			db, err := apmsql.Open(credentials.Driver, credentials.DSN)
			if err != nil {
				return nil, err
			}
			if err = db.PingContext(ctx); err != nil {
				db.Close()
				return nil, err
			}
			return db, nil
		},
	}
	options.Credentials.DSN = os.Getenv("DB_DSN")
	options.Credentials.Driver = os.Getenv("DB_DRIVER")
	return ConnectWithRetry(ctx, options)
}

There are about three notable parts to this change. First, by default we were using sqlx.Connect to create the database handle and issue a Ping request and error out. As this is an sqlx addon, we need to re-implement some functionality here by calling Open, followed with PingContext.

What apmsql does under the scenes is to produce a sql.Driver interface, that wraps the original driver for the drivers you’re already familiar with. The Elastic APM Go Agent provides the following packages to register popular SQL drivers:

• go.elastic.co/apm/module/apmsql/pq (github.com/lib/pq)
• go.elastic.co/apm/module/apmsql/mysql (github.com/go-sql-driver/mysql)
• go.elastic.co/apm/module/apmsql/sqlite3 (github.com/mattn/go-sqlite3)

Verifying it’s working

Each SQL query issued will produce what is called a “Span”. Like the transactions, the APM client sends the query metadata and duration, and nests it under the main request transaction. This way you can see particularly which queries have executed within a given request.

Let’s rebuild our service with make and make docker, and run our development stack with docker-compose up -d. Let’s re-run some requests with curl, and navigate to the new transactions in the APM interface.

The query shows up twice, the first one is the prepare of the statement, and the second one is the exec of the statement. We can click the span and get a detailed view.

The particular query which ran against the database is logged in it’s normalized form. It does not include the actual parameters of the query being inserted.

And finally, we can see the stack trace of the query being executed. We can know the path of the application call, all the way to the calling of the database function. It’s useful to have this but I fear that perhaps it’s not the most quick, as stack traces in Go are notably slow, and need to be avoided if you’re working on performance.

APM makes provisions for tuning this - a stack trace is only recorded if the span duration is longer than 5ms, and you can tune that with an environment variable:

ELASTIC_APM_SPAN_FRAMES_MIN_DURATION=5ms

Check out other possible configuration options for the APM agent on the agent configuration page.

Everything in your service stays the same, now there is just the APM agent under the hood, sending query metrics to Elastic APM as we wanted. We can use this data to analyze the query performance and improve our application significantly.

Like, I can’t believe that query took 12ms. We have not tuned the database AT ALL.

This article is part of a Advent of Go Microservices book. I’ll be publishing one article each day leading up to christmas. Please consider buying the ebook to support my writing, and reach out to me with feedback which can make the articles more useful for you.

All the articles from the series are listed on the advent2019 tag.

While I have you here...

It would be great if you buy one of my books:

• Go with Databases
• Advent of Go Microservices
• API Foundations in Go
• 12 Factor Apps with Docker and Go

Feel free to send me an email if you want to book my time for consultancy/freelance services. I'm great at APIs, Go, Docker, VueJS and scaling services, among many other things.