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.

session tempdb log cache size in ASE 15.02 EBF#2

7 posts in Performance and Tuning Last posting was on 2008-04-22 13:57:05.0Z
Isabella Posted on 2008-04-18 19:32:08.0Z
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: session tempdb log cache size in ASE 15.02 EBF#2
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: <4808f738@forums-1-dub>
Date: 18 Apr 2008 12:32:08 -0700
X-Trace: forums-1-dub 1208547128 10.22.241.152 (18 Apr 2008 12:32:08 -0700)
X-Original-Trace: 18 Apr 2008 12:32:08 -0700, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10772
Article PK: 89381

ASE15.02 EBF#2 come out with new cfg param :' session tempdb log cache
size', which is supposed to improve inserts performance , the default
is ASE page size , the range value sis quite big , I would like some
comments about increasing this value , what you suggest ?

Parameter Name Default Memory Used Config Value Run
Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
session tempdb log cache size 2048 0 2048
2048 bytes static


Thank you,
Isabella


A. M. Posted on 2008-04-19 05:40:17.0Z
Message-ID: <480985C2.56DE797@gmail.com>
From: "A. M." <amforums@gmail.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
References: <4808f738@forums-1-dub>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Apr 2008 22:40:17 -0700
X-Trace: forums-1-dub 1208583617 10.22.241.152 (18 Apr 2008 22:40:17 -0700)
X-Original-Trace: 18 Apr 2008 22:40:17 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10774
Article PK: 89385


Isabella wrote:
>
> ASE15.02 EBF#2 come out with new cfg param :' session tempdb log cache
> size', which is supposed to improve inserts performance , the default
> is ASE page size , the range value sis quite big , I would like some
> comments about increasing this value , what you suggest ?
>
> Parameter Name Default Memory Used Config Value Run
> Value Unit Type
> ------------------------------ ----------- ----------- ------------
> ------------ -------------------- ----------
> session tempdb log cache size 2048 0 2048
> 2048 bytes static

I'm not familiar with this. Do the notes say how its
different from the ULC? I notice that the param is
static so a change requires a bounce for it to take
effect.

-am © MMVIII


Stefan Karlsson Posted on 2008-04-20 20:41:02.0Z
Message-ID: <480BAA5B.6000804@Sybase.JustSayNoToSpam.com>
From: Stefan Karlsson <Stefan.Karlsson@Sybase.JustSayNoToSpam.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
References: <4808f738@forums-1-dub>
In-Reply-To: <4808f738@forums-1-dub>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 20 Apr 2008 13:41:02 -0700
X-Trace: forums-1-dub 1208724062 10.22.241.152 (20 Apr 2008 13:41:02 -0700)
X-Original-Trace: 20 Apr 2008 13:41:02 -0700, vip152.sybase.com
Lines: 28
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10775
Article PK: 89386


Isabella wrote:
> ASE15.02 EBF#2 come out with new cfg param :' session tempdb log cache
> size', which is supposed to improve inserts performance , the default
> is ASE page size , the range value sis quite big , I would like some
> comments about increasing this value , what you suggest ?
>
> Parameter Name Default Memory Used Config Value Run
> Value Unit Type
> ------------------------------ ----------- ----------- ------------
> ------------ -------------------- ----------
> session tempdb log cache size 2048 0 2048
> 2048 bytes static
>
>
> Thank you,
> Isabella

This feature completes work started in 12.5.0.3 with the then introduced lazy
writes. In 15.0.2 there are several features introduced to improve insert and
tempdb performance.

See the 15.0.2 What's New guide, "Chapter 7: Changes that improve the
performance for inserting data". Note that only some features are tempdb-only.

HTH,

/Stefan


Isabella Posted on 2008-04-21 16:37:57.0Z
Message-ID: <480CC2E5.4080409@nrc-cnrc.gc.ca>
From: Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
To: Stefan Karlsson <Stefan.Karlsson@Sybase.JustSayNoToSpam.com>
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
References: <4808f738@forums-1-dub> <480BAA5B.6000804@Sybase.JustSayNoToSpam.com>
In-Reply-To: <480BAA5B.6000804@Sybase.JustSayNoToSpam.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 21 Apr 2008 09:37:57 -0700
X-Trace: forums-1-dub 1208795877 10.22.241.152 (21 Apr 2008 09:37:57 -0700)
X-Original-Trace: 21 Apr 2008 09:37:57 -0700, vip152.sybase.com
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10776
Article PK: 89388

