|
|
|
|
|
|
| Author |
Message |
Matthias Apitz
*nix forums beginner
Joined: 11 Jul 2005
Posts: 22
|
 Posted: Thu Mar 23, 2006 11:56 am Post subject:
ASE 12.5.1 performance drop-out's
|
|
|
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(4 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/ |
|
| Back to top |
|
 |
Matthias Apitz
*nix forums beginner
Joined: 11 Jul 2005
Posts: 22
|
| Posted: Thu Apr 27, 2006 4:07 pm Post subject:
Re: ASE 12.5.1 performance drop-out's
|
|
|
Matthias Apitz <guru@Sisis.de> writes:
| Quote: | 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.
... |
We're still on this problem and coming closer and closer with logs of
Linux 'top' and 'tcpdump' to see what's going on. Last night I could
isolate such a situation and it turned out that the dataserver is in
such a moment for 3 secs consuming nearly all CPU and not responding
to *any* of its TCP/IP connections on port 4000; here is the top-output:
top - 18:35:15 up 8 days, 16:09, 1 user, load average: 0.11, 0.10, 0.09
Tasks: 177 total, 5 running, 172 sleeping, 0 stopped, 0 zombie
Cpu(s): 16.8% us, 79.2% sy, 0.0% ni, 3.0% id, 1.0% wa, 0.0% hi, 0.0% si
Mem: 2075504k total, 1949792k used, 125712k free, 25260k buffers
Swap: 4195320k total, 8k used, 4195312k free, 876940k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
32291 sybase 16 0 270m 246m 267m R 95.2 12.2 48:33.08 dataserver
22628 root 6 -10 1892 1008 1536 R 2.0 0.0 3:47.01 top
25597 root 15 0 712m 454m 49m R 0.0 22.4 0:00.22 java
25845 root 16 0 712m 454m 49m R 0.0 22.4 0:00.89 java
29901 sybase 20 0 270m 246m 267m R 0.0 12.2 0:00.00 dataserver
and here is the TCPDUMP of that moment; one ASE-client send over a
COMMIT and there is no response for 3 secs in any of the connetions:
18:35:14.608942 IP 10.4.234.83.57661 > 10.4.234.83.4000: P 372062:372094(32) ack 2901787 win 32696 <nop,nop,timestamp 749472986 749472986>
0x0000 4500 0054 03ee 4000 4006 4e07 0a04 ea53 E..T..@.@.N....S
0x0010 0a04 ea53 e13d 0fa0 13c5 d578 172e 1dd1 ...S.=.....x....
0x0020 8018 7fb8 e8f5 0000 0101 080a 2cac 0cda ............,...
0x0030 2cac 0cda 0f01 0020 0000 0000 2113 0000 ,...........!...
0x0040 0000 434f 4d4d 4954 2054 5241 4e53 4143 ..COMMIT.TRANSAC
0x0050 5449 4f4e TION
18:35:14.648797 IP 10.4.234.83.4000 > 10.4.234.83.57661: . ack 372094 win 47874 <nop,nop,timestamp 749473026 749472986>
0x0000 4500 0034 3377 4000 4006 1e9e 0a04 ea53 E..43w@.@......S
0x0010 0a04 ea53 0fa0 e13d 172e 1dd1 13c5 d598 ...S...=........
0x0020 8010 bb02 509c 0000 0101 080a 2cac 0d02 ....P.......,...
0x0030 2cac 0cda ,...
18:35:17.782157 IP 10.4.234.83.4000 > 10.4.234.83.57661: P 2901787:2901804(17) ack 372094 win 47874 <nop,nop,timestamp 749476160 749472986>
0x0000 4500 0045 3378 4000 4006 1e8c 0a04 ea53 E..E3x@.@......S
0x0010 0a04 ea53 0fa0 e13d 172e 1dd1 13c5 d598 ...S...=........
0x0020 8018 bb02 e8e6 0000 0101 080a 2cac 1940 ............,..@
0x0030 2cac 0cda 0401 0011 0000 0000 fd00 0002 ,...............
0x0040 0000 0000 00 .....
Any idea for that?
matthias |
|
| Back to top |
|
|
Matthias Apitz
*nix forums beginner
Joined: 11 Jul 2005
Posts: 22
|
| Posted: Tue May 09, 2006 10:12 am Post subject:
Re: ASE 12.5.1 performance drop-out's
|
|
|
Matthias Apitz <guru@Sisis.de> writes:
...
| Quote: | We're still on this problem and coming closer and closer with logs of
Linux 'top' and 'tcpdump' to see what's going on. Last night I could
isolate such a situation and it turned out that the dataserver is in
such a moment for 3 secs consuming nearly all CPU and not responding
to *any* of its TCP/IP connections on port 4000; here is the top-output:
|
To close this threat, it turned out that after configuring
allow sql server async i/o = 0
(the DEFAULT is 1) the problem of the CPU-loops in the ASE went
away. The kernel of the system (SuSE SLES 9) is 2.6.5-7.97-smp
and the glibc installed is glibc-2.3.3-98.28.
It seems that there are a lot of other issues if one Googles around
with 'allow sql server async i/o Linux'. Any comments?
matthias
--
Matthias Apitz
Manager Technical Support - OCLC PICA GmbH
Gruenwalder Weg 28g - 82041 Oberhaching - Germany
t +49-89-61308 351 - f +49-89-61308 399 - m +49-170-4527211
e <m.apitz@oclcpica.org> - w http://www.oclcpica.org/ http://guru.UnixLand.de/ |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Sat Jan 10, 2009 2:59 am | All times are GMT
|
|
Magazine Subscriptions | Mortgages | Homeowner Loans | Biker shirts shop | Credit Card Debt Consolidation
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Sybase
