Sybase ASE Installation Standards - PowerPoint PPT Presentation

About This Presentation
Title:

Sybase ASE Installation Standards

Description:

(Taken from Standards for Installing ASE Servers on Solaris Hosts', Mich ... ASE Emergency bug fixes (EBF) Time Zones. 9. What is covered. Environment Files ... – PowerPoint PPT presentation

Number of Views:622
Avg rating:3.0/5.0
Slides: 87
Provided by: stevel61
Category:

less

Transcript and Presenter's Notes

Title: Sybase ASE Installation Standards


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

2
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.

3
(No Transcript)
4
What is covered
  • Unix Server Configuration
  • DBA environment
  • Temporary database tempdb
  • tempdb Recommendation
  • tempdb Devices on tmpfs

5
What is covered
  • tempdb on file systems
  • tempdb Configuration
  • Devices
  • Building Sybase ASE
  • ASE Port Numbering
  • Default Character set

6
What is covered
  • Location of log files
  • Memory and Cache configuration

7
What is covered
  • Memory Configuration
  • Cache Configuration
  • Procedure Cache
  • Network Packet Size
  • Number of devices
  • User Connections

8
What is covered
  • Maximum number of Engines
  • Setting the number of locks
  • interfaces file
  • ASE Emergency bug fixes (EBF)
  • Time Zones

9
What is covered
  • Environment Files
  • RUN_DSQUERY file
  • References

10
Unix Server Configuration
  • Host built to Engineering approved standard
  • Currently Solaris 2.8-2.9
  • Shared memory settings

11
Unix Server Configuration (Shared memory settings
)
  • Force loading of semaphores
  • forceload sys/semsys
  • forceload sys/shmsys
  • set semsysseminfo_semmap250
  • set semsysseminfo_semmni500
  • set semsysseminfo_semmns500
  • set semsysseminfo_semmsl500
  • set semsysseminfo_semmnu500
  • set semsysseminfo_semume100

12
Unix Server Configuration
  • shared memory max
  • (32Bit) Set shmsysshminfo_shmmax (4096 x 1024
    x 1024)-1
  • (64Bit) Set shmsysshminfo_shmmax (available
    physical memory in multiples of 1024MB x 1024 x
    1024)-1
  • For multiple servers on a host / high no of
    stripes
  • Set shmsysshminfo_shmseg 50

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

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

15
DBA Environment
  • Sybase home
  • 2GB/4GB filesystem, dependant on products
    installed
  • Mounted as /export/home/sybase

16
DBA Environment
  • Sybase home directory structure
  • /ltproductgt/ltversiongt
  • /dba/.
  • ../bin main scripts for maintenance/monitoring
  • ../etc
  • ../log additional log files (debug)
  • ../tmp work files for ../bin scripts

17
DBA Environment
  • Sybase backup directory structure
  • Built on its own partition
  • /syblive/backup
  • ../ltserver-namegt/dbdumps dump files
  • ../ltserver-namegt/txn_logs log dumps
  • Requires reasonable size and capacity for future
    growth

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.

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

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 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.

21
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.

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 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.

23
tempdb Devices on tmpfs
  • You also need to modify your RUN_Server file 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.

24
tempdb Devices on tmpfs
  • Example
  • touch /tmp/tempdb_tmpfs_data.dat
  • touch /tmp/tempdb_tmpfs_log.dat
  • SYBASE/SYBASE_ASE/bin/dataserver \
  • -sltSERVER_NAMEgt \
  • -dHOME/ltSERVER_NAMEgt/devices/
  • ltSERVER_NAMEgt_master.dev \
  • -eSYBASE/SYBASE_ASE/install/
    ltSERVER_NAMEgt.log \
  • -cSYBASE/SYBASE_ASE/ltSERVER_NAMEgt.cfg \
  • -MSYBASE/SYBASE_ASE

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
    there
  • tempdb_devices/SYB_UDEQA2 ls
  • SYB_UDEQA2_tempdb_data1 SYB_UDEQA2_tempdb_log1

26
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
    1508 SYB_UDEQA2_tempdb_data1.dev -gt
    /tempdb_devices/SYB_UDEQA2/SYB_UDEQA2_tempdb_data1

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

28
Devices
  • Naming conventions for sybase data devices
  • master ltserver-namegt_master.dev
  • sybsystemprocs ltserver-namegt_sysprocsdev.dev
  • data devices ltserver-namegt_ dataltnngt.dev
  • log devices ltserver-namegt_ logltnngt.dev
  • Permissions
  • All devices permissioned for sybasedba

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

30
Building Sybase ASE
  • Server Naming Conventions
  • All servers names must follow the standard
    naming convention. This is defined as follows
  • ltPRODUCTgt_ltLOCATIONgt_ltAPPLICATION_ltSTATUSgt
  • ltProductgt 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 ltSERVER_NAMEgt_XP with SERVER_NAME in
    uppercase.

31
Building Sybase ASE
  • ltLOCATIONgt 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.

32
Building Sybase ASE
  • Application should reflect the application using
    the server such as
  • Application Meaning
  • UDE UDE
  • HOSPITALITY Hospitality
  • REC Reconciliation
  • GED Global Equities

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.

34
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.

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.

36
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
    formatltSERVER_NAMEgt.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_HM"
  • OLDLOG"LOGDIR/DSQUERY.log_NOW"
  • mv ERRORLOG OLDLOG
  • gzip OLDLOG

37
Location of log files
  • Recycle log every 24 hours
  • Backing up your errorlog via cron once a day
  • NOW"date d_b_Y_HM"
  • OLDLOG"LOGDIR/DSQUERY.log_NOW"
  • cp ERRORLOG OLDLOG gt ERRORLOG
  • gzip OLDLOG

38
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.

39
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.

40
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.

41
Memory Configuration
  • When you start up ASE, it will go and read the
    configuration file ltSERVER_NAMEgt.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.

42
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.

43
Memory Configuration
  • 1gt sp_configure memory
  • 2gt 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

44
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.

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 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.

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'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.

47
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 ASEs 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.

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.

49
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)

