Skip to content

Database throughput

August 6, 2009
Throughput measures the overall performance of the system. For transaction processing systems, throughput is typically measured in transactions per second (TPS) or transactions per minute (TPM). Throughput depends on the following factors:
1. The specifications of the host computer
2. The processing overhead in the software
3. The layout of data on disk
4. The degree of parallelism that both hardware and software support
5. The types of transactions being processed
Throughput Measurement
The best way to measure throughput for an application is to include code in the application that logs the time stamps of transactions as they commit. If your application does not provide support for measuring throughput directly, you can obtain an estimate by tracking the number of COMMIT WORK statements that the database server logs during a given time interval. You can use the onlog utility to obtain a listing of logical-log records that are written to log files. You can use information from this command to track insert, delete, and update operations as well as committed transactions. However, you cannot obtain information stored in the logical-log buffer until that information is written to a log file.
If you need more immediate feedback, you can use onstat ￯﾿ᄁ￯ᄒタ￯ᄒヤp to gather an estimate. You can use the SET LOG statement to set the logging mode to unbuffered for the databases that contain tables of interest. You can also use the trusted auditing facility in the database server to record successful COMMIT WORK events or other events of interest in an audit log file. Using the auditing facility can increase the overhead involved in processing any audited event, which can reduce overall throughput. For information about the trusted auditing facility, see your IBM Informix: Trusted Facility Guide.
Standard Throughput Benchmarks
Well known organizations such as the Transaction Processing Performance Council (TPC) provide standard benchmarks that allow reasonable throughput comparisons across hardware configurations and database servers. IBM is an active member in good standing of the TPC.
The TPC provides the following standardized benchmarks for measuring throughput:
This benchmark is used for simple online transaction-processing (OLTP) comparisons. It characterizes the performance of a simple transaction processing system, emphasizing update intensive services. TPC A simulates a workload that consists of multiple user sessions connected over a network with significant disk I/O activity.
This benchmark is used for stress-testing peak database throughput. It uses the same transaction load as TPC A but removes any networking and interactive operations to provide a best-case throughput measurement.
This benchmark is used for complex OLTP applications. It is derived from TPC A and uses a mix of updates, read-only transactions, batch operations, transaction rollback requests, resource contentions, and other types of operations on a complex database to provide a better representation of typical workloads.
This benchmark measures query processing power in terms of completion times for very large queries. TPC D is a decision-support benchmark built around a set of typical business questions phrased as SQL queries against large databases (in the gigabyte or terabyte range).
Because every database application has its own particular workload, you cannot use TPC benchmarks to predict the throughput for your application. The actual throughput that you achieve depends largely on your application.
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: