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.

db consolidation

9 posts in General Discussion Last posting was on 2009-09-21 23:37:43.0Z
jt09 Posted on 2009-09-15 04:52:32.0Z
Sender: 4d82.4aaf18c4.1804289383@sybase.com
From: jt09
Newsgroups: sybase.public.ase.general
Subject: db consolidation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aaf1d90.4e88.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Sep 2009 21:52:32 -0700
X-Trace: forums-1-dub 1252990352 10.22.241.41 (14 Sep 2009 21:52:32 -0700)
X-Original-Trace: 14 Sep 2009 21:52:32 -0700, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28303
Article PK: 77547

Hi all

I would like some help with this. I have two sybase dbs
sitting on one host/one instance.

What I am looking to do is to 'consolidate' these two
databases. Currently these dbs interleave across a couple of
disks (say for example 2 disks).

I would like to 'separate' them and say have db1 running of
disk 1 and db2 running of disk 2 - as opposed to having
these dbs running off db1 & 2.

I tried to hunt around for best practise and readings (as I
am still a juinior) but can't seem to find anything or
perhaps I don't really know the right terms to use.

Any help/guidance would be much appreciated.

Thanks.


Jeff Tallman [Sybase] Posted on 2009-09-15 11:13:31.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
References: <4aaf1d90.4e88.1681692777@sybase.com>
In-Reply-To: <4aaf1d90.4e88.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: <4aaf76db$1@forums-1-dub>
Date: 15 Sep 2009 04:13:31 -0700
X-Trace: forums-1-dub 1253013211 10.22.241.152 (15 Sep 2009 04:13:31 -0700)
X-Original-Trace: 15 Sep 2009 04:13:31 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28305
Article PK: 77548

One way (if you can take the downtime) would be to:

1 - dump the databases using backup server
2 - drop and recreate the databases with same allocations - but just on
the desired devices
3 - load the database dumps.

As Carl points out though - unless you are trying to exploit quiesce
db/mount/unmount - having more devices available for each DB improves
performance - and can improve recoverability. For example, if you have
everything on disk1 for database1 and disk1 crashes, you are in a world
of hurt. However, if the transaction log was on disk2, you can likely
still dump the tran log so that at least when the disk is replaced, you
can load from the previous dump(s) and apply the last transactions and
recover up to the second the failure occurred.


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

jt09 wrote:
> Hi all
>
> I would like some help with this. I have two sybase dbs
> sitting on one host/one instance.
>
> What I am looking to do is to 'consolidate' these two
> databases. Currently these dbs interleave across a couple of
> disks (say for example 2 disks).
>
> I would like to 'separate' them and say have db1 running of
> disk 1 and db2 running of disk 2 - as opposed to having
> these dbs running off db1 & 2.
>
> I tried to hunt around for best practise and readings (as I
> am still a juinior) but can't seem to find anything or
> perhaps I don't really know the right terms to use.
>
> Any help/guidance would be much appreciated.
>
> Thanks.


jt2009 Posted on 2009-09-15 22:32:25.0Z
Sender: 6fad.4ab013be.1804289383@sybase.com
From: jt2009
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab015f9.6fe4.1681692777@sybase.com>
References: <4aaf76db$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Sep 2009 15:32:25 -0700
X-Trace: forums-1-dub 1253053945 10.22.241.41 (15 Sep 2009 15:32:25 -0700)
X-Original-Trace: 15 Sep 2009 15:32:25 -0700, 10.22.241.41
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28309
Article PK: 77554

Carl Kayser:

Yeah I meant to type "off disk 1 & 2" thanks heaps.

Based on your input, I think I will need to sit down and
consider what I want to get out
of this db separation process. One reason that I know of is,
when dumping say db1 and/or db2
we have to dump it across multiple disks - i.e time
consuming. The idea of having it on one disk
means we can perhaps dump these two dbs simultaneously
without impacting on each other and decreasing
the time taken - please stop me here if my thoughts on this
is not right :P

(would you be able to explain devices vs disks? is?
is the difference simply a logical vs physical thing? If you
have any links I read up on
it as well that would be great)

Jeff's input is key I guess. I think it will be good to for
me to understand reason behind the initial
set up of db1 and db2 (why was it set up like this to begin
with).

On that note though, it seems to be a dump and load process.

I 'heard' that the master db contains info on symbolic links
to where each db is pointed to. I have yet to find info on
this (like reading materials just to help me understand
things conceptually and covering the basics). Would anyone
have any info or links to this perhaps?

