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.

Tuning Database by Specifying Disk Locations. (Still Relevant?)

7 posts in Performance and Tuning Last posting was on 2008-09-26 16:30:18.0Z
Wayne Happ Posted on 2008-05-06 03:27:43.0Z
Sender: 6cb.481fcf73.1804289383@sybase.com
From: Wayne Happ
Newsgroups: sybase.public.ase.performance+tuning
Subject: Tuning Database by Specifying Disk Locations. (Still Relevant?)
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <481fd02f.6d0.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 May 2008 20:27:43 -0700
X-Trace: forums-1-dub 1210044463 10.22.241.41 (5 May 2008 20:27:43 -0700)
X-Original-Trace: 5 May 2008 20:27:43 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10807
Article PK: 89418

I'm working in a large corporate data center with a typical
EMC (Like)
data storage array. I don't know exactly what it is... But
it's some
sort of storage system. I have a co-worker who is convinved
that
performance can be improved by specifying what segments to
place the
database on. An active database gets it's own disk and so
on.
Now I've been at this for a while and I know that this sort
of
optimization was done in the past. But when I have an SQL
server
connected to one of the modern storage devices, is the
concept of
segments and disks relevant anymore? I know there are
segments defined
on my dataserver. But as far as I know, that's an
abstraction to humor
me. My hardware huy tells me that these devices have
something of
their own OS and one never really knows how it's deciding
to store
data. Let alone where exactly.

Am I more wrong then right or visa-versa?
Wayne


Cory Sane Posted on 2008-05-06 06:00:08.0Z
Reply-To: "Cory Sane" <cory!=sane>
From: "Cory Sane" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <481fd02f.6d0.1681692777@sybase.com>
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
Lines: 50
Organization: [TeamSybase Intern}
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
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: <481ff3e8@forums-1-dub>
Date: 5 May 2008 23:00:08 -0700
X-Trace: forums-1-dub 1210053608 10.22.241.152 (5 May 2008 23:00:08 -0700)
X-Original-Trace: 5 May 2008 23:00:08 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10809
Article PK: 89419

I think you are both right.
If you can saturate the disk subsystem cache with writes then you are at the
mercy of each spindle.
If you never saturdate the write cache then the spindles will have less
impact.

The same is true for reads. If you have a poor cache read hit rate then
again you are at the mercy of the spindles.

Some of my co-workers did a test and found that ASE responds several times
better when the ASE page size matches the subsystem buffer read/write size.

--
Cory Sane
[Member of TeamSybase]
Certified Sybase Associate DBA for ASE 15.0
not a Sybase Inc. employee

<Wayne Happ> wrote in message news:481fd02f.6d0.1681692777@sybase.com...
> I'm working in a large corporate data center with a typical
> EMC (Like)
> data storage array. I don't know exactly what it is... But
> it's some
> sort of storage system. I have a co-worker who is convinved
> that
> performance can be improved by specifying what segments to
> place the
> database on. An active database gets it's own disk and so
> on.
> Now I've been at this for a while and I know that this sort
> of
> optimization was done in the past. But when I have an SQL
> server
> connected to one of the modern storage devices, is the
> concept of
> segments and disks relevant anymore? I know there are
> segments defined
> on my dataserver. But as far as I know, that's an
> abstraction to humor
> me. My hardware huy tells me that these devices have
> something of
> their own OS and one never really knows how it's deciding
> to store
> data. Let alone where exactly.
>
> Am I more wrong then right or visa-versa?
> Wayne


Sherlock, Kevin Posted on 2008-05-06 03:30:18.0Z
From: "Sherlock, Kevin" <ksherlock@tconl.com>
Newsgroups: sybase.public.ase.performance+tuning
References: <481fd02f.6d0.1681692777@sybase.com>
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <481fd0ca@forums-1-dub>
Date: 5 May 2008 20:30:18 -0700
X-Trace: forums-1-dub 1210044618 10.22.241.152 (5 May 2008 20:30:18 -0700)
X-Original-Trace: 5 May 2008 20:30:18 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10808
Article PK: 89422

By chance are you using a NetApps Filer as your storage platform?

