Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the network requirements between citus nodes in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

Network requirements between citus nodes:

1. The cn node accesses all worker nodes. Visits to oltp services are frequent.

2. When redistributing data, worker nodes access each other. The access frequency is small, and the OLAP service is common. Once it is possible, the data exchange throughput is large.

There are two modes for citus's cn node and worker node.

A persistent connection mode at the transaction level (a connection is established when each SQL is initiated, and the connection is released after the end of the SQL (unless in a transaction, the SQL ends and releases the connection immediately). ),

The other is session-level persistent connection mode (the connection is established when the session is initiated, and the connection is released at the end of the session. ).

1. When running SQL of OLAP class, the first instant connection mode is used (the concurrency of OLAP scenario is not high, and the extra cost of establishing a connection is not obvious)

You can open parameters in the worker node for tracking

Postgres=# show log_connections; log_connections-on (1 row) postgres=# show log_disconnections; log_disconnections-on (1 row)

Exampl

The following two SQL are in Custom Scan (Citus Task-Tracker) Custom Scan (Citus Real-Time) mode.

Postgres=# set citus.task_executor_type = task; ERROR: invalid value for parameter "citus.task_executor_type": "task" HINT: Available values: real-time, task-tracker. Postgres=# set citus.task_executor_type = 'task-tracker'; SET postgres=# explain select count (*) from pgbench_accounts QUERY PLAN -Aggregate (cost=0.00..0.00 rows=0 width=0)-> Custom Scan (Citus Task-Tracker) (cost=0.00..0.00 rows=0 width=0) Task Count: 128 Tasks Shown: One of 128 -> Task Node: host=172.24.211.224 port=1921 dbname=postgres-> Aggregate (cost=231.85..231.86 rows=1 width=8)-> Seq Scan on pgbench_accounts_106812 pgbench_accounts (cost=0.00..212.48 rows=7748 width=0) (8 rows) postgres=# set citus.task_executor_type = 'real-time' Postgres=# explain select count (*) from pgbench_accounts QUERY PLAN -Aggregate (cost=0.00..0.00 rows=0 width=0)-> Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) Task Count: 128 Tasks Shown: One of 128 -> Task Node: host=172.24.211.224 port=1921 dbname=postgres-> Aggregate (cost=231.85..231.86 rows=1 width=8)-> Seq Scan on pgbench_accounts_106812 pgbench_accounts (cost=0.00..212.48 rows=7748 width=0) (8 rows)

