Sybase ase installation standards
Download
1 / 86

Sybase ASE Installation Standards - PowerPoint PPT Presentation


  • 840 Views
  • Updated On :

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. Adaptive Server Enterprise.

Related searches for Sybase ASE Installation Standards

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Sybase ASE Installation Standards' - Ava


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sybase ase installation standards l.jpg
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


Adaptive server enterprise l.jpg
Adaptive Server Enterprise

  • Adaptive Server Enterprise (ASE) is Sybase Corporation's flagship enterprise-class relational database management system product. ASE is predominantly used on the Unix platform but is also available for Windows.


What is covered l.jpg
What is covered

  • Unix Server Configuration

  • DBA environment

  • Temporary database tempdb

  • tempdb Recommendation

  • tempdb Devices on tmpfs


What is covered5 l.jpg
What is covered

  • tempdb on file systems

  • tempdb Configuration

  • Devices

  • Building Sybase ASE

  • ASE Port Numbering

  • Default Character set


What is covered6 l.jpg
What is covered

  • Location of log files

  • Memory and Cache configuration


What is covered7 l.jpg
What is covered

  • Memory Configuration

  • Cache Configuration

  • Procedure Cache

  • Network Packet Size

  • Number of devices

  • User Connections


What is covered8 l.jpg
What is covered

  • Maximum number of Engines

  • Setting the number of locks

  • interfaces file

  • ASE Emergency bug fixes (EBF)

  • Time Zones


What is covered9 l.jpg
What is covered

  • Environment Files

  • RUN_${DSQUERY} file

  • References


Unix server configuration l.jpg
Unix Server Configuration

  • Host built to Engineering approved standard

    • Currently Solaris 2.8-2.9

  • Shared memory settings


Unix server configuration shared memory settings l.jpg
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


Unix server configuration12 l.jpg
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


Unix server configuration13 l.jpg
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


Dba environment l.jpg
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


Dba environment15 l.jpg
DBA Environment

  • Sybase home

    • 2GB/4GB filesystem, dependant on products installed

    • Mounted as /export/home/sybase


Dba environment16 l.jpg
DBA Environment

  • Sybase home directory structure

    • ~/<product>/<version>

    • ~/dba/….

      • ../bin : main scripts for maintenance/monitoring

      • ../etc :

      • ../log : additional log files (debug)

      • ../tmp : work files for ../bin scripts


Dba environment17 l.jpg
DBA Environment

  • Sybase backup directory structure

    • Built on its own partition

      • /syblive/backup…

        • ../<server-name>/dbdumps : dump files

        • ../<server-name>/txn_logs : log dumps

    • Requires reasonable size and capacity for future growth


Temporary database tempdb l.jpg
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.


Tempdb recommendation l.jpg
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 advised to allocate the memory reserved for tmpfs to ASE itself.


Tempdb recommendation20 l.jpg
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 with better 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.


Tempdb devices on tmpfs l.jpg
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.


Tempdb devices on tmpfs22 l.jpg
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 Server opens the device file with the dsync setting set to false. This is important because we have no interest in recovering the tempdb database. Once the device is created, extend your tempdb database on this device as usual.


Tempdb devices on tmpfs23 l.jpg
tempdb Devices on tmpfs

  • You also need to modify your RUN_Server file to issue a UNIX touchcommand 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.


Tempdb devices on tmpfs24 l.jpg
tempdb Devices on tmpfs

  • Example:

    touch /tmp/tempdb_tmpfs_data.dat

    touch /tmp/tempdb_tmpfs_log.dat

    #

    ${SYBASE}/${SYBASE_ASE}/bin/dataserver \

    -s<SERVER_NAME> \

    -d${HOME}/<SERVER_NAME>/devices/

    <SERVER_NAME>_master.dev \

    -e${SYBASE}/${SYBASE_ASE}/install/ <SERVER_NAME>.log \

    -c${SYBASE}/${SYBASE_ASE}/<SERVER_NAME>.cfg \

    -M${SYBASE}/$SYBASE_ASE}