<Wayne Happ> wrote in message news:481fd02f.6d0.1681692777@sybase.com...
> I'm working in a large corporate data center with a typical
> EMC (Like)
> data storage array. I don't know exactly what it is... But
> it's some
> sort of storage system. I have a co-worker who is convinved
> that
> performance can be improved by specifying what segments to
> place the
> database on. An active database gets it's own disk and so
> on.
> Now I've been at this for a while and I know that this sort
> of
> optimization was done in the past. But when I have an SQL
> server
> connected to one of the modern storage devices, is the
> concept of
> segments and disks relevant anymore? I know there are
> segments defined
> on my dataserver. But as far as I know, that's an
> abstraction to humor
> me. My hardware huy tells me that these devices have
> something of
> their own OS and one never really knows how it's deciding
> to store
> data. Let alone where exactly.
>
> Am I more wrong then right or visa-versa?
> Wayne
>


Derek Asirvadem Posted on 2008-05-07 13:51:16.0Z
From: Derek Asirvadem <derek.asirvadem@gmailDOTcom>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <4821b3d2@forums-1-dub>
References: <481fd02f.6d0.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 May 2008 06:51:16 -0700
X-Trace: forums-1-dub 1210168276 10.22.241.152 (7 May 2008 06:51:16 -0700)
X-Original-Trace: 7 May 2008 06:51:16 -0700, vip152.sybase.com
Lines: 141
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10813
Article PK: 89424

> On 2008-05-06 13:27:43 +1000, Wayne Happ said:

Good question.

Do me a personal favour, please, before you read the rest of my
response. Go up to your server and run sp_sysmon for the business day
(8 hours or more). Have a look at the device stats for the data
devices for one Db. Notice that one device has all utilisation (Total
I/Os percentage [across all devices] ). Why is that ? Now look
closely at the Writes on that device (vs the other devic es). Very
High. Why is that ? [*]

----------

It is a commonly accepted myth, that since each Logical Volume on the
SAN is striped, etc, the "blocks" are spread out all over the place,
and therefore you are getting One Response Time from it no matter what
you do; and there is no need for object placement. This is a fantasy
for the lazy, who (a) are not willing to do the work related to
implementing user segments, planning the spread of NC indices and data
across LVs so that the accesses do not compete, spreading partitions
over segments, and (b) actually running a benchmark on their test or
prod system. people who are not willing to do the work cherish the
myths that identify the work as being irrelevant.

You friend is right. Whenever we do a P&T exercise, we do the entire
segment implementation without making out that it is a big part of the
job (because the DBA has stated it is a huge job) (obviously we have
scripts and a plan, so that particular task is quite easy). Depending
on a number of other (related and unrelated) factors, you can get as
much as ten times the speed with good (correct, as opposed to incorrect
and therefore single-threading) object placement. I recently posted a
segment layout diagram from just such a job. (some of the queries ran
100 times faster, but that was due to correction of other problems not
related to SAN config and object allocation.)

The fact that 95% of the Sybase community do not perform object
placement, is indicative of the level of interest within the Sybase
communitiny of ASE performance features which are actually within their
sphere of control; it is not indicative of whether object placement
works, or whether it work on a SAN, or whther it is worth the effort.
It can be said that ASE is so fast (vis-a-vis expectations) that people
generally do not look for how/where they can make it faster. And it is
certainly true in these dark days that people seek to change things
that are outside their control.

The best way to understand it is, think of each LV as a spindle, a
single disk in the non-SAN context. The best way to understand a disk
in a performance context, is that it is actually a single-threaded
queue of requests (reads & writes mixed). You can take the MS approach
to ilfe, with one db per server, and one disk/LV per server, that is
not a problem when your machine is the price of a good pair shoes and
replaceable at will. However if you paid six figures for a real server
with a backplane, a deeply intergated I/O subsytem, fabric to the SAN,
etc.; you might want to use some of the grunt you have paid for. A big
dedicated server with 1000's of users will saturate a small number of
disks, but (more important) it will not saturate a large number of
disks, if the load is spread reasonably. If the load is not spread, it
will still saturate the few contentious disks, and everyone will wait,
while other disks are idle.

It is the difference between a disk-bound server and a cpu-bound
server, you want it to be disk-bound, but NOT on a few
disks/spindles/LVs, across all of them. You want the server saturating
ALL the slowest resources, not hung up on a small few of them.

In a SAN evironment, the exact same applies. You can either chase down
the LVs that you find contention on, or you can set them up correctly
from the outset to consciously avoid contention. On a dedicated Db
server, you need the largest number of smallest disks/LVs as you can
handle: for <100gb data (excluding log) use at least 10, I would use
16; for 1tb use at least 64. Note there is a small admin burden in
maintaining the segment/object placement after the initial setup, which
can be modulated with a good long term plan (as opposed to no plan or a
bad plan); but not a performance hit for searching for many disks/LVs,
that was removed a long time ago.

