Home

	Expand Search

Performance and Tuning Guide Volumes 1 - 3 (Online Only)

Chapter 1 Overview

Chapter 1

Overview

This chapter is an introduction to enhancing database performance.

Good performance

Performance is the measure of efficiency for an application or multiple applications running in the same environment. Performance is usually measured in response time and throughput.

Response time

Response time is the time that a single task takes to complete. The response time can be shortened by:

• Reducing contention and wait times, particularly disk I/O wait times

• Using faster components

• Reducing the amount of time the resources are needed

In some cases, Adaptive Server is optimized to reduce initial response time, that is, the time it takes to return the first row to the user.

This is especially useful in applications where a user may retrieve several rows with a query and then browse through them slowly with a front-end tool.

Throughput

Throughput refers to the volume of work completed in a fixed time period. There are two ways of thinking of throughput:

• As a single transaction, for example, 5 UpdateTitle transactions per minute, or

• As the entire Adaptive Server, for example, 50 or 500 server-wide transactions per minute

Throughput is commonly measured in transactions per second (tps), but it can also be measured per minute, per hour, per day, and so on.

Designing for performance

Most of the gains in performance derive from good database design, thorough query analysis, and appropriate indexing. The largest performance gains can be realized by establishing a good database design and by learning to work with the Adaptive Server query optimizer as you develop your applications.

Other considerations, such as hardware and network analysis, can locate performance bottlenecks in your installation.

Tuning performance

Tuning is optimizing performance. A system model of Adaptive Server and its environment can be used to identify performance problems at each layer.

A major part of tuning is reducing the contention for system resources. As the number of users increases, contention for resources such as data and procedure caches, spinlocks on system resources, and the CPU(s) increases. The probability of locking data pages also increases.

Tuning levels

Adaptive Server and its environment and applications can be broken into components, or tuning layers, to isolate certain components of the system for analysis. In many cases, two or more layers must be tuned so that they work optimally together.

In some cases, removing a resource bottleneck at one layer can reveal another problem area. On a more optimistic note, resolving one problem can sometimes alleviate other problems.

For example, if physical I/O rates are high for queries, and you add more memory to speed response time and increase your cache hit ratio, you may ease problems with disk contention.

The following information is on the tuning layers for Adaptive Server.

Most performance gains come from query tuning, based on good database design. This guide is devoted to an explanation of Adaptive Server internals with query processing techniques and tools to maintain high performance.

Issues at the application layer include the following:

• Decision Support System (DSS) and online transaction processing (OLTP) require different performance strategies.

• Transaction design can reduce performance, since long-running transactions hold locks, and reduce the access of other users to data.

• Relational integrity requires joins for data modification.

• Indexing to support selects increases time to modify data.

• Auditing for security purposes can limit performance.

Options to address these issues include:

• Using remote or replicated processing to move decision support off the OLTP machine

• Using stored procedures to reduce compilation time and network usage

• Using the minimum locking level that meets your application needs

Applications share resources at the database layer, including disks, the transaction log, and data cache.

One database may have 2^31 (2,147,483,648) logical pages. These logical pages are divided among the various devices, up to the limit available on each device. Therefore, the maximum possible size of a database depends on the number and size of available devices.

The "overhead" is space reserved to the server, not available forany user database. It is:

• size of the master database,

• plus size of the model database,

• plus size of tempdb

• (12.0 and beyond) plus size of sybsystemdb,

• plus 8k bytes for the server's configuration area.

Issues at the database layer include:

• Developing a backup and recovery scheme

• Distributing data across devices

• Auditing affects performance; audit only what you need

• Scheduling maintenance activities that can slow performance and lock users out of tables

Options to address these issues include:

• Using transaction log thresholds to automate log dumps and avoid running out of space

• Using thresholds for space monitoring in data segments

• Using partitions to speed loading of data

• Placing objects on devices to avoid disk contention or to take advantage of I/O parallel.

• Caching for high availability of critical tables and indexes

At the server layer, there are many shared resources, including the data and procedure caches, locks, and CPUs.

Issues at the Adaptive Server layer are as follows:

• The application types to be supported: OLTP, DSS, or a mix.

• The number of users to be supported can affect tuning decisions--as the number of users increases, contention for resources can shift.

• Network loads.

• Replication ServerŪ or other distributed processing can be an issue when the number of users and transaction rate reach high levels.

Options to address these issues include:

• Tuning memory (the most critical configuration parameter) and other parameters.

• Deciding on client vs. server processing--can some processing take place at the client side?

• Configuring cache sizes and I/O sizes.

• Adding multiple CPUs.

• Scheduling batch jobs and reporting for off-hours.

• Reconfiguring certain parameters for shifting workload patterns.

• Determining whether it is possible to move DSS to another Adaptive Server.

This layer is for the disk and controllers that store your data. Adaptive Server can manage up to 256 devices.

Issues at the devices layer include:

• You mirror the master device, the devices that hold the user database, or the database logs?

• How do you distribute system databases, user databases, and database logs across the devices?

• Do you need partitions for parallel query performance or high insert performance on heap tables?

Options to address these issues include:

• Using more medium-sized devices and controllers may provide better I/O throughput than a few large devices

• Distributing databases, tables, and indexes to create even I/O load across devices

• Using segments and partitions for I/O performance on large tables used in parallel queries

This layer has the network or networks that connect users to Adaptive Server.

Virtually all users of Adaptive Server access their data via the network. Major issues with the network layer are:

• The amount of network traffic

• Network bottlenecks

• Network speed

Options to address these issues include:

• Configuring packet sizes to match application needs

• Configuring subnets

• Isolating heavy network uses