2. When running OLTP queries (usually with high concurrency, there is a connection pool at the front end (session persistence), and the session-level persistent connection mode (Custom Scan (Citus Router)).

The following SQL is in persistent connection mode (not released immediately, but later to reduce the overhead caused by high concurrency)

Postgres=# explain select * from pgbench_accounts where aid=5 QUERY PLAN -Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) Task Count: 1 Tasks Shown: All -> Task Node: host=172.24.211.224 port=1921 dbname=postgres-> Index Scan using pgbench_accounts_pkey_106836 on pgbench_accounts_106836 pgbench_accounts (cost=0.28..2.50 rows=1 width=97) Index Cond: (aid = 5) (7 rows)

Looking at the above two scenarios, CITUS should say that the design is already very good. It can satisfy both TP and AP.

But as mentioned earlier, connection maintenance is at the transaction or session level, and if the number of queries is large, or if the user uses a short connection, the overhead of establishing a connection will be prominent.

Newdb= >\ c postgres postgres You are now connected to database "postgres" as user "postgres" Postgres=# select * from pgbench_accounts where aid=1 Aid | bid | abalance | filler-+-- -- 1 | 1 | 7214 | (1 row) Time: 11.264 ms-including the cost of a new connection postgres=# select * from pgbench_accounts where aid=1 Aid | bid | abalance | filler-+-- -- 1 | 1 | 7214 | (1 row) Time: 0.905 ms-established connection using pgbouncer Resolve the cost of establishing a connection

On the worker node, deploy pgbouncer, and all connections established with the worker node go through the pgbouncer connection pool, so as to maintain the connection and reduce the overhead of frequent new connections of the worker node.

Deployment method

1. All worker nodes

Pgbouncer

Yum install-y pgbouncer

Configuration

Vi / etc/pgbouncer/pgb.ini [databases] newdb = host=/tmp dbname=newdb port=1921 user=digoal pool_size=128 reserve_pool=10 [pgbouncer] logfile = / var/log/pgbouncer/pgbouncer.log pidfile = / var/run/pgbouncer/pgbouncer.pid listen_addr = 0.0.0.0 listen_port = 8001 auth_type = any auth_file = / etc/pgbouncer/userlist.txt pool_mode = session server_reset_query = DISCARD ALL max_client_conn = 5000 default_pool_size = 128A maximum should not exceed 4 times the number of CPU

Start

Pgbouncer-d-u pgbouncer/ etc/pgbouncer/pgb.ini

In a citus cluster, there can be either a directly connected worker or a pgbouncer connection to the worker.

Different database can have different configurations.

For the following example, create a new database and connect to worker. Exe using pgbouncer.

2. All nodes (including cn, worker)

New database, plug-in

Su-postgres psql-c "create role digoal login;" psql-c "create database newdb;" psql-c "grant all on database newdb to digoal;" psql-U postgres newdb-c "create extension citus;" cn node

Add worker to the cluster configuration, using the connection port of pgbouncer

Su-postgres-c "psql-U postgres newdb-c\" SELECT * from master_add_node ('xxx.xxx.xxx.224', 8001);\ "" su-postgres-c "psql-U postgres newdb-c\" SELECT * from master_add_node (' xxx.xxx.xxx.230', 8001);\ "" su-postgres-c "psql-U postgres newdb-c\" SELECT * from master_add_node ('xxx.xxx.xxx.231', 8001) " \ "su-postgres-c" psql-U postgres newdb-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.225', 8001);\"su-postgres-c" psql-U postgres newdb-c\ "SELECT * from master_add_node (' xxx.xxx.xxx.227', 8001);\"su-postgres-c" psql-U postgres newdb-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.232', 8001)" \ "su-postgres-c" psql-U postgres newdb-c\ "SELECT * from master_add_node ('xxx.xxx.xxx.226', 8001);\"su-postgres-c" psql-U postgres newdb-c\ "SELECT * from master_add_node (' xxx.xxx.xxx.229', 8001);\"

MX configuration, again, add worker nodes to metadata synchronization.

Psql newdb postgresselect * from master_add_node ('xxx.xxx.xxx.224',8001); select * from master_add_node (' xxx.xxx.xxx.230',8001)

Turn on synchronization to metadata.

Select start_metadata_sync_to_node ('xxx.xxx.xxx.224',8001); select start_metadata_sync_to_node (' xxx.xxx.xxx.230',8001); Test

1. Tpc-b persistent connection test

Pgbench-I-s-U digoal newdbpsql-U digoal newdb select create_distributed_table ('pgbench_accounts','aid'); select create_distributed_table (' pgbench_branches','bid'); select create_distributed_table ('pgbench_tellers','tid'); select create_distributed_table (' pgbench_history','aid'); pgbench-M prepared-v-r-P 1-c 64-j 64-T 120-U digoal newdb-S

Performance is similar to not using pgbouncer, because it uses a long connection test simple SQL (citus itself uses session-level connection maintenance, no short connection problems).

It takes time to hide the first QUERY of citus.

In a new session, the first query always takes more time (including the time it takes to establish a connection if no connection is established. Even if a connection is established, it will take some extra time) (you can track and analyze the code for specific reasons).

The pgbouncer connection worker is used below, so the first QUERY does not include the time to establish the connection.

Newdb= >\ Q postgres@digoal-citus-gpdb-test001- > psql newdb digoal psql (10.5) Type "help" for help. \ timing newdb= > select * from pgbench_accounts where aid=5 Aid | bid | abalance | filler-+-- -- 5 | 1 | 0 | (1 row) Time: 6.016 ms-excluding new connection (established using pgbouncer) But it's also a few milliseconds more-- but it's about 5 milliseconds less late than not using pgbouncer. Newdb= > select * from pgbench_accounts where aid=5 Aid | bid | abalance | filler-+-- -- 5 | 1 | 0 | (1 row) Time: 0.989 ms

