Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7f5b198

Browse files
Support connection load balancing in libpq
This adds support for load balancing connections with libpq using aconnection parameter: load_balance_hosts=<string>. When setting theparam to random, hosts and addresses will be connected to in randomorder. This then results in load balancing across these addresses andhosts when multiple clients or frequent connection setups are used.The randomization employed performs two levels of shuffling: 1. The given hosts are randomly shuffled, before resolving them one-by-one. 2. Once a host its addresses get resolved, the returned addresses are shuffled, before trying to connect to them one-by-one.Author: Jelte Fennema <postgres@jeltef.nl>Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>Reviewed-by: Michael Banck <mbanck@gmx.net>Reviewed-by: Andrey Borodin <amborodin86@gmail.com>Discussion:https://postgr.es/m/PR3PR83MB04768E2FF04818EEB2179949F7A69@PR3PR83MB0476.EURPRD83.prod.outlook.
1 parent44d85ba commit7f5b198

File tree

10 files changed

+431
-3
lines changed

10 files changed

+431
-3
lines changed

‎.cirrus.yml

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@ env:
2525
MTEST_ARGS:--print-errorlogs --no-rebuild -C build
2626
PGCTLTIMEOUT:120# avoids spurious failures during parallel tests
2727
TEMP_CONFIG:${CIRRUS_WORKING_DIR}/src/tools/ci/pg_ci_base.conf
28-
PG_TEST_EXTRA:kerberos ldap ssl
28+
PG_TEST_EXTRA:kerberos ldap ssl load_balance
2929

3030

3131
# What files to preserve in case tests fail
@@ -313,6 +313,14 @@ task:
313313
mkdir -m 770 /tmp/cores
314314
chown root:postgres /tmp/cores
315315
sysctl kernel.core_pattern='/tmp/cores/%e-%s-%p.core'
316+
317+
setup_hosts_file_script:|
318+
cat >> /etc/hosts <<-EOF
319+
127.0.0.1 pg-loadbalancetest
320+
127.0.0.2 pg-loadbalancetest
321+
127.0.0.3 pg-loadbalancetest
322+
EOF
323+
316324
setup_additional_packages_script:|
317325
#apt-get update
318326
#DEBIAN_FRONTEND=noninteractive apt-get -y install ...
@@ -564,6 +572,12 @@ task:
564572
setup_additional_packages_script:|
565573
REM choco install -y --no-progress ...
566574
575+
setup_hosts_file_script:|
576+
echo 127.0.0.1 pg-loadbalancetest >> c:\Windows\System32\Drivers\etc\hosts
577+
echo 127.0.0.2 pg-loadbalancetest >> c:\Windows\System32\Drivers\etc\hosts
578+
echo 127.0.0.3 pg-loadbalancetest >> c:\Windows\System32\Drivers\etc\hosts
579+
type c:\Windows\System32\Drivers\etc\hosts
580+
567581
# Use /DEBUG:FASTLINK to avoid high memory usage during linking
568582
configure_script:|
569583
vcvarsall x64

‎doc/src/sgml/libpq.sgml

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2115,6 +2115,67 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
21152115
</para>
21162116
</listitem>
21172117
</varlistentry>
2118+
2119+
<varlistentry id="libpq-connect-load-balance-hosts" xreflabel="load_balance_hosts">
2120+
<term><literal>load_balance_hosts</literal></term>
2121+
<listitem>
2122+
<para>
2123+
Controls the order in which the client tries to connect to the available
2124+
hosts and addresses. Once a connection attempt is successful no other
2125+
hosts and addresses will be tried. This parameter is typically used in
2126+
combination with multiple host names or a DNS record that returns
2127+
multiple IPs. This parameter can be used in combination with
2128+
<xref linkend="libpq-connect-target-session-attrs"/>
2129+
to, for example, load balance over standby servers only. Once successfully
2130+
connected, subsequent queries on the returned connection will all be
2131+
sent to the same server. There are currently two modes:
2132+
<variablelist>
2133+
<varlistentry>
2134+
<term><literal>disable</literal> (default)</term>
2135+
<listitem>
2136+
<para>
2137+
No load balancing across hosts is performed. Hosts are tried in
2138+
the order in which they are provided and addresses are tried in
2139+
the order they are received from DNS or a hosts file.
2140+
</para>
2141+
</listitem>
2142+
</varlistentry>
2143+
2144+
<varlistentry>
2145+
<term><literal>random</literal></term>
2146+
<listitem>
2147+
<para>
2148+
Hosts and addresses are tried in random order. This value is mostly
2149+
useful when opening multiple connections at the same time, possibly
2150+
from different machines. This way connections can be load balanced
2151+
across multiple <productname>PostgreSQL</productname> servers.
2152+
</para>
2153+
<para>
2154+
While random load balancing, due to its random nature, will almost
2155+
never result in a completely uniform distribution, it statistically
2156+
gets quite close. One important aspect here is that this algorithm
2157+
uses two levels of random choices: First the hosts
2158+
will be resolved in random order. Then secondly, before resolving
2159+
the next host, all resolved addresses for the current host will be
2160+
tried in random order. This behaviour can skew the amount of
2161+
connections each node gets greatly in certain cases, for instance
2162+
when some hosts resolve to more addresses than others. But such a
2163+
skew can also be used on purpose, e.g. to increase the number of
2164+
connections a larger server gets by providing its hostname multiple
2165+
times in the host string.
2166+
</para>
2167+
<para>
2168+
When using this value it's recommended to also configure a reasonable
2169+
value for <xref linkend="libpq-connect-connect-timeout"/>. Because then,
2170+
if one of the nodes that are used for load balancing is not responding,
2171+
a new node will be tried.
2172+
</para>
2173+
</listitem>
2174+
</varlistentry>
2175+
</variablelist>
2176+
</para>
2177+
</listitem>
2178+
</varlistentry>
21182179
</variablelist>
21192180
</para>
21202181
</sect2>

‎doc/src/sgml/regress.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -256,7 +256,7 @@ make check-world -j8 >/dev/null
256256
<varname>PG_TEST_EXTRA</varname> to a whitespace-separated list, for
257257
example:
258258
<programlisting>
259-
make check-world PG_TEST_EXTRA='kerberos ldap ssl'
259+
make check-world PG_TEST_EXTRA='kerberos ldap ssl load_balance'
260260
</programlisting>
261261
The following values are currently supported:
262262
<variablelist>
@@ -290,6 +290,17 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl'
290290
</listitem>
291291
</varlistentry>
292292

293+
<varlistentry>
294+
<term><literal>load_balance</literal></term>
295+
<listitem>
296+
<para>
297+
Runs the test <filename>src/interfaces/libpq/t/004_load_balance_dns.pl</filename>.
298+
This requires editing the system <filename>hosts</filename> file and
299+
opens TCP/IP listen sockets.
300+
</para>
301+
</listitem>
302+
</varlistentry>
303+
293304
<varlistentry>
294305
<term><literal>wal_consistency_checking</literal></term>
295306
<listitem>

‎src/interfaces/libpq/fe-connect.c

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,7 @@ static intldapServiceLookup(const char *purl, PQconninfoOption *options,
123123
#defineDefaultChannelBinding"disable"
124124
#endif
125125
#defineDefaultTargetSessionAttrs"any"
126+
#defineDefaultLoadBalanceHosts"disable"
126127
#ifdefUSE_SSL
127128
#defineDefaultSSLMode "prefer"
128129
#defineDefaultSSLCertMode "allow"
@@ -351,6 +352,11 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
351352
"Target-Session-Attrs","",15,/* sizeof("prefer-standby") = 15 */
352353
offsetof(structpg_conn,target_session_attrs)},
353354

355+
{"load_balance_hosts","PGLOADBALANCEHOSTS",
356+
DefaultLoadBalanceHosts,NULL,
357+
"Load-Balance-Hosts","",8,/* sizeof("disable") = 8 */
358+
offsetof(structpg_conn,load_balance_hosts)},
359+
354360
/* Terminating entry --- MUST BE LAST */
355361
{NULL,NULL,NULL,NULL,
356362
NULL,NULL,0}
@@ -435,6 +441,8 @@ static void pgpassfileWarning(PGconn *conn);
435441
staticvoiddefault_threadlock(intacquire);
436442
staticboolsslVerifyProtocolVersion(constchar*version);
437443
staticboolsslVerifyProtocolRange(constchar*min,constchar*max);
444+
staticboolparse_int_param(constchar*value,int*result,PGconn*conn,
445+
constchar*context);
438446

