autoprepare

README

autoprepare

Automatically prepare frequently-executed database/sql statements

autoprepare is a small library that transparently monitors the most frequent database/sql queries that your application executes and then automatically creates and uses the corresponding prepared statements.

Usage

Instead of doing (error handling omitted for brevity):

db, _ := sql.Open("mysql", "/mydb")
res, _ := db.QueryContext(context.Background(), "SELECT * FROM mytable WHERE id = ?", 1)
// ... many more queries ...

you can do:

db, _ := sql.Open("mysql", "/mydb")
dbsc, _ := autoprepare.New(db) // add autoprepare
res, _ := dbsc.QueryContext(context.Background(), "SELECT * FROM mytable WHERE id = ?", 1)
// ... many more queries ...

and autoprepare will transparently start using prepared statements for the most common queries.

Performance

tl;dr Depending on your workload and setup you can expect from no improvements to extremely improved throughput ¯\_(ツ)_/¯

The effect of using prepared statements varies wildly with your database, network latencies, type of queries and workloads. The only way to know for sure is to benchmark your workloads.

Depending on the configuration (see below) and your workload, it may take some time for all important queries to be executed with prepared statements. When benchmarking make sure the prepared statement cache is fully warmed up. By default it takes N*5000 SQL queries for autoprepare to prepare the statements for the N most frequently executed queries (by default N is limited to 16): so e.g. if your application performs 3 really hot SQL queries, it's going to take at least 15000 queries before statements are created for those 3 queries.

A small benchmark is included in the test harness. You can run it with:

go test -run=NONE -bench=.

This will run it against an in-memory sqlite database. To run it instead against a mysql database (replace $MYSQL_DSN with the DSN for your MySQL database):

go test -run=NONE -bench=. -benchdriver=mysql -mysql="$MYSQL_DSN" -benchparallel=false

Running the sqlite benchmark on my laptop yields something like this:

"Not prepared" means sending queries without preparing them, "Prepared" means sending the queries after manually calling Prepare() on them and "Auto prepared" is letting autoprepare transparently handle preparing the statements.

Currently the only drivers included in benchmark_test.go are sqlite and mysql. You can add additional drivers if you want to benchmark other databases.

Tips and notes

autoprepare is deliberately pretty conservative, as it will only prepare the most frequently executed statements, and it will only prepare a limited number of them (by default 16, see WithMaxPreparedStmt). Statement preparation occurs in the background, not when queries are executed, to limit latency spikes and to simplify the code. Statement preparation is triggered after a sizable amount of queries have been sent (currently 5000), and will result in a single statement (the most common in the last 5000 queries) being prepared. The frequency of executions is estimated using an exponential moving average. If a prepared statement stops being frequently executed it will be closed so that other statements can be prepared instead.

To limit the amount of memory used, both by the library and on the database, only statements shorter than a certain length (by default 4KB, see WithMaxQueryLen) are eligibile for preparation.

It is recommended to not raise WithMaxPreparedStmt unnecessarily and to always use sql.(*DB).SetMaxConn to set a limit to how many connections the database/sql pool will open to the database, as potentially every prepared statement will need to be created on every connection, and some databases have internal limits to how many prepared statements can exist at the same time: in this case the recommendation is to monitor the number of prepared statements and memory usage on the database server. Using WithMaxPreparedStmt(0) effectively disables all functionality provided by autoprepare.

It is important to understand that autoprepare uses the SQL query string to lookup prepared statements; this means that it is critical, to allow autoprepare to be effective, to use placeholders in queries (e.g. SELECT name FROM t WHERE id = ?). It is possible to not use placeholders, but in this case every combination of arguments (e.g. SELECT name FROM t WHERE id = 29 and SELECT name FROM t WHERE id = 81) will be considered a different query for the purpose of measuring the most frequently-executed queries. It is also important to note that the lookup is done on the SQL query string as-is (including whitespace), so e.g.

SELECT * FROM table

and

SELECT *
FROM table

are considered separate queries by autoprepare, even though they are equivalent for the database.

Also note that using multiple statements in the same query (e.g. SELECT 1; SELECT 2) may not be supported by the underlying driver.

autoprepare has been tested with the sqlite3 and mysql drivers, but should reasonably work with every conformant database/sql driver.

Documentation

Overview

Example

// connect to your database/sql of choice
db, err := sql.Open("sqlite3", "file::memory:?mode=memory&cache=shared")
if err != nil {
	panic(err)
}
defer db.Close()

// create a SqlStmtCache to automatically prepare your statements
dbsc, err := New(db)
if err != nil {
	panic(err)
}
defer dbsc.Close()

// now, instead of querying the database like usual:
//   res, err := db.QueryContext(context.Background(), "SELECT * FROM mytable WHERE id = ?", 1)
// you do (notice we are using the SqlStmtCache):
res, err := dbsc.QueryContext(context.Background(), "SELECT * FROM mytable WHERE id = ?", 1)
if err != nil {
	panic(err)
}
defer res.Close()

Index