Can this process be done say by changing the symbolic links
around - if say the information i heard above is true.

Thanks for your help again.

> One way (if you can take the downtime) would be to:
>
> 1 - dump the databases using backup server
> 2 - drop and recreate the databases with same allocations
> - but just on the desired devices
> 3 - load the database dumps.
>
> As Carl points out though - unless you are trying to
> exploit quiesce db/mount/unmount - having more devices
> available for each DB improves performance - and can
> improve recoverability. For example, if you have
> everything on disk1 for database1 and disk1 crashes, you
> are in a world of hurt. However, if the transaction log
> was on disk2, you can likely still dump the tran log so
> that at least when the disk is replaced, you can load
> from the previous dump(s) and apply the last transactions
> and recover up to the second the failure occurred.
>
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> jt09 wrote:
> > Hi all
> >
> > I would like some help with this. I have two sybase dbs
> > sitting on one host/one instance.
> >
> > What I am looking to do is to 'consolidate' these two
> > databases. Currently these dbs interleave across a
> > couple of disks (say for example 2 disks).
> >
> > I would like to 'separate' them and say have db1 running
> > of disk 1 and db2 running of disk 2 - as opposed to
> > having these dbs running off db1 & 2.
> >
> > I tried to hunt around for best practise and readings
> > (as I am still a juinior) but can't seem to find
> > anything or perhaps I don't really know the right terms
> > to use.
> > Any help/guidance would be much appreciated.
> >
> > Thanks.


Jeff Tallman [Sybase] Posted on 2009-09-16 01:31:11.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
References: <4aaf76db$1@forums-1-dub> <4ab015f9.6fe4.1681692777@sybase.com>
In-Reply-To: <4ab015f9.6fe4.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: <4ab03fdf$1@forums-1-dub>
Date: 15 Sep 2009 18:31:11 -0700
X-Trace: forums-1-dub 1253064671 10.22.241.152 (15 Sep 2009 18:31:11 -0700)
X-Original-Trace: 15 Sep 2009 18:31:11 -0700, vip152.sybase.com
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28310
Article PK: 77553

The 'symbolic links' you state are in a table called sysusages - which
records each allocation fragment and associated device. Unfortunately,
changing this typically prevents database recovery or could cause a
corruption.

As far as the backup server impacts, lets say that each database takes
up 1M pages. So each dump would be more (or less) 1M pages. If equally
spread across multiple devices, that would be 500K page reads per
device. Doing both simultaneously would be a total of 1M page reads per
device - 500K for each database. If separated, it still would be 1M
page reads per device - so no net gain.

All of this is theory on equal distribution. It could be 900K pages of
data on dev1 and 100K pages of log on dev2 (and you need 2 devices in
order to be able to do tran dumps). But it still works out the same.

However, dumping 2 databases simultaneously will add cpu and more IO
overhead vs. dumping sequentially - I just don't think it will matter a
whole lot if spread across two devices or just 1.

If you do the one device trick, you may want to really consider having
the unix SA partition each device into 3-4 smaller devices that are
exposed to the OS as separate disks (or minimally disk partitions).
While this doesn't resolve the IO concurrency the way having it spread
across 2 real disks does, it does give you the ability to separate log
and data and be able to do tran log dumps.

One of the biggest factors you need to consider is whether one database
is "hotter" than the other. If so, it likely is benefiting from more
than one disk.....


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

