86697486 connect requests dropped due to no listener

[chindi]

We have app & DB setup on same subnet/vlan .

OS hpux 11iv3 , 16cores , 256GB RAM , rx2800i4 .

When we are running batch code on App , we could see that there is performance issue on DB Server .

( Batch job taking more time to complete , than when it is ran when both app and DB are on same server )

PFB readings from DB server ;

root #/ >netstat -p tcp
tcp:
654453706493 packets sent
650197073511 data packets (376366843236469 bytes)
88691831 data packets (108410239806 bytes) retransmitted
3913278943 ack-only packets (257339208 delayed)
224 URG only packets
359253 window probe packets
111247638 window update packets
344275595 control packets
569117753978 packets received
553035675444 acks (for 376368073752414 bytes)
765282199 duplicate acks
0 acks for unsent data
536198671577 packets (61797283737811 bytes) received in-sequence
587 completely duplicate packets (33340 bytes)
21693 packets with some dup data (14249040 bytes duped)
37365619 out of order packets (53465467937 bytes)
19 packets (10127241484 bytes) of data after window
173 window probes
148841214 window update packets
1689433 packets received after close
6 segments discarded for bad checksum
0 bad TCP segments dropped due to state change
92845605 connection requests
76785850 connection accepts
169631455 connections established (including accepts)
257300745 connections closed (including 87670992 drops)
86762921 embryonic connections dropped
552874347201 segments updated rtt (of 552874347201 attempts)
10237772 retransmit timeouts
60961 connections dropped by rexmit timeout
359253 persist timeouts
2952926 keepalive timeouts
2902464 keepalive probes sent
6612 connections dropped by keepalive
0 connect requests dropped due to full queue
86697486 connect requests dropped due to no listener
0 suspect connect requests dropped due to aging
0 suspect connect requests dropped due to rate

[Bill Hassell]

It sounds like the DB is completely saturated and unable to reply to the requests. There's nothing you can do on the app side of things if the database is too slow. You need to profile the database to find out what is restricting performance. You've got a lot of horsepower in the server but a poorly designed database and SQL code will limit the app.

[chindi]

Hi Bill,

Let me mke it more clear to you :

We have environment where we have 3 App ( hpux 11iv3 ) and 2 DB Servers ( hpux 11iv3 ) all 16cores / 256GB Ram.
We did our POC of XtremIO Storage on Single Report Server ( hpux 11iv3 , 16cores , 256GB RAM ) for DB luns and for app luns on same server from VNX5500 Storage.
We ran batch job which took 18 min to complete on that Single Report Server .

But when we migrated to Production on XtremIO ( 1 DB and 1 App server ) the same batch job completed in 30 min.
 
Note : We ran the batch job when no other processes were running on Production ( Same as Single Report Server )
What could be the reason ?

We have compared kernel parameters all same.

PFA AWR reports of POC when it took 18 min , when app & db were on same server.

And Prod when it took 30 min , when app & db server were diferent.
Network is coming in picture ??
Oracle 11g db , hpux 11iv3 .

Note : We have 18 DB running on this single DB server.

             With avg SGA of 3GB each.

[Bill Hassell · Accepted solution]

The original concern: connect requests dropped due to no listener
This is simply a count of the number of times a SYN erquest came in with no corresponding service (or daemon) listening. This isn't a measurement of the network performance, just an indication of some runaway process trying to open a port that isn't enabled.

>> We ran batch job which took 18 min to complete on that Single Report Server .
>> But when we migrated to Production

What does "migrated" mean? Was the entire server (OS, database files, etc) copied using dd?
Or did you install the DB and then export/import the DB? Or did you replicate the data using Oracle tools?

>> Network is coming in picture ??

Probably not, but run Glance and look at the LAN performance. Are you seeing the rates in dozens of packets or thousands? If the packet rate is not in the thousands, the LAN is not an issue. The database is just not supplying data quickly.

When you said you ran the test with no other processes, I assume you mean that all the other databases were shut down, and the CPU and disk I/O were essentially zero, correct?

What does Glance report for disk I/O and CPU usage for the database during the two test runs?

>>  We have 18 DB running on this single DB server.
>>  With avg SGA of 3GB each.

3GB for SGA sounds quite small. For large and very active databases, 10-20GB is more common.
But all of this assumes that the database performance has been analyzed, not by HP-UX measuremenyts but with Oracle tools like statspack or similar. The most common answer from DBAs concerning a potential database performance issue is:

"I have checked the database and it is OK."

Not useful at all.

Have the application developers add a timestamp for each database request, that is, start time and finish time. This should be written to a log when debugging the application.

[chindi]

Hi Bill, Thanks for your inputs. We migrated DB by shutting it down and copying it from VNX to XtremIO. Currebtly our app is on VNX5500 & DB on XtremIO. We ran on test box when CPU/Disk I/O was almost zero . ( Though there were DB's running but no reports generation as such ) As far as LAN is concerned packets in / out for App-DB split ( 2 servers ) its 6104/14024 and packet rate out is 6256/13931. As far as LAN is concerned packets in / out for Single Server split its 1265/5489 and packet rate out is 2319/6365. CPU utilization was 80% idle during App-DB split batch job. CPU utilization was 75% idle during App-DB split batch job. Disk I/O was around 1600 IOPS for 2 luns ( out of 4 luns ) with avg serve of 0.4 ms and avg wait 0ms.