Hp COMPAQ PROLIANT 8500, COMPAQ PROLIANT 8000, COMPAQ PROLIANT 4000, COMPAQ PROLIANT 800, COMPAQ PROLIANT 6400R Configuration and Tuning of Sybase System 11 for NetWare

...

Download

Configuration and Tuning of Sybase System

11 for NetWare on Compaq Servers

White Paper

________________________________________________________________

Prepared By

Database Engineering

Compaq Computer Corporation

August 1996

NOTICE

The information in this publication is subject to change without notice.

COMPAQ COMPUTER CORPORATION SHALL NOT BE LIABLE FOR TECHNICAL OR EDITORIAL ERRORS OR OMISSIONS CONTAINED HEREIN, NOR FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES RESULTING FROM THE FURNISHING, PERFORMANCE, OR USE OF THIS MATERIAL.

This publication contains information protected by copyright. Except for internal use distribution, no part of this publication may be photocopied or reproduced in any form without prior written consent from Compaq Computer Corporation.

This publication does not constitute an endorsement of the product or products that were tested. The configuration or configurations tested or described may or may not be the only available solution. This test is not a determination of product quality or correctness, nor does it ensure compliance with any federal, state or local requirements. Compaq does not warrant products other than its own strictly as stated in COMPAQ product warranties.

Product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

COMPAQ, SYSTEMPRO, PROLIANT, PROSIGNIA

Registered US Patent and Trademark Office.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq

Servers

First Edition (August 1996)

Document Number 140A/0896

Compaq Computer Corporation

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page i

Table of Contents

Introduction......................................................................................................................................... 1

Tuning Goals....................................................................................................................................... 2

System Processor Planning.................................................................................................................. 2

Initial CPU Recommendations......................................................................................................2

Memory Planning................................................................................................................................ 3

Initial Memory Recommendations for Sybase ............................................................................... 3

Estimating Memory Requirements for Sybase............................................................................... 3

Disk Subsystem Planning .................................................................................................................... 5

Array Accelerator: Its Function and Benefit in a Sybase SQL Server Environment ....................... 5

SMART and SMART-2 SCSI Array Controller characteristics .............................................. 5

Write Cache........................................................................................................................... 6

Read Cache............................................................................................................................ 6

Housekeeper, Checkpoints, and Transaction Log Writes........................................................ 6

Integrity of cached data in the Array Accelerator ................................................................... 7

Configuring the Array Accelerator......................................................................................... 7

Fault Tolerance Considerations.............................................................................................. 9

1. RAID-based Disk Controllers...............................................................................9

2. NetWare Disk Controller Duplexing..................................................................... 9

3. Sybase SQL Server Mirroring/Duplexing ............................................................. 9

Network Planning................................................................................................................................ 10

Network Characteristics of a SQL Server Environment.......................................................... 10

IPX/SPX Network Packet Routing ......................................................................................... 10

TCP/I P Network Pack et Rout ing............................................................................................10

Network Interface Controller ................................................................................................. 11

Processor Tuning................................................................................................................................. 11

Memory Tuning .................................................................................................................................. 11

Estimating Memory Requirements for Stored Procedures.............................................................. 12

I/O Tuning .......................................................................................................................................... 12

Separate Sequential and Random I/O’s......................................................................................... 13

Layout of Tables and Files............................................................................................................ 13

Checking Disk I/O Rate................................................................................................................14

Network Tuning.................................................................................................................................. 15

Compaq System Configuration............................................................................................................ 15

NetWare 4.10 Configuration................................................................................................................ 15

Processes ...................................................................................................................................... 15

upgrade low priority threads .................................................................................................. 15

maximum service processes ................................................................................................... 16

Memory........................................................................................................................................ 16

garbage collection interval..................................................................................................... 16

number of frees for garbage collection.................................................................................... 16

minimum free memory for garbage collection........................................................................ 16

File Caching.................................................................................................................................16

read ahead enabled................................................................................................................. 17

minimum file cache buffers.................................................................................................... 17

Page ii Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

File System...................................................................................................................................17

volume block size................................................................................................................... 17

enable file compression..........................................................................................................17

sub-block allocation............................................................................................................... 17

enable disk read after write verify .......................................................................................... 17

immediate purge of deleted files............................................................................................. 18

file delete wait time................................................................................................................ 18

minimum file delete wait time................................................................................................ 18

remirror block size................................................................................................................. 18

concurrent remirror requests.................................................................................................. 18

mirrored devices are out of sync message frequency............................................................... 18

Communications........................................................................................................................... 19

maximum physical receive packet size................................................................................... 19

minimum packet receive buffers............................................................................................. 19

maximum packet receive buffers............................................................................................ 19

NetWare Partitions....................................................................................................................... 19

Upgrading From Previous Sybase Versions to System 11.....................................................................19

Sybase System 11 Configuration..........................................................................................................19

sp_configure command.................................................................................................................20

total memory.......................................................................................................................... 20

procedure cache percent.........................................................................................................20

number of devices.................................................................................................................. 20

number of user connections.................................................................................................... 20

recovery interval in minutes...................................................................................................20

housekeeper free write percent............................................................................................... 21

user log cache size ................................................................................................................. 21

default network packet size.................................................................................................... 21

max network packet size........................................................................................................ 21

additional network memory.................................................................................................... 21

number of extent i/o buffers ................................................................................................... 21

number of sort buffers............................................................................................................22

number of pre-allocated extents.............................................................................................. 22

stack size ............................................................................................................................... 22

event buffers per engine......................................................................................................... 22

Sybase Sybinit Utility.................................................................................................................... 22

Sybase Audit Feature.................................................................................................................... 22

Installing From CD-ROM.................................................................................................................... 23

Compaq SCSI based CD-ROM drive ............................................................................................ 23

Compaq IDE based CD-ROM drive.............................................................................................. 23

System Management Tools..................................................................................................................24

Novell Monitor Utility .................................................................................................................. 24

Sybase SQL Monitor..................................................................................................................... 24

Compaq Insight Manager ............................................................................................................. 24

Conclusion .......................................................................................................................................... 25

Check List of Recommendations.......................................................................................................... 26

Introduction

The purpose of this document is to share the knowledge acquired by Compaq Systems Engineers

in the area of configuration and performance tuning of Sybase SQL Server 11 for Novell

NetWare on the Compaq ProLiant family of servers. It is our desire to deliver the best technical

information possible on a specific topic in a timely manner and in a highly useable format. Any

comments, suggestions and feedback are always appreciated.

The information presented in this document is based on Sybase SQL Server 11.0.1 for NetWare

4.10 and is a result of numerous performance tests executed within the context of an industry-

standard TPC-C benchmark, internal benchmarking for hardware development and optimization,

and analyzing customer-reported expectations, performance trends, and solutions. Sybase and

Compaq do not recommend or support the running of Sybase SQL Server 11.0.1 for NetWare on

NetWare 3.11/3.12, 4.0x and SMP based servers.

Compaq is an active member of the Transaction Processing Performance Council, and publishes

a number of benchmarks every year proving the superior performance and price-performance

ratios of Compaq servers. The results of these benchmarks can be obtained directly from

Compaq Computer Corporation or from the Transaction Processing Performance Council. The

contact information is listed below.

Configuration and Tuning of Sybase

System 11 for NetWare on Compaq

Servers

Compaq Computer Corporation Database Performance Engineering P.O. Box 692000 Houston, TX 77269-2000 http://www.compaq.com

Even though most of the testing that provided basis for this document was done in the area of

online transaction processing, much of the information presented does apply to other

environments, such as decision support and batch processing. We recommend that you always

experiment before applying any changes to your production server.

Other publications covering these and related topics listed below:

• Configuring Compaq RAID Technology for Database Servers, Compaq TechNote, P/N

184206-001

• Sybase SQL Server System Administration Guide

• Sybase SQL Server Performance and Tuning Guide

• Introduction to Sybase SQL Server for NetWare

• Installing Sybase Products for Novell NetWare

• Using Sybase SQL Server Utility Programs for Novell NetWare

Transaction Processing Performance Council c/o Shanley Public Relations 777 North First Street, Suite 6000 San Jose, CA 95112-6311 http://www.tpc.org

• Sybase SQL Server Configuration Guide for Novell NetWare

Page 2 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

Tuning Goals

To achieve the best system performance possible, there are several factors which you must

review. These factors include optimization of the hardware, the Sybase SQL Server, the

operating system, and the application software. This paper focuses on the hardware, Sybase SQL

Server, and the operating system. Although it is important to tune the application to take

advantage of the system, due to the diversity of applications this is beyond the scope of this

paper.

Tuning is an iterative process that evolves as user and work loads change on your system. An

optimally tuned Sybase System 11 on NetWare 4.10 system should have the following

characteristics:

• Most of the CPU utilization is allocated to the user processes and not the background

processes. Make sure that the user process threads are getting the majority of the CPU resource.

• There will be little or no waiting on I/O. This indicates that the CPU will always have some

work to do while there are outstanding I/Os.

Users should see good response times. A system that appears to be tuned well and is

experiencing poor response times could have any or all of the following problems:

• An inefficient database design. This could include poor indexing schemes or inefficient

layout of the data on the drives when the database was created.

• Inefficient statements in the database application, such as poor SQL statements in stored

procedures.

• Excess latencies in the I/O subsystem or network.

If your database and application are well tuned, and if there is no idle time and no waiting on I/O

on the CPU, then you may be CPU bound. CPU bound means that in spite of your efforts to tune

the system, you cannot get more performance from it because there is no more processing power

left on the CPU. In that case, upgrading to a faster system processor could greatly improve

response times.

System Processor Planning

This section on system processor planning outlines some items that need to be considered before

deciding on which CPU to utilize for your database server.

Initial CPU Recommendations

The choice of the right system processor depends on your environment. As technology evolves,

more powerful processors are becoming available, pushing performance to new levels at very

competitive costs per processing unit. Our recommendation is to carefully evaluate your

environment, and experiment with various processor configurations, if possible. Always have

future growth and expansion in mind. You may want to start with Pentium

Pentium

higher demand systems.



/166 processors for smaller departmental systems, and PentiumPro processors for



/133 or

Before upgrading the system processor, you should closely monitor performance of the system

and tune it from the software perspective. If the performance bottleneck is in software, hardware

upgrades can only partially improve performance. It may be more cost effective to tune the

software rather than to purchase hardware upgrades. If the performance bottleneck is clearly at

the system processor, upgrading to the next level processor can dramatically improve

performance.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 3

Memory Planning

The amount of memory in the server will have a great impact on the overall performance of the

Sybase SQL Server. For NetWare servers dedicated as Sybase SQL Servers there should be a

minimum of 10% free buffers are reported by the NetWare monitor utility. For non-dedicated

NetWare servers, servers that perform file and print services along with running Sybase SQL

Server, this value should be at least 20%.

Initial Memory Recommendations for Sybase

Sybase recommends a minimum of 64 MB of memory to install and run Sybase System 11.

System 11 consists of Sybase SQL Server, Sybase Backup Server, Sybase Monitor Server and

Sybase Historical Server. Adding additional memory to the server and configuring it for use by

the Sybase SQL Server will in most cases increase the server throughput and performance.

Estimating Memory Requirements for Sybase

You can obtain an initial estimate the actual memory requirements for your Sybase System 11

database server by using the following procedures. The server used in this calculation had 512

MB of memory installed.

Install Sybase SQL Server, Backup Server and Monitor using the default installed configurations.

Restart NetWare and load the Novell Monitor utility. Record the number of

buffers

We will use these numbers later in this calculation.

total cache buffers

and

as reported on the Novell Monitor general information screen.

original cache

Original Cache Buffers: 130,561

Total Cache Buffers: 122,256

Start SQL Server, Backup Server and Monitor Server. Use the Novell Monitor utility to record

the memory allocation of the Sybase modules. The allocations on our test server were as follows:

For Sybase SQL S erver

Module NetWare 4KB buffers

International Library 6 Sybase Common Library 6 Sybase CS-Library 6 Netlib Driver for TLI 6 Sybase Transport Cont r ol Library 6 Sybase DB Library 6 Sybase SQLServer Stubs Libr ar y 6 Sybase SMP Stub Library 6 Sybase NetWare SQL Server 12008 Sybase Server Library 6

Total 12062 Total in MB

For Sybase Backup Server

Module NetWare 4KB buffers

Sybase Backup Server 929

Total 929 Total in MB

Page 4 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

For Sybase Monitor Server

Module NetWare 4KB buffers

Monitor Server 453

Total 453 Total in MB

48 MB SQL Server + 4 MB Backup Server + 2 MB Monitor Server = 54 MB total System

Add to this 51KB for each user connection beyond the initial default of 25 user connections. The

value of 51KB per user connection may need to be increased depending upon if you also

increased the user stack size and the default network packet size. Refer to the sections later in

this document for a discussion on the implications of altering the user stack size and the default

network packet size. Lets assume an additional 50 users, for a total of 75 user connections, at the

default allocation of 51KB per user.

50 additional users * 51 KB per user connection = 2550 KB ≈≈ 2.5 MB

For each database device beyond the initial 10 devices add another 0.5KB. For our example here

lets assume the addition of 12 more devices.

12 additional devices * 0.5 KB per device = 6 KB ≈≈ 0.6 MB

The type of transactions we are planning to execute will be transferring large amounts of data

between the client and server. To reduce the number of packets transferred on the network we

will increase the max network packet size from 512 to 4096 bytes. Each user connection requires

3 packet buffers. We will need to increase the setting for additional network memory to meet this

requirement.

75 user connections * 3 buffers per user * 4096 bytes + 2% overhead = 940032 bytes ≈≈ 0.9

At this time we don’t know how much memory will be required as procedure and data cache. We

do know that we have approximately 5 GB worth of data. Lets’ assume that we want 5% of the

data in the data cache. Theoretically you could increase the amount memory in the system so the

entire database would fit in memory, resulting in hopefully some rather phenomenal

performance. But typically most databases are larger than the available memory of the server.

5% data in cache * 5 GB = 0.25GB = 256 MB

So the initial estimate of how much memory is required by this implementation is:

Estimate of Sybase System 11 Memory Requirement

allocated by memory in MB

Sybase System 11 54.0 50 additional us er s 2.5 12 additional dev ic es 0.6 additional network memory 0.9 5% of data in cache 256.0

Total in MB 314.0 Total in NetWare 4KB buffers

80,384

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 5

For dedicated Sybase SQL Servers leave at least 10% of the original cache buffers as free cache

buffers. For non-dedicated Sybase SQL Servers leave at least 20% of the original cache buffers

as free cache buffers. Free cache buffers are reported as total cache buffers by the NetWare

Monitor utility.

Dedicated server:

130561 original cache buffers - 80384 System 11 buffers - 13056 free cache buffers = 37121

buffers remaining

Non-dedicated server:

130561 original cache buffers - 80384 System 11 buffers - 26112 free cache buffers = 24065

buffers remaining

If the number of buffers remaining is negative you will need to increase the amount of memory in

the server or reduce the memory allocations for Sybase System 11and run at a less than optimal

performance level. If the number of buffers remaining is positive you may want to increase the

value for the Sybase sp_configure total memory parameter. Remember a NetWare buffer is 4 KB

in size and a Sybase page is 2 KB in size.

Disk Subsystem Planning

The objective of this section is to provide information on the benefits of the Compaq SMART and

SMART-2 SCSI Array Controller Array Accelerator and the pros and cons of various fault

tolerance methods.

Additional information on disk subsystem configuration and Compaq drive array technology can

be found in the following manuals:

■

Configuring Compaq RAID Technology for Database Servers

Compaq TechNote, P/N 184206-001

■

Compaq SMART-2 Array Controller User Guide, P/N 184482-001

■

Compaq SMART SCSI Array Controller User Guide, P/N 142136-004

■

Sybase SQL Server System Administration Guide, Doc. ID 32500-01-1100-00

Array Accelerator: Its Function and Benefit in a Sybase SQL Server Environment

The Array Accelerator is a feature of the SMART and SMART-2 SCSI Array Controllers. The

