From 943d17deed24899cf4a0869d7addefa100fa9392 Mon Sep 17 00:00:00 2001 From: Cody Cutrer Date: Mon, 4 Feb 2013 16:58:05 -0700 Subject: [PATCH] implement database-wide connection limit --- doc/config.txt | 16 ++++++++++++++++ etc/pgbouncer.ini | 3 +++ include/bouncer.h | 4 ++++ include/server.h | 1 + src/admin.c | 10 ++++++---- src/loader.c | 4 ++++ src/main.c | 2 ++ src/objects.c | 10 ++++++++++ src/server.c | 9 +++++++++ 9 files changed, 55 insertions(+), 4 deletions(-) diff --git a/doc/config.txt b/doc/config.txt index 7e3c646..fa3518c 100644 --- a/doc/config.txt +++ b/doc/config.txt @@ -179,6 +179,17 @@ use of additional connections from reserve pool. 0 disables. Default: 5.0 +==== max_db_connections ==== + +Do not allow more than this many connections per-database (regardless of pool - i.e. +user). It should be noted that when you hit the limit, closing a client connection +to one pool will not immediately allow a server connection to be established for +another pool, because the server connection for the first pool is still open. +Once the server connection closes (due to idle timeout), a new server connection +will immediately be opened for the waiting pool. + +Default: unlimited + ==== server_round_robin ==== By default, pgbouncer reuses server connections in LIFO (last-in, first-out) manner, @@ -551,6 +562,11 @@ they are logged but ignored otherwise. Set the pool mode specific to this database. If not set, the default pool_mode is used. +==== max_db_connections ==== + +Configure a database-wide maximum (i.e. all pools within the database will +not have more than this many server connections). + === Extra parameters === They allow setting default parameters on server connection. diff --git a/etc/pgbouncer.ini b/etc/pgbouncer.ini index 12ec22b..bdcaef4 100644 --- a/etc/pgbouncer.ini +++ b/etc/pgbouncer.ini @@ -124,6 +124,9 @@ default_pool_size = 20 ; if a clients needs to wait more than this many seconds, use reserve pool ;reserve_pool_timeout = 3 +; how many total connections to a single database to allow from all pools +;max_db_connections = 50 + ; log if client connects or server connection is made ;log_connections = 1 diff --git a/include/bouncer.h b/include/bouncer.h index 4679cf2..1548de3 100644 --- a/include/bouncer.h +++ b/include/bouncer.h @@ -279,6 +279,7 @@ struct PgDatabase { int pool_size; /* max server connections in one pool */ int res_pool_size; /* additional server connections in case of trouble */ int pool_mode; /* pool mode for this database */ + int max_db_connections; /* max server connections between all pools */ const char *dbname; /* server-side name, pointer to inside startup_msg */ @@ -288,6 +289,8 @@ struct PgDatabase { usec_t inactive_time; /* when auto-database became inactive (to kill it after timeout) */ unsigned active_stamp; /* set if autodb has connections */ + int connection_count; /* total connections for this database in all pools */ + struct AATree user_tree; /* users that have been queried on this database */ }; @@ -371,6 +374,7 @@ extern int cf_default_pool_size; extern int cf_min_pool_size; extern int cf_res_pool_size; extern usec_t cf_res_pool_timeout; +extern int cf_max_db_connections; extern char * cf_autodb_connstr; extern usec_t cf_autodb_idle_timeout; diff --git a/include/server.h b/include/server.h index 9e3490c..3f40eb1 100644 --- a/include/server.h +++ b/include/server.h @@ -18,3 +18,4 @@ bool server_proto(SBuf *sbuf, SBufEvent evtype, struct MBuf *pkt) _MUSTCHECK; int pool_pool_mode(PgPool *pool) _MUSTCHECK; +int database_max_connections(PgDatabase *db) _MUSTCHECK; diff --git a/src/admin.c b/src/admin.c index cfe7df9..0f7e871 100644 --- a/src/admin.c +++ b/src/admin.c @@ -457,10 +457,10 @@ static bool admin_show_databases(PgSocket *admin, const char *arg) return true; } - pktbuf_write_RowDescription(buf, "ssissiis", + pktbuf_write_RowDescription(buf, "ssissiisii", "name", "host", "port", "database", "force_user", "pool_size", "reserve_pool", - "pool_mode"); + "pool_mode", "max_connections", "current_connections"); statlist_for_each(item, &database_list) { db = container_of(item, PgDatabase, head); @@ -469,12 +469,14 @@ static bool admin_show_databases(PgSocket *admin, const char *arg) cv.value_p = &db->pool_mode; if (db->pool_mode != POOL_INHERIT) pool_mode_str = cf_get_lookup(&cv); - pktbuf_write_DataRow(buf, "ssissiis", + pktbuf_write_DataRow(buf, "ssissiisii", db->name, db->host, db->port, db->dbname, f_user, db->pool_size, db->res_pool_size, - pool_mode_str); + pool_mode_str, + database_max_connections(db), + db->connection_count); } admin_flush(admin, buf, "SHOW"); return true; diff --git a/src/loader.c b/src/loader.c index 6b83774..bc00370 100644 --- a/src/loader.c +++ b/src/loader.c @@ -180,6 +180,7 @@ bool parse_database(void *base, const char *name, const char *connstr) struct CfValue cv; int pool_size = -1; int res_pool_size = -1; + int max_db_connections = -1; int dbname_ofs; int pool_mode = POOL_INHERIT; @@ -241,6 +242,8 @@ bool parse_database(void *base, const char *name, const char *connstr) pool_size = atoi(val); else if (strcmp("reserve_pool", key) == 0) res_pool_size = atoi(val); + else if (strcmp("max_db_connections", key) == 0) + max_db_connections = atoi(val); else if (strcmp("pool_mode", key) == 0) { if (!cf_set_lookup(&cv, val)) { log_error("skipping database %s because" @@ -317,6 +320,7 @@ bool parse_database(void *base, const char *name, const char *connstr) db->pool_size = pool_size; db->res_pool_size = res_pool_size; db->pool_mode = pool_mode; + db->max_db_connections = max_db_connections; if (db->host) free(db->host); diff --git a/src/main.c b/src/main.c index 79c8d25..9ef693a 100644 --- a/src/main.c +++ b/src/main.c @@ -92,6 +92,7 @@ int cf_default_pool_size; int cf_min_pool_size; int cf_res_pool_size; usec_t cf_res_pool_timeout; +int cf_max_db_connections; char *cf_server_reset_query; char *cf_server_check_query; @@ -182,6 +183,7 @@ CF_ABS("default_pool_size", CF_INT, cf_default_pool_size, 0, "20"), CF_ABS("min_pool_size", CF_INT, cf_min_pool_size, 0, "0"), CF_ABS("reserve_pool_size", CF_INT, cf_res_pool_size, 0, "0"), CF_ABS("reserve_pool_timeout", CF_TIME_USEC, cf_res_pool_timeout, 0, "5"), +CF_ABS("max_db_connections", CF_INT, cf_max_db_connections, 0, "0"), CF_ABS("syslog", CF_INT, cf_syslog, 0, "0"), CF_ABS("syslog_facility", CF_STR, cf_syslog_facility, 0, "daemon"), CF_ABS("syslog_ident", CF_STR, cf_syslog_ident, 0, "pgbouncer"), diff --git a/src/objects.c b/src/objects.c index e20259b..474c84e 100644 --- a/src/objects.c +++ b/src/objects.c @@ -801,6 +801,8 @@ void disconnect_server(PgSocket *server, bool notify, const char *reason, ...) server->dns_token = NULL; } + server->pool->db->connection_count--; + change_server_state(server, SV_JUSTFREE); if (!sbuf_close(&server->sbuf)) log_noise("sbuf_close failed, retry later"); @@ -1015,6 +1017,13 @@ void launch_new_connection(PgPool *pool) } allow_new: + total = database_max_connections(pool->db); + if (total > 0 && pool->db->connection_count >= total) { + log_debug("launch_new_connection: database full (%d >= %d)", + pool->db->connection_count, total); + return; + } + /* get free conn object */ server = slab_alloc(server_cache); if (!server) { @@ -1028,6 +1037,7 @@ allow_new: server->connect_time = get_cached_time(); pool->last_connect_time = get_cached_time(); change_server_state(server, SV_LOGIN); + pool->db->connection_count++; dns_connect(server); } diff --git a/src/server.c b/src/server.c index bbc6982..dac4867 100644 --- a/src/server.c +++ b/src/server.c @@ -195,6 +195,15 @@ int pool_pool_mode(PgPool *pool) return pool_mode; } +int database_max_connections(PgDatabase *db) +{ + if (db->max_db_connections <= 0) { + return cf_max_db_connections; + } else { + return db->max_db_connections; + } +} + /* process packets on logged in connection */ static bool handle_server_work(PgSocket *server, PktHdr *pkt) { -- 2.40.0