Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

New System Performance

11 posts in General Discussion Last posting was on 2012-03-02 13:38:45.0Z
SteveS Posted on 2012-02-28 18:39:59.0Z
Sender: 1e85.4f4d185c.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4d1f7f.2011.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4f4d1f7f"
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 28 Feb 2012 10:39:59 -0800
X-Trace: forums-1-dub 1330454399 172.20.134.41 (28 Feb 2012 10:39:59 -0800)
X-Original-Trace: 28 Feb 2012 10:39:59 -0800, 172.20.134.41
Lines: 348
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30950
Article PK: 73839

I posted previously I'm trying to understand the performance
of my new pre-production system. We currently run ASE 15.0.3
ESD1 on HPUX on a PA-RISC system. The new environment is ASE
15.5 ESD5 on HPUX on Itanium. I've encountered something I
can't explain. To compare cpu speed, I created a simple
stored procedure that loops 10000 times and I iterate
through that 15 times to find an average per iteration. You
can see in the attachments for some unknown reason the speed
is slow for some random number of iterations and then gets
fast. Once it runs fast once, it will continue to run fast
until you disconnect. Once you reconnect the slow pattern
continues. The two attachments represent back to back runs
of the same proc. One other thing I noticed is the new
system runs at least twice as fast as the current when the
loop count is 1000, but is slower than the current one when
the count is 10,000. I have the system to myself and this is
all that's running at the time. I have been experiencing
random slowness in other processes as well.

create proc dba_cpu
as
declare @total int,@val2 int,@val3 int
select @total=0,@val3=1
declare @val datetime

while @val3 <16
begin
select @val=getdate()
select @val2 = 0
while @val2 < 10000
begin
select @val2 = @val2+1
end
select @total=@total+datediff(ms,@val,getdate())
select 'Iteration ',@val3,'Elapsed ms'=datediff(ms,@val,getdate())
select @val3 = @val3+1
end
select 'Average Elapsed'=@total/@val3


SPID
------
399

(1 row affected)

-------
RUN ONE

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 1690

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 1760

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 1720

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 1760

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 1800

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 1573

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 900

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 900

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 880

(1 row affected)
Average Elapsed
---------------
1141

(1 row affected)
(return status = 0)

-------
RUN TWO

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 900

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 256

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 96

(1 row affected)
Average Elapsed
---------------
542

(1 row affected)
(return status = 0)

---------
RUN THREE

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 76

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 96

(1 row affected)
Average Elapsed
---------------
88

create proc dba_cpu
as
declare @total int,@val2 int,@val3 int
select @total=0,@val3=1
declare @val datetime

while @val3 <16
begin
select @val=getdate()
select @val2 = 0
while @val2 < 10000
begin
select @val2 = @val2+1
end
select @total=@total+datediff(ms,@val,getdate())
select 'Iteration ',@val3,'Elapsed ms'=datediff(ms,@val,getdate())
select @val3 = @val3+1
end
select 'Average Elapsed'=@total/@val3


SPID
------
398

(1 row affected)

-------
RUN ONE

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 1586

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 880

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 883

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 896

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 580

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 73

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 96

(1 row affected)
Average Elapsed
---------------
457

(1 row affected)
(return status = 0)

-------
RUN TWO

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 76

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 93

(1 row affected)
Average Elapsed
---------------
87

(1 row affected)
(return status = 0)

---------
RUN THREE

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 1 73

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 2 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 3 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 4 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 5 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 6 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 7 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 8 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 9 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 10 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 11 96

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 12 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 13 76

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 14 93

(1 row affected)
Elapsed ms
---------- ----------- -----------
Iteration 15 96

(1 row affected)
Average Elapsed
---------------
86


SteveS Posted on 2012-02-28 18:43:41.0Z
Sender: 1e85.4f4d185c.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4d205d.204e.1681692777@sybase.com>
References: <4f4d1f7f.2011.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 28 Feb 2012 10:43:41 -0800
X-Trace: forums-1-dub 1330454621 172.20.134.41 (28 Feb 2012 10:43:41 -0800)
X-Original-Trace: 28 Feb 2012 10:43:41 -0800, 172.20.134.41
Lines: 597
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30951
Article PK: 73840


> I posted previously I'm trying to understand the
> performance of my new pre-production system. We currently
> run ASE 15.0.3 ESD1 on HPUX on a PA-RISC system. The new
> environment is ASE 15.5 ESD5 on HPUX on Itanium. I've
> encountered something I can't explain. To compare cpu
> speed, I created a simple stored procedure that loops
> 10000 times and I iterate through that 15 times to find an
> average per iteration. You can see in the attachments for
> some unknown reason the speed is slow for some random
> number of iterations and then gets fast. Once it runs fast
> once, it will continue to run fast until you disconnect.
> Once you reconnect the slow pattern continues. The two
> attachments represent back to back runs of the same proc.
> One other thing I noticed is the new system runs at least
> twice as fast as the current when the loop count is 1000,
> but is slower than the current one when the count is
> 10,000. I have the system to myself and this is all that's
> running at the time. I have been experiencing random
> slowness in other processes as well.
>

