marrowyung
asked on
enhance sybase tempdb structure.
hi,
I am new to Sybase and I have a operation to optimize the tempdB by creating more than one tempdb files and move to different disk, please let me know if the steps are correct and command are correct :
: 1.Created 4*250GB LUN for tempdb
2.Created 4 DG (data group in SAN for creating LUN) used for tempdb device
LUN - TempDB ======> Used for tempdb
LUN - TempDB_large ======> Used for tempdb_large
LUN - TempDB_online1 ======> Used for tempdb_online1
LUN - TempDB_online2 ======> Used for tempdb_online2
3. Dedicated Raw device for tempdb (70 GB Data, 30 GB): tempdev_data1 , tempdev_log1, tempdev_online_data1 , tempdev_online_log1, tempdev_large_data1 , tempdev_large_log1...etc.
4.Current Status-
tempdb 37504.0 MB Mixed
tempdb2 500.0 MB Mixed
tempdb3 30048.0 MB Mixed
tempdb4 47600.0 MB Mixed
we need to shrink / drop and make each tempdb 25000 MB with data and log separated
CREATE TEMPORARY DATABASE tempdb_large ON tempdev_large_data1 = '20000M' LOG ON tempdev_large_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online1 ON tempdev_online_data1 = '20000M' LOG ON tempdev_online_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online2 ON tempdev_online_data2 = '20000M' LOG ON tempdev_online_log2 = '5000M'
exec master.dbo.sp_dboption tempdb, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb, 'abort tran on log full', true
is it good to setup each tempDB using these 4 x option, is it necessary ?
exec master.dbo.sp_dboption tempdb_large, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_large, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_large, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_large, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online1, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online1, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online1, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online1, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online2, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online2, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online2, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online2, 'abort tran on log full', true
5. Create new defined cache
Add memory - sp_configure 'max memory',23600000
tempdb will continue use of default data cache ( 12500.0 MB). There will be three more named cache with 5GB for each tempdb
In Sybase, is it a correct way to enlarge tempdb and at the same time, adjust msx memory using sp_configure 'max memory' ?
Define new data cache -
sp_cacheconfig 'tempdb_large_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_large_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_large_cache, "800M", "16K", "2K"
is it a good way to setup data cache for Sybase temp in this way?
sp_cacheconfig 'tempdb_online1_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online1_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online1_cache, "800M", "16K", "2K"
sp_cacheconfig 'tempdb_online2_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online2_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online2_cache, "800M", "16K", "2K"
Bind newly created data cache -
sp_bindcache tempdb_large_cache, tempdb_large
sp_bindcache tempdb_online1_cache, tempdb_online1
sp_bindcache tempdb_online2_cache, tempdb_online2
correct way to map a tempdb file to a tempdb cache ? is it a MUST to map a cache to EACH tempDB file ?
6. Group - creation & mapping
sp_tempdb add, tempdb, "default"
sp_tempdb 'create', 'tempdb_large'
sp_tempdb 'create', 'tempdb_online_grp'
sp_tempdb add, tempdb_large , "tempdb_large"
sp_tempdb add, tempdb_online1, "tempdb_online_grp"
sp_tempdb add, tempdb_online2, "tempdb_online_grp"
Bind application id to tempdb_large group - happsrv , asysmz7 , asys454 & asysvxt
usually do we need to create tempdb group and map to tempdb file ? why need to keep mapping for tempdb group, cache and file ?
7. Housekeeping
Remove current tempdb devices
Release raw device and LUN associated with them
I am new to Sybase and I have a operation to optimize the tempdB by creating more than one tempdb files and move to different disk, please let me know if the steps are correct and command are correct :
: 1.Created 4*250GB LUN for tempdb
2.Created 4 DG (data group in SAN for creating LUN) used for tempdb device
LUN - TempDB ======> Used for tempdb
LUN - TempDB_large ======> Used for tempdb_large
LUN - TempDB_online1 ======> Used for tempdb_online1
LUN - TempDB_online2 ======> Used for tempdb_online2
3. Dedicated Raw device for tempdb (70 GB Data, 30 GB): tempdev_data1 , tempdev_log1, tempdev_online_data1 , tempdev_online_log1, tempdev_large_data1 , tempdev_large_log1...etc.
4.Current Status-
tempdb 37504.0 MB Mixed
tempdb2 500.0 MB Mixed
tempdb3 30048.0 MB Mixed
tempdb4 47600.0 MB Mixed
we need to shrink / drop and make each tempdb 25000 MB with data and log separated
CREATE TEMPORARY DATABASE tempdb_large ON tempdev_large_data1 = '20000M' LOG ON tempdev_large_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online1 ON tempdev_online_data1 = '20000M' LOG ON tempdev_online_log1 = '5000M'
CREATE TEMPORARY DATABASE tempdb_online2 ON tempdev_online_data2 = '20000M' LOG ON tempdev_online_log2 = '5000M'
exec master.dbo.sp_dboption tempdb, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb, 'abort tran on log full', true
is it good to setup each tempDB using these 4 x option, is it necessary ?
exec master.dbo.sp_dboption tempdb_large, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_large, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_large, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_large, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online1, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online1, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online1, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online1, 'abort tran on log full', true
exec master.dbo.sp_dboption tempdb_online2, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb_online2, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb_online2, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb_online2, 'abort tran on log full', true
5. Create new defined cache
Add memory - sp_configure 'max memory',23600000
tempdb will continue use of default data cache ( 12500.0 MB). There will be three more named cache with 5GB for each tempdb
In Sybase, is it a correct way to enlarge tempdb and at the same time, adjust msx memory using sp_configure 'max memory' ?
Define new data cache -
sp_cacheconfig 'tempdb_large_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_large_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_large_cache, "800M", "16K", "2K"
is it a good way to setup data cache for Sybase temp in this way?
sp_cacheconfig 'tempdb_online1_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online1_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online1_cache, "800M", "16K", "2K"
sp_cacheconfig 'tempdb_online2_cache', "5000M", mixed, strict, "cache_partition=64"
sp_poolconfig tempdb_online2_cache, "3000M", "4K", "2K"
sp_poolconfig tempdb_online2_cache, "800M", "16K", "2K"
Bind newly created data cache -
sp_bindcache tempdb_large_cache, tempdb_large
sp_bindcache tempdb_online1_cache, tempdb_online1
sp_bindcache tempdb_online2_cache, tempdb_online2
correct way to map a tempdb file to a tempdb cache ? is it a MUST to map a cache to EACH tempDB file ?
6. Group - creation & mapping
sp_tempdb add, tempdb, "default"
sp_tempdb 'create', 'tempdb_large'
sp_tempdb 'create', 'tempdb_online_grp'
sp_tempdb add, tempdb_large , "tempdb_large"
sp_tempdb add, tempdb_online1, "tempdb_online_grp"
sp_tempdb add, tempdb_online2, "tempdb_online_grp"
Bind application id to tempdb_large group - happsrv , asysmz7 , asys454 & asysvxt
usually do we need to create tempdb group and map to tempdb file ? why need to keep mapping for tempdb group, cache and file ?
7. Housekeeping
Remove current tempdb devices
Release raw device and LUN associated with them
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Regarding "creating more than one tempdb files and move to different disk" it sounds like they are all on the same disk still since you are creating LUNs, presumably on the same target.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Joe Woodhouse,
no best practice on the tempdb files? and sybase has DMV (MDA) to query the best use of tempdb?
in MS SQL we only concern about tempdb contention.
in MS SQL, it has proportional fill model, which make sure that!
Sybase can create different tempDB files in different tempdb group for different user ? does it help ?
how it make thing worse? if the load it not distributed evenly?
why can make it worse?
any relationship between tempdb database and tempdb cache ?
but here I just want to focus on if the command I post is right or not ! any problem you can see if it is done by operator?
Please just assume I want to splite tempdb load ot many files, with the command I post, is it ok ?
probably if they are on the SAME LUN RAID volumn, right ?
for tempdb, should we focus on tempdb file contention in ms so that, let's say if contenion is higher than 50ms, it is tempdb contention ? then we need to add one more tempdb of the SAME SIZE ?
tempdb P&T decisions based on evidence see in monitoring like sp_sysmon and from the MDA tables.
no best practice on the tempdb files? and sybase has DMV (MDA) to query the best use of tempdb?
Contention: if we see unacceptable lock contention on tempdb system tables, then we consider splitting up one big tempdb into a greater number of smaller tempdbs. We hope that tempdb usage will be divided up between them and we will see lock contention decrease.
in MS SQL we only concern about tempdb contention.
But this will only help if there was lock contention in the first place, and if the workload truly is divided up evenly between the new tempdb databases.
in MS SQL, it has proportional fill model, which make sure that!
Resilience: we like to add a small DBA-only tempdb so if users fill their tempdb database(s) we as DBAs still have one that we can use. This only works if we use tempdb groups to make sure only DBAs use the tempdb_dba database.
Sybase can create different tempDB files in different tempdb group for different user ? does it help ?
This is an important question because if you don't see tempdb lock contention then making multiple tempdbs can make things worse.
how it make thing worse? if the load it not distributed evenly?
Making too many tempdb caches (or the wrong size) can kill your ASE performance.
why can make it worse?
Because it might be that you should have just one tempdb database and just one tempdb cache.
any relationship between tempdb database and tempdb cache ?
but here I just want to focus on if the command I post is right or not ! any problem you can see if it is done by operator?
What problem(s) are you trying to solve, and what monitoring have you captured?
Please just assume I want to splite tempdb load ot many files, with the command I post, is it ok ?
Given that almost all LUNs these days are on a SAN which is itself striped and doing its own balancing and reordering, there's usually little benefit in splitting LUNs for performance.
probably if they are on the SAME LUN RAID volumn, right ?
It is possible to see RHEL queue size and average waits for a LUN get unacceptably high,
for tempdb, should we focus on tempdb file contention in ms so that, let's say if contenion is higher than 50ms, it is tempdb contention ? then we need to add one more tempdb of the SAME SIZE ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Go right ahead. I've already said your syntax is correct. I strongly recommend you measure performance before and after, because with your comments here I am fairly sure performance will get worse.
which command make you think it will get worse ? some command worrying you and what is it ? and why?
rather than because we've proven that we should, and then proven again that they helped. Good luck!yeah! some DMV can help on that !
ASKER
Nothing official from SAP. I'll be giving a webinar presentation on tempdb tuning for the UKSUG in June; you should see that advertised through their website.
under your name: Joe Woodhouse ?
This is important: a connection can use only one tempdb.
interested to hear that! for MS SQL, we have tools to check if the tempdb file have evenly distributed ! but can't verify if a single tempdb connection only use one tempdb data file !
in sybase how can you know ?
The issue is if you have 100 OLTP connections and 3 connections running reports. They might all end up assigned to the same tempdb, meaning one tempdb will be very busy (or even run out of space) while the others are empty.
then this is not round robin !
like I suggested doing for DBAs. You might create ones to be used only by logins for a particular application, say, or another that all report users will be confined to.
I think in this way sybase is better than MS SQL, in MS SQL we can't set it .
can you tell me how to set it ?
what you can do with the sp_tempdb procedure.
and we do this:
exec master.dbo.sp_dboption tempdb, 'select into/bulkcopy/pllsort', true
exec master.dbo.sp_dboption tempdb, 'trunc log on chkpt', true
exec master.dbo.sp_dboption tempdb, 'allow wide dol rows', true
exec master.dbo.sp_dboption tempdb, 'abort tran on log full', true
what is :
1) 'select into/bulkcopy/pllsort', true
2) 'trunc log on chkpt', true
3) 'allow wide dol rows', true
4)'abort tran on log full', true
for ?
which worked fine in my old 100Gb cache - will now have to squeeze into only 25Gb of cache, and run much more slowly as a result.
then seems create 4x 100GB will be better instead of 4 x 25GB?
A smaller logonly cache just for tempdb logs can be a good idea. An even smaller cache for tempdb system tables can be a good idea.
why it is better? or just :
"then seems create 4x 100GB will be better instead of 4 x 25GB?"?
... no offence but if you're asking that question you shouldn't be running any of these commands.
nono I am verifying sybase script, not sure if it is right ! it is from other department.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sp_who output shows you which tempdb a connection is using.
this one , in MS SQL , can't shows! need other tools.
Round-robin means just assign connections in the order they log in. It is the same in MS SQL. There is no attempt to load balance. My comment was that round-robin can still result in heavy users being assigned to the same tempdb.
in MS SQL, if you use use file explorer to take a look and other disk monitoring tools to see the throughput, nearly a good load balance! their size keep growth nearly together.
but it is true that not that load balanced, round-robin always make the first one a bit more loading.
splitting into more numerous but smaller tempdb caches (
I understand this, it is about smaller one and each connection only can connect to one ! just like application connection ! connect to only one DB node at a time.
ASKER
I've already said the syntax is correct. :)
I can see it , but want to learn more about sybase with you .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
do you familiar with linux
?
please hele on
https://www.experts-exchange.com/questions/29179700/yum-and-dnf-update-failed.html
tks
for personal testing , I use Centos but it seems from time to time it givers problem, what other linux is good if I tried to install oracle on linux ? or MysQL , MariaDB and sybase on linux ?
?
please hele on
https://www.experts-exchange.com/questions/29179700/yum-and-dnf-update-failed.html
tks
for personal testing , I use Centos but it seems from time to time it givers problem, what other linux is good if I tried to install oracle on linux ? or MysQL , MariaDB and sybase on linux ?
ASKER
any kind of documentation you can share so that I can learn how to optimize sybase tempdb, it seems very different from MS SQL !
Yep, the ASE documentation from the SAP website, and then the chapter from the white paper I gave before.
The only specific tempdb paper out there is mine and I'm in the process of bringing it up to date for ASE 16.0.x.
The only specific tempdb paper out there is mine and I'm in the process of bringing it up to date for ASE 16.0.x.
ASKER
Under-the-Hood-with-SAP-ASE-Always-O
this one ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tks.