There is a very important additional point that many people do not
consider. You get usage (contention) stats at the device (SAN LV)
level. If for no other reason, you want the the objects spread out
over the devices/LVs, so that you can identify the actual
usage/contention. And if you have everything in one default segment
across say 30 devices/LVs, the stats are completely meaningless. What
I am saying is, the stats alone (if not the perf improvement) are worth
the small effort required for segment/object placement. When you look
at the dev stats and it is unusual (eg. one unit stands out), you want
to be in a position to know, hey that contains the NC indices for my
transaction tables; or that is the fourth partition for all my history
tables, as opposed to not having a clue what is on that dev/LV.

* Where you have no segments (ok fine, one big fat default segment)
ALL inserts are inserting in the one single location, with the demanded
new extents for all inserted tables interspersed with each other for
life; the one massive contention you have across any Db is the one
device/LV upon which all CURRENT inserts are taking place. Just as the
results of object placement are predictable, the results of NO object
placement are also quite predictable.

Always good for a bit of shock value when you have to deal with a
smirking resident DBA. The smirkers are usually younger than your
friend and I, too young to imagine that we landed men on the moon a
decade or more before they were born, using kilobyte memory systems.
The technology keeps changing but the engineering, the principles do
not change.

Segments abstract you from the hardware and the physical, but they are
not an abstraction in themselves. They are the method for object
placement. They are also critical for partitions but I will not start
that chapter here.

When you (or anyone) understands this completely, you will understand
that it is a higher order principle; and that whether the disk/LV is
striped/raided/SANed does not affect it, because the latter is a lower
order principle (it has an impact on thatlevel but not on a higher
order principle).

It is very much like named caches. We have had it for years, and its
functionality/config improves with every release. Those of us who have
realised the perf benefits cannot imagine a server without named
caches. However, many people out there still use one big fat default
data cache.

Finally, there is a fair amount of perf you can get out of the SAN disk
config, striping (or not), stripesize, correct boundaries, etc. There
are a few good papers on the subject. If you want a simple rule as a
starting point: set all your LVs that contain data [default segment for
now] to striped only, with a stripesize of 16 (on a 2k pagesize
server). Of course SANs have a little o/s of their own, and a cache
(you pay heftily for that), but that is a lower order principle, or [a
nicely configured disk subsystem] operating at a lower order to that of
object placement.

There are a few closely related threads, but you will have to search for them.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability


peter collard Posted on 2008-05-25 20:50:13.0Z
Message-ID: <4839d104@forums-1-dub>
From: peter collard <peterrrrrr@glossop.org>
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
Newsgroups: sybase.public.ase.performance+tuning
References: <481fd02f.6d0.1681692777@sybase.com>
Lines: 42
User-Agent: KNode/0.10.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 25 May 2008 13:50:13 -0700
X-Trace: forums-1-dub 1211748613 10.22.241.152 (25 May 2008 13:50:13 -0700)
X-Original-Trace: 25 May 2008 13:50:13 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10861
Article PK: 89472

One other question to ask you san rep - if I spread my database over lots of
disks would I get more cache allocated. Some sans allocate a min/max per
device, so more devices means more cache allocated which is what you want.

Forget the idea of allocating to sybase devices, just use striping (raid 0
not raid5) as the perfect system is one where all disks are equally
balanced at all times, and no amount of sybase segmenting will achieve
that. It was useful in 1990 when OS's just did raw disks, but is now only
peddled by consultants trying to rip you off.

Wayne Happ wrote:

> I'm working in a large corporate data center with a typical
> EMC (Like)
> data storage array. I don't know exactly what it is... But
> it's some
> sort of storage system. I have a co-worker who is convinved
> that
> performance can be improved by specifying what segments to
> place the
> database on. An active database gets it's own disk and so
> on.
> Now I've been at this for a while and I know that this sort
> of
> optimization was done in the past. But when I have an SQL
> server
> connected to one of the modern storage devices, is the
> concept of
> segments and disks relevant anymore? I know there are
> segments defined
> on my dataserver. But as far as I know, that's an
> abstraction to humor
> me. My hardware huy tells me that these devices have
> something of
> their own OS and one never really knows how it's deciding
> to store
> data. Let alone where exactly.
>
> Am I more wrong then right or visa-versa?
> Wayne


