Database Query/Backup Fails due to Error "could not create thread 1 of 1"

Products

• VMware Tanzu Greenplum
• VMware Tanzu Data Suite
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• Greenplum

Issue/Introduction

Symptoms:

A database query is failing and the database logs report messages of "could not create thread 1 of 1":

2015-03-21 22:26:35.899357 CET,"gpadmin","kettle_prod",p31231,th334903040,"10.18.191.182","32435",2015-03-21 22:25:57 CET,76676764,con10997991,cmd1161,seg-1,,dx14910989,x76676764,sx1,"FATAL","XX000","could not create thread 1 of 1 (cdbdisp.c:754)","pthread_create() failed with err 11",,,,,"COMMIT",0,,"cdbdisp.c",754,"Stack trace:
1 0xa7494e postgres errstart + 0x4ee
2 0xb4adab postgres cdbdisp_dispatchToGang + 0x45b
3 0xb52204 postgres cdbdisp_dispatchDtxProtocolCommand + 0x194
4 0xc2bc98 postgres  + 0xc2bc98
5 0xc33384 postgres prepareDtxTransaction + 0x234
6 0x4f9f4a postgres CommitTransaction + 0x13a
7 0x4fe4c5 postgres CommitTransactionCommand + 0xc5
8 0x9134f5 postgres PostgresMain + 0x36a5
9 0x878265 postgres  + 0x878265
10 0x87f2d0 postgres PostmasterMain + 0x12f0
11 0x78364a postgres main + 0x4da
12 0x33c2c1ed1d libc.so.6 __libc_start_main + 0xfd
13 0x47d2a9 postgres  + 0x47d2a9

2015-03-21 22:28:01.687998 CET,"gpadmin","planeta",p24908,th334903040,"[local]",,2015-03-21 22:28:01 CET,76676942,con10998086,,seg-1,,,x76676942,sx1,"FATAL","XX000","InitMotionLayerIPC: failed to create thread (ic_udpifc.c:1455)","pthread_create() failed with err 11",,,,,,0,,"ic_udpifc.c",1455,"Stack trace:
1 0xa7494e postgres errstart + 0x4ee
2 0xb1def3 postgres InitMotionUDPIFC + 0x343
3 0xb08e1e postgres InitMotionLayerIPC + 0xae
4 0xc34425 postgres cdb_setup + 0x45
5 0xa8e4ec postgres InitPostgres + 0x25c
6 0x9103aa postgres PostgresMain + 0x55a
7 0x878265 postgres  + 0x878265
8 0x87f2d0 postgres PostmasterMain + 0x12f0
9 0x78364a postgres main + 0x4da
10 0x33c2c1ed1d libc.so.6 __libc_start_main + 0xfd
11 0x47d2a9 postgres  + 0x47d2a9
"
2015-03-21 22:28:01.692340 CET,"gpadmin","planeta",p24911,th334903040,"[local]",,2015-03-21 22:28:01 CET,76676946,con10998087,cmd1,seg-1,,dx14911075,x76676946,sx1,"LOG","00000","statement: select version()",,,,,,"select version()",0,,"postgres.c",1563,
The messages related to "could not fork new process" is indicating that it cannot fork Unix process.

2015-03-21 22:28:01.700023 CET,,,p19781,th334903040,,,,0,,,seg-1,,,,,"LOG","00000","could not fork new process for connection: Resource temporarily unavailable",,,,,,,0,,"postmaster.c",6595,
2015-03-21 22:28:01.700079 CET,,,p19781,th334903040,,,,0,,,seg-1,,,,,"LOG","00000","could not fork new process for connection: Resource temporarily unavailable",,,,,,,0,,"postmaster.c",6595,
2015-03-21 22:28:01.704107 CET,,,p19781,th334903040,,,,0,,,seg-1,,,,,"LOG","00000","could not fork new process for connection: Resource temporarily unavailable",,,,,,,0,,"postmaster.c",6595,
2015-03-21 22:28:01.704199 CET,,,p19781,th334903040,,,,0,,,seg-1,,,,,"LOG","00000","could not fork new process for connection: Resource temporarily unavailable",,,,,,,0,,"postmaster.c",6595,

Environment

Cause

The issue is caused due to maximum processes limit setting on the Unix server, which resulted in Pivotal Greenplum unable to spawn new thread/fork.

[gpadmin@mdw gpAdminLogs]$ gpssh -f ~/gpconfigs/hostfile "ulimit -u"
[sdw4] 1024
[sdw5] 1024
[sdw6] 1024
[sdw7] 1024
[sdw1] 1024
[sdw3] 1024
[smdw] 1024
[ mdw] 1024

Resolution

Change the settings for "max user process" to recommended values as described in Configuring Your Systems

Log out and back into the hosts and verify the new limits with "ulimit -a"

Note A database restart must be done for the new limits are applied to all Greenplum processes.