19.19. Postgres-XL Specific Parameters

Because Postgres-XL distributes data into multiple Datanodes and multiple Coordinators can accept transactions in parallel, the Coordinators must know what Coordinators and Datanodes to connect to. The Coordinators and Datanodes also must know where they can request transaction information. The following describes these additional GUC parameters.

max_pool_size (integer)

Specify the maximum connection pool of the Coordinator to Datanodes. Because each transaction can be involved by all the Datanodes, this parameter should at least be max_connections multiplied by number of Datanodes.

min_pool_size (integer)

Minumum number of the connection from the Coordinator to Datanodes.

pool_maintenance_timeout (integer)

This parameter specifies how long to wait until pooler maintenance is performed. During such maintenance, old idle connections are discarded. This parameter is useful in multi-tenant environments where many connections to many different databases may be used, so that idle connections may cleaned up.

remote_query_cost (integer)

This parameter specifies the cost overhead of setting up a remote query to obtain remote data. It is used by the planner in costing queries.

network_byte_cost (integer)

This parameter is used in query cost planning to estimate the cost involved in row shipping and obtaining remote data based on the expected data size. Row shipping is expensive and adds latency, so this setting helps to favor plans that minimizes row shipping.

sequence_range (integer)

This parameter is used to get several sequence values at once from GTM. This greatly speeds up COPY and INSERT SELECT operations where the target table uses sequences. Postgres-XL will not use this entire amount at once, but will increase the request size over time if many requests are done in a short time frame in the same session. After a short time without any sequence requests, decreases back down to 1. Note that any settings here are overriden if the CACHE clause was used in CREATE SEQUENCE or ALTER SEQUENCE.

max_coordinators (integer)

Maximum number of Coordinators that can be configured in the cluster.

max_datanodes (integer)

Maximum number of Datanodes that can be configured in the cluster.

pgxc_node_name (integer)

Specifies the node name. A node uses this parameter to identify itself with pgxc_node .nodename

pooler_port (integer)

Specifies the port number assigned to the connection pooler process. This is used in both the Coordinator and Datanode processes to connect to other nodes.

gtm_host (string)

Specifies the host name or IP address of the GTM. If you use GTM-Proxy, specify the GTM-Proxy's one.

gtm_port (integer)

Specifieis the port number of GTM.

xc_maintenance_mode (bool)

Specify to enter into maintenance mode. Turning this parameter to on will change behavior of several statements. Each statement behavior may not be compatible in future releases, so users must be extremely careful when they attempt to set this value to on.

Behavior of COMMIT PREPARED and ROLLBACK PREPARED statements are affected by this parameter.

If this is set to ON, you can do maintenance work to update Datanode locally through EXECUTE DIRECT or connecting directly to Datanodes. As a DBA, you are totally responsible to any side effects. You must be extremely careful not to bring in any inconsistencies to Postgres-XL database cluster.

This parameter can only be set by superuser with SET command. Otherwise, the setting will be ignored.

As a session parameter, this parameter is shared among all the connections of the session. It affects originating Coordinator as well as remote nodes involved in session.

shared_queues (integer)

Datanode Only

For some joins that occur in queries, data from one Datanode may need to be joined with data from another Datanode. Postgres-XL uses shared queues for this purpose. During execution each Datanode knows if it needs to produce or consume tuples, or both.

Note that there may be mulitple shared_queues used even for a single query. So a value should be set taking into account the number of connections it can accept and expected number of such joins occurring simultaneously.

shared_queue_size (integer)

Datanode Only

This parameter sets the size of each each shared queue allocated.

pgxl_remote_fetch_size (integer)

This parameter sets the number of rows processed and fetched from the datanode in one execution round. Once those many rows are fetched, coordinator then switches connection and fetches rows from another node, giving the previous node a chance to produce more rows.

This value must not be set too large or too small, because either might affect the cluster-wide parallelism. But user may set it to 0 to fetch all rows from the remote node in a single execution. This is an experimental way to utilise parallel query processing on the datanodes.