jt2009 wrote:
> Carl Kayser:
>
> Yeah I meant to type "off disk 1 & 2" thanks heaps.
>
> Based on your input, I think I will need to sit down and
> consider what I want to get out
> of this db separation process. One reason that I know of is,
> when dumping say db1 and/or db2
> we have to dump it across multiple disks - i.e time
> consuming. The idea of having it on one disk
> means we can perhaps dump these two dbs simultaneously
> without impacting on each other and decreasing
> the time taken - please stop me here if my thoughts on this
> is not right :P
>
> (would you be able to explain devices vs disks? is?
> is the difference simply a logical vs physical thing? If you
> have any links I read up on
> it as well that would be great)
>
> Jeff's input is key I guess. I think it will be good to for
> me to understand reason behind the initial
> set up of db1 and db2 (why was it set up like this to begin
> with).
>
> On that note though, it seems to be a dump and load process.
>
> I 'heard' that the master db contains info on symbolic links
> to where each db is pointed to. I have yet to find info on
> this (like reading materials just to help me understand
> things conceptually and covering the basics). Would anyone
> have any info or links to this perhaps?
>
> Can this process be done say by changing the symbolic links
> around - if say the information i heard above is true.
>
> Thanks for your help again.
>
>
>
>
>
>
>
>
>> One way (if you can take the downtime) would be to:
>>
>> 1 - dump the databases using backup server
>> 2 - drop and recreate the databases with same allocations
>> - but just on the desired devices
>> 3 - load the database dumps.
>>
>> As Carl points out though - unless you are trying to
>> exploit quiesce db/mount/unmount - having more devices
>> available for each DB improves performance - and can
>> improve recoverability. For example, if you have
>> everything on disk1 for database1 and disk1 crashes, you
>> are in a world of hurt. However, if the transaction log
>> was on disk2, you can likely still dump the tran log so
>> that at least when the disk is replaced, you can load
>> from the previous dump(s) and apply the last transactions
>> and recover up to the second the failure occurred.
>>
>>
>> Jeff Tallman
>> Enterprise Data Management Products Technical Evangelism
>> jeff.tallman@sybase.com
>> http://blogs.sybase.com/database
>>
>> jt09 wrote:
>>> Hi all
>>>
>>> I would like some help with this. I have two sybase dbs
>>> sitting on one host/one instance.
>>>
>>> What I am looking to do is to 'consolidate' these two
>>> databases. Currently these dbs interleave across a
>>> couple of disks (say for example 2 disks).
>>>
>>> I would like to 'separate' them and say have db1 running
>>> of disk 1 and db2 running of disk 2 - as opposed to
>>> having these dbs running off db1 & 2.
>>>
>>> I tried to hunt around for best practise and readings
>>> (as I am still a juinior) but can't seem to find
>>> anything or perhaps I don't really know the right terms
>>> to use.
>>> Any help/guidance would be much appreciated.
>>>
>>> Thanks.


jt2009 Posted on 2009-09-16 05:40:40.0Z
Sender: 7d21.4ab0785e.1804289383@sybase.com
From: jt2009
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab07a58.7d54.1681692777@sybase.com>
References: <4ab03fdf$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Sep 2009 22:40:40 -0700
X-Trace: forums-1-dub 1253079640 10.22.241.41 (15 Sep 2009 22:40:40 -0700)
X-Original-Trace: 15 Sep 2009 22:40:40 -0700, 10.22.241.41
Lines: 153
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28311
Article PK: 77555

Thanks for that. Another informative read. I don't think I
am aiming for a 'one device' approach.

I did some more digging around. DB1 & DB2 span across
several devices and 'are supposedly' interleaved across a
number of disks. I was thinking to check out if indeed the
devices used by the DBs are indeed sitting on one disk.

I queried sysusages and got the dbid for the DBs but am
still unsure how to work back to which disks are being used.
I am assuming that only the unix guys can tell me that?

(My current understanding of devices is that it is like a
partition. e.g Disk A could have dev 1-5 that is 5
partitions. I guess I am listing it here so in case I've got
my concepts wrong I can be corrected)

