internal/database: set db connection pool limits

Under high-load, the frontend can issue many queries and trigger many
connections. This can exceed the Cloud SQL connection limit of 100 per
instance enforced by the Cloud Run environment, even if the database
itself has remaining global capacity.

To prevent this, we introduce limits on the database connection pool.
We also include settings for idle connections and connection lifetimes
to ensure better resource management and connection rotation.

Introduces new env vars:

  GO_DISCOVERY_DATABASE_MAX_OPEN_CONNS
  GO_DISCOVERY_DATABASE_MAX_IDLE_CONNS
  GO_DISCOVERY_DATABASE_CONN_MAX_LIFETIME
  GO_DISCOVERY_DATABASE_CONN_MAX_IDLE_TIME

Updates SetPoolSettings to validate that MaxIdleConns does not exceed
MaxOpenConns, providing a warning if it does and capping it.

Change-Id: I74edac05c4a23102d64e74a180c661c223e1b757
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/747620
LUCI-TryBot-Result: Go LUCI <golang-scoped@luci-project-accounts.iam.gserviceaccount.com>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
Reviewed-by: Nicholas Husin <nsh@golang.org>
Reviewed-by: Nicholas Husin <husin@google.com>
kokoro-CI: kokoro <noreply+kokoro@google.com>

1 files changed, 17 insertions, 0 deletions

diff --git a/internal/database/database.go b/internal/database/database.go
index 30571632..bcb7c09a 100644
--- a/internal/database/database.go
+++ b/internal/database/database.go
@@ -63,6 +63,23 @@ func New(db *sql.DB, instanceID string) *DB {
 	return &DB{db: db, instanceID: instanceID}
 }
 
+// Underlying returns the underlying *sql.DB.
+func (db *DB) Underlying() *sql.DB {
+	return db.db
+}
+
+// SetPoolSettings sets the connection pool settings for the database.
+func (db *DB) SetPoolSettings(maxOpen, maxIdle int, maxLifetime, maxIdleTime time.Duration) {
+	if maxOpen > 0 && maxIdle > maxOpen {
+		log.Warningf(context.Background(), "SetPoolSettings: maxIdle (%d) > maxOpen (%d); capping maxIdle to maxOpen", maxIdle, maxOpen)
+		maxIdle = maxOpen
+	}
+	db.db.SetMaxOpenConns(maxOpen)
+	db.db.SetMaxIdleConns(maxIdle)
+	db.db.SetConnMaxLifetime(maxLifetime)
+	db.db.SetConnMaxIdleTime(maxIdleTime)
+}
+
 func (db *DB) Ping() error {
 	return db.db.Ping()
 }