how to tune exadata

Oracle Sun Database Machine Performance Best Practices [ID 1067520.1]

In this Document
  Purpose
  Scope and Application
  Oracle Sun Database Machine Performance Best Practices
      Optimize ASM Diskgroup Balance
      Optimize Scan Rates
      Optimize file creation performance
      Confirm correct latency for Small IOs
      Ensure auto extend size will allocate space on all disks in the diskgroup
      Optimize network throughput by setting SDU size to 32767
      Size ASM cache properly for large environments
      Understand kipmi0 CPU usage
      Ensure database node IB MTU size is 65520 for external connectivity
      Recommended Memory Configuration 

---

Applies to:

Oracle Exadata Storage Server Software - Version: 11.2.1.2.0 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Purpose

The goal of this document is to present the best practices for the deployment of Oracle Sun Database Machine in the performance area. Describe important elements on the ASM and RDBMS that help to improve the performance of the environment

Scope and Application

General audience working with Oracle Sun Database Machine

Oracle Sun Database Machine Performance Best Practices

 

Performance Best Practices

Optimize ASM Diskgroup Balance

• Check Diskgroup Balance

Optimize Scan Rates

• Set ASM Allocation Unit Size to 4MB
• Ensure Database Extent Sizes are at Least 4MB

Optimize file creation performance 
Confirm correct latency for Small IOs 
Ensure auto extend size will allocate space on all disks in the diskgroup 
Optimize network throughput by setting SDU size to 32767 
Size ASM cache properly for large environments 
Understand kipmi0 CPU usage 
Ensure database node IB MTU size is 65520 for external connectivity 
Recommended Memory Configuration

• OLTP
• Data Warehouse

Performance Best Practices

Below you will find performance best practices specifically important in a Database Machine/Exadata environment. For more generic performance information, see thePerformance Tuning Guide documentation.

Optimize ASM Diskgroup Balance

It is important that files are equally balanced across all disks. The following sections ensure that this occurs.

Check Diskgroup Balance

If you want to perform an imbalance check for all mounted diskgroups, run the script in MOS Note 367445.1.

If you want to determine if you already have imbalance for a file in an existing diskgroup, use the following query:

select disk_kffxp, sum(size_kffxp) from x$kffxp where group_kffxp=AAA and number_kffxp=BBB and lxn_kffxp=0 group by disk_kffxp order by 2;

Breakdown of input/output is as follows:

• AAA is the group_number in v$asm_alias
• BBB is file_number in v$asm_alias
• disk_kffxp gives us the disk number.
• size_kffxp is used such that we account for variable sized extents.
• sum(size_kffxp) provides the number of AUs that are on that disk.
• lxn_kffxp is used in the query such that we go after only the primary extents, not secondary extents

If you want to check balance from an IO perspective, query the statistics in v$asm_disk_iostat before and after running a large SQL statement. For example, if the running a large query that does just reads, the reads and read_bytes columns should be roughly the same for all disks in the diskgroup.

Optimize Scan Rates

The following sections ensure scans are as efficient as possible

Set ASM Allocation Unit Size to 4MB

In order to achieve fast disk scan rates with today's disk technology, it is important that segments be laid out on disk with at least 4MB of contiguous disk space. This allows disk scans to read 4MB of data from disk before having to perform a seek to another location on disk and therefore ensures that most of the time during a scan is spent transferring data from disk rather than seeking between disk locations. To ensure that segments are layed out with 4MB of contiguous data on disk, you will need to set the ASM allocation unit (AU) size to 4MB and ensure that data file extents are at least 4MB in size. The ASM allocation unit can be specified when a disk group is created. For Exadata, we recommend setting the AU size to 4MB. The ASM allocation unit size (AU_SIZE) can be set at disk group creation time as can be seen in the following example:

CREATE diskgroup data normal redundancy 
DISK 'o/*/DATA*'
ATTRIBUTE 
 'AU_SIZE' = '4M',
 'cell.smart_scan_capable'='TRUE',
 'compatible.rdbms'='11.2.0.0', 
 'compatible.asm'='11.2.0.0';

Ensure Database Extent Sizes are at Least 4MB

To achieve the aforementioned fast scan rates, you should also ensure the database extent sizes are at least 4MB. Locally Managed Tablespaces can have a uniform or auto-allocate extent policy and considerations for both can be found on the Application focus best practices MOS note Document 1297112.1

Optimize file creation performance

If creating multiple files in parallel in the same diskgroup is a requirement (ex: tablespaces, backups, restores), run the file creations on one node in the RAC cluster. Doing so optimizes lock acquisition and minimizes buffer pins associated with file allocation. File allocation intra and inter node scalability will be optimized in Oracle 11.2.0.2.

Note that Exadata's fast file creation feature pushes datafile initialization down to the cells, so there should be no concern that multiple file creations on the same node will max out the database server's resources. However, since Exadata fast file creation consumes most of the cell's write bandwidth, running file creations in parallel is at most an incremental benefit, so that requirement should be investigated to see if it is truly needed.

Confirm correct latency for Small IOs