• Moving to a higher-capacity network

• Configuring for multiple network engines

• Designing applications to limit the amount of network traffic required

This layer concerns the CPUs available.

Issues at the hardware layer include:

• CPU throughput

• Disk access: controllers as well as disks

• Disk backup

• Memory usage

Options to address these issues include:

• Adding CPUs to match workload

• Configuring the housekeeper task to improve CPU utilization

• Following multiprocessor application design guidelines to reduce contention

• Configuring multiple data caches

Ideally, Adaptive Server is the only major application on a machine, and must share CPU, memory, and other resources only with the operating system, and other Sybase software such as Backup Server(TM) and Adaptive Server Monitor(TM).

At the operating system layer, the major issues are:

• The file systems available to Adaptive Server

• Memory management - accurately estimating operating system overhead and other program memory use

• CPU availability and allocation to Adaptive Server

Options include:

• Network interface

• Choosing between files and raw partitions

• Increasing the memory size

• Moving client operations and batch processing to other machines

• Multiple CPU utilization for Adaptive Server

Identifying system limits

There are limits to maximum performance. The physical limits of the CPU, disk subsystems, and networks impose limits. Some of these can be overcome by adding memory, using faster disk drives, switching to higher bandwidth networks, and adding CPUs.

Given a set of components, any individual query has a minimum response time. Given a set of system limitations, the physical subsystems impose saturation points.

Setting tuning goals

For many systems, a performance specification developed early in the application life cycle sets out the expected response time for specific types of queries and the expected throughput for the system as a whole.

Analyzing performance

When there are performance problems, you need to determine the sources of the problems and your goals in resolving them. The steps for analyzing performance problems are:

1. Collect performance data to get baseline measurements. For example, you might use one or more of the following tools:

   • Benchmark tests developed in-house or industry-standard third-party tests.

   • sp_sysmon, a system procedure that monitors Adaptive Server performance and provides statistical output describing the behavior of your Adaptive Server system.

     See Performance and Tuning Guide: Tools for Performance Statistics for information on using sp_sysmon.

   • Adaptive Server Monitor provides graphical performance and tuning tools and object-level information on I/O and locks.

   • Any other appropriate tools.

2. Analyze the data to understand the system and any performance problems. Create and answer a list of questions to analyze your Adaptive Server environment. The list might include questions such as:

   • What are the symptoms of the problem?

   • What components of the system model affect the problem?

   • Does the problem affect all users or only users of certain applications?

   • Is the problem intermittent or constant?

3. Define system requirements and performance goals:

   • How often is this query executed?

   • What response time is required?

4. Define the Adaptive Server environment--know the configuration and limitations at all layers.

5. Analyze application design--examine tables, indexes, and transactions.

6. Formulate a hypothesis about possible causes of the performance problem and possible solutions, based on performance data.

7. Test the hypothesis by implementing the solutions from the last step:

   • Adjust configuration parameters.

   • Redesign tables.

   • Add or redistribute memory resources.

8. Use the same tests used to collect baseline data in step 1 to determine the effects of tuning. Performance tuning is usually a repetitive process.

   • If the actions taken based on step 7 do not meet the performance requirements and goals set in step 3, or if adjustments made in one area cause new performance problems, repeat this analysis starting with step 2. You might need to reevaluate system requirements and performance goals.

9. If testing shows that your hypothesis is correct, implement the solution in your development environment.

Normal Forms

Usually, several techniques are used to reorganize a database to minimize and avoid inconsistency and redundancy, such as Normal Forms.

Using the different levels of Normal Forms organizes the information in such a way that it promotes efficient maintenance, storage and updating. It simplifies query and update management, including the security and integrity of the database. However, such normalization usually creates a larger number of tables which may in turn increase the size of the database.

Database Administrators must decide the various techniques best suited their environment.

Use the Adaptive Server Reference Manual as a guide in setting up databases.

Locking

Adaptive Server protects the tables, data pages, or data rows currently used by active transactions by locking them. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.Locking affects performance when one process holds locks that prevent another process from accessing needed data. The process that is blocked by the lock sleeps until the lock is released. This is called lock contention.A more serious locking impact on performance arises from deadlocks. A deadlock occurs when two user processes each have a lock on a separate page or table and each wants to acquire a lock on the same page or table held by the other process. The transaction with the least accumulated CPU time is killed and all of its work is rolled back.Understanding the types of locks in Adaptive Server can help you reduce lock contention and avoid or minimize deadlocks.

See the System Administration Guide for an introduction on locking.

Locking for performance is discussed in Chapter 13, "Locking Configuration and Tuning,"Chapter 11, "Using Locking Commands," and Chapter 12, "Reporting on Locks."

Special Considerations

Databases are allocated among the devices in fragments called "disk pieces", where each disk piece is represented by one entry in master.dbo.sysusages. Each disk piece:

• Represents a contiguous fragment of one device, up to the size of the device.

• Is an even multiple of 256 logical pages.

One device may be divided among many different databases. Many fragments of one device may be apportioned to one single database as different disk pieces.

There is no practical limit on the number of disk pieces in one database, except that the Adaptive Server's configured memory must be large enough to accommodate its in-memory representation.

Because disk pieces are multiples of 256 logical pages, portions of odd-sized devices may remain unused. For example, if a device has 83 Mb and the server uses a 16k page size, 256 logical pages is 256 * 16k = 4 Mb. The final 3 Mb of that device will not be used by any database because it's too small to make a group of 256 logical pages.

The master device sets aside its first 8k bytes as a configuration area. Thus, to avoid any wasted space, a correctly-sized master device should be an even number of 256 logical pages *plus* 8 kb.