Tempdb on file systems l.jpg
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 there

    tempdb_devices/SYB_UDEQA2% ls

    SYB_UDEQA2_tempdb_data1 SYB_UDEQA2_tempdb_log1


Tempdb on file systems26 l.jpg
tempdb on file systems

  • An example of creating a tempdb device

    disk init

    name = "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.dev

    lrwxrwxrwx 1 sybase dba 50 May 16 15:08 SYB_UDEQA2_tempdb_data1.dev -> /tempdb_devices/SYB_UDEQA2/SYB_UDEQA2_tempdb_data1


Tempdb configuration l.jpg
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 can drop the master device from the default and log segments.


Devices l.jpg
Devices

  • Naming conventions for sybase data devices

    • master : <server-name>_master.dev

    • sybsystemprocs : <server-name>_sysprocsdev.dev

    • data devices : <server-name>_ data<nn>.dev

    • log devices : <server-name>_ log<nn>.dev

  • Permissions

    • All devices permissioned for sybase:dba


Building sybase ase l.jpg
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


Building sybase ase30 l.jpg
Building Sybase ASE

  • Server Naming Conventions

    • All servers’ names must follow the standard naming convention. This is defined as follows:

    • <PRODUCT>_<LOCATION>_<APPLICATION_<STATUS>

    • <Product> should reflect the Sybase product for which one of the following standard abbreviations should be used:

      Product Sybase product

      SYB ASE

      REP Replication Server

    • XP server is a special case. The XP server will be called <SERVER_NAME>_XP with SERVER_NAME in uppercase.


Building sybase ase31 l.jpg
Building Sybase ASE

  • <LOCATION> is the geographical location where the server resides. The following standard abbreviations should be used:

    Location Name

    LDN London

    TYO Tokyo

    HK Hong Kong

    NY New York

    SYD Sydney

    Other names can be added as needed. 2 or 3 characters should be sufficient.


Building sybase ase32 l.jpg
Building Sybase ASE

  • Application should reflect the application using the server such as:

    Application Meaning

    UDE UDE

    HOSPITALITY Hospitality

    REC Reconciliation

    GED Global Equities


Building sybase ase33 l.jpg
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.


Ase port numbering l.jpg
ASE Port Numbering

  • ASE port numbering should begin at 5000 with each subsequent ASE incrementing this value by 100. For a group of related servers the port numbers should be allocated sequentially, i.e. for the ASE with port number 5000 the Backup Server should have 5001, the Monitor Server 5002 and the XP server 5003. 5004-5099 should be used for any other related servers.


Default character set l.jpg
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.


Location of log files l.jpg
Location of log files

  • All current Sybase product log files should always be left in the default directory $SYBASE/install and have name of the format<SERVER_NAME>.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}


Location of log files37 l.jpg
Location of log files

  • Recycle log every 24 hours

    #

    ## Backing up your errorlog via cron once a day

    #

    NOW="`date +%d_%b_%Y_%H:%M`"

    OLDLOG="${LOGDIR}/${DSQUERY}.log_${NOW}"

    cp ${ERRORLOG} ${OLDLOG} && > ${ERRORLOG}

    gzip ${OLDLOG}


Memory and cache configuration l.jpg
Memory and Cache configuration

  • Memory allocated to Sybase ASE

    • The maximum memory parameter max memory should be set as high as possible taking into consideration anything else which is running on the same host. You have to be especially 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 multiple engines configured for a server will not increase the memory overhead as the configured memory is shared among the engines.


Memory configuration l.jpg
Memory Configuration

  • Case study:

    • one host and one ASE, plus the backup server and replication 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 that the backup server will take is 144MB. You will be pretty safe with giving the replication server around 200MB. That leaves the rest to OS.


Memory configuration40 l.jpg
Memory Configuration

  • After taking the consideration for tempdb on tmpfs (assuming that you will be creating tempdb on tmpfs) and if you have enough left over memory, you can give a max memory of 4000MB to ASE itself.


