We have a situation where the server/client count exceeds the number of database connections.
From promon -> R&D -> 1 -> 17:
11/30/10 Status: Servers By Broker
11:17:54
Sv Pend. Cur. Max. Port
No Pid Type Protocol Logins Users Users Users Num
0 16144 Login TCP 42193 0 0 10 16000
2 7418 Auto TCP 8105 0 10 10 1375
3 11792 Auto TCP 6270 0 10 10 1417
4 10209 Auto TCP 1686 0 10 10 1427
5 628 Auto TCP 4054 0 10 10 1435
6 5220 Auto TCP 3008 0 10 10 1437
7 14091 Auto TCP 3498 0 10 10 1438
8 9343 Auto TCP 908 0 9 10 1378
9 13806 Auto TCP 3682 0 9 10 1449
10 29497 Auto TCP 6449 0 9 10 1450
11 1151 Auto TCP 4466 0 9 10 1454
1 6647 Login TCP 0 0 0 5 16150
for a total of 96.
promon -> 5 is:
Activity - Sampled at 11/30/10 11:17 for 614:16:37.
Event Total Per Sec Event Total Per Sec
Commits 27883950 12.6 Undos 23425 0.0
Record Updates 14475348 6.5 Record Reads 3193942191 1444.3
Record Creates 3385250 1.5 Record Deletes 3853133 1.7
DB Writes 2406018 1.1 DB Reads 112487662 50.9
BI Writes 1537086 0.7 BI Reads 3096988 1.4
AI Writes 1071652 0.5
Record Locks 159345026 72.1 Record Waits 205 0.0
Checkpoints 234 0.0 Buffs Flushed 11725 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 46 % Writes by AIW 89 %
Buffer Hits 99 %
DB Size 35 GB BI Size 2930 MB AI Size 5296 K
FR chain 8486 blocks RM chain 239742 blocks
Shared Memory 563195904 Segments 1
11 Servers, 68 Users (17 Local, 51 Remote, 12 Batch),2 Apws
and a list from proshut <db> -C list is:
a300sue1:/a4381/amfp438p > proshut amfp438p -C list | wc -l
75
...and out users who are connecting through the servers are getting "no more resources" errors.
v10.1B SP3/HPUX 11.11
Any ideas?
One possible cause of this is if clients terminate or are terminated without disconnecting. Eventually the server will notice that the client is gone when the kernel's keepalive timeout is reached (default is around 2 hours).
This makes perfect sense to us. The database is is Holland, Michigan while the users who connect to it are in Mexico at a plant site that has a history of network problems.
I don't know if there is a way for us to proactively (no pun intended) remove the users from the servers, though.
Thanks!!
If you lower the TCP keepalive timeout setting on the server, that might
help. When the keepalive timeout expires, the database server processes will
discover clients it can no lnger communicate with and they will be
disconnected.