1, Background introduction
The maximum number of connections of PostgreSQL will become a problem that annoys the business at some time. The number of business connections has soared, and the number of connections is full, but the service cannot be restarted. This scenario is very embarrassing. The maximum number of connections can only be restarted and adjusted. If you want to keep the library, it seems "no solution".
Why does it need to restart to adjust the maximum number of connections and cannot be changed to reload mode?
This problem is not expanded in detail. In short, the maximum number of connections parameter is related to shared memory and semaphores. After setting the maximum number of connections, the system will allocate these resources accordingly at startup. It seems that the original maximum number of connections parameter can only be the restart mode, and the modification takes effect.
Native pg except max_connections, you can also change user / database... CONNECTION LIMIT connlimit to dynamically adjust the number of connections through the two dimensions of user and database. However, it is only applicable to specific scenes. After a little deliberation, it will be found that it is not applicable to most scenes. There are more databases and users than instances, and each user needs a certain number of connections. The bottleneck returns to max_connections
After thinking about it, I decided to try to add an online parameter db_connlimit, which can dynamically adjust the maximum number of connections. The parameter level is PGC_SIGHUP means reload takes effect.
The default value is - 1. Keep the default value. The maximum number of connections still follows the native logic. The value range is from - 1 (default) to max_connnections . In other words, system resources are still managed by max_connections parameter, db_connlimit parameter is only used to manage the number of connections. If the resources meet the requirements, enlarge Max appropriately_ Connections, such as db_connlimit is set to max_ Half of connections. When the number of connections is full, you can continue to increase db_connlimit, to relieve some pressure.
Of course, there are problems with this scheme: for example, version differences are not considered. To be compatible with all versions, HooK extensions or middleware can be considered, but the architecture may be more complex. The R & D leader must have a mature scheme. As the operation and maintenance students, we will finally struggle to verify this scheme.
PG13 is used here 0 to test.
2, Problem thinking
db_ The connlimit parameter must meet the following requirements:
-
The impact on the native logic should be small. You can turn it off (default is off). After closing, the native logic will not be affected;
-
Reliability. When the number of connections reaches the maximum, it should be consistent with the original processing logic;
-
The maximum value must be less than or equal to max_ Connections when trying to configure a value greater than this value, reload cannot take effect and remains the original value
3, Code modification
In view of the above three questions, in turn:
- Add parameter db_connlimit
global.c set the global variable DbConnlimit
/* * Modify by Nickxyang at 2021-06-07 PM * Add the global variable DbConnlimit. */ int DbConnlimit = 100; /* * Modify End at 2021-06-07 PM */
miscadmin.h
/* * Modify by Nickxyang at 2021-06-07 PM */ extern PGDLLIMPORT int DbConnlimit; /* * Modify End at 2021-06-07 PM */
guc.c add parameter db_connlimit, the first - 1 is the default, and the second - 1 is the minimum
/*
* Modify by Nickxyang at 2021-06-07 PM
* Add the parameter db_connlimit, you can modify the maximum number of connections online.
* The valid range is -1 (default) to Maxconntions. A value of -1 means that the parameter is not open.
*/
{
{"db_connlimit", PGC_SIGHUP, CONN_AUTH_SETTINGS,
gettext_noop("Sets the maximum number of concurrent connections, which can be set online."),
NULL
},
&DbConnlimit,
-1, -1, MAX_BACKENDS,
check_dbconnlimit, NULL, NULL
},
/*
* Modify End at 2021-06-07 PM
*/
guc.c add the check function, which is pointed by the function pointer. Check the validity of the parameter in initdb and start links
/*
* Modify by Nickxyang at 2021-06-07 PM
*/
static bool
check_dbconnlimit(int *newval, void **extra, GucSource source)
{
if ((*newval + autovacuum_max_workers + 1 +
max_worker_processes + max_wal_senders > MAX_BACKENDS) && *newval > MaxConnections)
return false;
return true;
}
/*
* Modify End at 2021-06-07 PM
*/
postgresql.conf.sample
#db_connlimit = -1 # range -1 to max_connections
- Ensure that it has little impact on the native logic. When the parameter is closed, it will not affect the native logic
- Reliability to ensure that the processing logic is consistent with the original when the maximum number of connections is reached
postinit. InitPostgres function in C
/*
* Modify by Nickxyang at 2021-06-07 PM
*
* If the DbConnlimit parameter is configured
* and the number of connections reaches DbConnlimit - ReservedBackends,
* non-superuser will not be able to create new connections
*/
if (DbConnlimit > 0 && DbConnlimit < MaxConnections)
{
if (!am_superuser && !am_walsender
&& ReservedBackends > 0 &&
!HaveNFreeProcs(MaxConnections - DbConnlimit + ReservedBackends))
ereport(FATAL,
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("remaining connection slots are reserved for non-replication superuser connections,please check parameter db_connlimit")));
if (!am_walsender &&
!HaveNFreeProcs(MaxConnections - DbConnlimit))
ereport(FATAL,
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("sorry, too many clients already,please check parameter db_connlimit ")));
}
/* Modify End at 2021-06-07 PM */
The processing logic here completely follows the original logic. The old logic is retained and DB is added_ Control logic of connlimit parameter. Dbconnlimit > 0 in If condition can ensure that the logic is not entered when using the default value of - 1.
In addition, to judge whether the number of connections is full, the HaveNFreeProcs function modifies the input parameters
For the judgment logic of non super users, the input parameters are: MaxConnections – DbConnlimit + ReservedBackends,
For the judgment logic of all users, the input parameters are (including reserved backends): MaxConnections - DbConnlimit
This function determines whether the number of connections is full by viewing the length of the shared memory procglobal - > freeprocs list.
- The maximum value is valid. The valid maximum value must not exceed MaxConnections
guc-file. ProcessConfigFileInternal function in C
/* Modify by Nickxyang at 2021-06-07 PM */
char *dbconnectlimit = "db_connlimit";
/* Modify End at 2021-06-07 PM */
/* Modify by Nickxyang at 2021-06-07 PM
* When the db_connlimit configuration is greater than MaxConnections
* or is less than ReservedBackends (when ReservedBackends > 0)
* reload will not be effective */
if (strcmp(item->name,dbconnectlimit) == 0 && atoi(item->value) != -1
&& (atoi(item->value) > MaxConnections || atoi(item->value) <= ReservedBackends || atoi(item->value) == 0))
{
if (ReservedBackends > 0 && atoi(item->value) <= ReservedBackends)
{
ereport(elevel,
(errmsg("parameter \"%s\" is not set to \"%s\", because it must be bigger than superuser_reserved_connections=%d",
item->name, item->value,ReservedBackends)));
}
else
{
if (atoi(item->value) == 0)
{
ereport(elevel,
(errmsg("parameter \"%s\" is not set to \"%s\", because this will not allow non-superuser to login",
item->name, item->value)));
}
else
{
ereport(elevel,
(errmsg("parameter \"%s\" is not set to \"%s\", because it is bigger than max_connnections=%d",
item->name, item->value, MaxConnections)));
}
}
scres = -1;
}
else
/* Modify End at 2021-06-07 PM*/
scres = set_config_option(item->name, item->value,
context, PGC_S_FILE,
GUC_ACTION_SET, applySettings, 0, false);
Here only when DB is modified_ Connlimit parameter, and the parameter value does not meet the requirements (the value is greater than MaxConnections or less than ReservedBackends or the value is equal to 0), will enter the logic, and a message of setting failure will be recorded in pglog. And assign scres to - 1, and set will not be called_ config_ The option function validates the modified value of the parameter.
If you do not modify or close db_connlimit parameter, or native logic.
4, Test verification
Compile, install and start the database to view the parameter information
postgres=# select * from pg_settings where name='db_connlimit'; -[ RECORD 1 ]---+---------------------------------------------------------------------------- name | db_connlimit setting | -1 unit | category | Connections and Authentication / Connection Settings short_desc | Sets the maximum number of concurrent connections, which can be set online. extra_desc | context | sighup vartype | integer source | configuration file min_val | -1 max_val | 262143 enumvals | boot_val | -1 reset_val | -1 sourcefile | /data/pg13-0debug/data/postgresql.conf sourceline | 3 pending_restart | f postgres=#
- Scenario 1: reach the maximum number of connections and see if there will be no connection
Modify the parameter value to 5
postgres=# alter system set db_connlimit to 5 ; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show db_connlimit ; db_connlimit -------------- 5 (1 row) postgres=# show max_connections ; max_connections ----------------- 10 (1 row) postgres=# show superuser_reserved_connections ; superuser_reserved_connections -------------------------------- 3 (1 row) postgres=#
You can see that after two connections are established using testuser (testuser is a non super user), non administrators can no longer create new connections
[postgres@NickCentos:pg13.0:5432 ~]$psql -U testuser -d postgres
psql: error: could not connect to server: FATAL: remaining connection slots are reserved for non-replication superuser connections, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$psql -U testuser -d postgres
psql: error: could not connect to server: FATAL: remaining connection slots are reserved for non-replication superuser connections, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql (13.0)
PG dry O,Everyday elephant!
Type "help" for help.
postgres=# select count(1),usename from pg_stat_activity where backend_type='client backend' group by 2 order by 1;
count | usename
-------+----------
1 | postgres
2 | testuser
(2 rows)
postgres=#
When the number of connections reaches DB_ After connlimit, superuser can't log in
postgres=# select count(1),usename from pg_stat_activity where backend_type='client backend' group by 2 order by 1;
count | usename
-------+----------
2 | testuser
3 | postgres
(2 rows)
postgres=# ^Z
[1]+ Stopped psql -U postgres -d postgres
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql: error: could not connect to server: FATAL: sorry, too many clients already, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql: error: could not connect to server: FATAL: sorry, too many clients already, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$
Here, to modify the number of connections, you need to manually change the file, pg_ctl reload takes effect, just as we verify the function added online,
It turned out to be effective.
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql: error: could not connect to server: FATAL: sorry, too many clients already, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql: error: could not connect to server: FATAL: sorry, too many clients already, please check parameter db_connlimit
[postgres@NickCentos:pg13.0:5432 ~]$vim $PGDATA/postgresql.conf
[postgres@NickCentos:pg13.0:5432 ~]$vim $PGDATA/postgresql.auto.conf
[postgres@NickCentos:pg13.0:5432 ~]$grep db_connlimit $PGDATA/postgresql*conf
/data/pg13-0debug/data/postgresql.auto.conf:db_connlimit = '8'
/data/pg13-0debug/data/postgresql.conf:db_connlimit = 8 # range -1 to max_connections
[postgres@NickCentos:pg13.0:5432 ~]$pg_ctl reload
server signaled
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql (13.0)
PG dry O,Everyday elephant!
Type "help" for help.
postgres=# select count(1),usename from pg_stat_activity where backend_type='client backend' group by 2 order by 1;
count | usename
-------+----------
2 | testuser
4 | postgres
(2 rows)
postgres=#
- Scenario 2: modify the parameter to 0 or greater than MaxConnections to see if it takes effect
postgres=# alter system set db_connlimit to 11 ; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show db_connlimit ; db_connlimit -------------- 8 (1 row) postgres=# alter system set db_connlimit to 0 ; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show db_connlimit ; db_connlimit -------------- 8 (1 row) postgres=# alter system set db_connlimit to 2 ; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show db_connlimit ; db_connlimit -------------- 8 (1 row) postgres=#
It doesn't take effect here. You can see the reason for printing in the log.
2021-06-07 14:36:48.866 CST [720] LOG: received SIGHUP, reloading configuration files 2021-06-07 14:36:48.866 CST [720] LOG: parameter "db_connlimit" is not set to "11", because it is bigger than max_connnections=10. 2021-06-07 14:37:29.966 CST [720] LOG: received SIGHUP, reloading configuration files 2021-06-07 14:37:29.967 CST [720] LOG: parameter "db_connlimit" is not set to "0", because it must be bigger than superuser_reserved_connections=3. 2021-06-07 14:37:47.122 CST [720] LOG: received SIGHUP, reloading configuration files 2021-06-07 14:37:47.123 CST [720] LOG: parameter "db_connlimit" is not set to "2", because it must be bigger than superuser_reserved_connections=3.
- Scenario 3: change to other invalid values
Invalid values will not be recognized
postgres=# alter system set db_connlimit to -2 ;
ERROR: -2 is outside the valid range for parameter "db_connlimit" (-1 .. 262143)
postgres=# alter system set db_connlimit to a ;
ERROR: invalid value for parameter "db_connlimit": "a"
postgres=# alter system set db_connlimit to # ;
ERROR: syntax error at or near "#"
LINE 1: alter system set db_connlimit to # ;
^
postgres=#
- Scenario 4: modify it to - 1 (closing parameter) to see whether the native logic is normal
The configuration is as follows:
postgres=# alter system set db_connlimit to -1 ; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show db_connlimit ; db_connlimit -------------- -1 (1 row) postgres=# show max_connections ; max_connections ----------------- 10 (1 row) postgres=# show superuser_reserved_connections ; superuser_reserved_connections -------------------------------- 3 (1 row) postgres=#
Here, non super users can only establish 7 connections. You can see that the result is normal.
[postgres@NickCentos:pg13.0:5432 ~]$psql -U testuser -d postgres
psql: error: could not connect to server: FATAL: remaining connection slots are reserved for non-replication superuser connections
[postgres@NickCentos:pg13.0:5432 ~]$psql -U testuser -d postgres
psql: error: could not connect to server: FATAL: remaining connection slots are reserved for non-replication superuser connections
[postgres@NickCentos:pg13.0:5432 ~]$psql -U postgres -d postgres
psql (13.0)
PG dry O,Everyday elephant!
Type "help" for help.
postgres=# select count(1),usename from pg_stat_activity where backend_type='client backend' group by 2 order by 1;
count | usename
-------+----------
1 | postgres
7 | testuser
(2 rows)
postgres=#
Subsequent tests have also been carried out for many times, which is in line with expectations.
5, Summary
Try adding online management parameter db_connlimit preliminary verification is in line with expectations. This is only a tentative plan. Whether it is suitable for production still needs careful consideration. In the case of an abnormal surge in the number of application connections and failure to restart, this scheme can alleviate some pressure, so as not to be so embarrassing as to modify the maximum number of connections but fail to restart and take effect. However, at the same time, system resources such as semaphores should be considered.