Hp COMPAQ PROSIGNIA 500, COMPAQ PROSIGNIA 300, COMPAQ PROSIGNIA 720, COMPAQ PROLIANT 2000, COMPAQ PROSIGNIA 740 Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1

Config uration and Tuning of

Sybase SQ L Server 11 for

SCO UnixWare 2.1 on

Compaq Servers

Whit e Pa per

________________________________________________________________

Prepared By

Database Engineering

Compaq Computer Corporation

June 1997

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 INCIDE NT AL 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 warr a nties.

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.

Configurat i o n a nd Tuning of Syba se SQL Server 11

for SCO UnixWare 2.1

on Compaq Servers

Second Edition (June 1997)

Document Number 541A/0697

Compaq Computer Corporation

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Page i

Table of Contents

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

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

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

CPU Scalability............................................................................................................................ 2

CPU Tuning................................................................................................................................. 5

Disk Subsystem Planning .................................................................................................................... 6

Array Accelerator: Its Fu nction an d Benefit i n a Sybase SQL Server E nvir onmen t ....................... 6

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

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

Layout of Tables and Files............................................................................................................ 12

Checking Disk I/O Rate................................................................................................................ 13

Enabling Asynchronous I/O.......................................................................................................... 14

Memory Tuning .................................................................................................................................. 15

Initial Memory Recommendations for Sybase ............................................................................... 15

UnixWare Patches for 4GB Support and Sybase Support............................................................... 15

UnixWare Shared Memory Parameters......................................................................................... 15

UnixWare PSE Feature................................................................................................................. 17

UnixWare User Process Capacity Parameters................................................................................ 18

Sybase Installation Issues.....................................................................................................................18

Installation with CD-ROM........................................................................................................... 18

Sybase Devices.............................................................................................................................18

SQL Server 11 Configuration and Tuning Parameters......................................................................... 19

sp_configure and Configuration file (

servername.cfg

)................................................................... 19

Recovery Interval in Minutes ........................................................................................................ 19

Number of User Connections........................................................................................................19

Total Memory...............................................................................................................................20

Procedure Cache Percent .............................................................................................................. 20

SQL Server Clock Tick Length..................................................................................................... 21

HouseKeeper Free Write Percent...................................................................................................21

Named Data Caches......................................................................................................................21

Large I/O...................................................................................................................................... 21

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

Compaq Insight Man a ger (CIM) .........................................................................................................21

Conclusion .......................................................................................................................................... 22

References........................................................................................................................................... 22

 1997 Compaq Computer Corporation, Al Rights Reserved Doc No 541A/0697

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 on UnixWare 2.1 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 for UnixWare 2.1 and is a result of numerous performance tests executed within the context of an industry-standard TPC-C benchmark, intern al benchmarking for hardware development and optimization, and analyzing customer-reported expectations, performance trends, and solutions.

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 SQL

Server 11 for SCO UnixWare 2.1 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 are listed below:

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

184206-001

• Sybase SQL Server System Administration Guide

• Sybase SQL Server Performan ce and Tun i ng Guide

• Compaq T ech C omm u niqué ‘COMPAQ INSI GHT Ser ver Ma nagemen t’

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

Page 2

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Tuning Goals

In order to achieve the best performing system possible there are several factors which must be reviewed. These include optimization of the hardware, the Sybase SQL Server, the operating system and the application software. This paper will focus on the hardware, Sybase SQL Server and the OS. It is also important to tune the Sybase application to take advantage of the system. Due to the diversity of database applications, they are beyond the scope of this paper. This paper will discuss CPU scalability and tuning, disk controller optimization and I/O tuning, memory tuning, and network tuning. Also, specific Sybase and UnixWare configuration and tuning issues will be presented throughout the paper.

System Processor Planning

This section is provided to demonstrate the scalability of various processor configurations on the Compaq Proliant family of servers and to provide you with some performance information necessary to determine the best configuration for your environment.

CPU Scalability

The performance information presented in this section was gathered on three Compaq Proliant models, Proliant 4500 and Proliant 5000 and Proliant 6000, with different system processor configurations and different amounts of memory. All configurations had one aspect in common: all were CPU bound.

The tests run were all disk-intensive tests. Disk-intensive tests are designed to access th e entire range of a database that is many times larger than the data cache of the server. The net effect is that only a very small portion of the database can fit into the data cache at any point in time, and a large amount of physical I/O is generated in addition to the transaction log I/O. Such an environment tests heavily the CPU, memory, and the disk subsystem.

The tests generated a large number of update-intensive transactions and utilized heavily the transaction log. No hardware or software options were disabled to sacrifice data integrity of the system.

Figure 1 shows scalability on the Proliant 4500 with Pentium processors, using 1GB RAM. Figure 2 shows scalability on the Proliant 5000 with PentiumPro PentiumPro 6000 with PentiumPro



/166MHz processors, using 1GB RAM and 2GB RAM in the server. The



/166MHz has a 512KB level 2 cache. Figure 3 shows the scalability on the Proliant



200MHz processors with 512K level 2 cache and 4GB RAM. Figure 4

shows memory scalability on the Proliant 6000 with 4 PentiumPro



/100MHz and Pentium/133MHz



200MHz processors with

512K level 2 cache.

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure 1: CPU Scalability of the Proliant 4500

CPU Scalabilty on Proliant 4500

2.5

Page 3

1.5

Transaction Rate Normalized

0.5

124

Number of CPUs

Figure 2: CPU Scalability for Proliant 5000

CPU Scalabilty on Proliant 5000 with 1GB vs. 2GB RAM

2.5

1.5

100MHz Pe nt ium

133MHz Pentium w/ 512K cache

133MHz Pentium w/ 2M cache

Transaction Rate Normalized

0.5

1234

Number of CPUs

166MH z PentiumPro w / 1GB RAM

166MH z Pentium Pro w/ 2GB RAM

Page 4

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Figure 3: CPU Scalability for Proliant 6000

CPU Scalabi lity for Proliant 6000

2.5

1.5

Transaction Rate Normalized

0.5

1234

Number of CPUs

Figure 4: Memory Scalability for Proliant 6000

Memory Scalability for ProLiant 6000

1.6

1.4

1.2 1

0.8

0.6

0.4

Transaction Rate Normalized

0.2 0

1GB 2GB 3GB 4GB

Memory

Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers

Initial CPU Re c ommendations

The choice of the rig ht system p rocessor d ep ends on your envir onmen t. As t echnol ogy evolves, more powerful processors are becoming available, pushing performance to new levels at very competitive costs per processing unit. PentiumPro application servers, especially the PentiumPro



-based system s have become the sta ndard for



166MHz and 200MHz processors.

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



processors for smaller departmental systems, and PentiumPro processors for higher demand systems. If your environment has many concurrent users, you should evaluate benefits of multiprocessing.

Befor e upgr a ding the system processor(s), you sh ould closely monitor performance of the system and tune it from the software perspective. If the performance bottleneck is in software, har dware upgrades can only par tially improve per formance. 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(s), upgr a ding to a higher speed processor, or adding another processor can dramatically improve performance.

CPU Tuning

To monitor CPU performan ce and determine whether your system is CPU bound or not, you can run the UnixWare per formance monitor, rtpm. This should be run during a normal workload to get an accur ate reading. You also may use the sar utility to save the information to a file. A system that is tuned well will have the following CPU char acteristics:

Page 5



• Most of the CPU utilization is in user mode (%usr). Again, this is verified by runnin g

and looking at the percentage of CPU time spent in system and in user time. System

rtpm

sar

time (%sys) can be thought of as operatin g system overhead such as time spent in the I/O subsystem or in system calls. The higher the percentage of user to system time th at you have, the better. For a system performing mostly Sybase processes, 83 %usr and 17 %sys is a very good split, assuming there is no CPU idle time. The key is that Sybase should be getting most of the CPU time. Here is some sample output from

sar -u

> sar -u 5 5

09:00:10 09:00:15 09:00:20 09:00:25 09:00:30 09:00:35

Average

%usr %sys 82 17 83 17 80 18 82 17 81 18

82 17

%wio %idle 01 00 02 01 00

The above exam ple shows a system that is CPU bou nd if all other areas had already been tuned and optimized for performance. CPU bound means that in spite of your efforts to tune the system, you cannot get more per formance from it because there is no mor e processin g power left on the CPU(s). If your database and application are well tuned, and if there is no idle time an d

+ 18 hidden pages