ocsql

README

OpenCensus SQL database driver wrapper.

Add an ocsql wrapper to your existing database code to instrument the interactions with the database.

installation

go get -u contrib.go.opencensus.io/integrations/ocsql

initialize

To use ocsql with your application, register an ocsql wrapper of a database driver as shown below.

Example:

import (
    _ "github.com/mattn/go-sqlite3"
    "contrib.go.opencensus.io/integrations/ocsql"
)

var (
    driverName string
    err        error
    db         *sql.DB
)

// Register our ocsql wrapper for the provided SQLite3 driver.
driverName, err = ocsql.Register("sqlite3", ocsql.WithAllTraceOptions(), ocsql.WithInstanceName("resources"))
if err != nil {
    log.Fatalf("unable to register our ocsql driver: %v\n", err)
}

// Connect to a SQLite3 database using the ocsql driver wrapper.
db, err = sql.Open(driverName, "resource.db")

A more explicit and alternative way to bootstrap the ocsql wrapper exists as shown below. This will only work if the actual database driver has its driver implementation exported.

Example:

import (
    sqlite3 "github.com/mattn/go-sqlite3"
    "contrib.go.opencensus.io/integrations/ocsql"
)

var (
    driver driver.Driver
    err    error
    db     *sql.DB
)

// Explicitly wrap the SQLite3 driver with ocsql.
driver = ocsql.Wrap(&sqlite3.SQLiteDriver{})

// Register our ocsql wrapper as a database driver.
sql.Register("ocsql-sqlite3", driver)

// Connect to a SQLite3 database using the ocsql driver wrapper.
db, err = sql.Open("ocsql-sqlite3", "resource.db")

Projects providing their own abstractions on top of database/sql/driver can also wrap an existing driver.Conn interface directly with ocsql.

Example:

import "contrib.go.opencensus.io/integrations/ocsql"

func GetConn(...) driver.Conn {
    // Create custom driver.Conn.
    conn := initializeConn(...)

    // Wrap with ocsql.
    return ocsql.WrapConn(conn, ocsql.WithAllTraceOptions())    
}

Finally database drivers that support the new (Go 1.10+) driver.Connector interface can be wrapped directly by ocsql without the need for ocsql to register a driver.Driver.

Example:

import(
    "contrib.go.opencensus.io/integrations/ocsql"
    "github.com/lib/pq"
)

var (
    connector driver.Connector
    err       error
    db        *sql.DB
)

// Get a database driver.Connector for a fixed configuration.
connector, err = pq.NewConnector("postgres://user:passt@host:5432/db")
if err != nil {
    log.Fatalf("unable to create our postgres connector: %v\n", err)
}

// Wrap the driver.Connector with ocsql.
connector = ocsql.WrapConnector(connector, ocsql.WithAllTraceOptions())

// Use the wrapped driver.Connector.
db = sql.OpenDB(connector)

metrics

Next to tracing, ocsql also supports OpenCensus stats. To record call stats, register the available views or create your own using the provided Measures.

// Register default views.
ocsql.RegisterAllViews()

From Go 1.11 and up, ocsql also has the ability to record database connection pool details. Use the RecordStats function and provide a *sql.DB to record details on, as well as the required record interval.

// Register default views.
ocsql.RegisterAllViews()

// Connect to a SQLite3 database using the ocsql driver wrapper.
db, err = sql.Open("ocsql-sqlite3", "resource.db")

// Record DB stats every 5 seconds until we exit.
defer ocsql.RecordStats(db, 5 * time.Second)()

Recorded metrics

Metric	Search suffix	Additional tags
Number of Calls	"go.sql/client/calls"	"method", "error", "status"
Latency in milliseconds	"go.sql/client/latency"	"method", "error", "status"

If using RecordStats:

Metric	Search suffix
Number of open connections	"go.sql/db/connections/open"
Number of idle connections	"go.sql/db/connections/idle"
Number of active connections	"go.sql/db/connections/active"
Total number of connections waited for	"go.sql/db/connections/wait_count"
Total time blocked waiting for new connections	"go.sql/db/connections/wait_duration"
Total number of closed connections by SetMaxIdleConns	"go.sql/db/connections/idle_close_count"
Total number of closed connections by SetConnMaxLifetime	"go.sql/db/connections/lifetime_close_count"

jmoiron/sqlx

If using the sqlx library with named queries you will need to use the sqlx.NewDb function to wrap an existing *sql.DB connection. Do not use the sqlx.Open and sqlx.Connect methods. sqlx uses the driver name to figure out which database is being used. It uses this knowledge to convert named queries to the correct bind type (dollar sign, question mark) if named queries are not supported natively by the database. Since ocsql creates a new driver name it will not be recognized by sqlx and named queries will fail.