Derek Asirvadem Posted on 2008-05-27 01:58:41.0Z
From: Derek Asirvadem <derek.asirvadem@gmailDOTcom>
Organization: Software Gems Pty Ltd
Newsgroups: sybase.public.ase.performance+tuning
Message-ID: <483b6acf@forums-1-dub>
References: <481fd02f.6d0.1681692777@sybase.com> <4839d104@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
User-Agent: Unison/1.7.7
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 26 May 2008 18:58:41 -0700
X-Trace: forums-1-dub 1211853521 10.22.241.152 (26 May 2008 18:58:41 -0700)
X-Original-Trace: 26 May 2008 18:58:41 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:10865
Article PK: 89476


> On 2008-05-26 06:50:13 +1000, peter collard <peterrrrrr@glossop.org> said:
>
> Forget the idea of allocating to sybase devices, just use striping (raid 0
> not raid5) as the perfect system is one where all disks are equally
> balanced at all times,

Well, definitely do that, not instead of segments, but as proper
configuration of the SAN LUNs for Sybase use.

Understanding Raid 0+1 or 1+0 would help.

> and no amount of sybase segmenting will achieve
> that. It was useful in 1990 when OS's just did raw disks, but is now only
> peddled by consultants trying to rip you off.

You misunderstand segments. The value of segments is placing OBJECTS
(tables, separately NC Indices, text, etc) on segments, as detailed in
my post. It is not about merely segmenting at the sybase level vs
striping at the SAN LUN level (which means you have missed the
point/value proposition).

Using segments for partitioned tables provides an additional layer of
performance (but you need to have experience with non-partitioned
tables first).

I do not know who you are talking about, but whenever we do a P&T
assignment that includes setting up the devices for a server, we lay
out segments and place all Db objects at no additional charge, as an
ordinary part of the job.

Just because segments are not commonly used, does not mean they are not
effective, or not worth doing.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability


Chris Jones Posted on 2008-09-26 16:30:18.0Z
From: Chris Jones <chris@j2consulting.co.uk>
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Tuning Database by Specifying Disk Locations. (Still Relevant?)
Message-ID: <pa3qd4t6lp4h4s7rcku7qpkiickost6nl2@4ax.com>
References: <481fd02f.6d0.1681692777@sybase.com> <4839d104@forums-1-dub> <483b6acf@forums-1-dub>
X-Newsreader: Forte Agent 4.2/32.1118 trialware
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 26 Sep 2008 09:30:18 -0700
X-Trace: forums-1-dub 1222446618 10.22.241.152 (26 Sep 2008 09:30:18 -0700)
X-Original-Trace: 26 Sep 2008 09:30:18 -0700, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:11212
Article PK: 89815

On 26 May 2008 18:58:41 -0700, Derek Asirvadem

<derek.asirvadem@gmailDOTcom> wrote:

>> On 2008-05-26 06:50:13 +1000, peter collard <peterrrrrr@glossop.org> said:
>>
>> Forget the idea of allocating to sybase devices, just use striping (raid 0
>> not raid5) as the perfect system is one where all disks are equally
>> balanced at all times,
>
>Well, definitely do that, not instead of segments, but as proper
>configuration of the SAN LUNs for Sybase use.
>
>Understanding Raid 0+1 or 1+0 would help.
>
>> and no amount of sybase segmenting will achieve
>> that. It was useful in 1990 when OS's just did raw disks, but is now only
>> peddled by consultants trying to rip you off.
>
>You misunderstand segments. The value of segments is placing OBJECTS
>(tables, separately NC Indices, text, etc) on segments, as detailed in
>my post. It is not about merely segmenting at the sybase level vs
>striping at the SAN LUN level (which means you have missed the
>point/value proposition).
>
>Using segments for partitioned tables provides an additional layer of
>performance (but you need to have experience with non-partitioned
>tables first).
>
>I do not know who you are talking about, but whenever we do a P&T
>assignment that includes setting up the devices for a server, we lay
>out segments and place all Db objects at no additional charge, as an
>ordinary part of the job.
>

Using segments within ASE :-
- Might provide performance improvements under certain circumstances
- Will create a server with new administration challanges

So - yep it might go faster - but - do the users require that extra
performance because it comes at a cost - a higher admin overhead.

Keep it vanilla ...

>Just because segments are not commonly used, does not mean they are not
>effective, or not worth doing.