Small IOs (equivalent to the block size) should complete within 10-15ms (non flash cache) when there is no interference (example interference is smart scans without IORM configured). Below is a query that provides a histogram detailing timing for small IOs (tracked by 'cell single block read' wait event)

select WAIT_TIME_MILLI, WAIT_COUNT, WAIT_TIME_MILLI*WAIT_COUNT total_waited_milli ,LAST_UPDATE_TIME, 
LPAD('*', WAIT_TIME_MILLI*WAIT_COUNT/500000, '*') graph 
from v$event_histogram where event='cell single block physical read';

One known issue that can result in excessive IO times is RDS-level failover so check for that if you see times in the second range. Any other cases where excessive times are observed should be reported to Oracle Support.

Ensure auto extend size will allocate space on all disks in the diskgroup

When configuring a file to auto extend, the size of the extension should cover all disks in the diskgroup to optimize balance. For example, with a 4MB AU size and 128 disks, the size of the extension should be a multiple of 512M (4*128).

Optimize network throughput by setting SDU size to 32767

With Oracle Net Services, it is possible to control data transfer by adjusting the size of the Oracle Net setting for the session data unit (SDU). Oracle internal testing indicates that setting the SDU to its maximum value of 32767 can improve performance. You can set SDU on a per connection basis using the SDU parameter in the local naming configuration file (TNSNAMES.ORA) and the listener configuration file (LISTENER.ORA), or you can set the SDU for all Oracle Net connections with the profile parameter DEFAULT_SDU_SIZE in the SQLNET.ORA file.

Size ASM cache properly for large environments

For environments where the number of disks and/or the number of files is very large, the default ASM buffer cache size (db_cache_size parameter) may not be adequate. To ensure the proper size use the following formula:

5 * (<# of disks> + <# of files>) * 4K

Do not change the default unless the formula indicates that it is too small.

Understand kipmi0 CPU usage

You may see kipmi0 consuming CPU on a cell and be concerned. The scheduling policy ensures that kipmi0 only runs during idle times and yields to other more important processes. To double check you don't have a problem, you can also run ipmitool sel elist

to see what is flagged (though MS does this for you so this should only be needed in a corner case)

Ensure database node IB MTU size is 65520 for external connectivity

The MTU Size for the IB Interfaces on the DB Nodes should be set to 65520 to ensure a high transfer rate to external devices that are using TCP/IP over IB such as Media Servers / Data Mules or NFS Servers.

For the Media Servers / Data Mules or NFS Servers to take optimal use of the IB interface, these devices should also be configured with a 65520 MTU Size.

The MTU Size for the IB Interfaces on the Exadata Cells should be left unchanged at 1500 because the communication between the DB Nodes and the Exadata Cells use RDS and not IP protocols.

To verify the MTU Size for the IB Interfaces and the bonded interface

# ifconfig bond0 | grep MTU
          UP BROADCAST RUNNING MASTER MULTICAST  MTU:65520  Metric:1
# ifconfig ib0 | grep MTU
          UP BROADCAST RUNNING SLAVE MULTICAST  MTU:65520  Metric:1
# ifconfig ib1 | grep MTU
          UP BROADCAST RUNNING SLAVE MULTICAST  MTU:65520  Metric:1

Additionally, to exploit the larger MTU Size, the IB Mode should be configured for "connected" mode as opposed to the legacy / default "datagram" mode. To verify the current IB Mode used

# grep [a-z] /sys/class/net/ib*/mode
/sys/class/net/ib0/mode:connected
/sys/class/net/ib1/mode:connected

Details on how to configure the larger MTU Size and IB Mode, please refer to Backup and Recovery Performance and Best Practices for Sun Oracle Database Machine and Exadata Cell

Recommended Memory Configuration

The correct setting of database memory parameters is significant for reliable performance and stability in a production environment. Incorrectly configured values are likely to affect query performance. Running with low values, not using all available memory will slow down the system as more data must be read from disk, and more of the sort and join operations are likely to spill to disk. Setting memory values too high may cause the queries to abort, or the system to swap, or worst case for the node to reboot.

Use SGA_TARGET and SGA_MAX_SIZE for shared memory, and PGA_AGGREGATE_TARGET for private memory. Memory usage can be monitored using MOSNote 223730.1 (for the database) and Note 233753.1 (for Linux). For systems where there are multiple databases running, remember the values are a sum of all databases.

• OLTP

For OLTP systems, where most of the queries are short-running and do not require heavy joins, sorting, or aggregates, 50% (36GB) of the physical memory should be given to SGA_TARGET/SGA_MAX_SIZE, and 25% (18GB) to PGA_AGGREGATE_TARGET.

Hugepages should be used, as this reduces the page table size and also reduces process startup time. Ensure that you allocate enough hugepages to fit all of your SGA (the sum of all SGA's on the node when running more than one database instance). Review  MOS Note 361323.1 and Note 401749.1 for details on hugepages.

• Data Warehouse

For Data Warehouse systems, it is recommended to start with SGA_TARGET=8G and PGA_AGGREGRATE_TARGET = 16G with PARALLEL_MIN_SERVERS=32, PARALLEL_MAX_SERVERS=128. There is a DBCA template built specifically for the Database Machine which includes these values. The default database created during deployment will have these settings.