Postgres-XL happens ERROR:maximum number of prepared transactions reached

Question

I am testing Postgres-XL 9.5r1.2. I build a cluster with gtm, coord1, datanode1, datanode2.

I use pgbench to test its performance with below commands:

pgbench -h db -U postgres -i -s 100 testdb;
pgbench -h db -U postgres -c 70 -t 40 -r testdb

In the test result, the below error happens many times

Client NN aborted in state 13: ERROR:  maximum number of prepared transactions reached
HINT:  Increase max_prepared_transactions (currently 10).

So, it seems that I need to adjust max_prepared_transactions.

My problem is, on which host should I adjust postgresql.conf ? coord1 ? datanode1 and datanode2 ? or what else ?

Answer 1

You can set max_prepared_transactions in postgres.conf for both coordinator and datanodes, but it needs to be set differently:

• For datanodes: max_connections of datanodes.

• For coordinator: at least the number of coordinators in the cluster.

See Postgres-XL docs.

Note that max_connections also needs to be set differently for coordinator and datanodes:

• For datanodes: sum of max_connections of all coordinators.

• For coordinator: number of connections accepted from client application.