I should add that the batch isql process run the proc three
times in the same session to show the consisten fast speed
once that's achieved.
>
> [dba_cpu_run1.txt]
> create proc dba_cpu
> as
> declare @total int,@val2 int,@val3 int
> select @total=0,@val3=1
> declare @val datetime
>
> while @val3 <16
> begin
> select @val=getdate()
> select @val2 = 0
> while @val2 < 10000
> begin
> select @val2 = @val2+1
> end
> select @total=@total+datediff(ms,@val,getdate())
> select 'Iteration ',@val3,'Elapsed ms'=datediff(ms
> ,@val,getdate())
> select @val3 = @val3+1
> end
> select 'Average Elapsed'=@total/@val3
>
>
> SPID
> ------
> 399
>
> (1 row affected)
>
> -------
> RUN ONE
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 1690
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 1760
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 1720
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 1760
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 1800
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 1573
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 900
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 900
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 880
>
> (1 row affected)
> Average Elapsed
> ---------------
> 1141
>
> (1 row affected)
> (return status = 0)
>
> -------
> RUN TWO
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 900
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 256
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 96
>
> (1 row affected)
> Average Elapsed
> ---------------
> 542
>
> (1 row affected)
> (return status = 0)
>
> ---------
> RUN THREE
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 76
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 96
>
> (1 row affected)
> Average Elapsed
> ---------------
> 88
>
>
>
> <a
> href="/cgi-bin/webnews.cgi/dba_cpu_run2.txt?cmd=itempart-3
> 0950&part=3/dba_cpu_run2.txt">dba_cpu_run2.txt</a> create
> proc dba_cpu as
> declare @total int,@val2 int,@val3 int
> select @total=0,@val3=1
> declare @val datetime
>
> while @val3 <16
> begin
> select @val=getdate()
> select @val2 = 0
> while @val2 < 10000
> begin
> select @val2 = @val2+1
> end
> select @total=@total+datediff(ms,@val,getdate())
> select 'Iteration ',@val3,'Elapsed ms'=datediff(ms
> ,@val,getdate())
> select @val3 = @val3+1
> end
> select 'Average Elapsed'=@total/@val3
>
>
> SPID
> ------
> 398
>
> (1 row affected)
>
> -------
> RUN ONE
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 1586
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 880
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 883
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 896
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 580
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 73
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 96
>
> (1 row affected)
> Average Elapsed
> ---------------
> 457
>
> (1 row affected)
> (return status = 0)
>
> -------
> RUN TWO
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 76
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 93
>
> (1 row affected)
> Average Elapsed
> ---------------
> 87
>
> (1 row affected)
> (return status = 0)
>
> ---------
> RUN THREE
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 1 73
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 2 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 3 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 4 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 5 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 6 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 7 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 8 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 9 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 10 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 11 96
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 12 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 13 76
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 14 93
>
> (1 row affected)
> Elapsed ms
> ---------- ----------- -----------
> Iteration 15 96
>
> (1 row affected)
> Average Elapsed
> ---------------
> 86
>
> [Attachment: dba_cpu_run1.txt]
> [Attachment: dba_cpu_run2.txt]


Rob V Posted on 2012-02-28 19:37:12.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
References: <4f4d1f7f.2011.1681692777@sybase.com>
In-Reply-To: <4f4d1f7f.2011.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f4d2ce8@forums-1-dub>
Date: 28 Feb 2012 11:37:12 -0800
X-Trace: forums-1-dub 1330457832 10.22.241.152 (28 Feb 2012 11:37:12 -0800)
X-Original-Trace: 28 Feb 2012 11:37:12 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30952
Article PK: 73841


On 28-Feb-2012 19:39, SteveS wrote:
> I posted previously I'm trying to understand the performance
> of my new pre-production system. We currently run ASE 15.0.3
> ESD1 on HPUX on a PA-RISC system. The new environment is ASE
> 15.5 ESD5 on HPUX on Itanium. I've encountered something I
> can't explain. To compare cpu speed, I created a simple
> stored procedure that loops 10000 times and I iterate
> through that 15 times to find an average per iteration. You
> can see in the attachments for some unknown reason the speed
> is slow for some random number of iterations and then gets
> fast. Once it runs fast once, it will continue to run fast
> until you disconnect. Once you reconnect the slow pattern
> continues. The two attachments represent back to back runs
> of the same proc. One other thing I noticed is the new
> system runs at least twice as fast as the current when the
> loop count is 1000, but is slower than the current one when
> the count is 10,000. I have the system to myself and this is
> all that's running at the time. I have been experiencing
> random slowness in other processes as well.

This can happen, it can be caused by the internal housekeeper process
periodically kicking in for example.
if you use the new threaded kernel in 15.7, you will likely see
reductions of this type of response time spike.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


SteveS Posted on 2012-02-28 20:20:56.0Z
Sender: 1e85.4f4d185c.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4d3728.25a9.1681692777@sybase.com>
References: <4f4d2ce8@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 28 Feb 2012 12:20:56 -0800
X-Trace: forums-1-dub 1330460456 172.20.134.41 (28 Feb 2012 12:20:56 -0800)
X-Original-Trace: 28 Feb 2012 12:20:56 -0800, 172.20.134.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30953
Article PK: 73843