50
Cache Configuration
  • Named Cache
  • ASEs 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.

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

52
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.

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.

54
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.

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

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

57
Cache Configuration
  • 1gt sp_cacheconfig 'default data cache'
  • 2gt 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)

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
59
Cache Configuration
  • Example of creating a user defined cache and the
    associated pools
  • 1gt sp_cacheconfig tempdb_log_cache,
    '500M','logonly','relaxed', 'cache_partition1'
  • 2gt go
  • Since this is a log cache, it should be mainly
    made up of 4K buffer pools and some 16K buffer
    pools as well
  • 1gt sp_poolconfig "tempdb_log_cache", "370M",
    "4K", "2K"
  • 2gt go
  • 1gt sp_poolconfig "tempdb_log_cache", "125M",
    "16K", "2K"
  • 2gt go

60
Cache Configuration
  • Display information about this cache
  • 1gt sp_cacheconfig tempdb_log_cache
  • 2gt 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

61
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

62
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 cache in 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.

63
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.

64
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.

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

66
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.

67
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.

68
Number of devices
  • As a rule of thumb you can set this one to 30
  • 1gt sp_configure devices
  • 2gt go
  • Parameter Name Default
    Memory Used Config Value Run Value
  • ------------------------------ -----------
    ----------- ------------ -----------
  • number of devices 10
    17 30 30

69
User Connections
  • A value of 100 should be sufficient for most non
    production servers
  • 1gt sp_configure 'user connections'
  • 2gt go
  • Parameter Name Default
    Memory Used Config Value Run Value
  • ------------------------------ -----------
    ----------- ------------ -----------
  • number of user connections 25
    17261 100 100

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 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.

