ase build standards
TRANSCRIPT
-
8/4/2019 Ase Build Standards
1/86
1
Sybase ASE Installation Standards An overview of the current Standards for building Sybase
ASE servers
(Taken from Standards for Installing ASE Servers on
Solaris Hosts, Mich Talebzadeh)
Draft 1, December 2007
-
8/4/2019 Ase Build Standards
2/86
2
Adaptive Server Enterprise Adaptive Server Enterprise (ASE) is Sybase
Corporation's flagship enterprise-class relational
database management system product. ASE ispredominantly used on the Unix platform but isalso available for Windows.
-
8/4/2019 Ase Build Standards
3/86
3
-
8/4/2019 Ase Build Standards
4/86
4
What is covered Unix Server
Configuration
DBA environment
Temporary database
tempdb
tempdbRecommendation
tempdb Devices on
tmpfs
-
8/4/2019 Ase Build Standards
5/86
5
What is covered tempdb on file
systems
tempdb Configuration
Devices
Building Sybase ASE
ASE Port Numbering Default Character set
-
8/4/2019 Ase Build Standards
6/86
6
What is covered Location of log files
Memory and Cache
configuration
-
8/4/2019 Ase Build Standards
7/86
7
What is covered Memory
Configuration
Cache Configuration
Procedure Cache
Network Packet Size
Number of devices User Connections
-
8/4/2019 Ase Build Standards
8/86
8
What is covered Maximum number of
Engines
Setting the number oflocks
interfaces file
ASE Emergency bugfixes (EBF)
Time Zones
-
8/4/2019 Ase Build Standards
9/86
9
What is covered Environment Files
RUN_${DSQUERY}
file
References
-
8/4/2019 Ase Build Standards
10/86
10
Unix Server Configuration Host built to Engineering approved standard
Currently Solaris 2.8-2.9
Shared memory settings
-
8/4/2019 Ase Build Standards
11/86
11
Unix Server Configuration
(Shared memory settings )Force loading of semaphores
forceload: sys/semsys
forceload: sys/shmsys set semsys:seminfo_semmap=250
set semsys:seminfo_semmni=500
set semsys:seminfo_semmns=500
set semsys:seminfo_semmsl=500
set semsys:seminfo_semmnu=500 set semsys:seminfo_semume=100
-
8/4/2019 Ase Build Standards
12/86
12
Unix Server Configuration shared memory max
(32Bit) Set shmsys:shminfo_shmmax = (4096 x 1024 x 1024)-1
(64Bit) Set shmsys:shminfo_shmmax = (available physical memory
[in multiples of 1024MB] x 1024 x 1024)-1
For multiple servers on a host / high no of stripes
Set shmsys:shminfo_shmseg = 50
-
8/4/2019 Ase Build Standards
13/86
13
Unix Server Configuration Swap space
Multiple server hosts may require additional adjustments to
virtual address space File descriptor settings
Solaris 2.6 restriction = 1024
Solaris 2.8 would therefore need to be set, as default is 1024
Multiple IP addresses
For H/W failure issues
-
8/4/2019 Ase Build Standards
14/86
14
DBA Environment Sybase Account
sybase
group : dba
csh
Environment files
.login sets default server for host
server specific environment.ksh to set ASE variables
and paths
-
8/4/2019 Ase Build Standards
15/86
15
DBA Environment Sybase home
2GB/4GB filesystem, dependant on products installed
Mounted as /export/home/sybase
-
8/4/2019 Ase Build Standards
16/86
16
DBA Environment Sybase home directory structure
~//
~/dba/. ../bin : main scripts for maintenance/monitoring
../etc :
../log : additional log files (debug)
../tmp : work files for ../bin scripts
-
8/4/2019 Ase Build Standards
17/86
17
Sybase backup directory structure
Built on its own partition
/syblive/backup
..//dbdumps : dump files
..//txn_logs : log dumps
Requires reasonable size and capacity for future
growth
DBA Environment
-
8/4/2019 Ase Build Standards
18/86
18
Temporary database tempdb Mixed approach for tempdb
Configuring tempdb is crucial to your server.
Applications use it, worktables are created in tempdb,and your server uses it for a variety of other reasons.
Thus you need to get optimal performance from your
tempdb.
-
8/4/2019 Ase Build Standards
19/86
19
tempdb Recommendation Put your tempdb devices on tmpfs or any other
form of RAM disk if possible if you are using a
32-bit version of ASE (4GB max Sybase memory)and you have spare RAM.
If you are using 64-bit version of ASE and your
data disks are reasonably fast or you are using
SAN disks with large cache, then you are advisedto allocate the memory reserved for tmpfs to ASE
itself.
-
8/4/2019 Ase Build Standards
20/86
20
tempdb Recommendation Split the data and log in tempdb. This not only
yields better performance, but allows you to
measure the size of your segments in tempdb withbetter accuracy.
Create a private cache for tempdb with appropriate
buffer pools.
You may decide to split the tempdb caches into
tempdb_data_cache and tempdb_log_cache.
-
8/4/2019 Ase Build Standards
21/86
2
1
tempdb Devices on tmpfs tmpfs is a RAM disk file system type. Files that
are written are never put out to disk as long as
some RAM is available to keep them in memory. If there is a shortage of RAM, tmpfs pages are
stored in swap space.
In Solaris, the most common way to use this file
system is to mount /tmp, which is associated with
tmpfs by default.
-
8/4/2019 Ase Build Standards
22/86
22
tempdb Devices on tmpfs You can create ASE devices on /tmp with disk init command just as
you create any other file system device:
disk init name = "tempdb_tmpfs_data",physname = /tmp/tempdb_tmpfs_data.dat,
size = 100M,
dsync = false
This creates a 100MB device for tempdb on tmpfs. Adaptive Serveropens the device file with the dsync setting set to false. This isimportant because we have no interest in recovering the tempdbdatabase. Once the device is created, extend your tempdb database onthis device as usual.
-
8/4/2019 Ase Build Standards
23/86
23
tempdb Devices on tmpfs You also need to modify yourRUN_Serverfile to
issue a UNIX touch command against tempdb on
the tmpfs device before the call to the dataserver.This creates the file if it does not exist, as might
happen if the operating system had been rebooted.
Upon startup, the server can activate the device
and recreate tempdb. If the file entry was missing,the server would not be able to activate it and
tempdb would not be available.
-
8/4/2019 Ase Build Standards
24/86
24
tempdb Devices on tmpfs Example:
touch /tmp/tempdb_tmpfs_data.dat
touch /tmp/tempdb_tmpfs_log.dat#${SYBASE}/${SYBASE_ASE}/bin/dataserver \
-s \-d${HOME}//devices/
_master.dev \
-e${SYBASE}/${SYBASE_ASE}/install/.log \
-c${SYBASE}/${SYBASE_ASE}/.cfg \
-M${SYBASE}/$SYBASE_ASE}
-
8/4/2019 Ase Build Standards
25/86
25
tempdb on file systems Create a dedicated partition for tempdb. For
example/dev/vx/dsk/sybasedg/tempdb_devices
12386108 4098059 8164188 34%/tempdb_devices
Create a sub-directory under this partition with
server name and put the tempdb data and log files
theretempdb_devices/SYB_UDEQA2% ls
SYB_UDEQA2_tempdb_data1 SYB_UDEQA2_tempdb_log1
-
8/4/2019 Ase Build Standards
26/86
26
tempdb on file systems An example of creating a tempdb device
disk initname = "tempdb_data1",
physname ="/export/home/sybase/SYB_UDEQA2/devices/SYB_UDEQA2_tempdb_data1.dev",
size = "1000M",
dsync = false
Note the soft link/export/home/sybase/SYB_UDEQA2/devices% ls -l
SYB_UDEQA2_tempdb_data1.devlrwxrwxrwx 1 sybase dba 50 May 16 15:08
SYB_UDEQA2_tempdb_data1.dev ->/tempdb_devices/SYB_UDEQA2/SYB_UDEQA2_tempdb_data1
-
8/4/2019 Ase Build Standards
27/86
27
tempdb Configuration Remove tempdb from the master Device
It is a good idea to remove the first segment of tempdb
from the master device. The size of this segment is 2MB. The system tables for
tempdb are created there and this can create
performance bottlenecks.
Once you allocate a second device to tempdb, you candrop the master device from the default and log
segments.
-
8/4/2019 Ase Build Standards
28/86
28
Devices Naming conventions for sybase data devices
master : _master.dev
sybsystemprocs : _sysprocsdev.dev data devices : _ data.dev
log devices : _ log.dev
Permissions All devices permissioned for sybase:dba
-
8/4/2019 Ase Build Standards
29/86
29
Building Sybase ASE Decides on ASE page size. Cannot change it later
System databases
master : 100MB min
sybsystemprocs : 256MB min
tempdb :
No hard rules. Can expand later
log should be 25% of tempdb size
-
8/4/2019 Ase Build Standards
30/86
30
Building Sybase ASE Server Naming Conventions
All servers names must follow the standard naming convention. This is defined asfollows:
__
-
8/4/2019 Ase Build Standards
31/86
31
Building Sybase ASE is the geographical location where the server resides.
The following standard abbreviations should be used:
L
ocation NameLDN LondonTYO Tokyo
HK Hong Kong
NY New York
SYD Sydney
Other names can be added as needed. 2 or3 characters should besufficient.
-
8/4/2019 Ase Build Standards
32/86
32
Building Sybase ASE Application should reflect the application using the server
such as:
Application MeaningUDE UDE
HOSPITALITY Hospitality
REC Reconciliation
GED Global Equities
-
8/4/2019 Ase Build Standards
33/86
33
Building Sybase ASE Status should reflect the operational status of the server
for which one of the following codes should be used:
Status Meaning
PRODnm Production
DEVnm Development
DR Disaster Recovery
UATnm User Acceptance Testing
QAnm Quality Assurance
Where nm is the numerical suffix 01, 02, 03, 11, 12 etc.
-
8/4/2019 Ase Build Standards
34/86
34
ASE Port Numbering
ASE port numbering should begin at 5000 with
each subsequent ASE incrementing this value by100. For a group of related servers the port numbers
should be allocated sequentially, i.e. for the ASE
with port number5000 the Backup Server should
have 5001, the Monitor Server5002 and the XPserver5003. 5004-5099 should be used for any
other related servers.
-
8/4/2019 Ase Build Standards
35/86
35
Default Character set
Unless the application requires differently, the
default character set and sort order should be used.These are ISO Latin-1 and Dictionary Order Case
sensitive respectively.
-
8/4/2019 Ase Build Standards
36/86
36
Location of log files
All current Sybase product log files should always be left in the defaultdirectory $SYBASE/install and have name of the
format.log.
Two ways of cycling log files
At ASE startup in the RUN file:#
## Move the current errorlog and save it
#NOW="`date +%d_%b_%Y_%H:%M`"
OLDLOG="${LOGDIR}/${DSQUERY}.log_${NOW}"
mv ${ERRORLOG} ${OLDLOG}
gzip ${OLDLOG}
-
8/4/2019 Ase Build Standards
37/86
37
Location of log files
Recycle log every 24 hours
#
## Backing up your errorlog via cron once aday
#
NOW="`date +%d_%b_%Y_%H:%M`"
OLDLOG="${LOGDIR}/${DSQUERY}.log_${NOW}"
cp ${ERRORLOG} ${OLDLOG} && > ${ERRORLOG}
gzip ${OLDLOG}
-
8/4/2019 Ase Build Standards
38/86
38
Memory and Cache configuration
Memory allocated to Sybase ASE
The maximum memory parametermax memory should beset as high as possible taking into consideration anythingelse which is running on the same host. You have to beespecially aware of any other Adaptive Servers, Sybase
products or other database systems that may be running
on the box and taking memory. Note that having multipleengines configured for a server will not increase thememory overhead as the configured memory is sharedamong the engines.
-
8/4/2019 Ase Build Standards
39/86
39
Memory Configuration
Case study:
one host and one ASE, plus the backup server andreplication server.
If I am running a 32-bit Sybase then leaving 600-800MB
to the operating system plus the replication server and the
backup server should be sufficient. The max memory thatthe backup server will take is 144MB. You will be pretty
safe with giving the replication server around 200MB.
That leaves the rest to OS.
-
8/4/2019 Ase Build Standards
40/86
40
Memory Configuration
After taking the consideration for tempdb on tmpfs
(assuming that you will be creating tempdb on tmpfs) andif you have enough left over memory, you can give a max
memory of4000MB to ASE itself.
-
8/4/2019 Ase Build Standards
41/86
41
Memory Configuration
When you start up ASE, it will go and read the
configuration file .cfg. The totalmemory allocated during start up is the sum of memory
required for all the configuration needs of ASE as
specified in the configuration file. This value can be
obtained from the read-only configuration parametertotal
logicalmemory. The configuration parametermaxmemory must be greater than or equal to total logical
memory, otherwise ASE will fail to start.
-
8/4/2019 Ase Build Standards
42/86
42
Memory Configuration
Additionally, when you start up ASE, it will go and
check whether there is enough shared memory availableas specified by max memoryparameter. If the memory is
not there or the equivalent swap space is not there, ASE
will fail to start as well.
The important point to remember here is that checking
for the availability of memory does not mean that ASE
will go and grab it! ASE will use as much memory as
required.
-
8/4/2019 Ase Build Standards
43/86
43
Memory Configuration1> sp_configure memory2> go
Msg 17411, Level 16, State 1:Server 'SYB_UDEQA2', Procedure 'sp_configure', Line 214:Configuration option is not unique.
Parameter Name Default Memory Used Config Value Run Value UnitType------------------------------ ----------- ----------- ------------ ----------- --------------------
----------additional network memory 0 740 757760 757760 bytes
dynamicallocate max shared memory 0 0 0 0 switch
dynamiccompression memory size 0 76 0 0 memory pages(2k)
dynamicengine memory log size 0 2 0 0 memory pages(2k)
dynamic
heap memory per user 4096 0 4096 4096 bytesdynamic
lock shared memory 0 0 0 0 switchstatic
max memory 33792 4096000 2048000 2048000 memory pages(2k)dynamic
memory alignment boundary 2048 0 2048 2048 bytesstatic
memory per worker process 1024 48 1024 1024 bytesdynamic
messaging memory 400 0 400 400 memory pages(2k)dynamic
shared memory starting address 0 0 0 0 not applicable
statictotal logical memory 33792 3914828 1957414 1957411 memory pages(2k)
read-only
-
8/4/2019 Ase Build Standards
44/86
44
Memory Configuration
Note that I have highlighted both the max memory and
total physical memory. Total physical memory runvalue is the one telling you how much memory ASE
is currently using. For example in this case ASE has
4GB of max memory but only using 1957416/512 =
3823MB in practice. Let us go through the aboveand make sense out of some of the parameters of
interest.
-
8/4/2019 Ase Build Standards
45/86
45
Memory Configuration
additional network memory
This is the memory required for networks. I will cover it later allocate max shared memory
During start up ASE allocates memory based on the value oftotal
logical memory. However, if the configuration parameter, allocate
max shared memory has been set, then the memory allocated will be
based on the value of max memory. Unless you are competing withother resources, leave this parameter as default.
-
8/4/2019 Ase Build Standards
46/86
46
Memory Configuration
lock shared memory Lock shared memory disallows swapping of Adaptive Server pages
to disk and allows the operating system kernel to avoid the server'sinternal page locking code. This can reduce disk reads, which areexpensive.
It is not a sort of parameter that you want to play around with.Always ensure (through liaison with UNIX SA) that there is enoughRAM to cover for yourmax memory specification at all times.
memory per worker process memory per worker process specifies the amount of memory used byworker processes. Each worker process requires memory formessaging during query processing. For most needs the default valueis perfectly adequate.
-
8/4/2019 Ase Build Standards
47/86
47
Cache Configuration
After taking any memory needed by the Sybase kernel therest is allocated to the default data cache and the procedure
cache. Default data cache
When ASE is created, it only has one cache, the default data cache.The raw default data cache only has default buffer pools. The defaultbuffer pool is the ASEs page size. So if you created ASE with 2Kpage size, then the default data cache will be made of2Kbuffer
pools. However, you can add additional buffer pools as we willconsider shortly. In ASE, any table or index, which does not have abinding or is not within a database bound to a named cache, will usethe default data cache. You cannot rename or delete the default datacache.
-
8/4/2019 Ase Build Standards
48/86
48
Cache Configuration
However, you can add additional buffer pools as we
will consider shortly. In ASE, any table or index,which does not have a binding or is not within a
database bound to a named cache, will use the
default data cache. You cannot rename or delete the
default data cache.
-
8/4/2019 Ase Build Standards
49/86
49
Cache Configuration
You can adjust the size of default data cache
dynamically. For example to allocate 900MB todefault data cache you can use the following
command:
sp_cacheconfig 'default data cache' ,
'900Mgo
(return status = 0)
-
8/4/2019 Ase Build Standards
50/86
50
Cache Configuration
Named Cache
ASEs logical memory manager is capable of maintainingmultiple caches. These user-defined caches are referred
to as named cache. When you create named caches, you
are effectively slicing up the shared memory allocated to
ASE into a number of separate data caches. Each named
cache can be reserved for specific databases or database
objects such as tables and indexes. In ASE, this process is
called binding databases or objects to cache.
-
8/4/2019 Ase Build Standards
51/86
51
Cache Configuration
ASE binding enables the following:
Allows frequently accessed objects to be kept in memory by
preventing other objects data pages to use the reserved pages
Minimizes the effect of one application from another
Stops the memory hungry applications from flushing out the useful
data from the cache
Can be configured with different cache pools Gives DBA a degree of control on using the available memory
efficiently
-
8/4/2019 Ase Build Standards
52/86
52
Cache Configuration
ASE binding enables the following:
Both the default data cache and named caches can beconfigured dynamically. This reduces or minimizes the
downtime of the server. Additionally the default data
cache and named cache can also be split into multiple
cachlets to reduce the spinlock contention.
-
8/4/2019 Ase Build Standards
53/86
53
Cache Configuration
An example of a named cache would be a private
cache for dbccdb (database consistency check)database. You are advised to create a private cache
for dbcc operations and bind dbccdb database to this
cache. This is because typically a dbcc operation
has to read data for all tables sequentially. This means caching as much data in a single IO as
possible and obviously all the data pages.
-
8/4/2019 Ase Build Standards
54/86
54
Cache Configuration
Thus, the larger block size of 16Kor more is ideal
for dbcc operations and hence you should creategood size large buffer pools in dbccdb cache.
You also do not want dbcc operation to flush outyour valuable data out of memory. Having aproperly configured named cache for this purposemakes dbcc operation more efficient, and stopslarge table scans potentially flushing out useful datafrom the default data cache.
-
8/4/2019 Ase Build Standards
55/86
55
Cache Configuration
Each named cache can be further tuned including:
Size of the cache Multiple buffer pools.
Location of the wash marker in the MRU/LRU chain
The type of cache, log or mixed
The cache replacement policy, strict or relaxed
The number of partitions (cachlets) in the cache
-
8/4/2019 Ase Build Standards
56/86
56
Cache Configuration
Large IO and Multiple Buffer Pools
When more than one buffer pool is available in a cache,
the ASE optimizer determines the optimal page size foreach query and uses the appropriate buffer pool.
ASE allows you to configure up to four larger page sizes.
With 8Kpage size, buffer pools of8K, 16K, 32Kand
64Kcan be configured. Example below shows this:
-
8/4/2019 Ase Build Standards
57/86
57
Cache Configuration
1> sp_cacheconfig 'default data cache'
2> go
Cache Name Status Type Config Value Run Value
------------------ ------ ------- ------------ ------------
default data cache Active Default 1625.00 Mb 1625.00 Mb
------------ ------------Total 1625.00 Mb 1625.00 Mb
==========================================================================
Cache: default data cache, Status: Active, Type: Default
Config Size: 1625.00 Mb, Run Size: 1625.00 Mb
Config Replacement: strict LRU, Run Replacement: strict LRU
Config Partition: 4, Run Partition: 4
IO Size Wash Size Config Size Run Size APF Percent-------- --------- ------------ ------------ -----------
16 Kb 5120 Kb 25.00 Mb 25.00 Mb 10
32 Kb 20480 Kb 100.00 Mb 100.00 Mb 10
64 Kb 61440 Kb 300.00 Mb 300.00 Mb 10
8 Kb 61440 Kb 0.00 Mb 1200.00 Mb 10
(return status = 0)
-
8/4/2019 Ase Build Standards
58/86
58
Cache Configuration
Table below summarizes the logical page size and
the corresponding Buffer pool sizes.
Logical page size/KB Buffer pool sizes/KB
2 2, 4, 8, 16
4 4, 8, 16, 32
8 8, 16, 32,64
16 16, 32, 64, 128
-
8/4/2019 Ase Build Standards
59/86
59
Cache Configuration
1. Example of creating a user defined cache and the associated pools
1> sp_cacheconfig tempdb_log_cache,'500M','logonly','relaxed', 'cache_partition=1'
2> go
Since this is a log cache, it should be mainly made up of4Kbuffer pools andsome 16Kbuffer pools as well:
1> sp_poolconfig "tempdb_log_cache", "370M", "4K","2K"
2> go1> sp_poolconfig "tempdb_log_cache", "125M", "16K",
"2K"
2> go
-
8/4/2019 Ase Build Standards
60/86
60
Cache Configuration
1. Display information about this cache
1> sp_cacheconfig tempdb_log_cache2> goCache Name Status Type Config Value Run Value------------------------------ --------- -------- ------------ ------------
tempdb_log_cache Active Log Only 500.00 Mb 500.00 Mb------------ ------------
Total 500.00 Mb 500.00 Mb==========================================================================Cache: tempdb_log_cache, Status: Active, Type: Log Only
Config Size: 500.00 Mb, Run Size: 500.00 MbConfig Replacement: relaxedLRU, Run Replacement: relaxedLRUConfig Partition: 1, Run Partition: 1
IO Size Wash Size Config Size Run Size APF Percent
-------- --------- ------------ ------------ -----------2 Kb 1024 Kb 0.00 Mb 5.00 Mb 104 Kb 61440 Kb 370.00 Mb 370.00 Mb 10
16 Kb 25600 Kb 125.00 Mb 125.00 Mb 10
-
8/4/2019 Ase Build Standards
61/86
61
Procedure Cache
ASE uses procedure cache to hold thefollowing in the memory:
Cached stored procedure plans, triggers andcursors
Object permissions and column statistics
Statement Cache (ad hoc SQL)
Short term memory needs
Others
-
8/4/2019 Ase Build Standards
62/86
62
Procedure Cache
This makes the size ofprocedure cacheimportant. Also you may choose to sizeprocedure cache larger in DEV compared toproduction. You specify the size ofprocedurecache in 2Kpages. If ASE finds a copy of aprocedure already in the cache, it does not needto read it from the disk. If you create a procedure
WITHRECOMPILEoption, it will add anotherplan anytime it is executed that could be farfrom ideal.
-
8/4/2019 Ase Build Standards
63/86
63
Procedure Cache
Since the optimum value for procedure cachesize differs from application to application,resetting it may improve Adaptive Server'sperformance. For example, if you run manydifferent procedures or ad hoc queries, yourapplication uses the procedure cache moreheavily, so you may want to increase this value.
As a rule of sum aprocedure cache size between150-300MB should be sufficient for most ofyour applications.
-
8/4/2019 Ase Build Standards
64/86
64
Procedure Cache
ASE uses StatementCache to store the text of ad
hoc SQL statements. ASE compares a newly
received ad hoc SQL statement to cached SQLstatements and, if a match is found, uses the plan
cached from the initial execution. The statement
cache is a server-wide resource, which allocates
and consumes memory from the procedurecache. However, the ad hoc SQL is used in ASE
is far from perfect.
-
8/4/2019 Ase Build Standards
65/86
65
Procedure Cache
Both Oracle and ASE use a hashing mechanism
to identify the ad-hoc SQL. In ASE the hash is a
combination of literal SQL text, login ID andsession settings (results of "set" commands,
generally). Unfortunately this means the hash is
not so useful for determining highly repeated
SQL.
-
8/4/2019 Ase Build Standards
66/86
66
Network Packet Size
ASE 15 comes with a default network packet size of2048 bytes. Inearlier versions of ASE this used be 512 bytes.
For most OLTP type queries the default packet size is more thanadequate. However DSS type applications normally include largebatches of SQL and tend to return larger result sets. Operations likeBCP and text processing like XML can benefit from larger packetsizes. In ASE this is specified by the configuration parametermaxnetwork packet size.
When ASE is started it goes and works out the total memoryrequired for all users that can log in to ASE. ASE uses defaultnetwork packet size * 3 for allocation of memory to each user.Additionally, worker processes take the same amount of memory.
-
8/4/2019 Ase Build Standards
67/86
67
Network Packet Size
For example to work out total memory allocated for network packets youcalculate:
(number of user connections + number of worker processes) * 3 * defaultnetwork packet size
If you set the default network packet size to 4096 bytes, and you have 100user connections and 20 worker processes, the amount of network memoryrequired is:
(100 + 20) * 3 * 4096 = 1474560 bytes
Larger packet sizes will require additional network memory. Assuming thatyou set your maximum packet size to 8192 bytes and you will have fivesimultaneous users using these large packets then the amount of additionalnetwork memory with customary 2% overhead would be
(5 * 3 * 8192) x 1.02 ~ 125337
You need to round up this value to the next highest multiple of2048. In thiscase it will be 14,336 bytes. Without increasing additional networkmemory, you will not be able to use larger packet sizes.
-
8/4/2019 Ase Build Standards
68/86
68
Number of devices
As a rule of thumb you can set this one to 30 1>sp_configure devices
2> go
Parameter Name Default Memory Used Config Value
Run Value
------------------------------ ----------- ----------- ------------
-----------
number of devices 10 #17 30
30
-
8/4/2019 Ase Build Standards
69/86
69
User Connections
A value of 100 should be sufficient for most non production
servers
1> sp_configure 'user connections'
2> go
Parameter Name Default
Memory Used Config Value Run Value
------------------------------ --------
--- ----------- ------------ -----------
number of user connections
25 17261 100 100
-
8/4/2019 Ase Build Standards
70/86
70
Maximum number of engines
Again there are no fast rules about this. You can
setup the maximum number of sybase engines to be
the same as the number of CPUs assuming that thisis a pure Data server host. On multiple Sybase
servers environment you can do the same. The idea
that you will starve the OS does not seem to stand
up.
-
8/4/2019 Ase Build Standards
71/86
71
Setting the number of locks
The default value of5,000 is not going to be enough. A setting of50,000 should be sufficient for dev environments. For high activityproduction servers a value of400-500Kmay be more appropriate.
1> sp_configure 'number of locks' 2> go
Parameter Name DefaultMemory Used Config Value Run Value
------------------------------ ----------- ----------- ------------ -----------
number of locks 500011719 50000 50000
-
8/4/2019 Ase Build Standards
72/86
72
interfaces file
On hosts with multiple Sybase servers and versions
it is important to maintain one interface file for all!
This file should reside in ~sybase directory (asopposed to $SYBASE). Be aware that the default
interfaces file presented by dsedit will need to be
changed when adding/updating the interfaces file
-
8/4/2019 Ase Build Standards
73/86
73
ASE Emergency bug fixes (EBF)
1. It is important to be aware of newly released ASE
Emergency Bug Fixes or EBFs. Before applying
EBFs to production, try it on the dev servers for agood week or two to assess the impact or side
effects. Running batch jobs with a new EBF is a
particularly good way of testing the code and
timings.
-
8/4/2019 Ase Build Standards
74/86
74
Time Zones
It is becoming increasingly necessary to have
multiple Sybase servers running on the same host
with different time zones. Thus, it will benecessary to have an environment file for each
server to ensure the correct settings (see below).
A list of available timezones is provided in the
directory /usr/share/lib/zoneinfo as follows:
-
8/4/2019 Ase Build Standards
75/86
75
Time Zones
ls /usr/share/lib/zoneinfo
America Cuba GB GMT+13 GMT+9 GMT-4Hongkong MET PRC Turkey posixrules
Asia EET GB-Eire GMT+2 GMT-0 GMT-5
Iceland MST PST8PDT UCT src Australia EST GMT GMT+3 GMT-1 GMT-6Iran MST7MDT Pacific US
Brazil EST5EDT GMT+0 GMT+4 GMT-10 GMT-7Israel Mexico Poland UTC
CET Egypt GMT+1 GMT+5 GMT-11 GMT-8Jamaica Mideast Portugal Universal
CST6CDT Eire GMT+10 GMT+6 GMT-12 GMT-9Japan NZ ROC W-SU
Canada Etc GMT+11 GMT+7 GMT-2Greenwich Kwajalein NZ-CHAT ROK WET Chile Factory GMT+12 GMT+8 GMT-3 HST
Libya Navajo Singapore Zulu
-
8/4/2019 Ase Build Standards
76/86
76
Environment Files
ASE by default provides two environment files in
$SYBASE. Depending on the shell that you are
using you can source eitherSYBASE.sh orSYBASE.csh.
-
8/4/2019 Ase Build Standards
77/86
77
Environment Files
.profile
The .profile script will be executed first. As an example, it will have the following entries
(lon_epg_sql_dev1)$ more .profile export ENV=$HOME/.kshrc
#
export PATH=$PATH:/usr/local/bin:$HOME/dba/bin:$HOME # echo ""
echo "+============================================================+" echo "| |"
echo "| Enter the following commands: |" echo "| |"
echo "| SYB_LDN_EPG_DEV01 - 12.5.4 (default) |" echo "| SYB_LDN_EPG_UAT01 - 12.5.4 |"
echo "| SYB_LDN_EPG_DEV02 - 12.5.4 64 bit version |" echo "| |"
echo "+============================================================+" echo ""
stty erase set -o vi
HOSTNAME=`hostname` ulimit -Sn 10 24
export EDITOR=vi . ~sybase/SYB_LDN_EP G_DEV01/dba/bin/environment.ksh
-
8/4/2019 Ase Build Standards
78/86
78
Environment Files
It will in turn allow you to invoke any of the above server settings by havingthe correct entries in the .kshrc file
(SYB_LDN_EPG_DEV01)$ more .kshrc export TERM=vt100
alias isql='isql -w132'
alias sa='isql -w0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000 -Usa -P$(~/dba/bin/get_password.ksh $DSQUERY sa)
'
alias SYB_LDN_EPG_UAT01='. ~sybase/
SYB_LDN_EPG_UAT01/dba/bin/environment.ksh' alias SYB_LDN_EPG_DEV01='.
~sybase/SYB_LDN_EPG_DEV01/dba/bin/environment.ksh'
alias SYB_LDN_EPG_DEV02='. ~sybase/SYB_LDN_EPG_DEV02/dba/bin/environment.ksh'
-
8/4/2019 Ase Build Standards
79/86
79
Environment Files
environment.ksh
A typical environment file will have the following entries
#!/bin/ksh
# # Program: environment.ksh
# Type: Server specific
# Description: This script will be modified on each site to contain thecorrect
# values to be held in environment variables. This script is
# called from every other script to ensure that the correct
# environment has been defined for their execution.
# Author: Mich Talebzadeh
# Version: 3.0
# # Modified: #
-
8/4/2019 Ase Build Standards
80/86
80
Environment Files
environment.ksh
###################################
# Modify the following lines only
export TZ=GB
export SYBASE=~sybase/SYB_UTS1
export SYSTEM=UDE
export DSQUERY=SYB_UTS1
export REFERENCE_SERVER=SYB_UTS1
export BSQUERY=${DSQUERY}_BACKUP
export DUMPDIR=/localdisk/refresh_db_temp_dumps export AUDITDIR=${DUMPDIR}/audit
##ulimit -Sn 2048
###################################
-
8/4/2019 Ase Build Standards
81/86
81
Environment Files
environment.ksh
###################################
# 12.5 Stuff
export SYBASE_ASE=ASE-12_5 export SYBASE_OCS=OCS-12_5
export SYBASE_FTS=FTS-12_5
export SYBASE_SYSAM=SYSAM-1_0
exportLD_LIBRARY_PATH=$SYBASE/$SYBASE_ASE/lib:$SYBASE/$SYBAS
E_OCS/lib:$SYBASE/$SYBASE_OCS/lib3
p exportLM_LICENSE_FILE=$SYBASE/$SYBASE_SYSAM/licenses/license.dat
###################################
-
8/4/2019 Ase Build Standards
82/86
82
Environment Files
###################################
GENERIC_ROOT=~/dba
SPECIFIC_ROOT=~/$DSQUERY/dba
export GEN_APPSDIR=$GENERIC_ROOT/bin
export GEN_LO
GDIR
=$GENER
IC_ROO
T/log export GEN_ETCDIR=$GENERIC_ROOT/etc
export GEN_ENVDIR=$GENERIC_ROOT/env
export GEN_ADMINDIR=$GENERIC_ROOT/admin
export PASSFILE=$GENERIC_ROOT/env/.syb_accounts
export ADMINDIR=$SPECIFIC_ROOT/admin
export TMPDIR=$SPECIFIC_ROOT/tmp
##export LO
GDIR
=$SPECIFIC_ROO
T/log export LOGDIR=/var/tmp
export ETCDIR=$SPECIFIC_ROOT/etc
export HTMLDIR=$SPECIFIC_ROOT/html
export APPSDIR=$SPECIFIC_ROOT/bin
-
8/4/2019 Ase Build Standards
83/86
83
Environment Files
for ELOG in $SYBASE/$SYBASE_ASE/install/$DSQUERY.log $SYBASE/$SYBASE_REP/install/$DSQUERY.log$SYBASE/install/$DSQUERY.log
do
if [ -f ${ELOG} ] then
export ERRORLOG=${ELOG} fi
done for BLOG in $SYBASE/$SYBASE_ASE/install/$BSQUERY.log $SYBASE/install/$BSQUERY.log
do if [ -f ${BLOG} ]
then export BACKUPLOG=${BLOG}
fi done
PATH=.:$PATH:/usr/bin:/usr/sbin:$APPSDIR:$GEN_APPSDIR:/opt/misc/bin for DIR_CHECK in ${SYBASE}/${SYBASE_REP}/bin ${SYBASE}/${SYBASE_REP}/install ${SYBASE}/${SYBASE_ASE}/bin
${SYBASE}/${SYBASE_ASE}/install ${SYBASE}/${SYBASE_OCS}/bin ${SYBASE}/bin ${SYBASE}/install
do
if [ -d ${DIR_CHECK} ] then PATH=$PATH:${DIR_CHECK}
fi done
-
8/4/2019 Ase Build Standards
84/86
84
Environment Files
export TRANDUMPDIR=$DUMPDIR/tran
PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/u
sr/bin/X11:/usr/X11R6/bin:/usr/sbin:$SYBASE/$SYBA
SE_ASE/bin:$SYBASE/$SYBASE_OCS/bin:$SYBAS
E/$SYBASE_ASE/install:$SYBASE/ASEP/bin/:$APPS
DIR:$GEN_APPSDIR
##export SUPPORT=sybase@`hostname`
export [email protected]
export PS1=`hostname`:'($DSQUERY)$ '
-
8/4/2019 Ase Build Standards
85/86
85
RUN_${DSQUERY} file
To ensure that the Sybase Server starts in the correct timezone etc, make sure that you modify the RUN_server file toexecute the relevant environment.ksh file as shown in the following example:
#!/bin/ksh
#
# Define Sybase environment variables
#
. /export/home/sybase/SYB_UDEQA2/dba/bin/environment.ksh #
# Adaptive Server name: SYB_UDEQA2
# ASE page size (KB): 2k
# Master device path: /export/home/sybase/SYB_UDEQA2/devices/SYB_UDEQA2_master.dev
# Error log path: /export/home/sybase/ASE/1254/ASE-12_5/install/SYB_UDEQA2.log
# Configuration file path: /export/home/sybase/ASE/1254/ASE-12_5/SYB_UDEQA2.cfg
# Directory for shared memory files: /export/home/sybase/ASE/1254/ASE-12_5
#
$SYBASE/$SYBASE_ASE/bin/dataserver \
-sSYB_UDEQA2 \
-d/export/home/sybase/SYB_UDEQA2/devices/SYB_UDEQA2_master.dev \
-e$SYBASE/$SYBASE_ASE/install/SYB_UDEQA2.log \
-c$SYBASE/$SYBASE_ASE/SYB_UDEQA2.cfg \
-M$SYBASE/$SYBASE_ASE \
-
8/4/2019 Ase Build Standards
86/86
References
1. Sybooks. Online ASE manuals from:
http://infocenter.sybase.com/help/index.jsp
2. Configuring and Setting Up tempdbs forOptimal Performance
Mich Talebzadeh and Ryan Thomas Putnam,
International Sybase Group Publication, Q4, 2004,