> On 28-Feb-2012 19:39, SteveS wrote:
> > I posted previously I'm trying to understand the
> > performance of my new pre-production system. We
> > currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
> > system. The new environment is ASE 15.5 ESD5 on HPUX on
> > Itanium. I've encountered something I can't explain. To
> > compare cpu speed, I created a simple stored procedure
> > that loops 10000 times and I iterate through that 15
> > times to find an average per iteration. You can see in
> > the attachments for some unknown reason the speed is
> > slow for some random number of iterations and then gets
> > fast. Once it runs fast once, it will continue to run
> fast until you disconnect. Once you reconnect the slow
> > pattern continues. The two attachments represent back to
> > back runs of the same proc. One other thing I noticed is
> > the new system runs at least twice as fast as the
> > current when the loop count is 1000, but is slower than
> > the current one when the count is 10,000. I have the
> > system to myself and this is all that's running at the
> > time. I have been experiencing random slowness in other
> processes as well.
>
> This can happen, it can be caused by the internal
> housekeeper process periodically kicking in for example.
> if you use the new threaded kernel in 15.7, you will
> likely see reductions of this type of response time
> spike.
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

Possibly. But my concern is this is an unknown and I have
limited capabilty to load test before moving to production.
This is a system with 4 engines and only one process besides
the system spids. Why always slow to start. I had a couple
of runs where I had reconnect before it would eventually run
fast. I also don't see this on my current test and
production systems. Is there any way to see what's
happening. I've used the monitor tables to view
resource/waits while running and don't see anything
revealing.

As far as 15.7: I need to leave the client at 15.0 for some
third party software. Doesn't 15.7 require a client upgrade
as well.


Rob V Posted on 2012-02-28 20:36:00.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
References: <4f4d2ce8@forums-1-dub> <4f4d3728.25a9.1681692777@sybase.com>
In-Reply-To: <4f4d3728.25a9.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f4d3ab0@forums-1-dub>
Date: 28 Feb 2012 12:36:00 -0800
X-Trace: forums-1-dub 1330461360 10.22.241.152 (28 Feb 2012 12:36:00 -0800)
X-Original-Trace: 28 Feb 2012 12:36:00 -0800, vip152.sybase.com
Lines: 94
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30954
Article PK: 73844


On 28-Feb-2012 21:20, SteveS wrote:
>> On 28-Feb-2012 19:39, SteveS wrote:
>>> I posted previously I'm trying to understand the
>>> performance of my new pre-production system. We
>>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
>>> system. The new environment is ASE 15.5 ESD5 on HPUX on
>>> Itanium. I've encountered something I can't explain. To
>>> compare cpu speed, I created a simple stored procedure
>>> that loops 10000 times and I iterate through that 15
>>> times to find an average per iteration. You can see in
>>> the attachments for some unknown reason the speed is
>>> slow for some random number of iterations and then gets
>>> fast. Once it runs fast once, it will continue to run
>> fast until you disconnect. Once you reconnect the slow
>>> pattern continues. The two attachments represent back to
>>> back runs of the same proc. One other thing I noticed is
>>> the new system runs at least twice as fast as the
>>> current when the loop count is 1000, but is slower than
>>> the current one when the count is 10,000. I have the
>>> system to myself and this is all that's running at the
>>> time. I have been experiencing random slowness in other
>> processes as well.
>>
>> This can happen, it can be caused by the internal
>> housekeeper process periodically kicking in for example.
>> if you use the new threaded kernel in 15.7, you will
>> likely see reductions of this type of response time
>> spike.
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
> Possibly. But my concern is this is an unknown and I have
> limited capabilty to load test before moving to production.
> This is a system with 4 engines and only one process besides
> the system spids. Why always slow to start. I had a couple
> of runs where I had reconnect before it would eventually run
> fast. I also don't see this on my current test and
> production systems. Is there any way to see what's
> happening. I've used the monitor tables to view
> resource/waits while running and don't see anything
> revealing.
>
> As far as 15.7: I need to leave the client at 15.0 for some
> third party software. Doesn't 15.7 require a client upgrade
> as well.

It can be difficult nailing this down. If you see it on one system but
not on others, then it is likely some environmental aspect (like:
available memory; swap space; unexpected daemons; Unix/Linux file system
buffer flushing kicking in; other processes suddenly waking up (e.g.
webservers); etc.)

ASE 15.7 will work fine with a 15.0 client. Some of the 15.7 performance
enhancements between client and server (optimized network traffic) will
not apply but that will be transparent.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2012-02-28 23:59:16.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
References: <4f4d2ce8@forums-1-dub> <4f4d3728.25a9.1681692777@sybase.com>
In-Reply-To: <4f4d3728.25a9.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f4d6a54$1@forums-1-dub>
Date: 28 Feb 2012 15:59:16 -0800
X-Trace: forums-1-dub 1330473556 10.22.241.152 (28 Feb 2012 15:59:16 -0800)
X-Original-Trace: 28 Feb 2012 15:59:16 -0800, vip152.sybase.com
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30956
Article PK: 73846

Could you modify your test to grab deltas (or just simple snapshots) of monProcessActivity.CpuTime/WaitTIme and
monProcessWaits?

