White Paper

La Crosse Management Systems, Inc.

Performance Testing

May 2003

La Crosse Nextgen SQL Server Strategy

Abstract

For Nextgen™ installations with twenty or more users, La Crosse Management Systems, Inc. recommends a two-tier client/server arrangement, with separate drive channels and arrays for the OS, the application, the data files, and the log files.  Theory and real-world testing show best performance when configuring each channel with the RAID level suited to its respective task:  RAID 10 should be used for the database channels, while RAID 5 should be used for the application/OS channels.

1.0 Background

1.1 La Crosse Nextgen™ Background

Nextgen™ is a fully graphical, Windows-based application targeted to office equipment dealers and resellers.  Nextgen™ is developed using a modeling tool from Computer Associates International, Inc. called Advantage: Plex.  Plex allows for rapid development of client/server-based applications, which can be built to a variety of platforms.  La Crosse currently uses Plex to build Windows C++ server functions, MS SQL Server database objects, and Windows C++ client functions.

1.2 Client/Server or Two-Tier Architecture Background

Client/server or two-tier architecture divides the functions of an application between a powerful computer, known as a server, and many less powerful computers connected to it, known as clients.  In this model, the server handles database storage and access, while the clients handle the presentation of information for the user interface.  Application logic (the business rules that bring relationships and meaning to raw data, turning the data into useful information) can theoretically exist on both the client tier and the server tier.  In practice, the server tier typically handles the largest amount of the application logic/processing, with the clients handling only small pieces of logic such as basic input validation.  This idea describes the La Crosse Nextgen™ system well.

1.3 RAID Background

A Redundant Array of Independent (or Inexpensive) Disks, or RAID, provides a way to store data that can increase performance, provide redundancy, or both.  RAID can increase storage performance through striping – the act of dividing sequential data across multiple hard drives to spread the load and increase total throughput.  RAID can provide redundancy, or fault tolerance, through mirroring or parity.  Mirroring achieves redundancy through duplicating all writes to a second, backup drive – the “mirror.”  Parity achieves redundancy through computing checksums that can reconstruct data in the event of a lost drive.  Because writing data with parity information involves computations, it will always be a slower method to redundancy than mirroring.  RAID can be configured in many different levels, with each combining the options for performance and redundancy in its own unique way.  The most common RAID levels are 0, 1, 3, 5, and 10.  For the purposes of this discussion please note that the alias “RAID 10” is used to mean either a RAID 1+0 or RAID 0+1 configuration.  The differences between the 1+0 and 0+1 configurations are beyond the scope of this paper.  Both configurations are commonly referred to as RAID 10.

1.4 Database Management System Background

A DBMS combines the organization of data files; log files, data interfaces, monitoring tools, and security within a single application package.  A modern DBMS handles changes to data in a controlled, multi-step process involving logs to assure data integrity in case of disaster or system failure.  Updating, inserting, or deleting records results in a check on the validity of the change, a preliminary writing of an intention to change data to a log, the actual changing of the data files, and finally a confirmation that the data was successfully changed in the log.  As you can see, this is a very write-intensive process, divided between the log and the data files.  If the changes do not complete successfully or in their entirety, this process allows for an easy rollback to the pre-change state.

2.0 La Crosse Nextgen™ System Needs Assessment

2.1 The Application Logic Role of the Server

The Nextgen™ server will be running the Plex dispatch service, which acts as a “traffic cop” between the clients, the server application logic, and the DBMS.  The dispatch service takes requests from clients, calls the appropriate server functions, and passes information from the server functions back to the clients.  The calling and subsequent loading of the server functions is the primary disk-intensive task of this role, and it is always a read-only task.

2.2 The DBMS Role of the Server

After the appropriate server functions are called, but before these functions pass information back to the dispatcher, the server functions must retrieve data for processing or write data for storage, using the interfaces provided by the DBMS.  When using DBMS interfaces for data retrieval, the server is engaged in a read-only task.  However, when writing changes (inserting new records, changing existing records, deleting records) the server is engaged in read-write activity, with the activity very write-intensive towards the end.  As mentioned in section 1.4, records will be read to check the validity of the proposed change, and then records and entries will be written alternately to the log and data files.

3.0 RAID Level Explanations

3.1 RAID 0

RAID 0 uses disk striping with no redundancy.  It provides excellent performance.  Some people do not consider RAID 0 a “true” RAID because no redundancy is provided.

3.2 RAID 1

RAID 1 uses mirroring, the simplest way to achieve redundancy.  There are no performance benefits, as striping is not used.

3.3 RAID 3

RAID 3 uses striping for performance, and parity written to a dedicated drive for redundancy.  RAID 3 offers better performance than RAID 5 as block sizes increase.

3.4 RAID 5

RAID 5 also uses striping for performance and parity for redundancy.  However, RAID 5 also stripes its parity information across all available drives.  RAID 5 is excellent for smaller reading operations, but when writing larger blocks RAID 5 can be very slow.  When writing data, a RAID 5 controller must calculate how it will stripe the data itself, the parity information itself, and how it will stripe the parity information.

3.5 RAID 10

RAID 10 is a combination of RAID 0 and RAID 1 – where at least two disks are striped across, and at least two other disks are used as mirrors for redundancy.  Mirroring information for redundancy requires much less processing while writing than computing parity.  Striping provides excellent performance.