Use one of the above methods to first create a *sql.DB connection and then create a *sqlx.DB connection by wrapping the *sql.DB like this:

    // Register our ocsql wrapper for the provided Postgres driver.
    driverName, err := ocsql.Register("postgres", ocsql.WithAllTraceOptions())
    if err != nil { ... }

    // Connect to a Postgres database using the ocsql driver wrapper.
    db, err := sql.Open(driverName, "postgres://localhost:5432/my_database")
    if err != nil { ... }

    // Wrap our *sql.DB with sqlx. use the original db driver name!!!
    dbx := sqlx.NewDB(db, "postgres")

context

To really take advantage of ocsql, all database calls should be made using the *Context methods. Failing to do so will result in many orphaned ocsql traces if the AllowRoot TraceOption is set to true. By default AllowRoot is disabled and will result in ocsql not tracing the database calls if context or parent spans are missing.

Old	New
*DB.Begin	*DB.BeginTx
*DB.Exec	*DB.ExecContext
*DB.Ping	*DB.PingContext
*DB.Prepare	*DB.PrepareContext
*DB.Query	*DB.QueryContext
*DB.QueryRow	*DB.QueryRowContext

*Stmt.Exec	*Stmt.ExecContext
*Stmt.Query	*Stmt.QueryContext
*Stmt.QueryRow	*Stmt.QueryRowContext

*Tx.Exec	*Tx.ExecContext
*Tx.Prepare	*Tx.PrepareContext
*Tx.Query	*Tx.QueryContext
*Tx.QueryRow	*Tx.QueryRowContext

Example:


func (s *svc) GetDevice(ctx context.Context, id int) (*Device, error) {
    // Assume we have instrumented our service transports and ctx holds a span.
    var device Device
    if err := s.db.QueryRowContext(
        ctx, "SELECT * FROM device WHERE id = ?", id,
    ).Scan(&device); err != nil {
        return nil, err
    }
    return device
}

Expand ▾ Collapse ▴

Documentation

Index ¶

Variables
func RecordStats(db *sql.DB, interval time.Duration) (fnStop func())
func Register(driverName string, options ...TraceOption) (string, error)
func RegisterAllViews()
func RegisterWithSource(driverName string, source string, options ...TraceOption) (string, error)
func Wrap(d driver.Driver, options ...TraceOption) driver.Driver
func WrapConn(c driver.Conn, options ...TraceOption) driver.Conn
func WrapConnector(dc driver.Connector, options ...TraceOption) driver.Connector
type TraceOption
type TraceOptions

Constants ¶

This section is empty.

Variables ¶

View Source

var (
	// GoSQLInstance is the SQL instance name.
	GoSQLInstance, _ = tag.NewKey("go_sql_instance")
	// GoSQLMethod is the SQL method called.
	GoSQLMethod, _ = tag.NewKey("go_sql_method")
	// GoSQLError is the error received while calling a SQL method.
	GoSQLError, _ = tag.NewKey("go_sql_error")
	// GoSQLStatus identifies success vs. error from the SQL method response.
	GoSQLStatus, _ = tag.NewKey("go_sql_status")
)

The following tags are applied to stats recorded by this package.

View Source

var (
	MeasureLatencyMs         = stats.Float64("go.sql/latency", "The latency of calls in milliseconds", stats.UnitMilliseconds)
	MeasureOpenConnections   = stats.Int64("go.sql/connections/open", "Count of open connections in the pool", stats.UnitDimensionless)
	MeasureIdleConnections   = stats.Int64("go.sql/connections/idle", "Count of idle connections in the pool", stats.UnitDimensionless)
	MeasureActiveConnections = stats.Int64("go.sql/connections/active", "Count of active connections in the pool", stats.UnitDimensionless)
	MeasureWaitCount         = stats.Int64("go.sql/connections/wait_count", "The total number of connections waited for", stats.UnitDimensionless)
	MeasureWaitDuration      = stats.Float64("go.sql/connections/wait_duration", "The total time blocked waiting for a new connection", stats.UnitMilliseconds)
	MeasureIdleClosed        = stats.Int64("go.sql/connections/idle_closed", "The total number of connections closed due to SetMaxIdleConns", stats.UnitDimensionless)
	MeasureLifetimeClosed    = stats.Int64("go.sql/connections/lifetime_closed", "The total number of connections closed due to SetConnMaxLifetime", stats.UnitDimensionless)
)

The following measures are supported for use in custom views.

View Source