I'm be curious to see if your longer runs show up with higher cpu usage numbers (via mPA) or higher wait times (via
mPA/mPW).

Granted, for some of the tests the MDA tables may not be granular enough ... but for the longer runs I'm thinking there
should be enough granularity to get a high-level view of where the time is being spent.

On 02/28/2012 13:20, SteveS wrote:
>> On 28-Feb-2012 19:39, SteveS wrote:
>>> I posted previously I'm trying to understand the
>>> performance of my new pre-production system. We
>>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
>>> system. The new environment is ASE 15.5 ESD5 on HPUX on
>>> Itanium. I've encountered something I can't explain. To
>>> compare cpu speed, I created a simple stored procedure
>>> that loops 10000 times and I iterate through that 15
>>> times to find an average per iteration. You can see in
>>> the attachments for some unknown reason the speed is
>>> slow for some random number of iterations and then gets
>>> fast. Once it runs fast once, it will continue to run
>> fast until you disconnect. Once you reconnect the slow
>>> pattern continues. The two attachments represent back to
>>> back runs of the same proc. One other thing I noticed is
>>> the new system runs at least twice as fast as the
>>> current when the loop count is 1000, but is slower than
>>> the current one when the count is 10,000. I have the
>>> system to myself and this is all that's running at the
>>> time. I have been experiencing random slowness in other
>> processes as well.
>>
>> This can happen, it can be caused by the internal
>> housekeeper process periodically kicking in for example.
>> if you use the new threaded kernel in 15.7, you will
>> likely see reductions of this type of response time
>> spike.
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
> Possibly. But my concern is this is an unknown and I have
> limited capabilty to load test before moving to production.
> This is a system with 4 engines and only one process besides
> the system spids. Why always slow to start. I had a couple
> of runs where I had reconnect before it would eventually run
> fast. I also don't see this on my current test and
> production systems. Is there any way to see what's
> happening. I've used the monitor tables to view
> resource/waits while running and don't see anything
> revealing.
>
> As far as 15.7: I need to leave the client at 15.0 for some
> third party software. Doesn't 15.7 require a client upgrade
> as well.


SteveS Posted on 2012-02-29 14:43:36.0Z
Sender: 616b.4f4e35cc.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4e3998.61fa.1681692777@sybase.com>
References: <4f4d6a54$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 29 Feb 2012 06:43:36 -0800
X-Trace: forums-1-dub 1330526616 172.20.134.41 (29 Feb 2012 06:43:36 -0800)
X-Original-Trace: 29 Feb 2012 06:43:36 -0800, 172.20.134.41
Lines: 97
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30958
Article PK: 73847


> Could you modify your test to grab deltas (or just simple
> snapshots) of monProcessActivity.CpuTime/WaitTIme and
> monProcessWaits?
>
> I'm be curious to see if your longer runs show up with
> higher cpu usage numbers (via mPA) or higher wait times
> (via mPA/mPW).
>
> Granted, for some of the tests the MDA tables may not be
> granular enough ... but for the longer runs I'm thinking
> there should be enough granularity to get a high-level
> view of where the time is being spent.
>
> On 02/28/2012 13:20, SteveS wrote:
> >> On 28-Feb-2012 19:39, SteveS wrote:
> >>> I posted previously I'm trying to understand the
> >>> performance of my new pre-production system. We
> >>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
> >>> system. The new environment is ASE 15.5 ESD5 on HPUX
> on >>> Itanium. I've encountered something I can't
> explain. To >>> compare cpu speed, I created a simple
> stored procedure >>> that loops 10000 times and I iterate
> through that 15 >>> times to find an average per
> iteration. You can see in >>> the attachments for some
> unknown reason the speed is >>> slow for some random
> number of iterations and then gets >>> fast. Once it runs
> fast once, it will continue to run >> fast until you
> disconnect. Once you reconnect the slow >>> pattern
> continues. The two attachments represent back to >>> back
> runs of the same proc. One other thing I noticed is >>>
> the new system runs at least twice as fast as the >>>
> current when the loop count is 1000, but is slower than
> >>> the current one when the count is 10,000. I have the
> >>> system to myself and this is all that's running at the
> >>> time. I have been experiencing random slowness in
> other >> processes as well.
> >>
> >> This can happen, it can be caused by the internal
> >> housekeeper process periodically kicking in for
> example. >> if you use the new threaded kernel in 15.7,
> you will >> likely see reductions of this type of
> response time >> spike.
> >>
> >> --
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Professional DBA for Sybase ASE, IQ,
> Replication >> Server
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
> (new!) >> "The Complete Sybase ASE Quick Reference Guide"
> >> "The Complete Sybase Replication Server Quick Reference
> >> Guide"
> >>
> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >> Commerce 27138666
> >>
> ----------------------------------------------------------
> > >> ------- Possibly. But my concern is this is an
> > unknown and I have limited capabilty to load test before
> > moving to production. This is a system with 4 engines
> > and only one process besides the system spids. Why
> > always slow to start. I had a couple of runs where I had
> > reconnect before it would eventually run fast. I also
> > don't see this on my current test and production
> > systems. Is there any way to see what's happening. I've
> > used the monitor tables to view resource/waits while
> > running and don't see anything revealing.
> >
> > As far as 15.7: I need to leave the client at 15.0 for
> > some third party software. Doesn't 15.7 require a client
> > upgrade as well.

