| Matthias Apitz 2006-03-23, 7:23 am |
|
Hi,
We're still locking for the reasons of short, but seriously, performance
drop-out's on ASE 12.5.1 and SuSE Linux. These drop-out's can be described
in that manner that a simple ESQL/C statement, like the below one
SELECT count(*) from bestnotiz where bnr = x and band = y
will last some seconds (in the case below 97!!! seconds) and you can
re-run it some moments later in milliseconds. I also checked the case
below with 'showplan' and it *is* using the indexes and does not
do a full table scan.
To catch such situation I monitored with tcpdump the connection
between the ESQL/C client and the ASE and wrote some scripts to
find such a situation of a drop-out in the data produced by
tcpdump. Here you can see such a case in Detail
ESQL/C client sends 'SELECT count(*)...' to ASE server (port 4000):
1143107913.036112 10.128.0.249.37597 > 10.128.0.249.4000: P 964223:964350(127) ack 2528510 win 32767 <nop,nop,timestamp 381564505 381564505> (DF)
0x0000 4500 00b3 97dc 4000 4006 8b77 0a80 00f9 E.....@.@..w....
0x0010 0a80 00f9 92dd 0fa0 04e5 1a1a 0511 0140 ...............@
0x0020 8018 7fff f485 0000 0101 080a 16be 3659 ..............6Y
0x0030 16be 3659 0f01 007f 0000 0000 2159 0000 ..6Y........!Y..
0x0040 0001 5345 4c45 4354 2043 4f55 4e54 282a ..SELECT.COUNT(*
0x0050 2920 0909 4652 4f4d 2062 6573 746e 6f74 )...FROM.bestnot
0x0060 697a 2009 0957 4845 5245 2062 6e72 203d iz...WHERE.bnr.=
0x0070 2040 7371 6c31 5f62 6e72 2041 4e44 2062 .@sql1_bnr.AND.b
0x0080 616e 6420 3d20 3020 0909 414e 4420 6578 and.=.0...AND.ex
0x0090 656d 706c 6172 203d 2030 200e 0000 0001 emplar.=.0......
0x00a0 0000 0000 0000 0000 0000 2604 00d7 04f9 ..........&.....
0x00b0 3a00 00 :..
ASE answers after a few millisecs, not even time enough for a
separate ACK pakage from TCP layer, the ACK comes with the data pkg:
1143107913.037232 10.128.0.249.4000 > 10.128.0.249.37597: P 2528510:2528558(48) ack 964350 win 46182 <nop,nop,timestamp 381564506 381564505> (DF)
0x0000 4500 0064 eed9 4000 4006 34c9 0a80 00f9 E..d..@.@.4.....
0x0010 0a80 00f9 0fa0 92dd 0511 0140 04e5 1a99 ...........@....
0x0020 8018 b466 fe6d 0000 0101 080a 16be 365a ...f.m........6Z
0x0030 16be 3659 0401 0030 0000 0000 6111 0000 ..6Y...0....a...
0x0040 0001 0000 0000 0000 1000 0000 0700 0000 ................
0x0050 3800 ae01 0000 d100 0000 00fd 1400 0100 8...............
0x0060 0100 0000 ....
ESQL/C client sends next SQL:
1143107913.037347 10.128.0.249.37597 > 10.128.0.249.4000: P 964350:964485(135) ack 2528558 win 32767 <nop,nop,timestamp 381564506 381564506> (DF)
0x0000 4500 00bb 97dd 4000 4006 8b6e 0a80 00f9 E.....@.@..n....
0x0010 0a80 00f9 92dd 0fa0 04e5 1a99 0511 0170 ...............p
0x0020 8018 7fff 47ca 0000 0101 080a 16be 365a ....G.........6Z
0x0030 16be 365a 0f01 0087 0000 0000 2150 0000 ..6Z........!P..
0x0040 0001 5345 4c45 4354 2043 4f55 4e54 282a ..SELECT.COUNT(*
0x0050 2920 0909 4652 4f4d 2062 6573 746e 6f74 )...FROM.bestnot
0x0060 697a 2009 0957 4845 5245 2062 6e72 203d iz...WHERE.bnr.=
0x0070 2040 7371 6c31 5f62 6e72 2041 4e44 2062 .@sql1_bnr.AND.b
0x0080 616e 6420 3d20 4073 716c 325f 6261 6e64 and.=.@sql2_band
0x0090 2020 1a00 0000 0200 0000 0000 0000 0000 ................
0x00a0 0026 0400 0000 0000 0000 0000 0026 0400 .&...........&..
0x00b0 d704 f93a 0000 0400 0000 00 ...:.......
TCP/IP gives ACK for it;
1143107913.076990 10.128.0.249.37597 > 10.128.0.249.4000: . ack 2528667 win 32767 <nop,nop,timestamp 381564510 381564506> (DF)
0x0000 4500 0034 97e1 4000 4006 8bf1 0a80 00f9 E..4..@.@.......
0x0010 0a80 00f9 92dd 0fa0 04e5 1b62 0511 01dd ...........b....
0x0020 8010 7fff 7be5 0000 0101 080a 16be 365e ....{.........6^
0x0030 16be 365a ..6Z
now 97!!! seconds nothing happens, and after this 97 secs
the client library send an additional package, don't know what it
is exactly; for me it looks like somekind of 'poll' or
some re-quest for the answer:
1143108010.144410 10.128.0.249.37597 > 10.128.0.249.4000: P 964551:964567(16) ack 2528667 win 32767 <nop,nop,timestamp 381574216 381564506> (DF)
0x0000 4500 0044 97e2 4000 4006 8be0 0a80 00f9 E..D..@.@.......
0x0010 0a80 00f9 92dd 0fa0 04e5 1b62 0511 01dd ...........b....
0x0020 8018 7fff ae3e 0000 0101 080a 16be 5c48 .....>........\H
0x0030 16be 365a 0f01 0010 0000 0000 8005 0098 ..6Z............
0x0040 17f5 0001 ....
now ASE is responding with the data of the SELECT:
1143108010.144767 10.128.0.249.4000 > 10.128.0.249.37597: P 2528667:2528694(27) ack 964567 win 46182 <nop,nop,timestamp 381574216 381574216> (DF)
0x0000 4500 004f eede 4000 4006 34d9 0a80 00f9 E..O..@.@.4.....
0x0010 0a80 00f9 0fa0 92dd 0511 01dd 04e5 1b72 ...............r
0x0020 8018 b466 1ebb 0000 0101 080a 16be 5c48 ...f..........\H
0x0030 16be 5c48 0401 001b 0000 0000 8307 0098 ..\H............
0x0040 17f5 0003 4000 fd00 0003 0000 0000 00 ....@..........
What could be the reason for this. The server has 4 CPU's nearly
idle...
Thx
matthias
--
Matthias Apitz / Sisis Informationssysteme GmbH
ein Tochterunternehmen der OCLC PICA B.V. Leiden (NL)
D-82041 Oberhaching, Gruenwalder Weg 28g
Fon: +49 89 / 61308-351, Fax: -399, Mobile +49 170 4527211
http://www.sisis.de/~guru/
|