71
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.
  • 1gt sp_configure 'number of locks'
  • 2gt go
  • Parameter Name Default
    Memory Used Config Value Run Value
  • ------------------------------ -----------
    ----------- ------------ -----------
  • number of locks 5000
    11719 50000 50000

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 (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

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 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.

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

75
Time Zones
  • ls /usr/share/lib/zoneinfo
  • America Cuba GB GMT13
    GMT9 GMT-4 Hongkong MET
    PRC Turkey posixrules
  • Asia EET GB-Eire GMT2
    GMT-0 GMT-5 Iceland MST
    PST8PDT UCT src
  • Australia EST GMT GMT3
    GMT-1 GMT-6 Iran MST7MDT
    Pacific US
  • Brazil EST5EDT GMT0 GMT4
    GMT-10 GMT-7 Israel Mexico
    Poland UTC
  • CET Egypt GMT1 GMT5
    GMT-11 GMT-8 Jamaica Mideast
    Portugal Universal
  • CST6CDT Eire GMT10 GMT6
    GMT-12 GMT-9 Japan NZ
    ROC W-SU
  • Canada Etc GMT11 GMT7
    GMT-2 Greenwich Kwajalein NZ-CHAT
    ROK WET
  • Chile Factory GMT12 GMT8
    GMT-3 HST Libya Navajo
    Singapore Zulu

76
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.

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 ENVHOME/.kshrc
  • export PATHPATH/usr/local/binHOME/dba/binHO
    ME
  • 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 "
    "

78
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 TERMvt100
  • alias isql'isql -w132'
  • alias sa'isql -w000000000000000000000000000000000
    00000000000000000000000000000000000000000000000000
    00000000000000000000000000000000000000000000000001
    000 -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_DEV
    01/dba/bin/environment.ksh'
  • alias SYB_LDN_EPG_DEV02'. sybase/
    SYB_LDN_EPG_DEV02/dba/bin/environment.ksh'

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

80
Environment Files
  • environment.ksh
  • Modify the following lines only
  • export TZGB
  • export SYBASEsybase/SYB_UTS1
  • export SYSTEMUDE
  • export DSQUERYSYB_UTS1
  • export REFERENCE_SERVERSYB_UTS1
  • export BSQUERYDSQUERY_BACKUP
  • export DUMPDIR/localdisk/refresh_db_temp_dumps
  • export AUDITDIRDUMPDIR/audit
  • ulimit -Sn 2048

81
Environment Files
  • environment.ksh
  • 12.5 Stuff
  • export SYBASE_ASEASE-12_5
  • export SYBASE_OCSOCS-12_5
  • export SYBASE_FTSFTS-12_5
  • export SYBASE_SYSAMSYSAM-1_0
  • export LD_LIBRARY_PATHSYBASE/SYBASE_ASE/libSY
    BASE/SYBASE_OCS/libSYBASE/SYBASE_OCS/lib3p
  • export LM_LICENSE_FILESYBASE/SYBASE_SYSAM/licen
    ses/license.dat

82
Environment Files
  • GENERIC_ROOT/dba
  • SPECIFIC_ROOT/DSQUERY/dba
  • export GEN_APPSDIRGENERIC_ROOT/bin
  • export GEN_LOGDIRGENERIC_ROOT/log
  • export GEN_ETCDIRGENERIC_ROOT/etc
  • export GEN_ENVDIRGENERIC_ROOT/env
  • export GEN_ADMINDIRGENERIC_ROOT/admin
  • export PASSFILEGENERIC_ROOT/env/.syb_accounts
  • export ADMINDIRSPECIFIC_ROOT/admin
  • export TMPDIRSPECIFIC_ROOT/tmp
  • export LOGDIRSPECIFIC_ROOT/log
  • export LOGDIR/var/tmp
  • export ETCDIRSPECIFIC_ROOT/etc
  • export HTMLDIRSPECIFIC_ROOT/html
  • export APPSDIRSPECIFIC_ROOT/bin

83
Environment Files
  • for ELOG in SYBASE/SYBASE_ASE/install/DSQUERY.l
    og SYBASE/SYBASE_REP/install/DSQUERY.log
    SYBASE/install/DSQUERY.log
  • do
  • if -f ELOG
  • then
  • export ERRORLOGELOG
  • fi
  • done
  • for BLOG in SYBASE/SYBASE_ASE/install/BSQUERY.l
    og SYBASE/install/BSQUERY.log
  • do
  • if -f BLOG
  • then
  • export BACKUPLOGBLOG
  • fi
  • done
  • PATH.PATH/usr/bin/usr/sbinAPPSDIRGEN_APPS
    DIR/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

84
Environment Files
  • export TRANDUMPDIRDUMPDIR/tran
  • PATH/usr/kerberos/bin/bin/usr/bin/usr/local/bi
    n/usr/bin/X11/usr/X11R6/bin/usr/sbinSYBASE/S
    YBASE_ASE/binSYBASE/SYBASE_OCS/binSYBASE/SYB
    ASE_ASE/installSYBASE/ASEP/bin/APPSDIRGEN_AP
    PSDIR
  • export SUPPORTsybase_at_hostname
  • export SUPPORTmich.talebzadeh_at_uk.fid-intl.com
  • export PS1hostname'(DSQUERY) '

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 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/environme
    nt.ksh
  • Adaptive Server name SYB_UDEQA2
  • ASE page size (KB) 2k
  • Master device path /export/home/sybase/SYB_UD
    EQA2/devices/SYB_UDEQA2_master.dev
  • Error log path /export/home/sybase/ASE/12
    54/ASE-12_5/install/SYB_UDEQA2.log
  • Configuration file path
    /export/home/sybase/ASE/1254/ASE-12_5/SYB_UDEQA2.c
    fg
  • 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_UDEQA
    2_master.dev \
  • -eSYBASE/SYBASE_ASE/install/SYB_UDEQA2.log \
  • -cSYBASE/SYBASE_ASE/SYB_UDEQA2.cfg \

86
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,
Write a Comment
User Comments (0)
About PowerShow.com