> The 'symbolic links' you state are in a table called
> sysusages - which records each allocation fragment and
> associated device. Unfortunately, changing this
> typically prevents database recovery or could cause a
> corruption.
>
> As far as the backup server impacts, lets say that each
> database takes up 1M pages. So each dump would be more
> (or less) 1M pages. If equally spread across multiple
> devices, that would be 500K page reads per device. Doing
> both simultaneously would be a total of 1M page reads per
> device - 500K for each database. If separated, it still
> would be 1M page reads per device - so no net gain.
>
> All of this is theory on equal distribution. It could be
> 900K pages of data on dev1 and 100K pages of log on dev2
> (and you need 2 devices in order to be able to do tran
> dumps). But it still works out the same.
>
> However, dumping 2 databases simultaneously will add cpu
> and more IO overhead vs. dumping sequentially - I just
> don't think it will matter a whole lot if spread across
> two devices or just 1.
>
> If you do the one device trick, you may want to really
> consider having the unix SA partition each device into
> 3-4 smaller devices that are exposed to the OS as
> separate disks (or minimally disk partitions). While this
> doesn't resolve the IO concurrency the way having it
> spread across 2 real disks does, it does give you the
> ability to separate log and data and be able to do tran
> log dumps.
>
> One of the biggest factors you need to consider is whether
> one database is "hotter" than the other. If so, it
> likely is benefiting from more than one disk.....
>
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> jt2009 wrote:
> > Carl Kayser:
> >
> > Yeah I meant to type "off disk 1 & 2" thanks heaps.
> >
> > Based on your input, I think I will need to sit down and
> > consider what I want to get out
> > of this db separation process. One reason that I know of
> > is, when dumping say db1 and/or db2
> > we have to dump it across multiple disks - i.e time
> > consuming. The idea of having it on one disk
> > means we can perhaps dump these two dbs simultaneously
> > without impacting on each other and decreasing
> > the time taken - please stop me here if my thoughts on
> > this is not right :P
> >
> > (would you be able to explain devices vs disks? is?
> > is the difference simply a logical vs physical thing? If
> > you have any links I read up on
> > it as well that would be great)
> >
> > Jeff's input is key I guess. I think it will be good to
> > for me to understand reason behind the initial
> > set up of db1 and db2 (why was it set up like this to
> > begin with).
> >
> > On that note though, it seems to be a dump and load
> > process.
> > I 'heard' that the master db contains info on symbolic
> > links to where each db is pointed to. I have yet to find
> > info on this (like reading materials just to help me
> > understand things conceptually and covering the basics).
> > Would anyone have any info or links to this perhaps?
> >
> > Can this process be done say by changing the symbolic
> > links around - if say the information i heard above is
> > true.
> > Thanks for your help again.
> >
> >
> >
> >
> >
> >
> >
> >
> >> One way (if you can take the downtime) would be to:
> >>
> >> 1 - dump the databases using backup server
> >> 2 - drop and recreate the databases with same
> allocations >> - but just on the desired devices
> >> 3 - load the database dumps.
> >>
> >> As Carl points out though - unless you are trying to
> >> exploit quiesce db/mount/unmount - having more devices
> >> available for each DB improves performance - and can
> >> improve recoverability. For example, if you have
> >> everything on disk1 for database1 and disk1 crashes,
> you >> are in a world of hurt. However, if the
> transaction log >> was on disk2, you can likely still
> dump the tran log so >> that at least when the disk is
> replaced, you can load >> from the previous dump(s) and
> apply the last transactions >> and recover up to the
> second the failure occurred. >>
> >>
> >> Jeff Tallman
> >> Enterprise Data Management Products Technical
> Evangelism >> jeff.tallman@sybase.com
> >> http://blogs.sybase.com/database
> >>
> >> jt09 wrote:
> >>> Hi all
> >>>
> >>> I would like some help with this. I have two sybase
> dbs >>> sitting on one host/one instance.
> >>>
> >>> What I am looking to do is to 'consolidate' these two
> >>> databases. Currently these dbs interleave across a
> >>> couple of disks (say for example 2 disks).
> >>>
> >>> I would like to 'separate' them and say have db1
> running >>> of disk 1 and db2 running of disk 2 - as
> opposed to >>> having these dbs running off db1 & 2.
> >>>
> >>> I tried to hunt around for best practise and readings
> >>> (as I am still a juinior) but can't seem to find
> >>> anything or perhaps I don't really know the right
> terms >>> to use.
> >>> Any help/guidance would be much appreciated.
> >>>
> >>> Thanks.


Jeff Tallman [Sybase] Posted on 2009-09-16 21:56:25.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
References: <4ab03fdf$1@forums-1-dub> <4ab07a58.7d54.1681692777@sybase.com>
In-Reply-To: <4ab07a58.7d54.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: <4ab15f09@forums-1-dub>
Date: 16 Sep 2009 14:56:25 -0700
X-Trace: forums-1-dub 1253138185 10.22.241.152 (16 Sep 2009 14:56:25 -0700)
X-Original-Trace: 16 Sep 2009 14:56:25 -0700, vip152.sybase.com
Lines: 168
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28313
Article PK: 77556

Which version of ASE?? <15 you would have to check sysusages.vstart
between sysdevices.low and sysdevices.high. For 15.0+, you simply join
sysusages.vdevno = sysdevices.vdevno

Once you have the sybase "devices", sysdevices reports the OS path (e.g.
/dev/rdsk/...)....

The OS path will tell Unix SA's which OS logical devices is being used.
This may be a single physical disk with multiple partitions by the OS
- or it could be a logical device that is mapped to a logical unit (LUN)
in the SAN....they would then have to tell you how that is layed out.

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

