go-sql-driver / go-sql-driver/mysql

Configurable caching of ephemeral prepared statements

Open
#1,149 1 comment 1 reaction 0 assignees View on GitHub
thinking
Dominant language
Go
Stars
15.3k
Forks
2.3k
Avg merge
2h 23m
Merged PRs (30d)
10

Description

This is more of as a feature suggestion than anything else (I would have added it to the wiki, but it's readonly): since the driver already creates ephemeral prepared statements when executing queries with `interpolateParams=false` it could be interesting to allow users to opt-in to have the driver dynamically cache up to a configurable number of automatically-created prepared statements corresponding to the most frequently executed queries.

By default this cache would be off, and users could opt-in e.g. with a DSN parameter like `cacheStatements=N`, where N is the maximum number of prepared statements that can be in the cache. The driver internally would keep a larger (but bounded) approximate set of the most frequently executed queries, but only the top N statements would be eligible for having the corresponding prepared statement be kept in the cache. The documentation should clearly state that N is per-connection, and that therefore the total number of prepared statements on the server used by this cache can reach up to `N*num_open_connections`, and that therefore careful configuration is required both on the client (`SetMaxOpenConns`) and on the server (`max_prepared_stmt_count`).

The rationale for this is that in our experience many services have a (small) subset of hot queries. If the driver implemented this feature, it would transparently give significant performance benefits - as it would avoid the prepared statement roundtrip at least on the most frequently executed queries without requiring users to manually curate and maintain the list of which statements should be prepared beforehand.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.