439447

440448
/* global variable because fe-auth.c needs to access it */
@@ -1020,6 +1028,31 @@ parse_comma_separated_list(char **startptr, bool *more)
10201028
returnp;
10211029
}
10221030

1031+
/*
1032+
* Initializes the prng_state field of the connection. We want something
1033+
* unpredictable, so if possible, use high-quality random bits for the
1034+
* seed. Otherwise, fall back to a seed based on the connection address,
1035+
* timestamp and PID.
1036+
*/
1037+
staticvoid
1038+
libpq_prng_init(PGconn*conn)
1039+
{
1040+
uint64rseed;
1041+
structtimevaltval= {0};
1042+
1043+
if (pg_prng_strong_seed(&conn->prng_state))
1044+
return;
1045+
1046+
gettimeofday(&tval,NULL);
1047+
1048+
rseed= ((uint64)conn) ^
1049+
((uint64)getpid()) ^
1050+
((uint64)tval.tv_usec) ^
1051+
((uint64)tval.tv_sec);
1052+
1053+
pg_prng_seed(&conn->prng_state,rseed);
1054+
}
1055+
10231056
/*
10241057
*connectOptions2
10251058
*
@@ -1619,6 +1652,49 @@ connectOptions2(PGconn *conn)
16191652
else
16201653
conn->target_server_type=SERVER_TYPE_ANY;
16211654

1655+
/*
1656+
* validate load_balance_hosts option, and set load_balance_type
1657+
*/
1658+
if (conn->load_balance_hosts)
1659+
{
1660+
if (strcmp(conn->load_balance_hosts,"disable")==0)
1661+
conn->load_balance_type=LOAD_BALANCE_DISABLE;
1662+
elseif (strcmp(conn->load_balance_hosts,"random")==0)
1663+
conn->load_balance_type=LOAD_BALANCE_RANDOM;
1664+
else
1665+
{
1666+
conn->status=CONNECTION_BAD;
1667+
libpq_append_conn_error(conn,"invalid %s value: \"%s\"",
1668+
"load_balance_hosts",
1669+
conn->load_balance_hosts);
1670+
return false;
1671+
}
1672+
}
1673+
else
1674+
conn->load_balance_type=LOAD_BALANCE_DISABLE;
1675+
1676+
if (conn->load_balance_type==LOAD_BALANCE_RANDOM)
1677+
{
1678+
libpq_prng_init(conn);
1679+
1680+
/*
1681+
* This is the "inside-out" variant of the Fisher-Yates shuffle
1682+
* algorithm. Notionally, we append each new value to the array and
1683+
* then swap it with a randomly-chosen array element (possibly
1684+
* including itself, else we fail to generate permutations with the
1685+
* last integer last). The swap step can be optimized by combining it
1686+
* with the insertion.
1687+
*/
1688+
for (i=1;i<conn->nconnhost;i++)
1689+
{
1690+
intj=pg_prng_uint64_range(&conn->prng_state,0,i);
1691+
pg_conn_hosttemp=conn->connhost[j];
1692+
1693+
conn->connhost[j]=conn->connhost[i];
1694+
conn->connhost[i]=temp;
1695+
}
1696+
}
1697+
16221698
/*
16231699
* Resolve special "auto" client_encoding from the locale
16241700
*/
@@ -2626,6 +2702,32 @@ PQconnectPoll(PGconn *conn)
26262702
if (ret)
26272703
gotoerror_return;/* message already logged */
26282704