4.0 La Crosse Recommendations

4.1 RAID 5 for the OS and Application Channels

As mentioned in section 2.1, the application logic role of the server is primarily a read-only one in terms of disk usage.  A RAID level that offers striping for performance should be a primary concern, with redundancy a secondary concern.  RAID 5 and RAID 3 offer both at excellent cost.  RAID 5 handles the smaller reads associated with modern operating systems better.

4.2 RAID 10 for the Database Channels

As mentioned in section 2.2, the DBMS role of the server is very write-intensive.  The primary goal is to achieve the best write performance, while still maintaining redundancy.  RAID 10 fills this role perfectly.

The following links provide supporting information on RAID 10’s suitability for a DBMS:

• Performance Tuning Checklist 4.5

• Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data Warehousing

• Capacity and Storage Management (scroll down to the Raid article)

4.3 Splitting of the DBMS Data and Log Files Between Two Separate Channels

Because the writing and logging of data in a DBMS are split between at least two separate files, and because these different writes happen so quickly as to be almost simultaneous, great performance gains are to be had by splitting the data and log files between their own separate channels.  This is very common in high performance DBMS arrangements.

4.4 General Channel Recommendations

In order of preference, but descending order of price:

4.4.1 Four RAID channels: one each for the OS, applications, DBMS data, DBMS log

4.4.2 Three RAID channels: one for the OS and applications, one each for the DBMS data and DBMS log

4.4.3 Two RAID channels: one for the OS and applications, one for the DBMS data and DBMS log

Note that running a multi-channel Storage Area Network (SAN) is often the easiest way to achieve these goals in terms of setup, maintenance, and cost assessment.

4.5 Hot-Swappable Drives

Hot-swappable drives are used to replace a failed drive within your array, without having to shut down the server. This requires supported hardware and software. This dramatically increases uptime for your server. For maximum uptime, La Crosse recommends all drive arrays contain hot-swappable hard drives.

4.6 True 100 Mb Connections to Full Clients

The connections from the server to the clients should not be overlooked, especially in the case of “full” clients (non-Terminal Server or non-Citrix arrangements).  La Crosse recommends true 100 Mb connections to all full clients.

5.0 Performance Benchmarks

The following are benchmarks La Crosse technical personnel have documented regarding this issue, as well as comments from clients operating Nextgen™ systems with RAID 10 drive arrays on their database volumes.

We benchmarked the following at a Nextgen™ customer site running a single RAID 5 array:

Following this test we transferred (with obvious permissions) the original customer’s database to a new Nextgen™ customer adhering to our published configurations.  We then went back to the original customer and executed an SQL trace for 5 minutes.  We were able to transfer the trace and execute it at the new customer’s site to simulate the database load of the original customer’s 65-user license.  While executing the trace we observed the following:

6.0 Customer Comments

Peter Casper - Office Technology Inc., Neenah, WI

“This box has a raid controller with 2 18 GB drives attached and is also attached to a SAN box via fiber. The internal drives are mirrored as are the boot (C:) drive containing only the operating system, SQL Server, antivirus, etc. On the SAN, we have a logical E: drive in a RAID 10 configuration that is dedicated to applications - Plex and NextGen, a logical F: drive in a RAID 10 configuration dedicated to SQL data files, and a logical G: drive in a RAID 10 configuration dedicated to SQL log files. This gives us the advantages mentioned in your white paper of splitting the I/O over essentially 4 separate controllers.

Another note, we had been hosting our GoldMine database on the SQL server also and last night I moved it to another SQL server because Goldmine was locking up for up to 5 minutes several times throughout the day. It's too early to tell if this solved the problem but I would be inclined to recommend putting other SQL apps on their own server anyway.  

The other thing that we did was replace our network infrastructure with new switches and new cat 5 cables to all of the direct attached workstations to ensure that they would communicate at 100Mb. This also included a 1Gb backbone between the servers. When this was completed, we noticed a dramatic increase in performance. For instance, a NextGen update used to take about 10 minutes per workstation and now it is usually done in under a minute.”

Mark Solomon - The Office Equipment Company, Rochester, NY

“Well what can I say?  It sounds good to me. It sounded good to me a year and a half ago when I built up the server.  It’s definitely the best configuration I've found for SQL.

Keep in mind that we ran SQL for our old system also, so in total we've been running SQL for about 4 years experience.”

Len Sandstrom - Wagers Business Systems, St. Paul, MN

“We have upgraded our Nextgen Servers to adhere to La Crosse specifications and I have documented our results.

Yes, speed has increased tremendously for our people.

The people that I did ask said it was many times faster for them. Sandy spends her time in parts doing inventory transfer. Denise spends her time in service closing tickets and billing. One of our service supervisors is also closing tickets.  All reported significant gains in productivity.

Our shipper who does order release writes the A/R Invoice Number on our order documents.  He used to write ARIN and wait and wait and wait for the number to be displayed before he could move on.  Now by the time he writes ARIN, the number is displayed.  This is all great news.  

Plex hasn't been a problem since we put the new server on line, so the performance of our new server is keeping the Plex service from halting.

This was obviously worth the investment."

La Crosse Management Systems’ published hardware specifications and configurations are available here:

• Hardware Specifications