main function of the Array Accelerator is to boost performance of disk operations by storing data

in the cache memory on the controller. The SMART SCSI Array controllers has 4MB of cache

that is mirrored, yielding 2MB of usable cache memory. The SMART-2 Array controller has

4MB of ECC (Error Checking and Correcting) memory as cache memory. The Array

Accelerator is shared among all logical drive volumes configured on the controller, and can be

enabled/disabled on a per-logical-volume basis.

SMART and SMART-2 SCSI Array Controller characteristics

On the SMART controller, the Array Accelerator functions as write cache only, while the

SMART-2 controller has an added feature that allows the Array Accelerator to function as write

cache, read-ahead cache, or a combination of both. With the added feature of the SMART-2

controller, you can, for example, configure the Array Accelerator on the particular controller to

function as 50% read-ahead and 50% write cache. Then every logical volume on this controller

is supported by the Array Accelerator in the 50/50 mode. The only exception is when the Array

Accelerator is disabled for the volume. Again, with the SMART controller, only writes can be

cached; there is no read-ahead option.

Page 6 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

Write Cache

When the Array Accelerator performs write caching, the drive controller writes data to the cache

memory on the Array Accelerator rather than directly to the drives. The system can access this

cache memory more than 100 times faster than accessing disk storage. The controller writes the

data in the Array Accelerator to the drive at a later time, when the controller is otherwise idle.

Without the Array Accelerator’s write cache, the application must wait until each write request is

written out to the disk. Writing to a disk device is slower than posting the write request in the

Array Accelerator, thus possibly resulting in decreased performance.

Read Cache

The SMART-2 controller uses the Array Accelerator to increase performance in some cases by

anticipating possible future read requests. The Array Accelerator uses a multi-threaded

algorithm to predict the next likely read operation for the drive array. That data is pre-read into

the cache on the Array Accelerator and therefore is ready before you access it. When the

SMART-2 controller receives a read request for the cached data, it can be burst read immediately

into system memory, thus avoiding a disk access after the read request.

The read-ahead option of the SMART-2 SCSI Array controller can boost performance in

environments that utilize sequential scans of data; for example, range lookups, data loads, table

scans, etc. Environments with a very random I/O profile, such as on-line transaction processing,

typically do not take advantage of the read-ahead capabilities of the controller, and in most cases

it is beneficial to configure 100% of the Array Accelerator for write caching.

Housekeeper, Checkpoints, and Transaction Log Writes

There are three main write-intensive operations Sybase SQL Server performs: housekeeper,

checkpoints, and transaction log writes.



During idle time on the SQL Server, the housekeeper writes dirty pages from the data cache to the disk at a lower priority than the checkpoint process. Unlike a checkpoint process which must write all dirty pages from the data cache to disk before terminating, the housekeeper writes only what it can during idle times of the system. If the system is idle for a long enough period of time the housekeeper may actually write all dirty pages from the data cache to disk. When this occurs the housekeeper notifies the checkpoint process and requests that a checkpoint be performed on the database so that the transaction log will have a record that all dirty pages were written to disk at that time. (For details on configuring the housekeeper, see Housekeeper Free Write Percent later in this document.)



During checkpoints, Sybase SQL Server generates a large number of write requests in a short time interval. The main objective of the checkpoint is to write all dirty pages from the data cache to the disk. The time it takes to write the dirty pages depends on several factors, such as the configuration of the housekeeper and the recovery interval of the SQL Server. (See the explanation of recovery interval later in this document.)

In some environments, the amount of write activity that the checkpoint generates can

saturate the Array Accelerator, thus interfering with read requests pending at the controller. Proper tuning of the housekeeper can help alleviate this problem.



The transaction log activity is composed exclusively of sequential writes and does not saturate the Array Accelerator. However, the benefits of caching the transaction log writes at the SMART or SMART-2 SCSI Array Controller level with the Array Accelerator can have a significant beneficial impact on performance. For optimal performance the Array Accelerator should be enabled. It is very important to make sure you follow the guidelines below for data integrity if you choose to enable the Array Accelerator on the transaction log.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 7

Integrity of cached data in the Array Accelerator

The Array Accelerator contains batteries that maintain any data in the cache if a system power

failure occurs. Make sure you maintain the batteries in a good condition and fully charged (they

are automatically recharged while system power is present). At a fully charged state, the

batteries can preserve data in the Array Accelerator for four days. When power is restored to

your system, an initialization feature writes the preserved data to the disk drives.

Another step to insure data integrity in case of system failure is to install an Uninterruptible

Power Supply (UPS). Installation of a UPS will allow the controller to flush all data out to disk in

the event of a power failure. The UPS does not, however, insure data integrity in the case of a

controller failure, when valid data exists in the Array Accelerator. In that case, the Array

Accelerator may be removed as a complete unit from one SMART-2 controller and installed on

another, while preserving any data cached. The data will be written to disk upon power up.

Configuring the Array Accelerator

The Array Accelerator of the SMART SCSI Array controller is configured via the Compaq

System Configuration Utility. The Array Accelerator of the SMART-2 Array controller is

configured via the Compaq Array Configuration Utility or the Compaq Array Configuration

Utility for NetWare. The changes made while running the Compaq Array Configuration Utility

for NetWare take effect immediately. In the following examples the Compaq Array

Configuration Utility is demonstrated. Always obtain the latest copy of the appropriate utility for

your Compaq SMART and SMART-2 Array controllers.

With the SMART Array controller, the Array Accelerator can either be enabled (100% write

cache) or disabled. This controller does not support read-ahead. The Array Accelerator can be

enabled or disabled on a per-logical-volume basis.

With the SMART-2 Array controller, you can select the ratio of read-ahead cache to write cache

for every controller. Once selected, this Array Accelerator ratio will apply to all logical volumes

on this controller that have the Array Accelerator enabled. You can enable or disable the Array

Accelerator on a per-logical-volume basis.

To select the Array Accelerator ratio for a SMART-2 Array controller, you must install and run

the Compaq Array Configuration Utility from diskettes or from the System Partition. Using the

Array Configuration Utility, highlight the appropriate controller and use the

“Controller/Settings...” option.

Page 8 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

The figure below shows how to enable/disable the Array Accelerator for a particular logical

drive. Using the Array Configuration Utility, highlight the appropriate logical volume and use

the “Logical Drive/Modify...” option.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 9

Fault Tolerance Considerations

Your transaction log needs to always be protected against a disk failure. Most mission critical

sites protect both the transaction log and the database devices and they usually choose hardware-

based fault tolerance. Non-mission critical sites are often satisfied with the protection of the

transaction log only and performing frequent backups/dumps.

You have three choices of protecting your data:

■

Use a RAID-based disk controller such as the Compaq SMART and SMART-2 SCSI Array Controllers

■

Use NetWare disk controller duplexing

■

Use Sybase SQL Server-based mirroring/duplexing.

Below are some key points to be aware of when choosing the appropriate fault tolerant method.

The performance differences between fault tolerance implementations can vary from insignificant

to very significant, depending on your configuration and environment. Therefore, we omitted

supplying performance differences for the purpose of not misleading our readers. We

recommend that you evaluate the various fault tolerance methods using your own applications.

1. RAID-based Disk Controllers



Hardware RAID is configurable on a logical volume basis. Therefore, the whole logical volume is protected by the appropriate fault tolerance. The capacity reduction depends on the size of the logical volume and the type of fault tolerance configured.



Hardware RAID offers the best reliability and automatic recovery. When a drive fails, the system continues operating. Once the failed drive is replaced, the controller automatically rebuilds the new drive and restores the system to its full fault tolerant capabilities.



Since the data protection occurs at the hardware (controller) level, there is no overhead on the system processor. This type of RAID is totally transparent to the operating system and the applications. With a CPU-intensive application/environment, such as Sybase SQL Server, hardware-based fault tolerance can provide the best performance. Please refer to the Compaq TechNote Configuring Compaq RAID Technology for Database Servers for a more complete discussion.

2. NetWare Disk Controller Duplexing