I see the guide: What's new in ASE 15.02 but is kind to summary , my
question is how much should we increase this param, and after how do I
measure/monitor the changed performance ?
Thank you.
Isabella

Stefan Karlsson wrote:
> Isabella wrote:
>> ASE15.02 EBF#2 come out with new cfg param :' session tempdb log cache
>> size', which is supposed to improve inserts performance , the default
>> is ASE page size , the range value sis quite big , I would like some
>> comments about increasing this value , what you suggest ?
>>
>> Parameter Name Default Memory Used Config Value
>> Run Value Unit Type
>> ------------------------------ ----------- ----------- ------------
>> ------------ -------------------- ----------
>> session tempdb log cache size 2048 0 2048
>> 2048 bytes static
>>
>>
>> Thank you,
>> Isabella
>
>
> This feature completes work started in 12.5.0.3 with the then introduced
> lazy writes. In 15.0.2 there are several features introduced to improve
> insert and tempdb performance.
>
> See the 15.0.2 What's New guide, "Chapter 7: Changes that improve the
> performance for inserting data". Note that only some features are
> tempdb-only.
>
> HTH,
>
> /Stefan


Derek Asirvadem Posted on 2008-04-22 04:42:30.0Z
From: Derek Asirvadem <derek.asirvadem@gmailDOTcom>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <480d6cb5@forums-1-dub>
References: <4808f738@forums-1-dub> <480BAA5B.6000804@Sybase.JustSayNoToSpam.com> <480CC2E5.4080409@nrc-cnrc.gc.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 21 Apr 2008 21:42:30 -0700
X-Trace: forums-1-dub 1208839350 10.22.241.152 (21 Apr 2008 21:42:30 -0700)
X-Original-Trace: 21 Apr 2008 21:42:30 -0700, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10777
Article PK: 89387


> On 2008-04-22 02:37:57 +1000, Isabella <isabella.ghiurea@nrc-cnrc.gc.ca> said:
>
> I see the guide: What's new in ASE 15.02 but is kind to summary , my
> question is how much should we increase this param, and after how do I
> measure/monitor the changed performance ?

In 15.0.2 the ULC has been "split" into a user-db-ULC and a separate
tempdb-ULC. This parm identifies the size of the latter. It should be
set to the same size as whatever you get when you run sp_logiosize on
that tempdb, which for 15.x now defaults to 4k (hopefully in future
versions the default setting for this parm will be 4k and thus this
mannual step can be avoided). It is a per-connection or per-session
unit, the memory required = connections x STLCS, just as memory reqd
for ULC is connections x ULC size. (15.x is MUCH faster, but nothing
is free, and it needs more memory to provide that speed.)

It is one piece of a whole set of optimisations for all I/O, and in
particular, for tempdb. The term "lazy write" is misleading, one is
better off understanding each of the various enhancements singularly
(and importantly, identifying what parm/resuorce that needs to be
allocated in order to take advantage of it), then forming an
understanding of the effect of the set of optimisations as a whole.

The changed performance will show up in many places, and it depends on
what else you are enhancing/changing and what stats you are using (P&T
is an art and heavily limited to one's experience; I do not have time
to explain but I will provide directions). Evaluate before-change vs
after-change (obviously) for, in descending order:
1 One clear indication of the effect will be in the stats for
sp_sysmon/Transaction Mgmt/ULC Flushes/by Single Log Record vs the
other five entries in that section. Also evaluate the whole
Transaction Mgmt section for changed effect.
2 A second is sp_sysmon Task Mgmt/Task Context Switches Due To/PLC
Lock Contention, Log Semaphore Contention & Last Log Page Writes
3 Another, more indirect but relevant indication is, the stats for the
named cache for that tempdb
4 And of course (1) against (3), and (2) against (3)

As per the verification of the enhancement of any ASE resource ...
if you have a heavily thrashed or updated tempdb, and the effect of
changing it from 2k to 4k is positive, you can set it higher (multiple
of 2k)
... then monitor again and change back as nec.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability


Carl Kayser Posted on 2008-04-22 12:07:30.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.performance+tuning
References: <4808f738@forums-1-dub> <480BAA5B.6000804@Sybase.JustSayNoToSpam.com> <480CC2E5.4080409@nrc-cnrc.gc.ca> <480d6cb5@forums-1-dub>
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <480dd502$1@forums-1-dub>
Date: 22 Apr 2008 05:07:30 -0700
X-Trace: forums-1-dub 1208866050 10.22.241.152 (22 Apr 2008 05:07:30 -0700)
X-Original-Trace: 22 Apr 2008 05:07:30 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10779
Article PK: 89390


"Derek Asirvadem" <derek.asirvadem@gmailDOTcom> wrote in message
news:480d6cb5@forums-1-dub...
>> On 2008-04-22 02:37:57 +1000, Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
>> said:
> [SNIP] (15.x is MUCH faster, but nothing is free, and it needs more
> memory to provide that speed.)
>

Warning: thread is being hijacked. I suspect that you mean all 15.x
"vanilla" versions without partitioned tables, encryption, or clustering in
comparison to 12.5.3 or 12.5.4. Correct? (And 15.0 <= 15.0.1 <<<= 15.0.2
<= 15.0.2 ESD#1 etc?) Although I've done some playing and functional
testing with 15.0.2 ESD#2 I haven't done any performance testing.


Derek Asirvadem Posted on 2008-04-22 13:57:05.0Z
From: Derek Asirvadem <derek.asirvadem@gmailDOTcom>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <480deeb0@forums-1-dub>
References: <4808f738@forums-1-dub> <480BAA5B.6000804@Sybase.JustSayNoToSpam.com> <480CC2E5.4080409@nrc-cnrc.gc.ca> <480d6cb5@forums-1-dub> <480dd502$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: session tempdb log cache size in ASE 15.02 EBF#2
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 22 Apr 2008 06:57:05 -0700
X-Trace: forums-1-dub 1208872625 10.22.241.152 (22 Apr 2008 06:57:05 -0700)
X-Original-Trace: 22 Apr 2008 06:57:05 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10781
Article PK: 89392


> On 2008-04-22 22:07:30 +1000, "Carl Kayser" <kayser_c@bls.gov> said:
>
>> [SNIP] (15.x is MUCH faster, but nothing is free, and it needs more
>> memory to provide that speed.)
>
> Warning: thread is being hijacked. I suspect that you mean all 15.x
> "vanilla" versions without partitioned tables, encryption, or
> clustering in comparison to 12.5.3 or 12.5.4. Correct? (And 15.0 <=
> 15.0.1 <<<= 15.0.2 <= 15.0.2 ESD#1 etc?) Although I've done some
> playing and functional testing with 15.0.2 ESD#2 I haven't done any
> performance testing.

Relax, Carl, your posts are always worthy.

No, I meant what I said. To be clear, 15.0.x vanilla versions (with
round-robin partitions but without semantic partitions and encryption)
is MUCH faster than 12.5.4, but nothing is free, and it needs more
memory to provide that speed. There is no substantial difference
between the 15.0.x minor versions, with the exception of 15.0.2 which
introduced a number of small optimisations and functions and related
tiny new features to administer them; some of these [like this thread]
provide a tangible gain and others are "ho hum". Other than that
exception the minor releases and ESDs are classic fix-it releases (in
my book, bugs hindering intended function [and thus affecting
performance] and new parms constraining the Optimiser are fix-its, not
enhancements). Therefore no substantial performance diff from 15.0
Beta to 15.0.2 and no diff from 15.0.2 to any ESD.

When I state "faster" I am talking about the 98% queries unchanged and
excluding the 2% problem queries (due to the new Optimiser) which
require attention.

We are involved in certification testing and performance testing of
almost every 15.0.x release, most but not all ESDs. We have licences
for semantic partitions and encryption on SPARC and x86 (yes, all o/s
but obviously Solaris comes first). These require an additional
licence but not a new release/edition; if you use these features, you
need a bit, not a lot, more memory and associated disk space for
encrypted cols depending on your design. Performance testing of
semantic partitions is where I am having the second most "fun" these
days.

Cluster Edition is a separate release/binary as well as an additional
licence. Although I would love to try it, we do not have a cust (and
the cust who wants it is too painful to consider, they already have 11
shoes in one shoebox and want to know why they cannot have a four-way
clustered shoebox).
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability