HP COMPAQ PROLIANT 5500 SERVER User Manual

White Paper

July 1998

ECG099/0698

Supporting Thousands of Sybase ASE

Prepared by Database Technology and Solutions Center

Compaq Computer Corporation

Contents

INTRODUCTION.......................... 3

BACKGROUND...........................4

CONFIGURATION ISSUES &

USEFUL FEATURES................. 10

Useful Documentation .............10

Useful Tools ...........................11

User Connections...................12

Memory Issues.......................14

APPLICATION SPECIFIC

ISSUES .....................................20

Network Resources................. 20

Logins and Database Users....21

Integrated E-Mail Helps Support Large User

Communities...........................23

TEST APPROACH..................... 25

Capacity and Performance

Considerations........................ 25

ASE Parameter Settings .........25

ProLiant Server

Configurations ........................25

TEST RESULTS ........................26

Users on Compaq ProLiant Servers

The Compaq Database Technology and Solutions Center (DTSC) has joined forces with Sybase to identify techniques and practices used in supporting thousands of concurrent Sybase ASE users on Compaq ProLiant Servers.

With the continued growth of Microsoft Windows NT based applications and solutions, there is an increasing need to support enterprise class applications on Windows NT systems and servers. The need to support large user communities creates challenges not only for the RDBMS environment, but the entire information technology (IT) infrastructure. This White Paper evaluates the capability of Sybase’s Adaptive Server Environment (ASE) in supporting enterprise class applications.

A series of tests were performed to address key issues such as scalability, performance, and system requirements as well as management and administration issues. The tests focused on the following objectives:

n Determine the number of concurrent users that Sybase ASE can

support.

n Identify the performance implications for supporting thousands of

users.

n Determine the system memory requirements when supporting

thousands of concurrent Sybase ASE users.

n Identify system management and database administration issues that

should be considered when supporting large user communities.

The test results conclude that Sybase ASE 11.5.1 with it’s enhanced features and functionality is capable of supporting large user communities. Key findings are listed below:

n Sybase ASE provides the capacity and functionality necessary to

support thousands of concurrent users Windows NT enterprise.

q Sybase ASE achieved 5,000 concurrent users under load

n Other Sybase ASE enterprise performance and scalability features

q Enhanced database cache management and proxy login support for

middle-tier components.

q Unified Sybase/NT login (single password for both) q Integrated E-mail support for sending messages and receiving

incoming queries

q Integrated Sybase/NT login administration using Extended Stored

Procedures

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 2

Notice

The information in this publication is subject to change without notice and is provided “AS IS” WITHOUT WARRANTY OF ANY KIND. THE ENTIRE RISK ARISING OUT OF THE USE OF THIS INFORMATION REMAINS WITH RECIPIENT. IN NO EVENT SHALL COMPAQ BE LIABLE FOR ANY DIRECT, CONSEQUENTIAL, INCIDENTAL, SPECIAL, PUNITIVE OR OTHER DAMAGES WHATSOEVER (INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION OR LOSS OF BUSINESS INFORMATION), EVEN IF COMPAQ HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

The limited warranties for Compaq products are exclusively set forth in the documentation accompanying such products. Nothing herein should be construed as constituting a further or additional warranty.

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 or product quality or correctness, nor does it ensure compliance with any federal state or local requirements.

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

Compaq, Contura, Deskpro, Fastart, Compaq Insight Manager, LTE, PageMarq, Systempro, Systempro/LT, ProLiant, TwinTray, ROMPaq, LicensePaq, QVision, SLT, ProLinea, SmartStart, NetFlex, DirectPlus, QuickFind, RemotePaq, BackPaq, TechPaq, SpeedPaq, QuickBack, PaqFax, Presario, SilentCool, CompaqCare (design), Aero, SmartStation, MiniStation, and PaqRap, registered United States Patent and Trademark Office.

Netelligent, Armada, Cruiser, Concerto, QuickChoice, ProSignia, Systempro/XL, Net1, LTE Elite, Vocalyst, PageMate, SoftPaq, FirstPaq, SolutionPaq, EasyPoint, EZ Help, MaxLight, MultiLock, QuickBlank, QuickLock, UltraView, Innovate logo, Wonder Tools logo in black/white and color, and Compaq PC Card Solution logo are trademarks and/or service marks of Compaq Computer Corporation.

