Has anyone seen client sessions disconnect from a database b

Posted by atrimbw on 30-Nov-2010 11:00

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?

All Replies

Posted by gus on 30-Nov-2010 12:24

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).

Posted by atrimbw on 06-Dec-2010 09:16

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!!

Posted by gus on 06-Dec-2010 11:40

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.

This thread is closed