1 / 86

Sybase ASE Installation Standards

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.

Ava
Download Presentation

Sybase ASE Installation Standards

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. What is covered • Unix Server Configuration • DBA environment • Temporary database tempdb • tempdb Recommendation • tempdb Devices on tmpfs

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

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

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

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

  8. What is covered • Environment Files • RUN_${DSQUERY} file • References

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related