Microsoft, Windows, Windows NT, Windows NT Server and Workstation, Microsoft SQL Server for Windows NT are trademarks and/or registered trademarks of Microsoft Corporation.

NetWare and Novell are registered trademarks and intraNetWare, NDS, and Novell Directory Services are trademarks of Novell, Inc.

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers

White Paper prepared by:

Sybase Corporation

Database Technology and Solutions Center

AUTHOR: Edward Bueche (Sybase Corporation)

First Edition (July 1998)

Document Number ECG099/0698

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 3

INTRODUCTION

This document outlines basic issues, techniques, and considerations for supporting thousands of users with Sybase ASE 11.5.1 for Windows NT.

Thousands of concurrent users challenge both the performance and the ability to manage 2-tier and 3-tier systems. Each additional user connection consumes more memory and processor capacity. Also, provisioning and supporting thousands of users can turn simple administrative tasks into tedious, error prone chores. System management problems begin with client setup where geographically dispersed machines can have different operating systems and different application versions, and continue to the database server where not only scalability, but also security, issues must be addressed.

The Adaptive Server Enterprise 11.5.1 provides the capacity and functionality necessary to support such installations in the Windows NT Enterprise. These features include support for:

• Thousands of simultaneous connected users

• Up to 3GB memory for data cache; configurable procedure cache and task stacks

• Integrated E-mail which can send and receive E-mail

• Unified Sybase/NT login (single password for both)

• Integrated Sybase/NT login administration using Extended Stored Procedures

• Proxy authorization for middle tier servers

The Enterprise support of Sybase Adaptive Server Enterprise for Windows NT is further enhanced with the new parallel features 11.5 (Parallel Query, sort, index creation, and load), mixed workload support (Engine Groups, Resource Governors), and the Active/Active Cluster support for high availability systems.

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 4

Sybase ASE 11.5

Server/RDBMS

BACKGROUND

Because of the current trend toward an increasing number of users, system architects must employ robust 2tier or 3-tier systems as appropriate. A 2-tier client/server system is shown below. The user community logs directly into the DBMS server. In this case, ASE manages all of the user connections and their resources. The primary benefit of this architecture is simplicity: there are fewer server software components to administer and troubleshoot. ASE supports thousands of simultaneous connections in this type of environment. More details on an example of this setup will be covered later.

Clients Interact Directly

with

Windows NT Server

Machine with

Figure 1 – Typical 2-Tier Client/Server Architecture

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 5

Server/RDBMS

There are two downsides of this architecture in environments with thousands of users. First, ensuring that all client machines have the proper application and DBMS software can be challenging. Users can be geographically dispersed with different versions of an operating system (Windows 3.1, Windows 95, Windows NT, Mac OS, UNIX). Second, although not all users will be active at one time, they are likely to be consuming extra memory on the server even if their connection is idle.

Three-tier systems offer several features that address some of the disadvantages of 2-tier client/server systems in large-scale deployments. An example of a 3-tier client/server system is shown below. Clients log into a middle tier server (web server, transaction manager, or application specific server) and this server logs in to the DBMS server on behalf of the clients. One advantage of this architecture is simplified client administration (in many cases the only software the client needs is a web browser the rest is downloaded from the middle tier server). This is a recent development in middle tier technology (and is a chief advantage of the Sybase Jaguar Component Transaction Server). The main function of many older middle tier servers was to contain the business logic of the application in a central server.

Clients interact

indirectly with

NT Server machine

with Sybase ASE

11.5.1

NT application or transaction Server machine

(with Sybase Jaguar or BEA Tuxedo)

Figure 2 – 3-Tier Architecture

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 6

Another advantage is the ability to share (or multiplex) many user connections into fewer database connections. That is, the middle tier server can maintain a smaller pool of permanent connections to the database. It then directs any incoming client requests down one of the permanent connections. This avoids any new connection setup and allows the DBMS to have fewer connections therefore reducing the DBMS memory requirements. This typically works well because in systems with thousands of concurrent connections, most connections are typically idle at any one time. For example, the TPC-C benchmark run with ASE 11.5 on Windows NT supported 6,400 users through BEA’s Tuxedo middle tier server multiplexed to less than 200 Sybase connections

