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

...

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.

+ 21 hidden pages