Operating system level mirroring through NetWare offers good reliability and provides fault tolerance when a disk controller should fail but it requires manual intervention for recovery. When a drive or disk controller fails, the system continues operating. To replace the failed drive or controller the server needs to be stopped and restarted. NetWare will then automatically re-synchronize the mirrored drive volume.



Mirroring through NetWare induces an additional processing overhead on the system, resulting in a lower performance.

3. Sybase SQL Server Mirroring/Duplexing



Sybase SQL Server mirroring is based on Sybase SQL Server devices. This offers you the finest granularity and lowest capacity reduction due to duplicated data. Refer to Sybase SQL Server, System Administration Guide for guidelines on choosing which devices to mirror.



You have an option of controller duplexing if you place the mirrored device on a different disk controller.

Page 10 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers



Sybase SQL Server supports both serial and nonserial write mode of mirroring. When utilizing the default, serial write mode, writes to the first device must finish before writes to the second device begin. Changing from serial to nonserial write mode allows the writes to both devices to be queued immediately, one to each mirrored device. The nonserial write mode will incur less overhead than serial writes that results in a slight performance improvement.



Mirroring through Sybase SQL Server induces an additional processing overhead on the system, resulting in a lower performance.

Network Planning

Sybase SQL Server for NetWare supports both IPX/SPX and TCP/IP network protocols.

NetWare was designed using the IPX/SPX network protocol and is relatively easy to maintain.

Sybase SQL Server was initially developed in the Unix environment where the TCP/IP network

protocol is more prevalent. TCP/IP based networks tend to require additional resources to

administer and maintain. The best performance of Sybase SQL Server for NetWare will be when

using the TCP/IP network protocol. From NetWare load and bind only the network protocols

necessary for your site. Disable network packet routing to increase performance. When

configuring Sybase SQL Server, Backup Server and SQL Monitor Server configure only the

network protocols that will be used.

Network Characteristics of a SQL Server Environment

A client workstation usually assembles a group of SQL commands and submits them for

execution by the database server. The server processes the commands and returns the resultant

data.

Rather than having the client workstation send a huge grouping of SQL commands, profile the

queries. Determine if any of the queries are candidates for conversion to stored procedures. A

stored procedure is a grouping of ‘standardized’ SQL query commands that are pre-compiled and

placed into the procedure area of the database by the System Administrator. The stored

procedure can then be referenced by name for execution. Using stored procedures for most of the

standard DBMS activities reduces the amount of network traffic and uses less server processor

resources to process the query.

IPX/SPX Network Packet Routing

By default NetWare will automatically route IPX/SPX packets from one server to another. For

best performance of your database server you should disabled automatic packet forwarding. The

database server should be an End Node server not an IPX Routing system.

Use the NetWare Inetcfg utility to view and modify the packet forwarding setting. If the Inetcfg

utility asks to “Transfer LAN driver, protocol and remote access commands?” reply with “yes”.

To disable automatic packet forwarding you need to first enable the Advanced IPX features.

From the Internetworking Configuration, Protocols, IPX selection screen display, enable the

Advanced IPX setting. From the same screen display, disable the Packet Forwarding setting.

The changes are saved in the sys:etc\initsys.ncf file. Restart the server for these changes to take

effect.

TCP/IP Network Packet Routing

By default NetWare will not automatically route TCP/IP packets from one server to another. For

best performance of your database server you should leave the automatic packet forwarding

disabled. The database server should be an End Node server not a TCP/IP Routing system.

Use the NetWare Inetcfg utility to view and modify the IP packet forwarding setting. If the

Inetcfg utility asks to “Transfer LAN driver, protocol and remote access commands?” reply with

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 11

“yes”. From the Internetworking Configuration, Protocols, TCP/IP selection screen display,

disable the IP Packet Forwarding setting. The changes are saved in the sys:etc\initsys.ncf file.

Restart the server for these changes to take effect.

Network Interface Controller

From the hardware perspective, you should have a 16- or 32-bit bus-master network controller or

a PCI-based network controller installed in the server to minimize the processing overhead

associated with non-bus master network cards.

Processor Tuning

For optimal performance, dedicate a NetWare server for Sybase SQL Server. Off load the file

and print services and other CPU intensive processes to another NetWare server. To determine

which processes are utilizing large amounts of CPU time use the Novell Monitor command

Monitor -> Scheduling information. Use the Sybase SQL Monitor utility to monitor the CPU

utilization of the Sybase SQL Server.

On the dedicated server, use the priority boost option -P to configure the Sybase SQL Server

process to surrender to NetWare at a rate less frequently than normal. Enable the NetWare set

parameter upgrade low priority threads, to insure that regularly scheduled low priority NetWare

maintenance processes can run. Enabling this parameter is especially important when running

SQL Server with the priority boost option of -P or when running Backup Server and Monitor

Server on the same system as SQL Server.

Under normal database operations the Monitor utility may report 100% CPU Utilization of

NetWare server but little or no database activity is taking place. Verify that the CPU really is

being 100% utilized, use the Monitor utility to look at the Scheduling information for the Idle

Loop process. Subtract its value from 100% to get a more accurate representation of CPU idle

time. Check the values for all other processes to make certain that an unexpected process isn’t

running that is consuming all of the CPU resource.

The Sybase SQL Monitor utility reports the CPU utilization from within the Sybase SQL Server.

It does not provide any CPU utilization information on other NetWare processes. If the Sybase

SQL Monitor consistently reports CPU utilization over 75% you should consider upgrading the

server to a faster processor.

Memory Tuning

In a typical Sybase SQL Server environment you have allocated most of the servers memory to

the database leaving the remainder for other NetWare processes to use. The key to memory

tuning then is how to instruct Sybase SQL Server to best use the memory allocated to it. The

amount of memory allocated to the database can be altered by setting the sp_configure total

memory value. Typically as more memory is allocated to the database, the performance of the

database increases.

The following chart shows how the performance of the server increased as memory was added to

the server. The Sybase dedicated named caches were reconfigured to obtain the best performance

for each memory size. With only 256 MB of memory in the server the system had 27% CPU idle

time. A check of the I/O rates showed that the data devices were experiencing 90 I/Os per drive.

This is well above the recommended optimal I/O rate of 55-60 I/Os per drive for the 4.3 GB

drives. (Refer to the I/O Tuning section for detailed information on the recommended I/O rates).

The server was bottle-necked awaiting on I/O to complete. When the memory was increased to

1024 MB, the percentage of CPU idle time was reduced to 2%. The I/O rate to the data devices

dropped to 47 I/Os per drive.

Page 12 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

Sybase System 11 on NetWare 4.10

1.00

0.90

0.80

0.70

0.60

0.50

0.40

0.30

transactions per minute, normalized

0.20

0.10

0.00

1.00

0.91

0.73

9.00%

2.00%

1024 768 512 256

3.00%

system memory in MB

Estimating Memory Requirements for Stored Procedures

0.56

30.00%

27.00%

25.00%

20.00%

15.00%

CPU % idle

10.00%

5.00%

0.00%

Sybase divides the total memory into two areas, procedure cache and data cache. The amount of

memory allocated to the procedure cache is defined as a percentage of the allocation for total

memory. To properly configure the size of the procedure cache you need to know the memory

requirements of each stored procedure.

To estimate the memory requirements of a stored procedure, you execute the procedure and then

use the dbcc memusage command to list the resources used by the stored procedure. Take the

number of plan bytes and divide by the number of plans to get the number of bytes per plan.

Divide the number of bytes per plan by 2048 bytes per Sybase page to get the number of Sybase

2K pages. Do this for each stored procedure, total the number of pages utilized and increase or

decrease the setting of the sp_configure procedure cache accordingly.

To estimate the memory requirements for a stored procedure without executing it. Execute the

following isql command:

select (count(*) / 8) + 1 from sysprocedures where id = object_id (“procedure_name”)

The query returns the number of Sybase 2K pages required by the stored procedure to execute. Do

this for each stored procedure, total the number of pages utilized and increase or decrease the

setting of the sp_configure procedure cache accordingly. Refer to the Sybase SQL Server

Performance and Tuning Guide for more detailed information.

I/O Tuning