There are, of course, some security issues when sharing a DBMS connection among different users. The proxy authorization feature in ASE allows the shared DBMS connection to change its access and security rights depending on which user is being served. Hence, multiple users across different applications can effectively and safely share the common pool of DBMS connections in the middle tier server.

However, this sharing (or multiplexing) of connections is by no means an essential component of 3-tier systems. A middle tier server could create one or more DBMS connections for each user connection. Hence, a large number of users could result from either 2-tier or 3-tier architectures.

In a mobile replication environment for Adaptive Server Enterprise, a large user community could arise. SQL Remote is the name of the mobile, “occasionally connected” data replication mechanism offered by ASE. A single ASE site might support thousands users of a mobile workforce via SQL Remote. In the following example, since the users are not connected all the time, it is unlikely that all users would log in at once. Hence, the number of concurrent users in the database is going to be smaller than the total number of actual users defined in the database. This architecture is optimal for environments with a mobile workforce, such as a direct sales force, and for environments where branch offices with minimal hardware setup only need to connect occasionally to the central office.

This benchmark published March 1997 listed Sybase SQL Server 11.1 which was the name of Adaptive Server Enterprise 11.5 before it was renamed. Throughput 7,650 TPM-C, $82 per TPM-C, Availability 9/97. TPC-C is a trademark of the Transaction Processing Council.

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 7

NT Server Machine With

Adaptive Server

Anywhere Clients

perform replication to

ASE via E-Mail

Sybase ASE 11.5 at

Center of Replication

Enterprise E-Mail

Infrastructure

Figure 3 – ASE Replication with E-Mail Clients

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 8

clients

Enterprise

Even though the number of simultaneous users may not be large in this case, several administrative issues are common to ones that show up in any large user community. For example, suppose that a planned outage occurs, such as a software upgrade of a business application. The DBA needs to notify every user of the database. Sybase Adaptive Server Enterprise is integrated with E-mail, so the DBA can easily notify the database user community of any pending maintenance. This feature is also extremely useful in serving Internet applications that want to contact potential customers or newly registered customers via E-mail.

E-mail messages or

query results sent to

Windows NT Server

Machine With Sybase

ASE 11.5 able to Send

and Receive E-mail

E-mail

Infrastructure

Figure 4 – Remote User Database Access

Furthermore, since Sybase ASE can receive e-mail queries and send results via e-mail, a large heterogeneous community of users can be served using just the existing corporate e-mail infrastructure. Although response time is probably the longest of all configurations discussed above, client setup overhead is very low. Hence, this architecture can be extremely useful for applications with large user communities, with mobile users, with users that are difficult to administer, and with users with relaxed response time requirements.

Supporting Thousands of Sybase ASE Users on Compaq ProLiant Servers 9

System availability becomes a significant issue when serving a large user community. By definition any hardware or software outage could affect thousands of users. Sybase Adaptive Server Enterprise for Windows NT supports an Active/Active configuration using Microsoft Cluster Server (covered in more detail in the Sybase white paper: Configuring Sybase Adaptive Server Enterprise for High Availability: Microsoft Cluster Server) and has several features to minimize database recovery time. The fast recovery features include using asynchronous pre-fetch during recovery and free checkpoints using the housekeeper internal DBMS task.

The more concurrent users supported on a single NT system, the more likely that these users will be performing very different tasks. Some may be Online Transaction Processing (OLTP) users with strict response time requirements, while others will be executing Decision Support (DSS) queries against the database. Parallel query allows DSS users to consume far more DBMS resources than OLTP users.

Adaptive Server Enterprise ensures excellent DSS and OLTP performance with its mixed workload features that include: the Logical Memory Manager, the Logical Process Manager, and Resource Governors. A Logical Memory Manager (LMM) lets you assign database objects to an unlimited number of named caches that you configure. High priority data remains in cache for improved response times. The Logical Process Manager (LPM) allows you to assign CPU resources to individual applications so low priority work does not impact high priority work, ensuring more predictable performance (also known as Engine Groups). Resource Governors prevent run away queries that can tie up resources needed for rapid response rates. Managing resource consumption for queries, batches, or transactions allows you to specify limits based on I/O cost, elapsed time, or number of rows returned.

+ 18 hidden pages