jt2009 wrote:
> Thanks for that. Another informative read. I don't think I
> am aiming for a 'one device' approach.
>
> I did some more digging around. DB1 & DB2 span across
> several devices and 'are supposedly' interleaved across a
> number of disks. I was thinking to check out if indeed the
> devices used by the DBs are indeed sitting on one disk.
>
> I queried sysusages and got the dbid for the DBs but am
> still unsure how to work back to which disks are being used.
> I am assuming that only the unix guys can tell me that?
>
> (My current understanding of devices is that it is like a
> partition. e.g Disk A could have dev 1-5 that is 5
> partitions. I guess I am listing it here so in case I've got
> my concepts wrong I can be corrected)
>
>
>
>> The 'symbolic links' you state are in a table called
>> sysusages - which records each allocation fragment and
>> associated device. Unfortunately, changing this
>> typically prevents database recovery or could cause a
>> corruption.
>>
>> As far as the backup server impacts, lets say that each
>> database takes up 1M pages. So each dump would be more
>> (or less) 1M pages. If equally spread across multiple
>> devices, that would be 500K page reads per device. Doing
>> both simultaneously would be a total of 1M page reads per
>> device - 500K for each database. If separated, it still
>> would be 1M page reads per device - so no net gain.
>>
>> All of this is theory on equal distribution. It could be
>> 900K pages of data on dev1 and 100K pages of log on dev2
>> (and you need 2 devices in order to be able to do tran
>> dumps). But it still works out the same.
>>
>> However, dumping 2 databases simultaneously will add cpu
>> and more IO overhead vs. dumping sequentially - I just
>> don't think it will matter a whole lot if spread across
>> two devices or just 1.
>>
>> If you do the one device trick, you may want to really
>> consider having the unix SA partition each device into
>> 3-4 smaller devices that are exposed to the OS as
>> separate disks (or minimally disk partitions). While this
>> doesn't resolve the IO concurrency the way having it
>> spread across 2 real disks does, it does give you the
>> ability to separate log and data and be able to do tran
>> log dumps.
>>
>> One of the biggest factors you need to consider is whether
>> one database is "hotter" than the other. If so, it
>> likely is benefiting from more than one disk.....
>>
>>
>> Jeff Tallman
>> Enterprise Data Management Products Technical Evangelism
>> jeff.tallman@sybase.com
>> http://blogs.sybase.com/database
>>
>> jt2009 wrote:
>>> Carl Kayser:
>>>
>>> Yeah I meant to type "off disk 1 & 2" thanks heaps.
>>>
>>> Based on your input, I think I will need to sit down and
>>> consider what I want to get out
>>> of this db separation process. One reason that I know of
>>> is, when dumping say db1 and/or db2
>>> we have to dump it across multiple disks - i.e time
>>> consuming. The idea of having it on one disk
>>> means we can perhaps dump these two dbs simultaneously
>>> without impacting on each other and decreasing
>>> the time taken - please stop me here if my thoughts on
>>> this is not right :P
>>>
>>> (would you be able to explain devices vs disks? is?
>>> is the difference simply a logical vs physical thing? If
>>> you have any links I read up on
>>> it as well that would be great)
>>>
>>> Jeff's input is key I guess. I think it will be good to
>>> for me to understand reason behind the initial
>>> set up of db1 and db2 (why was it set up like this to
>>> begin with).
>>>
>>> On that note though, it seems to be a dump and load
>>> process.
>>> I 'heard' that the master db contains info on symbolic
>>> links to where each db is pointed to. I have yet to find
>>> info on this (like reading materials just to help me
>>> understand things conceptually and covering the basics).
>>> Would anyone have any info or links to this perhaps?
>>>
>>> Can this process be done say by changing the symbolic
>>> links around - if say the information i heard above is
>>> true.
>>> Thanks for your help again.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>> One way (if you can take the downtime) would be to:
>>>>
>>>> 1 - dump the databases using backup server
>>>> 2 - drop and recreate the databases with same
>> allocations >> - but just on the desired devices
>>>> 3 - load the database dumps.
>>>>
>>>> As Carl points out though - unless you are trying to
>>>> exploit quiesce db/mount/unmount - having more devices
>>>> available for each DB improves performance - and can
>>>> improve recoverability. For example, if you have
>>>> everything on disk1 for database1 and disk1 crashes,
>> you >> are in a world of hurt. However, if the
>> transaction log >> was on disk2, you can likely still
>> dump the tran log so >> that at least when the disk is
>> replaced, you can load >> from the previous dump(s) and
>> apply the last transactions >> and recover up to the
>> second the failure occurred. >>
>>>> Jeff Tallman
>>>> Enterprise Data Management Products Technical
>> Evangelism >> jeff.tallman@sybase.com
>>>> http://blogs.sybase.com/database
>>>>
>>>> jt09 wrote:
>>>>> Hi all
>>>>>
>>>>> I would like some help with this. I have two sybase
>> dbs >>> sitting on one host/one instance.
>>>>> What I am looking to do is to 'consolidate' these two
>>>>> databases. Currently these dbs interleave across a
>>>>> couple of disks (say for example 2 disks).
>>>>>
>>>>> I would like to 'separate' them and say have db1
>> running >>> of disk 1 and db2 running of disk 2 - as
>> opposed to >>> having these dbs running off db1 & 2.
>>>>> I tried to hunt around for best practise and readings
>>>>> (as I am still a juinior) but can't seem to find
>>>>> anything or perhaps I don't really know the right
>> terms >>> to use.
>>>>> Any help/guidance would be much appreciated.
>>>>>
>>>>> Thanks.


Carl Kayser Posted on 2009-09-16 11:17:04.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4aaf76db$1@forums-1-dub> <4ab015f9.6fe4.1681692777@sybase.com>
Subject: Re: db consolidation
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ab0c930$1@forums-1-dub>
Date: 16 Sep 2009 04:17:04 -0700
X-Trace: forums-1-dub 1253099824 10.22.241.152 (16 Sep 2009 04:17:04 -0700)
X-Original-Trace: 16 Sep 2009 04:17:04 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28312
Article PK: 77557


<jt2009> wrote in message news:4ab015f9.6fe4.1681692777@sybase.com...
> Carl Kayser:
>
> Yeah I meant to type "off disk 1 & 2" thanks heaps.
>
> Based on your input, I think I will need to sit down and
> consider what I want to get out
> of this db separation process. One reason that I know of is,
> when dumping say db1 and/or db2
> we have to dump it across multiple disks - i.e time
> consuming. The idea of having it on one disk
> means we can perhaps dump these two dbs simultaneously
> without impacting on each other and decreasing
> the time taken - please stop me here if my thoughts on this
> is not right :P
>
> (would you be able to explain devices vs disks? is?
> is the difference simply a logical vs physical thing? If you
> have any links I read up on
> it as well that would be great)
>

(SNIP)

Unfortunately I may have written with a mix of Solaris-speak and
Sybase-speak. A "disk" is a physical disk and Sybase does not directly
reference an entire disk. Sybase references devices and a Sybase device is
the same as a Solaris partition. (And a Solaris disk can be partitioned
into a maximum of seven partitions. If it is only one partition then Sybase
may be referencing the entire disk - an exception to my previous statement
about not referencing a disk.)

So when doing a singular (one row) database write (delete/insert/update)
there will be a data update and a log record insert. If the data device and
log device are on the same disk then you're going to whipsaw the disk head.
(This is OVERLY simplistic but relevant.) If the data and log are on
different disks then you get the benefit of two disk heads instead of one.
Obviously this can become complex when you have multiple active databases.
If you have indexes then these must also be maintained from the database
writes. You cannot seperate an APL clustered index from the data but
nonclustered indexes can be put on another device and you get the possible
benefit of three disk heads.

Most of what I was pointing out was directed from a basic P&T perspective.
I don't think that disk/device configurations are an important "design
factor" with regards to database backups. Unless you restore a dump to a
different database or server. Then having a lot of devices/segments in the
original database can, er, uglify, the resulting load elsewhere.


jt2009 Posted on 2009-09-21 23:37:43.0Z
Sender: 2951.4ab80d4d.846930886@sybase.com
From: jt2009
Newsgroups: sybase.public.ase.general
Subject: Re: db consolidation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab80e47.2964.1681692777@sybase.com>
References: <4ab0c930$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Sep 2009 16:37:43 -0700
X-Trace: forums-1-dub 1253576263 10.22.241.41 (21 Sep 2009 16:37:43 -0700)
X-Original-Trace: 21 Sep 2009 16:37:43 -0700, 10.22.241.41
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28339
Article PK: 77583

Hi guys

thank you very much for the advise and information. Just
thought I'd give an update.