In most well tuned Sybase systems, I/O is not a limiting factor. In order to assure that this is not

a problem, the following factors need to be verified.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 13

• Sequential I/O’s are isolated to a controller volume, separate from volumes with random

I/O’s. This means sequential I/O volume should be alone on a controller or on one port of the controller.

• Random I/O’s are balanced across all drives allocated to data and indexes.

• Physical disk I/O limits are not exceeded.

Separate Sequential and Random I/O’s

In order to achieve maximum performance on data files being accessed sequentially, the disk(s)

need to be dedicated to this purpose. Of primary importance are the Sybase transaction log files,

which are accessed in a sequential, write-only fashion. Other partitions with little I/O activity

can share the disk(s) with the transaction logs, such as the OS partition.

In typical multi-user database systems, data access is random. This data should be spread out

over as many physical disks as necessary to achieve random I/O rates that do not exceed

recommendations. This is best achieved by using the disk striping available with the Compaq

SMART SCSI Array Controller and SMART-2 SCSI Array Controller. Spreading out the disk

requests among many disks allows a high degree of parallelism to occur on accesses. Using the

Compaq SMART or SMART-2 SCSI Array Controller ensures that the load will be balanced

equally across the disks. For more information on optimizing array configurations refer to the

Compaq TechNote, Configuring Compaq RAID Technology for Database Servers.

Layout of Tables and Files

In order to improve performance where disk I/O is a problem, keep in mind the following.

• Transaction log access is 100% sequential I/O and needs to be isolated if possible. Speed of

the log is essential to the performance of the system. If possible, these drives should be fault tolerant, either mirrored or distributed data guarding. Hardware fault tolerance provides the maximum performance and reliability. See the Compaq TechNote Configuring Compaq

RAID Technology for Database Servers.

• Data file access is usually random and needs to be spread across as many drives as necessary.

By increasing the number of physical drives, greater I/O rates can be achieved. Using a striped array will assure that the I/O’s are well distributed.

Use the following guidelines when monitoring and optimizing the drive subsystem. You should

not have more I/O requests (disk transfers) per second per disk drive than the values in the

following table.

Sequential Writes (Transaction Log)

Random Reads/Writes (Database Access)

1.0GB drives

(Max I/Os per

Second per Drive)

≈150 ≈160 ≈180

≈30-40 ≈50 ≈55-60

2.1GB drives

(Max I/Os per

Second per Drive)

4.3GB drives

(Max I/Os per

Second per Drive)

NOTE:

second per drive rates than suggested above. This increase is due to the Array Accelerator write

posting some of these I/Os. In the Compaq Database Performance labs we have actually

With the Array Accelerator enabled, you may actually see substantially higher I/O per

Page 14 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

measured rates of up to 90 random I/Os per second per drive, some of which were being

temporarily cached by the Array Accelerator.

Checking Disk I/O Rate

Try not to overload any individual disk with random I/Os. To determine the I/O rate per drive,

first determine the number of I/O’s per second to each logical volume. The Novell Monitor

utility reports on the number of concurrent disk requests that are queued but doesn’t tell you on

which database device the I/O is occurring on. The Sybase SQL Monitor utility can determine

the I/O rate per database device.

It is best to use values from the SQL Monitor averaged over a period of time to calculate I/O

rates. Using the above output as an example, the calculations for determining the number of

sequential writes to the transaction log device are relatively simple.

The transaction log is located on a single logical volume consisting of 2 2.1GB drives attached to

the SMART-2 Array Controller. The log volume is using RAID1 for fault tolerance. From

Sybase SQL Monitor the log device shows zero reads and 94 writes.

Log device I/O: (0 reads + (94 writes * 2 writes per volume for RAID1)) = 188 writes per

volume / 2 drives per volume = 94 writes per drive < 160 maximum sequential writes per

2.1GB drive

Now lets calculate the I/O rate for the data devices. The data devices are located on single logical

volume consisting of 14 2.1GB drives attached to a second SMART-2 Array Controller. The

volume is using RAID5 for fault tolerance. Sybase SQL Monitor show the following I/O device

rates for the data devices:

acct1: 42 reads + 28 writes

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 15

acct2: 50 reads + 28 writes

acct3: 43 reads + 27 writes

Data device I/O: ((42 + 50 + 43) reads + ((28 + 28 + 27) writes * 5 writes per volume for

RAID5)) = 467 I/Os per volume / 14 drives per volume = 34 I/Os per drive < 50 maximum

random reads/writes per 2.1GB drive

For both the log and data volumes the measured I/O rates are well below the established

maximums for the respective volumes. In fact both volumes have sufficient bandwidth and

should not experience any significant throughput problems as the transaction rate increases.

Network Tuning

Proper network tuning can decrease the amount of network traffic, decrease the CPU workload at

both the client and the server and improve the responsiveness of the database server as perceived

by the end users. Increasing the size of the max network packet size can reduce the number of

packets being transmitted by the database to the network resulting in increase throughput.

Experimentation will be necessary to determine the optimal setting for your environment.

Compaq System Configuration

The Compaq System Configuration utility is used to set the initial hardware configuration of

the Compaq server. The Compaq SmartStart installation process will automatically set the

following hardware configuration items correctly for you. If you prefer to run the Compaq

System Configuration utility manually, outside of the SmartStart process, you will need to set the

following items for optimal operation of your server under NetWare.

Set the operating system to NetWare. Set the memory setting as linear for linear addressing

model. Set the operating selection for the SMART Array and SMART-2 Array controllers to

NetWare.

NetWare 4.10 Configuration

By design, NetWare is highly optimized for file and print sharing services. Fortunately this high

level of optimization also makes it a good platform to use as an application server. Additional

optimization of NetWare can be performed to enhance the performance and throughput of Sybase

SQL Server. Some of the following NetWare parameters can be altered, not necessarily to

improve the throughput of Sybase SQL Server but to reduce the wasting of resources allocated for

non-database activities. The best performance of Sybase SQL Server is obtained on a properly

configured and tuned dedicated server. When feasible run Sybase SQL Server on a dedicated

server.

Processes

NetWare, like all operating systems, has processes that run at regular intervals performing

general maintenance tasks. The functional parameters of these processes can be altered to

improve the throughput of the server.

upgrade low priority threads

Enable the NetWare Set parameter upgrade low priority threads, to insure that regularly

scheduled low priority NetWare maintenance processes can run. Enabling this parameter is

especially important when running SQL Server with the priority boost option of -P or when

running Backup Server and Monitor Server on the same system as SQL Server.

Page 16 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

maximum service processes

The maximum service processes setting determines the number of concurrent NetWare processes

permitted to execute on the server. Each process is responsible for relinquishing control back to

NetWare so the next process can execute. At startup, NetWare allocates a certain amount of

buffers for each of these processes. If the number is set higher than necessary, buffers are

allocated and unused. If set to low, a process will not be able to load. Typically the initial value

set by the Compaq SmartStart installation process is sufficient. Use the Monitor nlm, General

Information screen to report on the maximum server processes currently loaded. If this number

approaches the current setting consider increasing the initial value for maximum service

processes.

Memory

Most problems occur when NetWare is unable to satisfy a request for additional memory. Under

NetWare this can happen if you frequently load and unload nlms that don’t properly release

memory back to the free buffer pool. The memory can become fragmented resulting in bizarre

behavior or a system abend. To avoid this we recommend that the garbage collection process be

configured to aggressively recover memory buffers.

For dedicated Sybase SQL Servers you should maintain at least 10% of the original cache buffers

as free cache buffers. On non-dedicated servers this value should be at least 20%. Free cache

buffers are reported as total cache buffers by the NetWare Monitor utility. Each NetWare buffer

is 4KB in size. Use the NetWare Monitor utility, General Information screen to obtain the values

for original cache buffers and total cache buffers or look on the Resource Utilization screen for

the percentage of cache buffers left on the system.

garbage collection interval

Set the garbage collection interval to 1 minute. NetWare will check once a minute to see if the

garbage collection process needs to run. This forces NetWare to aggressively pursue recently

unallocated memory and return it back to the free buffer chain. The garbage collection process is

unable to recover memory fragments larger than 1 MB. This limitation can cause memory

fragmentation problems.

Even with aggressive garbage collection techniques NetWare still may not have enough non-

fragmented memory to load another nlm. The NetWare console will display warning messages

about running out of cache memory allocations. When these warning messages appear the only

way to de-fragment and reclaim the NetWare memory is to restart the server. By rearranging the

order in which nlms are loaded and unloaded you maybe able to reduce or avoid the

fragmentation of NetWare memory thus permitting the loading of another nlm.

number of frees for garbage collection

Set the number of frees for garbage collection parameter to 100. This informs NetWare to wake

up the garbage collection process as soon as the number of buffers released by processes reaches

100.

minimum free memory for garbage collection

Set the minimum free memory for garbage collection parameter to 1000. This informs NetWare

to wake up the garbage collection process whenever a process requests a minimum of 1000 bytes.

File Caching

File caching is not important to the performance of Sybase SQL Server on dedicated or non-

dedicated systems. Sybase SQL Server uses the directfs nlm to bypass the NetWare file caching

and directly access its database files.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 17

read ahead enabled

Set read ahead enabled to off. When read ahead is enabled and sequential file accesses are being

performed, a background process will read the next block that it assumes will be requested by the

application into the file cache buffer. Sybase SQL Server doesn’t perform any NetWare

sequential file accesses, it manages its own database device accesses via the directfs nlm. By

disabling the read ahead enabled feature you prevent the background process from performing

unnecessary reads.

minimum file cache buffers

Set the minimum file cache buffers parameter to 20. This setting determines the number of file

cache buffers to be left by NetWare exclusively for file caching. By setting this to the minimum

value, fewer cache buffers will be allocated by NetWare, leaving more buffers available for

Sybase SQL Server.

File System

The NetWare file system is designed for optimal storage and access of data files and applications

by many users. Sybase bypasses the standard NetWare file system access routines and directly

accesses the database devices via the directfs nlm. Some features of the NetWare file system can

be reconfigured to improve the overall performance or increase resources available to Sybase

SQL Server

volume block size

Set the volume block size to 64K during the creation of the volume from the Compaq SmartStart

process or from the NetWare Install nlm. This parameter defines the minimum block size that

the NetWare file system can allocate to a particular file. Since Sybase bypasses the NetWare file

system CLIB routines, changing its value from the suggested size will not affect overall

performance. It will minimize the amount of memory that NetWare will use for caching the

directory structures, leaving NetWare with more buffers to dedicate to Sybase memory structures.

enable file compression

Set the parameter enable file compression to off. If enabled, NetWare will dynamically start a

thread looking for files to compress, this can impact the performance of the Sybase SQL Server.

Sybase database files (devices) are allocated at a pre-determined maximum file size during the

disk initialization process and managed by the SQL Server, therefore file compression is not

necessary.

sub-block allocation

Disable sub-block allocation during the creation of the volume from the Compaq SmartStart

process or from the NetWare Install nlm. Sub-block allocation allows multiple files to exist

within one NetWare volume block. When sub-block allocation is enabled, multiple files can exist

within a single NetWare file block on disk. Because the smallest Sybase disk initiation size is

512 Sybase pages (1MB) and NetWare blocks can vary from 4 to 64KB its highly unlikely that

one will gain any additional disk storage. Access times to the files on the NetWare volume will

be improved slightly, due to alignment of the start of the file coinciding with the start of the

block.

enable disk read after write verify

Set the enable disk read after write verify parameter to off. You will reduce the amount of I/O

that NetWare performs to the disk drive and for some disk device drivers you are enabling them

to perform direct I/O via the directfs nlm. The enable disk read after write feature was necessary

Page 18 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

in the early days of NetWare when disk drives had reliability problems. It is no longer necessary

with the high levels of reliability that disk drives today have. In fact , when enabled, some

NetWare disk device drivers ignore the setting all together.

immediate purge of deleted files

Set the immediate purge of deleted files to on. When immediate purge of deleted files is enabled

the disk space occupied by the file is immediately released and ready for reallocation. In a file

sharing environment this setting is usually set to off so the system administrator can recovery a

file that was accidentally deleted.

file delete wait time

Set the file delete wait time to 1 minute. This is the maximum amount of time that will elapse

after the user deletes a file and the start of the purge process. This setting is irrelevant when

immediate purge of deleted files is set to on.

minimum file delete wait time

Set the minimum file delete wait time to 30 seconds. This is the minimum amount of time that

will elapse after the user deletes a file and the start of the purge process. This setting is irrelevant

when immediate purge of deleted files is set to on.

remirror block size

The remirror block size can be set in 4K increments up to 32K. A setting of 1 = 4K, 2 = 8K … 8

= 32K. This setting defines the size of the block transfer utilized by the NetWare controller

duplexing remirror process. Setting the remirror block size to larger values can reduce the time

necessary to re-synchronize a NetWare controller duplexed volume but at the expense of

additional memory buffers. Typically the setting established by the Compaq SmartStart

installation process is acceptable and should not be changed. If you are not using NetWare

controller duplexing set the remirror block size to 1 to reduce the size of the buffers initially

allocated by the remirror process.

concurrent remirror requests

The concurrent remirror requests setting defines the number of concurrent remirror requests per

logical NetWare partition. The number of outstanding requests can be set from a minimum of 2

up to a maximum of 32. Increasing this value can reduce the time necessary to re-synchronize a

NetWare controller duplexed volume but at the expense of additional memory buffers and

possible higher I/O rates for the NetWare volumes. The total amount of memory used for the

remirror requests is the product of the values for remirror block size and concurrent remirror

requests. Typically the setting established by the Compaq SmartStart installation process is

acceptable and should not be changed. If you are not using NetWare controller duplexing set the

concurrent remirror requests to the minimum value of 2. This reduces the total number of

buffers initially allocated by the remirror process.

mirrored devices are out of sync message frequency

The mirrored devices are out of sync message frequency determines the frequency that NetWare

wakes up the process to verify that the NetWare controller duplexed volumes are in

synchronization. The process can be set to start as frequently as every 5 minutes up to a

maximum of 9999 minutes (approximately 7 days). If the volumes are out of synchronization,

NetWare issues a message to the console and the NetWare remirror process will be initiated.

Typically the setting established by the Compaq SmartStart installation process is acceptable and

should not be changed. If you are not using NetWare controller duplexing set the mirrored

devices are out of sync message frequency to its maximum value of 9999.

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 19

Communications

Communication buffer management is essential to providing stable and reliable network

communication between the server and the server.

maximum physical receive packet size

Set the maximum physical receive packet size to the value recommended by the network card

vendor. Remember to set this parameter to the same value for all network cards on the LAN

segment. Set this to the largest packet size supported by your protocol and network interface

boards. Larger numbers will simply be a waste of memory because the packets will not be

utilizing that memory space, but the operating system will still have it allocated to the packet

buffers. Setting this value too small will increase the traffic on the network, by increasing the

number of packets required to transmit the same amount of information. For Compaq NetFlex

Network Interface Controllers the settings would be 1514 for 10Mbs Ethernet, 2154 for 4Mbs

Token-Ring, and 4202 for 16Mbs Token-Ring.

minimum packet receive buffers

Start the server using the default value for minimum packet receive buffers, monitor it during the

course of a normal period of activity and adjust as necessary. Packet receive buffers are used by

NetWare to buffer up the incoming requests from the network while the processor is busy

servicing other requests. Set this value high enough to avoid having buffers dynamically

allocated during run time. Using the NetWare Monitor nlm, select Available Options,

LAN/WAN information, Available LAN Drivers, select the driver, and note the value for No

ECB available count. If the No ECB available count value is increasing, increase the setting for

the minimum packet receive buffers in the Startup.ncf file and restart the server. You may also

need to increase the setting for maximum packet receive buffers if the setting for minimum packet

receive buffers would exceed the current value.

maximum packet receive buffers

Refer to the discussion of minimum packet receive buffers for more information.

NetWare Partitions

Performance optimization of the NetWare partitions on a SMART or SMART-2 Drive Array can

result in a 20 to 40 percent increase in the drive arrays performance. Use the Compaq Drive

Array Optimization Utility, Cpqdaopt nlm, to determine the correct Hot Fix Redirection Area

settings for the NetWare Partitions. Refer to the Compaq SMART SCSI Array Controller User

Guide and the Compaq SMART-2 Array Controller User Guide for more detailed information.

Upgrading From Previous Sybase Versions to System 11

If you are running a previous version of Sybase SQL Server on NetWare 3.11/3.12 or NetWare

4.0x it is recommended that you upgrade to NetWare 4.10 before upgrading to Sybase System 11.

You can directly upgrade from Sybase SQL Server version 4.2.x to Sybase System 11. Compaq

and Sybase do not recommend or support Sybase System 11 running on NetWare 3.11/3.12, 4.0x

or SMP versions.

Sybase System 11 Configuration

Sybase SQL Server provides the database administrator with a wide variety of configuration

options. The default settings for the

and running But for optimal performance you will need to alter these parameters. For details

tuning these parameters refer to the topics covered later in this paper.

sp_configure

parameters are sufficient to get SQL Server up

Page 20 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

It is possible to configure the parameters such that you cannot start SQL Server. If this happens,

you can use a normal text file editor like Novell Edit to access the sys:\servername.cfg file and set

the offending parameter to a more appropriate value.

sp_configure command

The sp_configure command sets the values for the Sybase SQL Server tunable parameters. Some

sp_configure

restart SQL Server to take effect. Use ISQL to ensure that the

config_value of the items you changed match before permitting additional users onto the system.

total memory

The value for total memory determines the number of Sybase pages that Sybase SQL Server

allocates at startup from NetWare. One Sybase page is equal to 2KB. NetWare memory buffers

are 4KB in size. Therefore it takes 2 Sybase pages to equal one NetWare memory buffer. To

convert Sybase pages to MB simply divide the number of pages by 512. To convert Sybase pages

to NetWare memory buffers simply divide the number of pages by 2. You should set this value

such that there are al least 10% free buffers on a dedicated server and 20% free buffers on a non-

dedicated server. The percentage of free buffers is determined by the following method:

(total cache buffers / original cache buffers) * 100

The values for total cache buffers and original cache buffers are reported by the NetWare

Monitor utility on the General Information screen. The percentage of free cache buffers is also

reported on the Resource Utilization screen.

values take effect dynamically as you change them. Others require you to stop and

sp_configure

run_value and

procedure cache percent

The value for procedure cache is expressed as a percentage of the memory that is used for storing

and executing Sybase stored procedures. Refer to the Memory Tuning section of this document

for information on sizing the procedure cache.

number of devices

The value for devices simply determines the maximum number of Sybase devices allowed. Each

device requires approximately 512 bytes of memory. The memory required for the default setting

of 10 devices is already included in the initial requirement as recommended by Sybase. If you

increase the number of devices beyond 10 you will also have to increase your initial memory

estimate.

number of user connections

The number of user connections can greatly impact the amount of memory required by Sybase

SQL Server. Each user connection requires a minimum of 51KB. This allocation can be effected

by changing the values for default packet size and user stack size. Refer to the discussion of

these topics in this section.

recovery interval in minutes

The recovery interval determines how often the server should do a checkpoint. During a

checkpoint, the SQL Servers data cache area is forcibly written to disk, during which time all

other database activity is suspended. Immediately after a checkpoint, user response times are

slightly faster than normal until the data cache area becomes filled. Once the data cache area

fills, user response times slow down to "normal" levels because of the necessary disk access and

memory management.

Leave the recovery interval setting at its default value unless you are willing to take the risk of

setting it to a higher value. If you set the recovery interval too long, the user response times

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 21

deteriorate and become intolerable when a checkpoint occurs. If you set the recovery interval too

short, it wastes valuable system processor cycles and generates excessive disk I/O.

housekeeper free write percent

The housekeeper free write percent setting determines the percentage of I/O increase above

normal that the housekeeper process can generate. Typically it should be left at its default

setting. When the housekeeper process detects idle CPU time it begins to flush data pages to the

disk at a low priority, in a fashion similar to the checkpoint process. Unlike the checkpoint

process, it does not need to flush all the data pages to disk before terminating. If the housekeeper

flushes all data pages to disk it can request that the checkpoint process issue a checkpoint on the

database. The purpose of the housekeeper process is to reduce the impact of the checkpoint

process. Refer to the Sybase SQL Server Performance and Tuning Guide for more information

on checkpoints and the housekeeper processes.

user log cache size

By increasing the user log cache size you reduce contention on writing to the log device. Setting

it too high wastes memory that could be used for data caching. You will have to experiment with

its setting to obtain the optimal performance on your system.

default network packet size

The default network packet size is 512 bytes. Each user connection is allocated 3 packet buffers,

a read buffer, a read overflow buffer and a write buffer. A total of 1536 bytes of memory is

allocated per user connection. This memory is already included in the initial allocation of 51KB

per user connection. If you increase the size of the default packet size you will have to increase

the per user connection initial allocation accordingly.

max network packet size

By increasing the default value of max network packet size you can reduce the number of packets

being transmitted on the network. Increasing this value from 512 bytes to 4096 bytes reduced the

time required to perform a bulk load of a database. Additionally queries that return large

amounts of data will be more responsive because fewer packets need to be assembled for

transmission from the server to the client.

additional network memory

Each user has 1536 bytes of network memory that is calculated in the base memory requirements

for the user. Additional network memory is necessary when you increase the size of the max

network packet size. Additional network memory is statically allocated at the startup of SQL

Server. As you increase the max network packet size and number of user connections you need to

provide additional network memory. Each user connection requires 3 buffers areas.

32 user connections * 4096 max network packet size * 3 buffers for each user = 393216

bytes

323216 bytes * 2% = 6464 bytes for overhead

323216 bytes + 6464 bytes for over head = 329680 bytes rounded up to next Sybase page =

329728 bytes

In the above example, for 32 user connections with a max network packet size of 4096 require

minimum of 393216 bytes. Add to this the overhead of 2% for the and round up to the next

Sybase page for a total of 329728 bytes of additional network memory.

number of extent i/o buffers

Page 22 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

Increasing the number of extent i/o buffers can speed up the process of creating or re-indexing a

table. If you increase the number of extent i/o buffers you will also have to increase the number

of sort buffers. Only one process at a time can access the extent i/o buffers. If two or more

indexing commands are executing concurrently the first one started gets all the buffers. For this

reason indexing is best done in a serial manner. In out testing, values between 50 and 100 extent

i/o buffers provided the best throughput and minimized the elapsed time for the indexing

activities. Each extent i/o buffer consists of 8 Sybase pages or 16KB. Remember to include this

memory allocation in your initial estimate. Refer to the Sybase SQL Server System

Administration Guide for more information.

number of sort buffers

Increasing the number of sort buffers can reduce the time to create an index or re-index a table.

The value for number of sort buffers should be 8 times the value of number of extent i/o buffers.

Refer to the section in this document on number of extent i/o buffers for more information

number of pre-allocated extents

During BCP load operations, extents (1 extent equals 8 Sybase pages) are allocated from the page

manager. Each request to the page manager generates a log record. By increasing the number of

pre-allocated extents you reduce the number of requests to the page manager and reduce the

number of log records generated resulting in increased throughput of the BCP load operation.

Refer to the Sybase SQL Server System Administration Guide for more details.

stack size

Typically you will not need to increase the user stack size. The default allocation of memory

used by the user stack is included in the initial 51KB per user connection. If you increase the

user stack size, recalculate the per user connection and initial memory requirement estimate.

Refer to the Sybase SQL Server System Administration Guide for details on when to increase the

stack size.

event buffers per engine

Increasing the number of event buffers per engine to 2000, can reduce the performance impact of

running the Sybase SQL Monitor Server. If you are not using Sybase SQL Monitor Server set the

event buffers per engine to one.

Sybase Sybinit Utility

The Sybinit utility is responsible for installing and configuring System 11 components. For the

utility to work properly you need to check a couple of items BEFORE proceeding with the

installation.

The Sybinit utility will use the server name in the generation of unique names for various file

names, database objects, default logins and default passwords. All of these can be changed

during the install process but you can streamline the installation if you define your server name to

be a maximum of eight characters in length. Preferably unique in the first three or four

characters. This is not a limitation of the Sybinit utility itself but a residual effect from the DOS

compatible file naming convention of eight-dot-three characters.

Also if you are using TCP/IP as your network protocol make certain that the file

SYS:\ETC\HOSTS exists and that your servers IP address and name are defined in it. The

Sybinit utility will search this file looking for your server name to use in the definition of the

listener server for TCP/IP.

Sybase Audit Feature

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 23

The Sybase audit trail feature is an optional feature of Sybase SQL Server for environments

where additional security levels are necessary. On a properly configured and tuned server,

enabling this feature will have minimal impact the overall performance of the server.

The audit trail activity is composed almost exclusively of sequential writes and should be isolated

to its own fault tolerant device. The amount of I/O activity to the audit trail database is

dependent upon the level of security implemented. Refer to the section Checking Disk I/O Rate

to determine if you have sufficient I/O bandwidth for the audit device. If you have insufficient

I/O bandwidth, the addition of more disk drives and possibly another SMART Array Controller

will be necessary.

Installing From CD-ROM

To install Sybase System 11 from the Compaq CD-ROM drive you will need to load the

following device drivers either in Startup.ncf or manually before you can access the CD-ROM

drive as a NetWare volume.

Compaq SCSI based CD-ROM drive

LOAD CPQSXPT

LOAD CPQS710

LOAD CPQSCD

LOAD CPQSDSK

LOAD NWPALOAD

LOAD CDROM

…

CD Purge

...

CD Volume List

...

CD Mount 27

...

Volume SYBASE mounted

Compaq IDE based CD-ROM drive

LOAD IDECD.CDM

LOAD IDEATA.HAM

LOAD CDROM

...

CD Purge

…

CD Volume List

...

CD Mount 15

...

Volume SYBASE mounted

Page 24 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

System Management Tools

The key to a maintaining a Sybase SQL Server system in optimal performance is to monitor the

server. Look for any changes in the daily, weekly and monthly workloads that can effect the

throughput of the server. If the system is used in a mission critical role, it is especially important

that the administrator is notified of any fault in the system and that corrective action is taken

quickly. The Novell Monitor nlm utility, Sybase SQL Monitor and Compaq Insight Manager are

to be considered as a good starting point for most environments. There are many third party

utilities that can further enhance the data collection, trend analysis and reporting capabilities of

NetWare, Sybase SQL Server and SNMP system management.

Novell Monitor Utility

The Novell Monitor utility is an excellent tool for determining what is happening in your

NetWare environment. You can monitor some aspects of what effect changes to the Sybase SQL

Server have on the server. For best overall monitoring, tuning and trend analysis of the database

server you should team this utility up with one that is more specific to analyzing Sybase SQL

Server like, Sybase SQL Monitor.

Sybase SQL Monitor

The Sybase SQL Monitor management tool consists of two parts. The first part is the Sybase

Monitor Server which runs on the NetWare server along with Sybase SQL Server. The second

part is the SQL Monitor Client which runs on a Windows based workstation and communicates

to the SQL Monitor Server via Sybase Open Client for Windows.

Compaq Insight Manager

The Compaq Insight Manager (CIM) consists of two parts the server based operating system

agents and a Windows based client. CIM components are located on the Compaq SmartStart and

can be installed at the same time as the operating system.

The Compaq Insight Manager can be configured to do the following:

• provide pre-failure notification of hardware items that are experiencing difficulties operating

at optimal performance levels

• notify you in cases of a catastrophic server hardware or software failure

• check for latest updates to system and disk drive firmware

• check for latest updates of device drivers

• provide performance and resource utilization information

Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers Page 25

Conclusion

The information in this paper is not a complete tuning guide but a supplement to other tuning

information provided by Sybase and Novell. To achieve an optimal configuration, there are

several factors to include. Tuning the application, tuning the hardware, tuning the OS, and

tuning the network are all areas that must be carefully planned and tuned. The tuning process is

iterative and will be done several times to achieve the most optimal performance possible.

We hope that the information provided in this paper will help in this process. The information

given is based on many years of experience in tuning Sybase on NetWare, however, each

configuration is unique. Although all the hints given here have been tested extensively, do not

assume that tuning a specific parameter will always give the desired result. Do not be afraid to

experiment.

We welcome comments on your configurations and experiences to improve our information

products in the future. Please send us any comments or suggestions on the attached form,

attaching addition sheets if necessary. This will help us tailor future information products to

your needs, and will enable us to make future revisions of this document and related new

information products available to you.

Page 26 Configuration and Tuning of Sybase System 11 for NetWare on Compaq Servers

Check List of Recommendations

Initial recommendations for Sybase SQL Server 11.0.1 system running on Novell NetWare 4.10:

• Design your database with performance in mind from the start

• Start with minimum of 64 MB RAM

• Avoid memory fragmentation, do not load and unload unnecessary NLMs and utilities at the

server

• Perform administrative tasks at client workstation whenever possible

• Check revision levels of Novell files, update to newer revisions

• Dedicate the server to Sybase SQL Server, use other servers for file and print services

• Use SMART and SMART-2 SCSI Array Controllers with array accelerator enabled for increased

performance

• Use SMART and SMART-2 SCSI Array Controllers hardware fault tolerance features

• Use 64KB block factor when setting up NetWare partitions

• Disable sub-block allocation and file compression when setting up NetWare partitions

• Use Novell controller duplexing with caution, CPU overhead can impact performance

• Use Sybase database mirroring with caution, CPU overhead can impact performance

• Use database striping methods to balance workload across multiple controllers

• Standardize on one network protocol and frame type

• Use Compaq Insight Manager to monitor system hardware, CPU utilization, EISA bus

utilization, software and firmware revisions

• Load sqlsrvr with the -P option on dedicated systems

• Use Sybase SQL Monitor to monitor CPU utilization, memory allocations and device I/O rates

• Use stored procedures to reduce network traffic and server CPU load

• Use System Configuration Utility setting of Novell NetWare as operating system choice for

Server, SMART and SMART-2 SCSI Array Controller(s)

• Increase the value of the max network packet size to help minimize elapsed times for database

load activities like BCP and reduce CPU workload at the server

• Install the Sybase Sybaudit device on separate disk partition for optimal performance

• Upgrade to NetWare 4.10 before upgrading to Sybase System 11

User Registration/Evaluation Form

Please fill out and return to us this registration/evaluation form to help us keep you up to date with future revisions of this document and related new information products. Your effort will help us improve the quality of the future information products.

Name: Title: Company: Address:

Phone Number:

Please evaluate the quality of this document:

Excellent Poor

Technical Accuracy Organization Clarity Completeness Timeliness

Please indicate the type of environment you have at your site:

Operating Systems RDBMS Processing Type



SCO Open Server



Microsoft Windows NT



IBM OS/2



Novell NetWare



SCO UnixWare



Other:



Microsoft SQL Server



Sybase System 10



Sybase System 11



Oracle 7



Other:

12345 12345 12345 12345 12345



On-Line Transaction Processing



Decision Support



Batch Processing



Other:

Please indicate the type of information you would like us to provide in the future:

Topic Operating Systems RDBMS



Configuration and Tuning



Capacity Planning



Integration Information



Competitive Analysis



Systems Management



Other:

Additional Comments:

Return to:

Database Engineering Compaq Computer Corporation MailCode 090803 20555 SH 249 Houston, Texas 77070



Microsoft Windows NT



Novell NetWare



IBM OS/2



SCO UnixWare



SCO Open Server



Other:



Microsoft SQL Server



Sybase System 10



Sybase System 11



Oracle 7



Other:

Hp COMPAQ PROLIANT 8500, COMPAQ PROLIANT 8000, COMPAQ PROLIANT 4000, COMPAQ PROLIANT 800, COMPAQ PROLIANT 6400R Configuration and Tuning of Sybase System 11 for NetWare

Specifications and Main Features

Frequently Asked Questions

User Manual