In the extra few milliseconds, Deng Biao and Wang Jian, friends of our community, give the following reasons. In many places, you need to check whether the installed version of citus is compatible with the version of the citus.control control file (for example, when loading the RELCACHE of distributed TABLE, this is the problem of the first access), and the error report is not compatible:

See the code for details

Https://github.com/citusdata/citus/blob/3fa04d8f2c8f27b1377fe4c1468ee47358117e3c/src/backend/distributed/utils/metadata_cache.c

/ * * CheckAvailableVersion compares CITUS_EXTENSIONVERSION and the currently * available version from the citus.control file. If they are not compatible, * this function logs an error with the specified elevel and returns false, * otherwise it returns true. * / boolCheckAvailableVersion (int elevel) {char * availableVersion = NULL; if (! EnableVersionChecks) {return true;} availableVersion = AvailableExtensionVersion () If (! MajorVersionsCompatible (availableVersion, CITUS_EXTENSIONVERSION)) {ereport (elevel, (errmsg ("loaded Citus library version differs from latest"available extension version"), errdetail ("Loaded library requires s) But the latest control "" file specifies s. ", CITUS_MAJORVERSION, availableVersion) Errhint ("Restart the database to load the latest Citus"library.")) Return false;} return true;} / * * AvailableExtensionVersion returns the Citus version from citus.control file. It also * saves the result, thus consecutive calls to CitusExtensionAvailableVersion will * not read the citus.control file again. * / static char * AvailableExtensionVersion (void) {ReturnSetInfo * extensionsResultSet = NULL; TupleTableSlot * tupleTableSlot = NULL; FunctionCallInfoData * fcinfo = NULL; FmgrInfo * flinfo = NULL; int argumentCount = 0; EState * estate = NULL; bool hasTuple = false; bool goForward = true; bool doCopy = false; char * availableExtensionVersion; InitializeCaches (); estate = CreateExecutorState () ExtensionsResultSet = makeNode (ReturnSetInfo); extensionsResultSet- > econtext = GetPerTupleExprContext (estate); extensionsResultSet- > allowedModes = SFRM_Materialize; fcinfo = palloc0 (sizeof (FunctionCallInfoData)); flinfo = palloc0 (sizeof (FmgrInfo)); fmgr_info (F_PG_AVAILABLE_EXTENSIONS, flinfo) InitFunctionCallInfoData (* fcinfo, flinfo, argumentCount, InvalidOid, NULL, (Node *) extensionsResultSet); / * pg_available_extensions returns result set containing all available extensions * / (* pg_available_extensions) (fcinfo); tupleTableSlot = MakeSingleTupleTableSlot (extensionsResultSet- > setDesc) HasTuple = tuplestore_gettupleslot (extensionsResultSet- > setResult, goForward, doCopy, tupleTableSlot); while (hasTuple) {Datum extensionNameDatum = 0; char * extensionName = NULL; bool isNull = false ExtensionNameDatum = slot_getattr (tupleTableSlot, 1, & isNull); extensionName = NameStr (* DatumGetName (extensionNameDatum)); if (strcmp (extensionName, "citus") = = 0) {MemoryContext oldMemoryContext = NULL; Datum availableVersion = slot_getattr (tupleTableSlot, 2, & isNull) / * we will cache the result of citus version to prevent catalog access * / oldMemoryContext = MemoryContextSwitchTo (CacheMemoryContext); availableExtensionVersion = text_to_cstring (DatumGetTextPP (availableVersion)); MemoryContextSwitchTo (oldMemoryContext); ExecClearTuple (tupleTableSlot); ExecDropSingleTupleTableSlot (tupleTableSlot) Return availableExtensionVersion;} ExecClearTuple (tupleTableSlot); hasTuple = tuplestore_gettupleslot (extensionsResultSet- > setResult, goForward, doCopy, tupleTableSlot);} ExecDropSingleTupleTableSlot (tupleTableSlot) Ereport (ERROR, (errcode (ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg ("citus extension is not found")); return NULL;} benefits of pgbouncer connection pooling connection to worker

1. It will have a better effect on the business layer short connection. The delay can be reduced by at least 5 milliseconds.

2. For a large number of complex queries (queries that require motion), the number of connections between nodes can be reduced.

This is the end of the content of "what are the network requirements between citus nodes in PostgreSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.