Memory configuration41 l.jpg
Memory Configuration

  • When you start up ASE, it will go and read the configuration file <SERVER_NAME>.cfg. The total memory 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 parameter total logical memory. The configuration parameter max memory must be greater than or equal to total logical memory, otherwise ASE will fail to start.


Memory configuration42 l.jpg
Memory Configuration

  • Additionally, when you start up ASE, it will go and check whether there is enough shared memory available as specified by max memory parameter. 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.


Memory configuration43 l.jpg
Memory Configuration

1> sp_configure memory

2> 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 Unit Type

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

additional network memory 0 740 757760 757760 bytes dynamic

allocate max shared memory 0 0 0 0 switch dynamic

compression memory size 0 76 0 0 memory pages(2k) dynamic

engine memory log size 0 2 0 0 memory pages(2k) dynamic

heap memory per user 4096 0 4096 4096 bytes dynamic

lock shared memory 0 0 0 0 switch static

max memory 33792 4096000 2048000 2048000 memory pages(2k) dynamic

memory alignment boundary 2048 0 2048 2048 bytes static

memory per worker process 1024 48 1024 1024 bytes dynamic

messaging memory 400 0 400 400 memory pages(2k) dynamic

shared memory starting address 0 0 0 0 not applicable static

total logical memory 33792 3914828 1957414 1957411 memory pages(2k) read-only

total physical memory 0 3914832 0 1957416 memory pages(2k) read-only

(1 row affected)

An additional 181178 K bytes of memory is available for reconfiguration. This is the difference between 'max memory' and 'total logical memory'.


Memory configuration44 l.jpg
Memory Configuration

Note that I have highlighted both the max memory and total physical memory. Total physical memory run value 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 above and make sense out of some of the parameters of interest.


Memory configuration45 l.jpg
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 of total 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 with other resources, leave this parameter as default.


Memory configuration46 l.jpg
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's internal page locking code. This can reduce disk reads, which are expensive.

    • It is not a sort of parameter that you want to play around with. Always ensure (through liaison with UNIX SA) that there is enough RAM to cover for your max memory specification at all times.

  • memory per worker process

    • memory per worker process specifies the amount of memory used by worker processes. Each worker process requires memory for messaging during query processing. For most needs the default value is perfectly adequate.


Cache configuration l.jpg
Cache Configuration

  • After taking any memory needed by the Sybase kernel the rest 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 default buffer pool is the ASE’s page size. So if you created ASE with 2K page size, then the default data cache will be made of 2K buffer pools. 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.


Cache configuration48 l.jpg
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.


Cache configuration49 l.jpg
Cache Configuration

  • You can adjust the size of default data cache dynamically. For example to allocate 900MB to default data cache you can use the following command:

    sp_cacheconfig 'default data cache' , '900M‘

    go

    (return status = 0)


Cache configuration50 l.jpg
Cache Configuration

  • Named Cache

    • ASE’s logical memory manager is capable of maintaining multiple 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”.


Cache configuration51 l.jpg
Cache Configuration

  • ASE binding enables the following:

    • Allows frequently accessed objects to be kept in memory by preventing other object’s 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


Cache configuration52 l.jpg
Cache Configuration

  • ASE binding enables the following:

    • Both the default data cache and named caches can be configured 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.


Cache configuration53 l.jpg
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.


Cache configuration54 l.jpg
Cache Configuration

  • Thus, the larger block size of 16K or more is ideal for dbcc operations and hence you should create good size large buffer pools in dbccdb cache.

  • You also do not want dbcc operation to flush out your valuable data out of memory. Having a properly configured named cache for this purpose makes dbcc operation more efficient, and stops large table scans potentially flushing out useful data from the default data cache.


Cache configuration55 l.jpg
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


Cache configuration56 l.jpg
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 for each query and uses the appropriate buffer pool.

    • ASE allows you to configure up to four larger page sizes. With 8K page size, buffer pools of 8K, 16K, 32K and 64K can be configured. Example below shows this:


Cache configuration57 l.jpg
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)


Cache configuration58 l.jpg
Cache Configuration

  • Table below summarizes the logical page size and the corresponding Buffer pool sizes.