I've managed to get the unix sa to identify the disks that
the databases uses based on the device name. In doing so
I've identified that one device each for both DB.A and DB.B
are sharing a disk.

My intention now is to mirror DB.A only. So I was thinking
perhaps to create a master db just for DB.A.

-----------------------------------------------

> <jt2009> wrote in message
> > news:4ab015f9.6fe4.1681692777@sybase.com... Carl Kayser:
> >
> > Yeah I meant to type "off disk 1 & 2" thanks heaps.
> >
> > Based on your input, I think I will need to sit down and
> > consider what I want to get out
> > of this db separation process. One reason that I know of
> > is, when dumping say db1 and/or db2
> > we have to dump it across multiple disks - i.e time
> > consuming. The idea of having it on one disk
> > means we can perhaps dump these two dbs simultaneously
> > without impacting on each other and decreasing
> > the time taken - please stop me here if my thoughts on
> > this is not right :P
> >
> > (would you be able to explain devices vs disks? is?
> > is the difference simply a logical vs physical thing? If
> > you have any links I read up on
> > it as well that would be great)
> >
>
> (SNIP)
>
> Unfortunately I may have written with a mix of
> Solaris-speak and Sybase-speak. A "disk" is a physical
> disk and Sybase does not directly reference an entire
> disk. Sybase references devices and a Sybase device is
> the same as a Solaris partition. (And a Solaris disk can
> be partitioned into a maximum of seven partitions. If it
> is only one partition then Sybase may be referencing the
> entire disk - an exception to my previous statement about
> not referencing a disk.)
>
> So when doing a singular (one row) database write
> (delete/insert/update) there will be a data update and a
> log record insert. If the data device and log device are
> on the same disk then you're going to whipsaw the disk
> head. (This is OVERLY simplistic but relevant.) If the
> data and log are on different disks then you get the
> benefit of two disk heads instead of one. Obviously this
> can become complex when you have multiple active
> databases. If you have indexes then these must also be
> maintained from the database writes. You cannot seperate
> an APL clustered index from the data but nonclustered
> indexes can be put on another device and you get the
> possible benefit of three disk heads.
>
> Most of what I was pointing out was directed from a basic
> P&T perspective. I don't think that disk/device
> configurations are an important "design factor" with
> regards to database backups. Unless you restore a dump to
> a different database or server. Then having a lot of
> devices/segments in the original database can, er, uglify
> , the resulting load elsewhere.
>
>


Carl Kayser Posted on 2009-09-15 10:21:39.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4aaf1d90.4e88.1681692777@sybase.com>
Subject: Re: db consolidation
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aaf6ab3$1@forums-1-dub>
Date: 15 Sep 2009 03:21:39 -0700
X-Trace: forums-1-dub 1253010099 10.22.241.152 (15 Sep 2009 03:21:39 -0700)
X-Original-Trace: 15 Sep 2009 03:21:39 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28304
Article PK: 77551


<jt09> wrote in message news:4aaf1d90.4e88.1681692777@sybase.com...
>
> Hi all
>
> I would like some help with this. I have two sybase dbs
> sitting on one host/one instance.
>
> What I am looking to do is to 'consolidate' these two
> databases. Currently these dbs interleave across a couple of
> disks (say for example 2 disks).
>
> I would like to 'separate' them and say have db1 running of
> disk 1 and db2 running of disk 2 - as opposed to having
> these dbs running off db1 & 2.

I think you meant "off disk 1 & 2" here.

>
> I tried to hunt around for best practise and readings (as I
> am still a juinior) but can't seem to find anything or
> perhaps I don't really know the right terms to use.
>
> Any help/guidance would be much appreciated.
>
> Thanks.

Offhand, if you had six disks for the two DBs, the ideal config would be one
disk for each of log, data, and nonclustered indexes for each of the two
DBs. You could isolate further (benchmark wars!) by putting the system and
default segments on their own disks, if you have them. And do the same for
the system databases and tempd DBs.

Realistically, you don't have all of those disks (and it gets worse each day
as the vendors continue to produce larger but fewer disks per server). For
some of the above you would use different partitions instead of different
disks.

Post some followup info and questions. There are enough P&T experts better
than me that should be able to answer the questions. But after a point in
detail there may be more than one way to skin a cat and there will be
different opinions based on different exoeriences and goals (minimize CPU or
IO? "Fast user DB1" or "fast tempdb"? Applications are OLTP, DSS, or
ODSS?).