hammerdb_oracle_oltp_best_practice.pdf

漫步者

8页

1次

2025-03-19

5墨值下载

Oracle OLTP Best Practice on Linux

The document Introduction to Transactional (OLTP) Load Testing for all Databases provides a general

overview on the HammerDB OLTP workload and the document Oracle Transactional (OLTP) Load Testing

provides a detailed guide on Oracle OLTP Testing and should both be read prior to this document. It is

assumed that you already have Oracle installed and running and are familiar with running HammerDB tests.

This document provides advice and guidance on HammerDB and Oracle Configuration to achieve good

levels of throughput on HammerDB OLTP tests. It is important to be aware that this testing best practice

whilst identifying the maximum levels of throughput achievable on your system by focusing on performance

may not necessarily correspond with best practice on a production system where recoverability in the event

of failure is a key focus.

Best Practice for Oracle Performance and Scalability ..................................................................... 1

CPU, Memory and I/O .................................................................................................................. 1

BIOS Settings ................................................................................................................................ 2

Power Saving ................................................................................................................................ 2

Verify Single Threaded Performance ........................................................................................... 2

Hyper-Threading .......................................................................................................................... 3

Memory ........................................................................................................................................ 3

I/O and SSDs ................................................................................................................................. 3

Network Bandwith ....................................................................................................................... 3

Oracle Parameters ........................................................................................................................ 3

Database Creation ........................................................................................................................ 3

Schema Build and Configure ........................................................................................................ 4

Resize the Redo Log Files ............................................................................................................. 4

Monitoring ................................................................................................................................... 6

Support and Questions ..................................................................................................................... 8

Best Practice for Oracle Performance and Scalability

Oracle offers excellent scalability on 2, 4 and 8 socket systems. Best practices for Oracle configuration can

help take advantage of these scalable for maximum levels of performance. Through this document the

links provided in red contain crucial configuration details already published and therefore these should be

read and understood for maximum benefit.

CPU, Memory and I/O

The key dependence of performance is hardware related with the CPU being the most important factor on

the levels of performance available from the rest of the system. At the next level from the CPU is memory

with the best levels of performance available from having sufficient memory to cache all of the test

database. Finally I/O performance is crucial with modern systems and CPUs available to drive high levels of

throughput, In particular for OLTP workloads write performance to transaction logs is critical and often a

major resource constraint. Solid State Disks (SSDs) are strongly recommended for both data areas and redo

logs to provide the I/O capabilities to match the CPU performance of up to date systems.

BIOS Settings

Systems are shipped with default BIOS and are not necessarily optimized for database performance. BIOS

settings should be checked and settings verified with the vendor to ensure that they are advantageous to

Oracle Performance. A common error is to accept a default setting of “High Performance” that sets a subset

of lower level BIOS settings without verifying what these are. A default setting of “High Performance” will

often result in lower performance for a database environment.

Power Saving

Modern CPUs and systems running Linux are designed to offer high levels performance whilst incorporating

power saving features such as Turbo Boost and C-state and P-state management. Firstly you should ensure

that your version of Linux paying particular attention to the kernel version is compatible with the CPU in

your system. You should not assume full compatibility until verified. Once you have done this check the

features of your CPU model and verify your configuration of the scaling governor and energy performance

bias with tools such as PowerTop and turbostat. Full details on how to do this are provided in the blog post

How to Maximise CPU Performance for the Oracle Database on Linux.

Verify Single Threaded Performance

Once you have set your BIOS settings and Power Saving settings verify that you achieve maximum CPU

single threaded performance by creating and running the following stored procedure:

SET SERVEROUTPUT ON

SET TIMING ON

DECLARE

n NUMBER := 0;

BEGIN

FOR f IN 1..10000000

LOOP

n := MOD (n,999999) + SQRT (f);

END LOOP;

DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));

END;

More details on running this test are here Testing C-State Settings and Performance with the Oracle

Database on Linux.

The test will produce a result such as follows where the elapsed time is important.

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.88

Results will vary according to CPU model however typically a modern CPU will complete this test in 10

seconds or less according to configuration ensuring both system and Linux power saving settings are set

optimally for Oracle performance. Guidelines for details on ratings achieved by various CPU models are

available on juliandyke.com.

Hyper-Threading

Previously you will have determined your CPU features. One CPU feature that may be available is Hyper-

Threading. If this feature is available it should be enabled and under a correctly configured system will

result in a performance gain. Details on the benefits of Hyper-Threading are available here Hyper-

Threading On or Off for Oracle?

Memory

Correctly configuring memory is essential to Oracle performance. On Linux you should ensure that you

have your Oracle memory correctl configured for Huge Pages (Not transparent huge pages). Follow the

guide detailed here to ensure that your memory is correctly configured. Configuring Memory for Oracle on

Linux

I/O and SSDs

After correctly configuring memory your focus should be on I/O or disk performance. This focus lies in 2

areas firstly the data area defined by the tablespace or tablespaces you create to configure your schema

and secondly the redo logs. For both data and redo the best performance available is from good quality

and correctly configured SSDs (solid state disks). Further information on SSDs is available in the post Should

you put Oracle Database Redo on Solid State Disks (SSDs)?. Configuring the data area should be done in

conjunction with configuring memory for the buffer cache. With sufficient memory you can expect most of

your data blocks to be cached and therefore interaction with the data area until a checkpoint occurs may be

minimal. On the other hand an OLTP workload will continually write to the redo log file and throughput

should be high and latency low (< 1ms) to achieve the highest transaction numbers. With SSDs careful

consideration should be given to setup, the guides How to Configure Oracle Redo on SSD (Solid State

Disks) with ASM and Linux Database Performance on SSD 910 with Filesystem detail how this should be

done.

Network Bandwith

Oracle OCI is network efficient and issues should not be found with high throughput even on a typical

Gigabit Ethernet network. If system network utilisation does exceed Gigabit capacity between load

generation server and SUT to increase capacity you may use either use 10GbE or configure NIC teaming for

multiple Gigabit Ethernet adapters on both the server and both the LACP compatible switch in LACP mode.

Oracle Parameters

Your Oracle parameters will vary according to the size and configuration of your system and therefore there

is no single recommend Oracle parameter file. Nevertheless standard best practice includes sizing shared

memory manually rather than automatically and disabling database features that are not currently in use.

As an example the following white paper shows a configuration file used for a large high performance

system Mission-Critical Database Performance: Intel Xeon Processor E7 V2 Family vs. Ibm Power7+.

Database Creation

Building the HammerDB schema directly on the database server will be quicker as you will be able to take

advantage of more cores and not be required to pass all of the data across the network. You should already

have your database software installed and Oracle running. Before running the schema creation create the

tablespace required for your data area. Ensure that this file is suitably large not to autoextend and to allow

for sufficient table growth during tests.

of 8

5墨值下载

oracle

相关文档

评论