var (
	SQLClientLatencyView = &view.View{
		Name:        "go.sql/client/latency",
		Description: "The distribution of latencies of various calls in milliseconds",
		Measure:     MeasureLatencyMs,
		Aggregation: DefaultMillisecondsDistribution,
		TagKeys:     []tag.Key{GoSQLInstance, GoSQLMethod, GoSQLError, GoSQLStatus},
	}

	SQLClientCallsView = &view.View{
		Name:        "go.sql/client/calls",
		Description: "The number of various calls of methods",
		Measure:     MeasureLatencyMs,
		Aggregation: view.Count(),
		TagKeys:     []tag.Key{GoSQLInstance, GoSQLMethod, GoSQLError, GoSQLStatus},
	}

	SQLClientOpenConnectionsView = &view.View{
		Name:        "go.sql/db/connections/open",
		Description: "The number of open connections",
		Measure:     MeasureOpenConnections,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientIdleConnectionsView = &view.View{
		Name:        "go.sql/db/connections/idle",
		Description: "The number of idle connections",
		Measure:     MeasureIdleConnections,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientActiveConnectionsView = &view.View{
		Name:        "go.sql/db/connections/active",
		Description: "The number of active connections",
		Measure:     MeasureActiveConnections,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientWaitCountView = &view.View{
		Name:        "go.sql/db/connections/wait_count",
		Description: "The total number of connections waited for",
		Measure:     MeasureWaitCount,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientWaitDurationView = &view.View{
		Name:        "go.sql/db/connections/wait_duration",
		Description: "The total time blocked waiting for a new connection",
		Measure:     MeasureWaitDuration,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientIdleClosedView = &view.View{
		Name:        "go.sql/db/connections/idle_closed_count",
		Description: "The total number of connections closed due to SetMaxIdleConns",
		Measure:     MeasureIdleClosed,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	SQLClientLifetimeClosedView = &view.View{
		Name:        "go.sql/db/connections/lifetime_closed_count",
		Description: "The total number of connections closed due to SetConnMaxLifetime",
		Measure:     MeasureLifetimeClosed,
		Aggregation: view.LastValue(),
		TagKeys:     []tag.Key{GoSQLInstance},
	}

	DefaultViews = []*view.View{
		SQLClientLatencyView, SQLClientCallsView, SQLClientOpenConnectionsView,
		SQLClientIdleConnectionsView, SQLClientActiveConnectionsView,
		SQLClientWaitCountView, SQLClientWaitDurationView,
		SQLClientIdleClosedView, SQLClientLifetimeClosedView,
	}
)

Package ocsql provides some convenience views. You still need to register these views for data to actually be collected. You can use the RegisterAllViews function for this.

View Source

var AllTraceOptions = TraceOptions{
	AllowRoot:    true,
	Ping:         true,
	RowsNext:     true,
	RowsClose:    true,
	RowsAffected: true,
	LastInsertID: true,
	Query:        true,
	QueryParams:  true,
}

AllTraceOptions has all tracing options enabled.

View Source

var (
	DefaultMillisecondsDistribution = view.Distribution(
		0.0,
		0.001,
		0.005,
		0.01,
		0.05,
		0.1,
		0.5,
		1.0,
		1.5,
		2.0,
		2.5,
		5.0,
		10.0,
		25.0,
		50.0,
		100.0,
		200.0,
		400.0,
		600.0,
		800.0,
		1000.0,
		1500.0,
		2000.0,
		2500.0,
		5000.0,
		10000.0,
		20000.0,
		40000.0,
		100000.0,
		200000.0,
		500000.0)
)

Default distributions used by views in this package

Functions ¶

func RecordStats ¶

func RecordStats(db *sql.DB, interval time.Duration) (fnStop func())

RecordStats records database statistics for provided sql.DB at the provided interval.

func Register ¶

func Register(driverName string, options ...TraceOption) (string, error)

Register initializes and registers our ocsql wrapped database driver identified by its driverName and using provided TraceOptions. On success it returns the generated driverName to use when calling sql.Open. It is possible to register multiple wrappers for the same database driver if needing different TraceOptions for different connections.

func RegisterAllViews ¶

func RegisterAllViews()

RegisterAllViews registers all ocsql views to enable collection of stats.

func RegisterWithSource ¶

func RegisterWithSource(driverName string, source string, options ...TraceOption) (string, error)

RegisterWithSource initializes and registers our ocsql wrapped database driver identified by its driverName, using provided TraceOptions. source is useful if some drivers do not accept the empty string when opening the DB. On success it returns the generated driverName to use when calling sql.Open. It is possible to register multiple wrappers for the same database driver if needing different TraceOptions for different connections.

func Wrap ¶

func Wrap(d driver.Driver, options ...TraceOption) driver.Driver

Wrap takes a SQL driver and wraps it with OpenCensus instrumentation.

func WrapConn ¶

func WrapConn(c driver.Conn, options ...TraceOption) driver.Conn

WrapConn allows an existing driver.Conn to be wrapped by ocsql.

func WrapConnector ¶

func WrapConnector(dc driver.Connector, options ...TraceOption) driver.Connector

WrapConnector allows wrapping a database driver.Connector which eliminates the need to register ocsql as an available driver.Driver.

Types ¶

type TraceOption ¶

type TraceOption func(o *TraceOptions)

TraceOption allows for managing ocsql configuration using functional options.

func WithAllTraceOptions ¶

func WithAllTraceOptions() TraceOption

WithAllTraceOptions enables all available trace options.

func WithAllowRoot ¶

func WithAllowRoot(b bool) TraceOption

WithAllowRoot if set to true, will allow ocsql to create root spans in absence of exisiting spans or even context. Default is to not trace ocsql calls if no existing parent span is found in context or when using methods not taking context.

func WithDefaultAttributes ¶

func WithDefaultAttributes(attrs ...trace.Attribute) TraceOption

WithDefaultAttributes will be set to each span as default.

func WithDisableErrSkip ¶

func WithDisableErrSkip(b bool) TraceOption

WithDisableErrSkip, if set to true, will suppress driver.ErrSkip errors in spans.

func WithInstanceName ¶

func WithInstanceName(instanceName string) TraceOption

WithInstanceName sets database instance name.

func WithLastInsertID ¶

func WithLastInsertID(b bool) TraceOption

WithLastInsertID if set to true, will enable the creation of spans on LastInsertId calls.

func WithOptions ¶

func WithOptions(options TraceOptions) TraceOption

WithOptions sets our ocsql tracing middleware options through a single TraceOptions object.

func WithPing ¶

func WithPing(b bool) TraceOption

WithPing if set to true, will enable the creation of spans on Ping requests.

func WithQuery ¶

func WithQuery(b bool) TraceOption

WithQuery if set to true, will enable recording of sql queries in spans. Only allow this if it is safe to have queries recorded with respect to security.

func WithQueryParams ¶

func WithQueryParams(b bool) TraceOption

WithQueryParams if set to true, will enable recording of parameters used with parametrized queries. Only allow this if it is safe to have parameters recorded with respect to security. This setting is a noop if the Query option is set to false.

func WithRowsAffected ¶

func WithRowsAffected(b bool) TraceOption

WithRowsAffected if set to true, will enable the creation of spans on RowsAffected calls.

func WithRowsClose ¶

func WithRowsClose(b bool) TraceOption

WithRowsClose if set to true, will enable the creation of spans on RowsClose calls.

func WithRowsNext ¶

func WithRowsNext(b bool) TraceOption

WithRowsNext if set to true, will enable the creation of spans on RowsNext calls. This can result in many spans.

func WithSampler ¶

func WithSampler(sampler trace.Sampler) TraceOption

WithSampler will be used on span creation.

type TraceOptions ¶

type TraceOptions struct {
	// AllowRoot, if set to true, will allow ocsql to create root spans in
	// absence of existing spans or even context.
	// Default is to not trace ocsql calls if no existing parent span is found
	// in context or when using methods not taking context.
	AllowRoot bool

	// Ping, if set to true, will enable the creation of spans on Ping requests.
	Ping bool

	// RowsNext, if set to true, will enable the creation of spans on RowsNext
	// calls. This can result in many spans.
	RowsNext bool

	// RowsClose, if set to true, will enable the creation of spans on RowsClose
	// calls.
	RowsClose bool

	// RowsAffected, if set to true, will enable the creation of spans on
	// RowsAffected calls.
	RowsAffected bool

	// LastInsertID, if set to true, will enable the creation of spans on
	// LastInsertId calls.
	LastInsertID bool

	// Query, if set to true, will enable recording of sql queries in spans.
	// Only allow this if it is safe to have queries recorded with respect to
	// security.
	Query bool

	// QueryParams, if set to true, will enable recording of parameters used
	// with parametrized queries. Only allow this if it is safe to have
	// parameters recorded with respect to security.
	// This setting is a noop if the Query option is set to false.
	QueryParams bool

	// DefaultAttributes will be set to each span as default.
	DefaultAttributes []trace.Attribute

	// InstanceName identifies database.
	InstanceName string

	// DisableErrSkip, if set to true, will suppress driver.ErrSkip errors in spans.
	DisableErrSkip bool

	// Sampler to use when creating spans.
	Sampler trace.Sampler
}

TraceOptions holds configuration of our ocsql tracing middleware. By default all options are set to false intentionally when creating a wrapped driver and provide the most sensible default with both performance and security in mind.

Source Files

View all

?	: This menu
/	: Search site
f or F	: Jump to