Cache configuration59 l.jpg
Cache Configuration

  • 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 of 4K buffer pools and some 16K buffer pools as well:

    1> sp_poolconfig "tempdb_log_cache", "370M", "4K", "2K"

    2> go

    1> sp_poolconfig "tempdb_log_cache", "125M", "16K", "2K"

    2> go


Cache configuration60 l.jpg
Cache Configuration

  • Display information about this cache

    1> sp_cacheconfig tempdb_log_cache

    2> go

    Cache 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 Mb

    Config Replacement: relaxed LRU, Run Replacement: relaxed LRU

    Config 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 10

    4 Kb 61440 Kb 370.00 Mb 370.00 Mb 10

    16 Kb 25600 Kb 125.00 Mb 125.00 Mb 10


Procedure cache l.jpg
Procedure Cache

  • ASE uses procedure cache to hold the following in the memory:

    • Cached stored procedure plans, triggers and cursors

    • Object permissions and column statistics

    • Statement Cache (ad hoc SQL)

    • Short term memory needs

    • Others


Procedure cache62 l.jpg
Procedure Cache

  • This makes the size of procedure cache important. Also you may choose to size procedure cache larger in DEV compared to production. You specify the size of procedure cachein 2K pages. If ASE finds a copy of a procedure already in the cache, it does not need to read it from the disk. If you create a procedure WITH RECOMPILE option, it will add another plan anytime it is executed that could be far from ideal.


Procedure cache63 l.jpg
Procedure Cache

  • Since the optimum value for procedure cache size differs from application to application, resetting it may improve Adaptive Server's performance. For example, if you run many different procedures or ad hoc queries, your application uses the procedure cache more heavily, so you may want to increase this value. As a rule of sum a procedure cache size between 150-300MB should be sufficient for most of your applications.


Procedure cache64 l.jpg
Procedure Cache

  • ASE uses Statement Cache to store the text of ad hoc SQL statements. ASE compares a newly received ad hoc SQL statement to cached SQL statements 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 procedure cache. However, the ad hoc SQL is used in ASE is far from perfect.


Procedure cache65 l.jpg
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 and session settings (results of "set" commands, generally). Unfortunately this means the hash is not so useful for determining highly repeated SQL.


Network packet size l.jpg
Network Packet Size

  • ASE 15 comes with a default network packet size of 2048 bytes. In earlier versions of ASE this used be 512 bytes.

  • For most OLTP type queries the default packet size is more than adequate. However DSS type applications normally include large batches of SQL and tend to return larger result sets. Operations like BCP and text processing like XML can benefit from larger packet sizes. In ASE this is specified by the configuration parameter max network packet size.

  • When ASE is started it goes and works out the total memory required for all users that can log in to ASE. ASE uses default network packet size * 3 for allocation of memory to each user. Additionally, worker processes take the same amount of memory.


Network packet size67 l.jpg
Network Packet Size

  • For example to work out total memory allocated for network packets you calculate:

  • (number of user connections + number of worker processes) * 3 * default network packet size

  • If you set the default network packet size to 4096 bytes, and you have 100 user connections and 20 worker processes, the amount of network memory required is:

  • (100 + 20) * 3 * 4096 = 1474560 bytes

  • Larger packet sizes will require additional network memory. Assuming that you set your maximum packet size to 8192 bytes and you will have five simultaneous users using these large packets then the amount of additional network 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 of 2048. In this case it will be 14,336 bytes. Without increasing additional network memory, you will not be able to use larger packet sizes.


Number of devices l.jpg
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


User connections l.jpg
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


Maximum number of engines l.jpg
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 this is 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.


Setting the number of locks l.jpg
Setting the number of locks

  • The default value of 5,000 is not going to be enough. A setting of 50,000 should be sufficient for dev environments. For high activity production servers a value of 400-500K may be more appropriate.

  • 1> sp_configure 'number of locks'

  • 2> go

  • Parameter Name Default Memory Used Config Value Run Value

  • ------------------------------ ----------- ----------- ------------ -----------

  • number of locks 5000 11719 50000 50000