• Constants
• type SQLStmtCache
  • func New(db *sql.DB, opts ...SQLStmtCacheOpt) (*SQLStmtCache, error)
  • func (c *SQLStmtCache) Close()
  • func (c *SQLStmtCache) ExecContext(ctx context.Context, sql string, values ...interface{}) (sql.Result, error)
  • func (c *SQLStmtCache) ExecContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) (sql.Result, error)
  • func (c *SQLStmtCache) GetStats() SQLStmtCacheStats
  • func (c *SQLStmtCache) QueryContext(ctx context.Context, sql string, values ...interface{}) (*sql.Rows, error)
  • func (c *SQLStmtCache) QueryContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) (*sql.Rows, error)
  • func (c *SQLStmtCache) QueryRowContext(ctx context.Context, sql string, values ...interface{}) *sql.Row
  • func (c *SQLStmtCache) QueryRowContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) *sql.Row
• type SQLStmtCacheOpt
  • func WithMaxPreparedStmt(max int) SQLStmtCacheOpt
  • func WithMaxQueryLen(max int) SQLStmtCacheOpt
  • func WithMaxStmt(max int) SQLStmtCacheOpt
• type SQLStmtCacheStats

Examples

• Package

Constants

const (
	DefaultMaxQueryLen     = 4096
	DefaultMaxPreparedStmt = 16
	DefaultMaxStmt         = 1024
)

Types

type SQLStmtCache

type SQLStmtCache struct {
	// contains filtered or unexported fields
}

SQLStmtCache transparently caches and uses prepared SQL statements.

func New

func New(db *sql.DB, opts ...SQLStmtCacheOpt) (*SQLStmtCache, error)

New creates a new SQLStmtCache, with the provided options, that wraps the provided *sql.DB instance.

func (*SQLStmtCache) Close

func (c *SQLStmtCache) Close()

Close closes and frees all resources associated with the prepared statement cache. The SQLStmtCache should not be used after Close() has been called.

func (*SQLStmtCache) ExecContext

func (c *SQLStmtCache) ExecContext(ctx context.Context, sql string, values ...interface{}) (sql.Result, error)

ExecContext is equivalent to (*sql.DB).ExecContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

func (*SQLStmtCache) ExecContextTx

func (c *SQLStmtCache) ExecContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) (sql.Result, error)

ExecContextTx is equivalent to tx.ExecContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

func (*SQLStmtCache) GetStats

func (c *SQLStmtCache) GetStats() SQLStmtCacheStats

GetStats returns statistics about the state and effectiveness of the prepared statements cache.

func (*SQLStmtCache) QueryContext

func (c *SQLStmtCache) QueryContext(ctx context.Context, sql string, values ...interface{}) (*sql.Rows, error)

QueryContext is equivalent to (*sql.DB).QueryContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

func (*SQLStmtCache) QueryContextTx

func (c *SQLStmtCache) QueryContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) (*sql.Rows, error)

QueryContextTx is equivalent to tx.QueryContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

func (*SQLStmtCache) QueryRowContext

func (c *SQLStmtCache) QueryRowContext(ctx context.Context, sql string, values ...interface{}) *sql.Row

QueryRowContext is equivalent to (*sql.DB).QueryRowContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

func (*SQLStmtCache) QueryRowContextTx

func (c *SQLStmtCache) QueryRowContextTx(ctx context.Context, tx *sql.Tx, sql string, values ...interface{}) *sql.Row

QueryRowContextTx is equivalent to tx.QueryRowContext, but it transparently creates and uses prepared statements for the most frequently-executed queries.

type SQLStmtCacheOpt

type SQLStmtCacheOpt func(*SQLStmtCache) error

func WithMaxPreparedStmt

func WithMaxPreparedStmt(max int) SQLStmtCacheOpt

WithMaxPreparedStmt specifies the maximum number of prepared statements that will exist at any one time. It defaults to DefaultMaxPreparedStmt. Some databases (e.g. mysql) have limits to how many statements can be prepared at any one time, across all clients and connections: be sure not to set this number too high, or to use too many concurrent connections, or to use too many concurrent clients. Setting this value to 0 disables the SQLStmtCache.

func WithMaxQueryLen

func WithMaxQueryLen(max int) SQLStmtCacheOpt

WithMaxQueryLen specifies the maximum length of a SQL statement to be considered by autoprepare. Statements longer than this number are executed as-is and no prepared statements are ever cached. It defaults to DefaultMaxQueryLen.

func WithMaxStmt

func WithMaxStmt(max int) SQLStmtCacheOpt

WithMaxStmt specifies a soft upper limit on how many different SQL statements to track to be able to pick the most frequently used one, that will be promoted to a prepared statement. It defaults to DefaultMaxStmt.

type SQLStmtCacheStats

type SQLStmtCacheStats struct {
	Prepared   uint64 // number of autoprepared statements created (Prepare() calls issued)
	Unprepared uint64 // number of autoprepared statements deleted (sql.(*Stmt).Close() calls issued)
	Hits       uint64 // number of SQL queries that used automatically-prepared statements
	Misses     uint64 // number of SQL queries executed raw
	Skips      uint64 // number of SQL queries that do not qualify for caching
}