I hope I can explain this because it's pretty crazy. I added
the selects to the mon tables as requested and was shocked
to find the script runs fast all the time now. I took the
selects out and it was back to the slow pattern. Through
testing I found if I just selected from the mon tables once
before running the original proc, it always runs fast. I
tried to see if any select before the proc would make a
difference and nothing else did.(select getdate(), select
from a small user table, etc.) I did notice something about
network waits. I'm running the same script against my new
system and my production system(original proc without the
added selects). Typically in production there will be ~300
'waiting for network send to complete' with a wait time of
zero. In the new system there will typically be 100-200 with
an average wait time of 10-20 ms when it runs fast. But I
captured a couple ofreally slow runs on the new system and
found 2723 and 1592 network waits with respective wait times
of 26000 and 11650 ms total wait times.


SteveS Posted on 2012-02-29 16:36:29.0Z
Sender: 616b.4f4e35cc.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f4e540d.65d0.1681692777@sybase.com>
References: <4f4e3998.61fa.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 29 Feb 2012 08:36:29 -0800
X-Trace: forums-1-dub 1330533389 172.20.134.41 (29 Feb 2012 08:36:29 -0800)
X-Original-Trace: 29 Feb 2012 08:36:29 -0800, 172.20.134.41
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30959
Article PK: 73849


> > Could you modify your test to grab deltas (or just
> > simple snapshots) of monProcessActivity.CpuTime/WaitTIme
> > and monProcessWaits?
> >
> > I'm be curious to see if your longer runs show up with
> > higher cpu usage numbers (via mPA) or higher wait times
> > (via mPA/mPW).
> >
> > Granted, for some of the tests the MDA tables may not be
> > granular enough ... but for the longer runs I'm thinking
> > there should be enough granularity to get a high-level
> > view of where the time is being spent.
> >
> > On 02/28/2012 13:20, SteveS wrote:
> > >> On 28-Feb-2012 19:39, SteveS wrote:
> > >>> I posted previously I'm trying to understand the
> > >>> performance of my new pre-production system. We
> > >>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
> > >>> system. The new environment is ASE 15.5 ESD5 on HPUX
> > on >>> Itanium. I've encountered something I can't
> > explain. To >>> compare cpu speed, I created a simple
> > stored procedure >>> that loops 10000 times and I
> > iterate through that 15 >>> times to find an average per
> > iteration. You can see in >>> the attachments for some
> > unknown reason the speed is >>> slow for some random
> > number of iterations and then gets >>> fast. Once it
> > runs fast once, it will continue to run >> fast until
> > you disconnect. Once you reconnect the slow >>> pattern
> > continues. The two attachments represent back to >>>
> > back runs of the same proc. One other thing I noticed is
> > >>> the new system runs at least twice as fast as the
> > >>> current when the loop count is 1000, but is slower
> > than >>> the current one when the count is 10,000. I
> > have the >>> system to myself and this is all that's
> > running at the >>> time. I have been experiencing random
> > slowness in other >> processes as well.
> > >>
> > >> This can happen, it can be caused by the internal
> > >> housekeeper process periodically kicking in for
> > example. >> if you use the new threaded kernel in 15.7,
> > you will >> likely see reductions of this type of
> > response time >> spike.
> > >>
> > >> --
> > >> HTH,
> > >>
> > >> Rob V.
> > >>
> >
> >
> ----------------------------------------------------------
> > >> ------- Rob Verschoor >> >> Certified Professional
> > DBA for Sybase ASE, IQ, Replication >> Server
> > >>
> > >> Author of Sybase books (order online at
> > >> www.sypron.nl/shop): "Tips, Tricks& Recipes for
> > Sybase >> ASE" "The Complete Sybase IQ Quick Reference
> > Guide" (new!) >> "The Complete Sybase ASE Quick
> > Reference Guide" >> "The Complete Sybase Replication
> > Server Quick Reference >> Guide"
> > >>
> > >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> > >> @rob_verschoor Sypron B.V., The Netherlands |
> > Chamber of >> Commerce 27138666
> > >>
> >
> > >
> ----------------------------------------------------------
> > > >> ------- Possibly. But my concern is this is an
> > > unknown and I have limited capabilty to load test
> before moving to production. This is a system with 4
> > > engines and only one process besides the system spids.
> > > Why always slow to start. I had a couple of runs where
> > > I had reconnect before it would eventually run fast. I
> > > also don't see this on my current test and production
> > > systems. Is there any way to see what's happening.
> > > I've used the monitor tables to view resource/waits
> > > while running and don't see anything revealing.
> > >
> > > As far as 15.7: I need to leave the client at 15.0 for
> > > some third party software. Doesn't 15.7 require a
> > > client upgrade as well.
> I hope I can explain this because it's pretty crazy. I
> added the selects to the mon tables as requested and was
> shocked to find the script runs fast all the time now. I
> took the selects out and it was back to the slow pattern.
> Through testing I found if I just selected from the mon
> tables once before running the original proc, it always
> runs fast. I tried to see if any select before the proc
> would make a difference and nothing else did.(select
> getdate(), select from a small user table, etc.) I did
> notice something about network waits. I'm running the same
> script against my new system and my production
> system(original proc without the added selects). Typically
> in production there will be ~300 'waiting for network send
> to complete' with a wait time of zero. In the new system
> there will typically be 100-200 with an average wait time
> of 10-20 ms when it runs fast. But I captured a couple
> ofreally slow runs on the new system and found 2723 and
> 1592 network waits with respective wait times of 26000 and
> 11650 ms total wait times.