Interfaces file l.jpg
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 (as opposed to $SYBASE). Be aware that the default interfaces file presented by dsedit will need to be changed when adding/updating the interfaces file


Ase emergency bug fixes ebf l.jpg
ASE Emergency bug fixes (EBF)

  • 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 a good 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.


Time zones l.jpg
Time Zones

  • It is becoming increasingly necessary to have multiple Sybase servers running on the same host with different time zones. Thus, it will be necessary 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:


Time zones75 l.jpg
Time Zones

  • ls /usr/share/lib/zoneinfo

  • America Cuba GB GMT+13 GMT+9 GMT-4 Hongkong 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-6 Iran MST7MDT Pacific US

  • Brazil EST5EDT GMT+0 GMT+4 GMT-10 GMT-7 Israel Mexico Poland UTC

  • CET Egypt GMT+1 GMT+5 GMT-11 GMT-8 Jamaica Mideast Portugal Universal

  • CST6CDT Eire GMT+10 GMT+6 GMT-12 GMT-9 Japan NZ ROC W-SU

  • Canada Etc GMT+11 GMT+7 GMT-2 Greenwich Kwajalein NZ-CHAT ROK WET

  • Chile Factory GMT+12 GMT+8 GMT-3 HST Libya Navajo Singapore Zulu


Environment files l.jpg
Environment Files

  • ASE by default provides two environment files in $SYBASE. Depending on the shell that you are using you can source either SYBASE.sh or SYBASE.csh.


Environment files77 l.jpg
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 1024

    • export EDITOR=vi

    • . ~sybase/SYB_LDN_EPG_DEV01/dba/bin/environment.ksh


Environment files78 l.jpg
Environment Files

  • It will in turn allow you to invoke any of the above server settings by having the 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'


Environment files79 l.jpg
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 the correct

    • # 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:

    • #


Environment files80 l.jpg
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

    • ###################################


Environment files81 l.jpg
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

  • export LD_LIBRARY_PATH=$SYBASE/$SYBASE_ASE/lib:$SYBASE/$SYBASE_OCS/lib:$SYBASE/$SYBASE_OCS/lib3p

  • export LM_LICENSE_FILE=$SYBASE/$SYBASE_SYSAM/licenses/license.dat

  • ###################################


Environment files82 l.jpg
Environment Files

  • ###################################

  • GENERIC_ROOT=~/dba

  • SPECIFIC_ROOT=~/$DSQUERY/dba

  • export GEN_APPSDIR=$GENERIC_ROOT/bin

  • export GEN_LOGDIR=$GENERIC_ROOT/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 LOGDIR=$SPECIFIC_ROOT/log

  • export LOGDIR=/var/tmp

  • export ETCDIR=$SPECIFIC_ROOT/etc

  • export HTMLDIR=$SPECIFIC_ROOT/html

  • export APPSDIR=$SPECIFIC_ROOT/bin


Environment files83 l.jpg
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


Environment files84 l.jpg
Environment Files

  • export TRANDUMPDIR=$DUMPDIR/tran

  • PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/sbin:$SYBASE/$SYBASE_ASE/bin:$SYBASE/$SYBASE_OCS/bin:$SYBASE/$SYBASE_ASE/install:$SYBASE/ASEP/bin/:$APPSDIR:$GEN_APPSDIR

  • ##export [email protected]`hostname`

  • export [email protected]

  • export PS1=`hostname`:'($DSQUERY)$ '


Run dsquery file l.jpg
RUN_${DSQUERY} file

  • To ensure that the Sybase Server starts in the correct timezone etc, make sure that you modify the RUN_server file to execute 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 \


References l.jpg
References

  • Sybooks. Online ASE manuals from:

    http://infocenter.sybase.com/help/index.jsp

  • Configuring and Setting Up tempdbs for Optimal Performance

    Mich Talebzadeh and Ryan Thomas Putnam, International Sybase Group Publication, Q4, 2004,


ad