2705+
/*
2706+
* If random load balancing is enabled we shuffle the addresses.
2707+
*/
2708+
if (conn->load_balance_type==LOAD_BALANCE_RANDOM)
2709+
{
2710+
/*
2711+
* This is the "inside-out" variant of the Fisher-Yates shuffle
2712+
* algorithm. Notionally, we append each new value to the array
2713+
* and then swap it with a randomly-chosen array element (possibly
2714+
* including itself, else we fail to generate permutations with
2715+
* the last integer last). The swap step can be optimized by
2716+
* combining it with the insertion.
2717+
*
2718+
* We don't need to initialize conn->prng_state here, because that
2719+
* already happened in connectOptions2.
2720+
*/
2721+
for (inti=1;i<conn->naddr;i++)
2722+
{
2723+
intj=pg_prng_uint64_range(&conn->prng_state,0,i);
2724+
AddrInfotemp=conn->addr[j];
2725+
2726+
conn->addr[j]=conn->addr[i];
2727+
conn->addr[i]=temp;
2728+
}
2729+
}
2730+
26292731
reset_connection_state_machine= true;
26302732
conn->try_next_host= false;
26312733
}
@@ -4320,6 +4422,7 @@ freePGconn(PGconn *conn)
43204422
free(conn->outBuffer);
43214423
free(conn->rowBuf);
43224424
free(conn->target_session_attrs);
4425+
free(conn->load_balance_hosts);
43234426
termPQExpBuffer(&conn->errorMessage);
43244427
termPQExpBuffer(&conn->workBuffer);
43254428

‎src/interfaces/libpq/libpq-int.h

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,8 @@
2626
#include<netdb.h>
2727
#include<sys/socket.h>
2828
#include<time.h>
29-
#ifndefWIN32
29+
/* MinGW has sys/time.h, but MSVC doesn't */
30+
#ifndef_MSC_VER
3031
#include<sys/time.h>
3132
#endif
3233

@@ -82,6 +83,8 @@ typedef struct
8283
#endif
8384
#endif/* USE_OPENSSL */
8485

86+
#include"common/pg_prng.h"
87+
8588
/*
8689
* POSTGRES backend dependent Constants.
8790
*/
@@ -242,6 +245,13 @@ typedef enum
242245
SERVER_TYPE_PREFER_STANDBY_PASS2/* second pass - behaves same as ANY */
243246
}PGTargetServerType;
244247

248+
/* Target server type (decoded value of load_balance_hosts) */
249+
typedefenum
250+
{
251+
LOAD_BALANCE_DISABLE=0,/* Use the existing host order (default) */
252+
LOAD_BALANCE_RANDOM,/* Randomly shuffle the hosts */
253+
}PGLoadBalanceType;
254+
245255
/* Boolean value plus a not-known state, for GUCs we might have to fetch */
246256
typedefenum
247257
{
@@ -398,6 +408,7 @@ struct pg_conn
398408
char*ssl_max_protocol_version;/* maximum TLS protocol version */
399409
char*target_session_attrs;/* desired session properties */
400410
char*require_auth;/* name of the expected auth method */
411+
char*load_balance_hosts;/* load balance over hosts */
401412

402413
/* Optional file to write trace info to */
403414
FILE*Pfdebug;
@@ -469,6 +480,8 @@ struct pg_conn
469480

470481
/* Transient state needed while establishing connection */
471482
PGTargetServerTypetarget_server_type;/* desired session properties */
483+
PGLoadBalanceTypeload_balance_type;/* desired load balancing
484+
* algorithm */
472485
booltry_next_addr;/* time to advance to next address/host? */
473486
booltry_next_host;/* time to advance to next connhost[]? */
474487
intnaddr;/* number of addresses returned by getaddrinfo */
@@ -488,6 +501,8 @@ struct pg_conn
488501
PGVerbosityverbosity;/* error/notice message verbosity */
489502
PGContextVisibilityshow_context;/* whether to show CONTEXT field */
490503
PGlobjfuncs*lobjfuncs;/* private state for large-object access fns */
504+
pg_prng_stateprng_state;/* prng state for load balancing connections */
505+
491506

492507
/* Buffer for data received from backend and not yet processed */
493508
char*inBuffer;/* currently allocated buffer */

‎src/interfaces/libpq/meson.build

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,8 @@ tests += {
116116
'tests': [
117117
't/001_uri.pl',
118118
't/002_api.pl',
119+
't/003_load_balance_host_list.pl',
120+
't/004_load_balance_dns.pl',
119121
],
120122
'env': {'with_ssl': ssl_library},
121123
},

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp