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
Configuration and Tuning of
Sybase SQL Server 11 for
SCO UnixWare 2.1 on
Compaq Servers
White Paper
________________________________________________________________
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 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.
© 1997 Compaq Computer Corporation
All rights reserved. Printed in the USA
COMPAQ, SYSTEMPRO, PROLIANT, PROSIGNIA
Registered US Patent and Trademark Office.
Configuration and Tuning of Sybase 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 Function and Benefit in a Sybase SQL Server Environment ....................... |
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 Manager (CIM) ......................................................................................................... |
21 |
Conclusion .......................................................................................................................................... |
22 |
References ........................................................................................................................................... |
22 |
ã 1997 Compaq Computer Corporation, Al Rights Reserved |
Doc No 541A/0697 |
Configuration and Tuning of Sybase SQL
Server 11 for SCO UnixWare 2.1 on
Compaq Servers
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, internal 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.
Compaq Computer Corporation |
Transaction Processing Performance Council |
Database Performance Engineering |
c/o Shanley Public Relations |
P.O. Box 692000 |
777 North First Street, Suite 6000 |
Houston, TX 77269-2000 |
San Jose, CA 95112-6311 |
http://www.compaq.com |
http://www.tpc.org |
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 Performance and Tuning Guide
·Compaq Tech Communiqué ‘COMPAQ INSIGHT Server Management’
ã 1997 Compaq Computer Corporati, All Rights Reserved |
Doc No 541A/0697 |
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 the 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â/100MHz and Pentiumâ/133MHz processors, using 1GB RAM. Figure 2 shows scalability on the Proliant 5000 with PentiumProâ/166MHz processors, using 1GB RAM and 2GB RAM in the server. The PentiumProâ/166MHz has a 512KB level 2 cache. Figure 3 shows the scalability on the Proliant 6000 with PentiumProâ 200MHz processors with 512K level 2 cache and 4GB RAM. Figure 4 shows memory scalability on the Proliant 6000 with 4 PentiumProâ 200MHz processors with 512K level 2 cache.
ã 1997 Compaq Computer Corporation, All Rights Reserved |
Doc No 541A/0697 |
Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers Page 3
Figure 1: CPU Scalability of the Proliant 4500
|
|
CPU Scalabilty on Proliant 4500 |
|
|
3 |
|
|
Rate Normalized |
2.5 |
|
|
2 |
|
|
|
1.5 |
|
100MHz Pentium |
|
Transaction |
|
|
|
1 |
|
133MHz Pentium w/ 512K |
|
|
|
||
|
|
cache |
|
0.5 |
|
|
|
|
|
133MHz Pentium w/ 2M |
|
|
|
|
|
|
|
|
cache |
|
0 |
|
|
|
1 |
2 |
4 |
|
|
Number of CPUs |
|
Figure 2: CPU Scalability for Proliant 5000
|
|
CPU Scalabilty on Proliant 5000 with 1GB vs. 2GB RAM |
||
|
3 |
|
|
|
|
2.5 |
|
|
|
Normalized |
2 |
|
|
|
|
|
|
|
|
Rate |
1.5 |
|
|
|
|
|
|
|
|
Transaction |
1 |
|
|
166MHz PentiumPro w/ 1GB |
|
|
RAM |
||
|
|
|
||
|
|
|
166MHz Pentium Pro w/ 2GB |
|
|
0.5 |
|
|
RAM |
|
0 |
|
|
|
|
1 |
2 |
3 |
4 |
|
|
|
Number of CPUs |
|
ã 1997 Compaq Computer Corporation, All Rights Reserved |
Doc No 541A/0697 |
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 Scalability for Proliant 6000
|
3 |
|
|
|
Normalized |
2.5 |
|
|
|
2 |
|
|
|
|
|
|
|
|
|
Rate |
1.5 |
|
|
|
|
|
|
|
|
Transaction |
1 |
|
|
|
0.5 |
|
|
|
|
|
|
|
|
|
|
0 |
|
|
|
|
1 |
2 |
3 |
4 |
Number of CPUs
Figure 4: Memory Scalability for Proliant 6000
Memory Scalability for ProLiant 6000
|
1.6 |
|
|
|
Normalized |
1.4 |
|
|
|
1.2 |
|
|
|
|
1 |
|
|
|
|
Rate |
0.8 |
|
|
|
|
|
|
|
|
Transaction |
0.6 |
|
|
|
0.4 |
|
|
|
|
0.2 |
|
|
|
|
|
|
|
|
|
|
0 |
|
|
|
|
1GB |
2GB |
3GB |
4GB |
|
|
|
Memory |
|
ã 1997 Compaq Computer Corporation, All Rights Reserved |
Doc No 541A/0697 |
Configuration and Tuning of Sybase SQL Server 11 for SCO UnixWare 2.1 on Compaq Servers Page 5
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. PentiumProâ-based systems have become the standard for application servers, especially the PentiumProâ 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.
Before upgrading the system processor(s), 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(s), upgrading to a higher speed processor, or adding another processor can dramatically improve performance.
CPU Tuning
To monitor CPU performance and determine whether your system is CPU bound or not, you can run the UnixWare performance monitor, rtpm. This should be run during a normal workload to get an accurate 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 characteristics:
·Most of the CPU utilization is in user mode (%usr). Again, this is verified by running sar or rtpm and looking at the percentage of CPU time spent in system and in user time. System time (%sys) can be thought of as operating system overhead such as time spent in the I/O subsystem or in system calls. The higher the percentage of user to system time that 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 |
%usr |
%sys |
%wio |
%idle |
09:00:15 |
82 |
17 |
0 |
1 |
09:00:20 |
83 |
17 |
0 |
0 |
09:00:25 |
80 |
18 |
0 |
2 |
09:00:30 |
82 |
17 |
0 |
1 |
09:00:35 |
81 |
18 |
0 |
0 |
Average |
82 |
17 |
0 |
1 |
The above example shows a system that is CPU bound 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 performance from it because there is no more processing power left on the CPU(s). If your database and application are well tuned, and if there is no idle time and
ã 1997 Compaq Computer Corporation, All Rights Reserved |
Doc No 541A/0697 |
Loading...