The plot thickens. The slow response is definately related
to the network waits. Using the mon tables I see the script
always sends 3983 packets. On fast runs there are no network
waits. On slow runs the nework wait time ties directly to
the number of slow iterations. So the question now is why do
all the waits go away if I do a mon table select before
running my proc but they are always there if I don't.


Rob V Posted on 2012-02-29 16:52:42.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
References: <4f4e3998.61fa.1681692777@sybase.com> <4f4e540d.65d0.1681692777@sybase.com>
In-Reply-To: <4f4e540d.65d0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f4e57da$1@forums-1-dub>
Date: 29 Feb 2012 08:52:42 -0800
X-Trace: forums-1-dub 1330534362 10.22.241.152 (29 Feb 2012 08:52:42 -0800)
X-Original-Trace: 29 Feb 2012 08:52:42 -0800, vip152.sybase.com
Lines: 141
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30960
Article PK: 73850


On 29-Feb-2012 17:36, SteveS wrote:
>>> Could you modify your test to grab deltas (or just
>>> simple snapshots) of monProcessActivity.CpuTime/WaitTIme
>>> and monProcessWaits?
>>>
>>> I'm be curious to see if your longer runs show up with
>>> higher cpu usage numbers (via mPA) or higher wait times
>>> (via mPA/mPW).
>>>
>>> Granted, for some of the tests the MDA tables may not be
>>> granular enough ... but for the longer runs I'm thinking
>>> there should be enough granularity to get a high-level
>>> view of where the time is being spent.
>>>
>>> On 02/28/2012 13:20, SteveS wrote:
>>>>> On 28-Feb-2012 19:39, SteveS wrote:
>>>>>> I posted previously I'm trying to understand the
>>>>>> performance of my new pre-production system. We
>>>>>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
>>>>>> system. The new environment is ASE 15.5 ESD5 on HPUX
>>> on>>> Itanium. I've encountered something I can't
>>> explain. To>>> compare cpu speed, I created a simple
>>> stored procedure>>> that loops 10000 times and I
>>> iterate through that 15>>> times to find an average per
>>> iteration. You can see in>>> the attachments for some
>>> unknown reason the speed is>>> slow for some random
>>> number of iterations and then gets>>> fast. Once it
>>> runs fast once, it will continue to run>> fast until
>>> you disconnect. Once you reconnect the slow>>> pattern
>>> continues. The two attachments represent back to>>>
>>> back runs of the same proc. One other thing I noticed is
>>>>>> the new system runs at least twice as fast as the
>>>>>> current when the loop count is 1000, but is slower
>>> than>>> the current one when the count is 10,000. I
>>> have the>>> system to myself and this is all that's
>>> running at the>>> time. I have been experiencing random
>>> slowness in other>> processes as well.
>>>>>
>>>>> This can happen, it can be caused by the internal
>>>>> housekeeper process periodically kicking in for
>>> example.>> if you use the new threaded kernel in 15.7,
>>> you will>> likely see reductions of this type of
>>> response time>> spike.
>>>>>
>>>>> --
>>>>> HTH,
>>>>>
>>>>> Rob V.
>>>>>
>>>
>>>
>> ----------------------------------------------------------
>>>>> ------- Rob Verschoor>> >> Certified Professional
>>> DBA for Sybase ASE, IQ, Replication>> Server
>>>>>
>>>>> Author of Sybase books (order online at
>>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for
>>> Sybase>> ASE" "The Complete Sybase IQ Quick Reference
>>> Guide" (new!)>> "The Complete Sybase ASE Quick
>>> Reference Guide">> "The Complete Sybase Replication
>>> Server Quick Reference>> Guide"
>>>>>
>>>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>>>> @rob_verschoor Sypron B.V., The Netherlands |
>>> Chamber of>> Commerce 27138666
>>>>>
>>>
>>>>
>> ----------------------------------------------------------
>>>>>> ------- Possibly. But my concern is this is an
>>>> unknown and I have limited capabilty to load test
>> before moving to production. This is a system with 4
>>>> engines and only one process besides the system spids.
>>>> Why always slow to start. I had a couple of runs where
>>>> I had reconnect before it would eventually run fast. I
>>>> also don't see this on my current test and production
>>>> systems. Is there any way to see what's happening.
>>>> I've used the monitor tables to view resource/waits
>>>> while running and don't see anything revealing.
>>>>
>>>> As far as 15.7: I need to leave the client at 15.0 for
>>>> some third party software. Doesn't 15.7 require a
>>>> client upgrade as well.
>> I hope I can explain this because it's pretty crazy. I
>> added the selects to the mon tables as requested and was
>> shocked to find the script runs fast all the time now. I
>> took the selects out and it was back to the slow pattern.
>> Through testing I found if I just selected from the mon
>> tables once before running the original proc, it always
>> runs fast. I tried to see if any select before the proc
>> would make a difference and nothing else did.(select
>> getdate(), select from a small user table, etc.) I did
>> notice something about network waits. I'm running the same
>> script against my new system and my production
>> system(original proc without the added selects). Typically
>> in production there will be ~300 'waiting for network send
>> to complete' with a wait time of zero. In the new system
>> there will typically be 100-200 with an average wait time
>> of 10-20 ms when it runs fast. But I captured a couple
>> ofreally slow runs on the new system and found 2723 and
>> 1592 network waits with respective wait times of 26000 and
>> 11650 ms total wait times.
> The plot thickens. The slow response is definately related
> to the network waits. Using the mon tables I see the script
> always sends 3983 packets. On fast runs there are no network
> waits. On slow runs the nework wait time ties directly to
> the number of slow iterations. So the question now is why do
> all the waits go away if I do a mon table select before
> running my proc but they are always there if I don't.

FWIW (and anecdotical): I recall two issues with network I/O that you
wouldn't initially suspect. One was a NIC that was in auto config mode
meaning that it switched to 10Mbps or 100Mbps depending on the first
connection after host reboot. If the first client connected with a
10MBps connection then it would be stuck in 10MPBs mode forever
afterwards. If a 100Mbps client conencted, it would use that.
The second was a faulty network card that was causing huge amounts of
Ethernet collisions cuasing netio performance degradation.

You can exclude this type of HW problem by replcaing the NIC or using a
second NiC.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


J Posted on 2012-02-29 19:17:04.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
Reply-To: J@bogusemailAddress.com
Message-ID: <4f4e78bc.505471921@forums.sybase.com>
References: <4f4d6a54$1@forums-1-dub> <4f4e3998.61fa.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 29 Feb 2012 11:17:04 -0800
X-Trace: forums-1-dub 1330543024 10.22.241.152 (29 Feb 2012 11:17:04 -0800)
X-Original-Trace: 29 Feb 2012 11:17:04 -0800, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30961
Article PK: 73851

On 29 Feb 2012 06:43:36 -0800, SteveS wrote:

My newgroup tool does not get your attachments. In any case what I
suspect might be going on would a be a combination of stored procedure
"done_in_procs" coupled with "tcp_nodelay".

As you execute the stored procedure, tds tokens called "done_in_procs"
flow back to the client. They are rather small and can be used by
diagnostic software to determine where in the execution of a sproc the
server might be. They are mostly pretty useless "chatter" on the
line.

You are going to have many packets of these which must be received by
the client before tcp will release buffers and complete the dataserver
sends. The server will wait the client's spid during this network
activity.

The tcp_nodelay (Nagle Algorithm) will delay the sending of tcp
packets if the application does a send and does not follow it by a
receive. Essentially, it keeps many small sends off the network and
it has the effect of delaying the initial send by around 150-180 ms.

The "select statements" that you add to the output and can cause the
client underlying driver to read packets.

Check with a sniffer to see how long the initial delay is that you are
seeing.

Jay

>> Could you modify your test to grab deltas (or just simple
>> snapshots) of monProcessActivity.CpuTime/WaitTIme and
>> monProcessWaits?
>>
>> I'm be curious to see if your longer runs show up with
>> higher cpu usage numbers (via mPA) or higher wait times
>> (via mPA/mPW).
>>
>> Granted, for some of the tests the MDA tables may not be
>> granular enough ... but for the longer runs I'm thinking
>> there should be enough granularity to get a high-level
>> view of where the time is being spent.
>>
>> On 02/28/2012 13:20, SteveS wrote:
>> >> On 28-Feb-2012 19:39, SteveS wrote:
>> >>> I posted previously I'm trying to understand the
>> >>> performance of my new pre-production system. We
>> >>> currently run ASE 15.0.3 ESD1 on HPUX on a PA-RISC
>> >>> system. The new environment is ASE 15.5 ESD5 on HPUX
>> on >>> Itanium. I've encountered something I can't
>> explain. To >>> compare cpu speed, I created a simple
>> stored procedure >>> that loops 10000 times and I iterate
>> through that 15 >>> times to find an average per
>> iteration. You can see in >>> the attachments for some
>> unknown reason the speed is >>> slow for some random
>> number of iterations and then gets >>> fast. Once it runs
>> fast once, it will continue to run >> fast until you
>> disconnect. Once you reconnect the slow >>> pattern
>> continues. The two attachments represent back to >>> back
>> runs of the same proc. One other thing I noticed is >>>
>> the new system runs at least twice as fast as the >>>
>> current when the loop count is 1000, but is slower than
>> >>> the current one when the count is 10,000. I have the
>> >>> system to myself and this is all that's running at the
>> >>> time. I have been experiencing random slowness in
>> other >> processes as well.
>> >>
>> >> This can happen, it can be caused by the internal
>> >> housekeeper process periodically kicking in for
>> example. >> if you use the new threaded kernel in 15.7,
>> you will >> likely see reductions of this type of
>> response time >> spike.
>> >>
>> >> --
>> >> HTH,
>> >>
>> >> Rob V.
>> >>
>> ----------------------------------------------------------
>> >> ------- Rob Verschoor >>
>> >> Certified Professional DBA for Sybase ASE, IQ,
>> Replication >> Server
>> >>
>> >> Author of Sybase books (order online at
>> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> (new!) >> "The Complete Sybase ASE Quick Reference Guide"
>> >> "The Complete Sybase Replication Server Quick Reference
>> >> Guide"
>> >>
>> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
>> of >> Commerce 27138666
>> >>
>> ----------------------------------------------------------
>> > >> ------- Possibly. But my concern is this is an
>> > unknown and I have limited capabilty to load test before
>> > moving to production. This is a system with 4 engines
>> > and only one process besides the system spids. Why
>> > always slow to start. I had a couple of runs where I had
>> > reconnect before it would eventually run fast. I also
>> > don't see this on my current test and production
>> > systems. Is there any way to see what's happening. I've
>> > used the monitor tables to view resource/waits while
>> > running and don't see anything revealing.
>> >
>> > As far as 15.7: I need to leave the client at 15.0 for
>> > some third party software. Doesn't 15.7 require a client
>> > upgrade as well.
>I hope I can explain this because it's pretty crazy. I added
>the selects to the mon tables as requested and was shocked
>to find the script runs fast all the time now. I took the
>selects out and it was back to the slow pattern. Through
>testing I found if I just selected from the mon tables once
>before running the original proc, it always runs fast. I
>tried to see if any select before the proc would make a
>difference and nothing else did.(select getdate(), select
>from a small user table, etc.) I did notice something about
>network waits. I'm running the same script against my new
>system and my production system(original proc without the
>added selects). Typically in production there will be ~300
>'waiting for network send to complete' with a wait time of
>zero. In the new system there will typically be 100-200 with
>an average wait time of 10-20 ms when it runs fast. But I
>captured a couple ofreally slow runs on the new system and
>found 2723 and 1592 network waits with respective wait times
>of 26000 and 11650 ms total wait times.


SteveS Posted on 2012-03-02 13:38:45.0Z
Sender: 66c4.4f50c6da.1804289383@sybase.com
From: SteveS
Newsgroups: sybase.public.ase.general
Subject: Re: New System Performance
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f50cd65.680b.1681692777@sybase.com>
References: <4f4e78bc.505471921@forums.sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 Mar 2012 05:38:45 -0800
X-Trace: forums-1-dub 1330695525 172.20.134.41 (2 Mar 2012 05:38:45 -0800)
X-Original-Trace: 2 Mar 2012 05:38:45 -0800, 172.20.134.41
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30967
Article PK: 73860


> On 29 Feb 2012 06:43:36 -0800, SteveS wrote:
>
> My newgroup tool does not get your attachments. In any
> case what I suspect might be going on would a be a
> combination of stored procedure "done_in_procs" coupled
> with "tcp_nodelay".
>
> As you execute the stored procedure, tds tokens called
> "done_in_procs" flow back to the client. They are rather
> small and can be used by diagnostic software to determine
> where in the execution of a sproc the server might be.
> They are mostly pretty useless "chatter" on the line.
>
> You are going to have many packets of these which must be
> received by the client before tcp will release buffers and
> complete the dataserver sends. The server will wait the
> client's spid during this network activity.
>
> The tcp_nodelay (Nagle Algorithm) will delay the sending
> of tcp packets if the application does a send and does not
> follow it by a receive. Essentially, it keeps many small
> sends off the network and it has the effect of delaying
> the initial send by around 150-180 ms.
>
> The "select statements" that you add to the output and can
> cause the client underlying driver to read packets.
>
> Check with a sniffer to see how long the initial delay is
> that you are seeing.
>
> Jay
>

I keep doneinproc tokens turned off and normally have
tcp_nodelay on. But I tried different combos with no change.
I think it may be time to open a support call as I've just
noticed this as well:

Running this batch takes 13 seconds with array cache
disabled to eliminate variability. (I also shutdown Sybase
between each run to eliminate cache interferance) I'm
forcing a table scan on a table that's about 1GB:

select count(*) from invoice_lines_lv14 (index
invoice_lines_lv14 prefetch 16 )
go

This batch below takes 88 seconds to run under the same
conditions:

select SPID,Waits,WaitTime,'Avg'=WaitTime/Waits,WaitEventID
from master..monProcessWaits
go
select count(*) from invoice_lines_lv14 (index
invoice_lines_lv14 prefetch 16 )
go

All the wait time is in the second select (via set
statistics io,time)

99% of wait time is always eventID 124 which I believe is
waiting for predetch IO to complete and the wait count also
corresponds to prefetch waits seen via sp_sysmon.

If someone can explain how the simple select on the mon
table can slow down the IOs on the second select, I owe you
a beer.

The reason I started doing these benches was the random slow
response times I was getting in our apps. No way can I
deploy this new environment without understanding what's
going on. But I'm to the point I don't think it's a hardware
issue.