Queries Failing and the Logs Report "maximum number of prepared transactions reached"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

• A query that was executed on the database failed.
• The master log during that time period indicate or reports below messages when the "gang" (i.e one of the segment process died) got disconnected and had to reset.

  2014-08-01 18:13:58.549244 SGT,"cemweblogicpt","cempt_old",p58119,th443381536,"10.80.36.63","37098",2014-08-01 17:58:40 SGT,123996817,con49006,,seg-1,,,x123996817,sx1,"LOG","00000","no primary segworker group allocated",,,,,,,0,,"cdbgang.c",1625,
  2014-08-01 18:13:58.560980 SGT,"cemweblogicpt","cempt_old",p98099,th443381536,"10.80.36.62","38441",2014-08-01 18:13:15 SGT,0,con48992,,seg-1,,,,,"PANIC","XX000","Unable to complete 'Abort Prepared' broadcast for gid = 1406886598-0000604226 (cdbtm.c:999)",,,,,,,0,,"cdbtm.c",999,"Stack trace:
  1    0xabfba9 postgres  (elog.c:469)
  2    0xac1ce8 postgres elog_finish (elog.c:1417)
  3    0xc7cb26 postgres doDtxPhase2Retry (cdbtm.c:999)
  4    0x956bc1 postgres PostgresMain (palloc.h:142)
  5    0x8b454e postgres  (postmaster.c:6664)	
  6    0x8b72e0 postgres PostmasterMain (postmaster.c:7597)
  7    0x7b730f postgres main (main.c:206)
  8    0x7f811769ccdd libc.so.6 __libc_start_main (??:0)
  9    0x487599 postgres  (??:0)
  "

• Further dig on the master shows the start of the issue was a while back ( in this example 3 hours ago ) and started with segment server sdw1:1025

  2014-08-01 15:42:48.877458 SGT,"cemweblogicpt","cempt_old",p64818,th443381536,"10.80.36.63","34084",2014-08-01 11:27:00 SGT,123685381,con21516,cmd2,seg-1,,dx321562,x123685381,sx1,"LOG","58M01","Master unable to connect to seg0 sdw1:1025 with options gpqeid=21516;460127353758671;true options=' -c gp_segment=0 -c gp_qd_hostname=172.28.8.250 -c gp_qd_port=5432 -c gp_qd_callback_info=port

• During that of the time period, the master log also reports the issue of a limit has reached.

  maximum number of prepared transactions reached  (seg1 sdw1:1026 pid=72359)
  maximum number of prepared transactions reached  (seg2 sdw1:1027 pid=72417)
  maximum number of prepared transactions reached  (seg3 sdw1:1028 pid=72433)
  maximum number of prepared transactions reached  (seg8 sdw2:1025 pid=108850)
  maximum number of prepared transactions reached  (seg10 sdw2:1027 pid=108879)
  maximum number of prepared transactions reached  (seg11 sdw2:1028 pid=108891)

Environment

Cause

The cause of the issue is due to a low value of "max_prepared_transactions" is set on segments.

DEV gpadmin@mdw:/data/master/gpseg-1/pg_log$ gpconfig -s max_prepared_transactions
Values on all segments are consistent
GUC          : max_prepared_transactions
Master  value: 500
Segment value: 50

 

Resolution

Assign a proper value to the parameter 'max_prepared_transactions', check on the admin guide for more information on the parameter,