POWER7 optimization and tuning with third-party applications
This appendix describes the optimization and tuning of the POWER7 processor-based server running third-party applications. It covers the following topics:
Migrating Oracle to POWER7
This section provides you with key documents and links to assist with IBM Power Systems in an Oracle environment. This section includes information specific to POWER7 in an Oracle environment. IBM is not aware of any POWER7 specific Oracle issues at the time of this writing. Most issues that show up on POWER7 are the result of not following preferred practices that apply to all Power Systems generations.
This section also includes description about Oracle Non-Real Application Clusters (NON-RAC) and Real Application Clusters (RAC).
Table C-1 lists important documents that you should become familiar with.
Table C-1 Power Systems and Oracle information.
Title
Resource
Description
Must read first - Oracle and IBM references
Oracle Readme/Release Notes Main Page: The Readme/Release Notes are dynamic documents updated online with new information.
Main Oracle page from which you can select an Oracle product for specific information about a platform (installation information, AIX patches, tuning, implementation suggestions, and so on).
Oracle 11gR2 Readme/Release Notes.
Oracle Database Release Notes 11g Release 2 (11.2) for IBM AIX on Power Systems (64-bit)
Part Number E23560-03.
Oracle 10gR2 Readme/Release Notes.
Oracle Database Release Notes 10g Release 2 (10.2) for AIX
Part Number B19074-15.
Minimum Software Versions and Patches Required to Support Oracle Products on IBM Power Systems.
(registration required)
My Oracle Support (MOS) ID 282036.1.
Oracle Architecture and Tuning on AIX V2.20
AIX tuning reference guide available on Techdocs.
Oracle Real Application Clusters on IBM AIX – Best practices in memory tuning and configuring for system stability.
RAC tuning and configuration guide.
Oracle Real Application Clusters (RAC) and Oracle Clusterware Interconnect Virtual Local Area Networks (VLANs) Deployment Considerations.
RAC and VLAN deployment guide.
Managing Raw Disks in AIX to use with Oracle Automatic Storage Management (ASM).
(registration required)
MOS ID 1445870.1.
Oracle Database Cross Platform Migration to AIX.
Cross-platform migration reference guide available on Techdocs.
Must read for Oracle 11g
Oracle Database Online Patching on AIX.
AIX fixes and Oracle patches that must be used with the Oracle Database Online patching feature.
Oracle’s USLA HEAP patches available on AIX.
Addresses that are increased per-process memory consumption.
Oracle Database 11.2.0.3 available on AIX.
11.2.0.3 certification information.
Oracle DB & RAC 11gR2 on IBM AIX: Tips and Considerations.
11gR2 planning and implementation
Oracle DB & RAC 10gR2 on IBM AIX: Tips and Considerations.
10gR2 planning and implementation.
Additional IBM Information
Review Recent ICC Flashes on Techdocs – the Technical Sales Library.
Technical sales support database.
Guide to Multiple Page Size Support on AIX 5L
Version 5.3
Useful information about large page configuration.
Large page size.
Larger page size often provides performance improvements with Oracle.
Existing environment statistics capture.
Consider creating a historical comparison baseline to aid in solving a perceived change in performance, for example, when a new system seems slower than the old one.
Determine what is most applicable to capture in your environment (that is, AWR or PerfPMR types of reports).
Java Performance on POWER7 - Best Practice.
Information about migrating Java applications from POWER5 or POWER6 to POWER7.
Oracle 11gR2 preferred practices for AIX V6.1 and AIX V7.1 on Power Systems
This section is a summary of preferred practices for stand-alone Oracle 11gR2 instances on AIX V6.1 and AIX 7.1 on POWER7 Systems. Except for references to symmetric multithreading, quad-threaded mode (SMT4 mode), all of the preferred practices apply to POWER6 as well. Although the primary focus is on stand-alone Oracle 11gR2 with file system-based storage, stand-alone ASM is also addressed. These preferred practices apply to Oracle 10.2.0.4 as well, and where they differ from those practices for Oracle 11gR2, patches specific to Oracle 10.2.0.4 are noted.
Detailed Oracle/AIX preferred practices documents are available on IBM Techdocs at http://www.ibm.com/support/techdocs/atsmastr.nsf/Web/Techdocs, including Oracle Architecture and Tuning on AIX v2.20, found at:
The following sections describe memory, CPU, I/O, network, and miscellaneous settings. In addition, these sections list the AIX levels that are required for Oracle 11gR2, the Oracle patches for 11gR2 on AIX V6.1 and AIX V7.1, and the Oracle patches for 10.2.0.4
and 11gR2.
Memory
The specifications for kernel settings and Oracle large page usage are described in the
following sections.
Kernel settings
Kernel settings are listed in Table C-2. These values are commonly suggested ones.
Table C-2 Kernel settings for Oracle
Parameter
Proposed value
AIX V6.1 default
AIX V6.1 restricted
AIX V7.1 default
AIX V7.1 restricted
minperm%
 
 
No
 
No
maxperm%
90
90
Yes
90
Yes
maxclient%
90
90
Yes
90
Yes
strict_maxclient
 
 
Yes
 
Yes
strict_maxperm
 
 
Yes
 
Yes
lru_file_repage
 
 
Yes
N/A
N/A
lru_poll_interval
10
10
Yes
10
Yes
minfree
960
960
No
960
No
maxfree
1088
1088
No
1088
No
page_steal_method
 
 
Yes
 
Yes
memory_affinity
 
 
Yes
 
Yes
v_pinshm
 
 
No
 
No
lgpg_regions
 
 
No
 
No
lgpg_size
 
 
No
 
No
maxpin%
80
80
No
90
No
esid_allocator
11
 
No
 
No
1 The default value of 1 for esid_allocator enables terabyte segment aliasing, reducing addressing lookasides. This value can be set to 1 in AIX V6.1 and is suggested for Oracle.
In general, you should use AIX V7.1 defaults for Oracle.
Two noticeable changes from AIX V6.1 to AIX V7.1 are:
•The elimination of the lru_file_repage tunable
•The default value of 90 for maxpin%, which is increased from 80% in AIX V6.1
Oracle large page usage
Specifications for Oracle large page usage are:
•AIX V6.1 and AIX V7.1 support three or four page sizes, depending on the hardware: 4 KB (default), 64 KB, 16 MB, and 16 GB. All four page sizes are supported by Power Systems from POWER5+ firmware level 240-202 onward.
Page sizes 64 KB and 16 MB benefit Oracle performance by reducing kernel lookaside processing to resolve virtual to physical addresses. Oracle 11g uses 64 KB pages for data spaces by default.
•LOCK_SGA = FALSE:
 – This setting is the default, which means that the SGA is not pinned in memory.
 – In general, you should not pin SGA.
 – Automatic Memory Management (AMM) uses 64 KB pages for SGA if memory
is available.
 – This value is the preferred one, as it has been found that 64 KB pages yield nearly the same performance benefit as 16 MB pages and require no special management.
 – Oracle 10.2.0.4 with Oracle patch 7226548 also uses 64 KB pages for the SGA.
•LOCK_SGA = TRUE:
 – The AIX parameters to enable pinned memory and 16 MB large pages are:
 • vmo –p –o v_pinshm=1 (allows pinned memory and requires reboot)
 • vmo –p –o lgpg_size=16777216 -o lgpg_regions=number_of_large_pages, where number_of_large_pages=INT[SGA size -1)/16MB)]+1
 – AMM can be used with pinned 16 MB pages, provided the formula for calculating the number of large pages is modified so that the number of large pages=memory_max_target+1.
 – The capabilities that are required to allow Oracle to use 16 MB large pages (implement as root) can be set by running the following command:
#chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
 – With Oracle 10.2.0.4, patch 7226548 is also required to use 16 MB pinned pages.
•Using 64 KB pages for data, text, and stack regions (applies to Oracle 10.2.0.4 as well):
 – 64 KB page size for data, text, and stack regions is useful in environments with a large (for example. 64 KB+) SGA and many online transaction processing (OLTP) users. For smaller Oracle instances, 4 KB is sufficient for data, text, and stack.
 – 64 KB page use for data, text, and stack is implemented separately from 64 KB pages for the SGA, and is done through an environment variable that is exported on behalf of the Oracle user:
$ export LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K oracle
* LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K
export LDR_CNTRL
CPU
CPU specifications are as follows:
•SMT mode: POWER7 supports SMT4 mode, which is the AIX default. AIX and Oracle performance support encourages starting with the default.
•Virtual processor folding: This is a feature of Power Systems in which unused virtual processors are taken offline until demand requires that they be activated. The default is to allow virtual processor folding; do not alter this setting without consulting AIX support.
•Specific to POWER7 SMT4 mode: Certain Oracle 11G parameters, including DB_WRITER_PROCESSES and PARALLEL_MAX_SERVERS, are partly derived from CPU_COUNT, and CPU_COUNT is equal by default to the number of logical CPUs. CPU_COUNT automatically adjusts to changes in virtual processor count and to SMT mode, up to three times the value on startup.
•When you migrate from single-threaded platforms to Power Systems, or from POWER5 or POWER6 to POWER7 with SMT4 mode, the value of CPU_COUNT also increases, affecting DB_WRITER_PROCESSES, PARALLEL_MAX_SERVERS, and other dependent parameters. Queries that are sensitive to a degree of parallelism might change behavior because of the migration to POWER7. Reviewing the PARALLEL_MAX_SERVERS parameter after migration, but set DB_WRITER PROCESSES to the defaults.
I/O
I/O specifications are as follows:
•If ASM is not used, use max interpolicy striping (also known as pp spreading or poor man’s striping) when logical volumes are created. To get the most benefit from spreading physical partitions across the LUNs, use a small physical partition size, for example,
32 MB or 64 MB.
•Async I/O is used even with Concurrent I/O (CIO):
 – With AIXV 6.1 and AIX V7.1, start with the asynchronous I/O defaults. With AIX V6.1, there is a new implementation of AIO. AIO kernel extensions are loaded at system boot (always loaded), AIO servers stay active if there are service requests, and the number of AIO servers is dynamically increased or reduced based on demand of the workload. The aio_server_inactivity parameter defines how many seconds of idle time before an AIO server exits. AIO tunables are now based on logical CPU count, and it is usually not necessary to tune minservers, maxservers, and maxreqs.
 – In AIX V6.1, there are two tunables for minservers and maxservers, aio_minservers/aio_maxservers for older threads, and posix_aio_minservers/posix_aio_maxservers for POSIX threads. Oracle uses
older threads.
 – Increase aio_maxservers or posix_aio_maxservers Only by running ioo –p –o (the default is 30 per logical CPU) if you run pstat -a | fgrep aio or ps -k | fgrep aio shows that you are continually using maxservers.
 – Oracle parameter (init.ora) should be set to disk_asynch_io = TRUE.
•Buffered file I/O on JFS2:
 – The default is filesystemio_options=ASYNC.
 – In this case, all data spaces, redo log file systems, and control file systems are using the kernel buffers rather than writing directly to disk.
 – In this case, it does not matter whether redo log file systems and control file systems are 512 byte or 4 KB block size file systems.
 – The best performance for Oracle on AIX or Power Systems is, however, usually achieved by using CIO (though there are exceptions).
•Concurrent I/O (CIO) on JFS2:
 – Set the Oracle parameter filesystemio_options=SETALL or mount the file systems (other than dump devices) with the CIO option. It is not necessary to use both SETALL and mount file systems with the CIO option, although no harm is done either way. Metalink note: 272520.1 indicates that mounting with CIO is needed at the time of the writing of this guide.
 – If you use CIO with SETALL, CIO mount, or both, you must create separate file systems for redo logs and control files (or a single file system for both), with an agblksize of 512 rather than the default 4 KB.
 – The ioo parameter fs_fastpath accelerates CIO. It is enabled by default in AIX V6.1 and AIX V7.1.
•IBM mount advice for database files:
 – Data files: Use the CIO parameter filesystemio_options=SETALL, with a default of agblksize (4 KB). Use mount with no options.
 – Redo logs: Create the logs with agblksize of 512 and mount with no options. With SETALL, use direct I/O for Redo logs.
 – Control files: Create the files with agblksize of 512 and mount with no options. With SETALL, use direct I/O for Redo logs.
 – Archive logs: Run mount -o rbrw. Do not use CIO; use the jfs2 rbrw option.
 – Dumps: Run mount –o rbrw.
 – The mount option noatime, suggested for Oracle 10g, is no longer required.
•IOO tunables j2_nBufferPerPagerDevice and j2_dynamicBufferPreallocation:
 – Do not change these tunables unless there is a high delta in vmstat –v external pager file system I/Os that are blocked with no fsbuf. If this value is high, first increase j2_dynamicBufferPreallocation from 16 (16 KB slabs) to 32 and monitor the system. If increasing this tunable does not help, then consider raising the value of j2nBufferPerPagerDevice, which is the starting value for dynamic buffer allocation.
 – For information about these parameters, see the help pages available at http://pic.dhe.ibm.com/infocenter/aix/v7r1/topic/com.ibm.aix.cmds/doc/aixcmds3/ioo.htm. Do not change AIX V6.1 or AIX V7.1 restricted tunables unless directed to do so by IBM AIX support. In AIX V6.1, j2_nBufferPerPagerDevice is a restricted tunable, and j2_dynamicBufferPreallocation is not.
•ASM considerations for stand-alone Oracle 11gR2:
 – For identifying, renaming, and securing ASM raw devices, see Managing Raw Disks in AIX to use with Oracle Automatic Storage Management (ASM) in Table C-1 on page 186.
 – ASM uses asynchronous I/O by default, so filesystemio_options=ASYNC (the default) is appropriate.
 – In the stand-alone usage of ASM, unlike RAC, hdisks and hdiskpower devices do not need to have Small Computer System Interface (SCSI) reservation disabled.
 – The following initialization parameters must be adjusted for ASM:
 • Add 16 to the value of processes.
 • Add an additional 600 KB to the value of large pool size.
 • Add to shared pool size the aggregate of the values that are returned by
these queries:
 • SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;
 • SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#;
 • SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE WHERE status=’ONLINE’;
 – For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool, plus 2 MB.
 – For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool, plus 4 MB.
 – For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool, plus 6 MB.
For more information, go to:
Network
This section outlines the minimum values that are applicable to network configurations.
Kernel configurations
The following values can be considered as starting points for Oracle:
•Set sb_max >= 1MB (1048576), which must be greater than the maximum tpc or udp send or recvspace (if you are using RAC and an especially large udp_recvspace, you might need to increase sb_max).
•Set tcp_sendspace = 262144.
•Set tcp_recvspace = 262144.
•Set udp_sendspace = db_block_size * db_file_multiblock_read_count.
•Set udp_recvspace= 10 * (udp_sendspace).
•Set rfc1323 = 1.
•Ephemerals (non-defaults suggested for many connecting hosts or a high degree of parallel query; also to avoid install-time warnings) should be set as follows:
 – tcp_ephemeral_low=9000
 – tcp_ephemeral_high=65500
 – udp_ephemeral_low=9000
 – udp_ephemeral_high=65500
Jumbo frames are Ethernet frames larger than the standard MTU size of 1500 bytes. They can be up to 9000 bytes. They are used to reduce the number of frames to transmit a volume of network traffic, but they work only if enabled on every hop in the network infrastructure. Jumbo frames help reduce network and CPU processing impacts.
Miscellaneous
Miscellaneous specifications for Oracle are described in this section:
•ulimits (run smit chuser or edit /etc/security/limits to create a stanza for Oracle) should be set to -1 (unlimited) for all values except core.
•The maximum number of PROCESSES allowed per user (run smit chgsys to set) should be set to maxuproc >= 2048. 16 KB is a commonly suggested value for Oracle.
•Environment variables should be set as follows:
 – AIXTHREAD_SCOPE=S (set in Oracle profile)
 – LDR_CNTRL=DATAPSIZE=64K@TEXTPSIZE=64K@STACKPSIZE=64K (set in the Oracle profile and the Oracle listener's environment)
 – There is an option to use ldedit to edit the Oracle binary files so that they use 64 KB pages directly. Run the following command:
# ldedit –btextpsize=64k –bdatapsize=64k –bstackpsize=64k $ORACLE_HOME/bin/oracle
 
Using ldedit: Whenever a patch is applied or an Oracle relink is performed, the ldedit command must be run again.
•Disk and adapter resources
 – To set hdisk, run lsattr –El hdisk<>.
 • The queue depth might vary among 8, 16, 20, and 24, depending on the storage vendor. A queue depth of 2 on SAN devices usually indicates a driver mismatch, but is the default for Hitachi HDS on AIX; increase this value to 8 as a starting point. Queue wait and queue overflow that is detected by running iostat –Dl might indicate a need to increase queue depth.
 • max_transfer might need to be adjusted upward, depending on the largest I/O requested by Oracle; a typical starting point for Oracle on AIX is 0x100000.
 – To set FC Adapter, run lsattr –El fcs<> and fcstat -e.
 • max_xfer_size must be at least equal to max_transfer at the hdisk level.
 • num_cmd_elems might need to be increased if fcstat -e reports a persistent non-zero value for No Command Resource Count.
 • If fcstat –e reports a persistent, non-zero value for No DMA Resource Count, contact support.
Migrating Sybase ASE to POWER7
Sybase ASE is widely used. In the past two years, many migrations of Sybase ASE to POWER7 were completed or are in progress. Two published white papers provide comprehensive documentation for general preferred practices and for migration to POWER7:
•Optimizing Sybase Adaptive Server Enterprise (ASE) for IBM AIX, available on the IBM Techdocs website and on the Sybase website:
This paper is a joint IBM and Sybase publication that was originally written in 2006 and has been updated twice since then, most recently in Spring, 2012. The authors of the current version are Peter Barnett (IBM), Mark Kusma (Sybase), and Dave Putz (Sybase).
This publication directly addresses three main areas in which POWER7 differs from POWER5 and POWER6: SMT4 mode, the virtual processor folding algorithm, and
overall throughput.
Kernel tuning parameters are presented in detail, particularly regarding memory and I/O, and closely resemble preferred practices for Oracle.
The section Processor Considerations addresses the implications of SMT4 mode for allocation of Sybase ASE engines, the implications of the POWER7 virtual processor allocation algorithms on sizing ASE in a shared pool environment, and general preferred practices for aligning Sybase ASE resources with entitlement and virtual processor count.
The same section of this paper addresses the non-traditional technique of allocating more Sybase ASE engines than there are AIX virtual processors or cores. In particular, you should not exceed a ratio of two engines per virtual processor, even with SMT4
mode enabled.
Suggestions to reduce SMT mode to 2 or even ST mode for Sybase ASE and suggestions to turn off virtual processor folding are noted. Most of clients get the best performance from Sybase ASE on AIX/POWER7 with SMT4 mode and virtual processor
folding enabled.
Appendix A of this paper presents an iterative tuning exercise with Sybase ASE 15.5, AIX V7.1, and POWER7. Results are presented for the TPC-C benchmark and other workloads, which indicate that the SMT4 mode provides superior or equal performance when compared to SMT2 and ST modes.
•Migrating Sybase ASE to IBM Power Systems, available at:
The subtitle of this paper explains its purpose: It presents the case for POWER7 as the optimal platform for Sybase ASE, presents guidelines and preferred practices for migrations, and includes a field guide to migrations and proofs of concept.
Many sections of this paper are useful for systems and database managers in planning and carrying out migrations of Sybase ASE to POWER7.
Part I includes a description about Cross Platform Dump and Load (XPDL), the most widely used ASE migration technique, including preparation and post-migration cleanup steps. Other migration techniques are also described. The implications of upgrading ASE from Version 12.5 to Version 15.x, as part of a platform migration, are also described, and some preferred practices are offered. Specific scenarios and case studies are cited, including scale-ups and consolidations. Shared processor and virtualization concepts are reviewed in the context of Sybase ASE.
In Part II, the “Field guide to migrations and proofs of concept”, the first section on requirements gathering, presents a detailed list of configuration information and performance data to be collected in preparation for a migration to POWER7.
In Part III, commonly encountered problems in migrating Sybase ASE to POWER7 are identified and described in some detail.
A list of configuration and performance data to collect when you troubleshoot a performance issue is provided.
Several performance scenarios are described and compared: The case of spinlock contention is contrasted with a healthy CPU-bound workload. Likewise, the case of idling engines because of overallocation is contrasted with a healthy I/O-bound workload.
Implementing Sybase IQ to POWER7
Sybase IQ is a specialized data server that is optimized for ad hoc queries and reporting on data warehouses and data marts.
AIX kernel tunables for Sybase IQ are the same regarding virtual memory, I/O, network, and ulimits as for Sybase ASE and Oracle.
However, there are some special environment variables and scheduler tunables that should be considered, based on joint work between Sybase and AIX development teams. These items were originally developed for POWER6, but are also applicable to POWER7. Some of these are firm suggestions that we urge you to follow, and others are soft suggestions, as they are helpful with specific workloads, and can be used at your discretion.
Environment variables
The environment variables are:
•SPINLOOPTIME=500 (Firm suggestion)
If a user thread cannot get a mutex, it spins for some time, hoping to acquire the lock afterward. The environment variable SPINLOOPTIME controls the spin time duration. After the period, the thread either goes to sleep or stays runnable (see YIELDLOOPTIME).
•YIELDLOOPTIME=0 (Firm suggestion)
It is more efficient for a runnable thread to resume than to wake up from sleep and run. After the spin time is exhausted, the thread can go to sleep or give up CPU but stay runnable. The environment variable YIELDLOOPTIME defines the number of times a thread gives up the CPU before it sleeps.
•AIXTHREAD_SCOPE=S (Same as AIXTHREAD_MNRATIO=1:1) (Firm suggestion)
Setting AIXTHREAD_SCOPE=S means that user threads created with default attributes are placed into system-wide contention scope. If a user thread is created with system-wide contention scope, it is bound to a kernel thread, and it is scheduled by the kernel. The underlying kernel thread is not shared with any other user thread.
•AIXTHREAD_MNRATIO=8:1 (the default if AIXTHREAD_SCOPE is defaulted to P) (Soft suggestion)
Setting AIXTHREAD_SCOPE=S means that user threads created with default attributes are placed into system-wide contention scope. Defaulting to AIXTHREAD_SCOPE or specifying a ratio greater than 1:1 for AIXTHREAD_MNRATIO=M:N means that kernel threads might be shared by M user threads. M>N might be more efficient for highly threaded processes.
•Sybase IQ start script start_iq ships with deprecated values for AIXTHREAD_MNRATIO, SPINLOOPTIME, and YIELDLOOPTIME (Firm suggestion)
Sybase IQ start scripts set both AIXTHREAD_SCOPE=S and AIXTHREAD_MNRATIO=4;1. The latter overrides the former. The former is considered a starting point for systematic testing. IQ start scripts also set SPINLOOPTIME and YIELDLOOPTIME to 40 without any explanation.
•AIXTHREAD_MUTEX_FAST=ON (Firm suggestion)
Setting the variable to ON forces threaded applications to use an optimized mutex locking mechanism, resulting in increased performance.
•MALLOCOPTIONS=multiheap:8,considersize (Firm suggestion)
Multiheap defaults to 32 heaps, but a smaller number is suggested. The number must equal the number of logical CPUs, or 32, whichever is less. Watson and Yorktown versions of MALLOCOPTIONS are problematic for Sybase IQ.
•LDR_CNTRL=TEXTPSIZE=64K@STACKPSIZE=64K@DATAPSIZE=64K (Firm suggestion)
Set in the Sybase owner profile, not in start_asiq, this environment variable enables the application to use 64 KB pages.
•AIXTHREAD_PREALLOC=<size of IQ heap in bytes> (Firm suggestion)
 – A mostly undocumented feature of AIX 5L V5.3.0.10+ and AIX V6.1.0.3+
 – The kernel pre-sets the heap pointer, reducing the number of system calls in malloc
 – Saves some time that is otherwise taken by threads that are waiting on the
malloc mutex
 – Reported to benefit larger systems, 8-core or more
 – Probably most beneficial to IQ load phases
Special considerations for Sybase IQ with SMT4 mode
SMT4 mode provides special benefits for Sybase IQ, owing to its highly threaded architecture. The IQ configuration parameter, -iqnumbercpus, is determined by default by the number of logical processors that IQ detects. The -iqnumbercpus parameter affects the value of several downstream parameters, including -iqgovern and -iqmt. The configuration parameter -iqmt governs the number of threads created.
Some special considerations are:
•By default, -iqmt is set to 60 * (min(-iqnumbercpus,4)) + 50 * (-iqnumbercpus - 4) + 2*(numConnections + 2) + 1
•On a 4-core, single threaded system with -gm (number of connections), it is set to 20, that is, iqmt = 60 * (4) + 50 * (4-4) + 2 * (20+2) + 1 =285
•However, if SMT4 mode is on, -iqnumbercpus is set to 16 for a 4-core system, so -iqmt is 1605. Therefore, 1605 threads are allocated by IQ.
•Is IQ allocating too many threads in SMT4 mode? With POWER5 SMT2 mode, it is beneficial to set -iqnumbercpus to the number of cores or virtual processors. However, with POWER6 and POWER7, the best performance is generally obtained by leaving -iqnumbercpus at its default, even though IQ creates more threads.
Shared versus dedicated processors with Sybase IQ and POWER7
Sybase IQ is CPU hungry by design and scale both out and up with the addition of processors and workload. Sybase support generally favors dedicated cores over shared processor pool technology, and some enterprises report some negative experiences with IQ on shared processors, which might be because of extraneous factors.
The IBM team conducted systematic comparisons of dedicated and shared processor performance using a Sybase provided test workload to produce the Stockmarket Benchmark. This benchmark is similar to TPC-H in that it consists of simulated data that can be inflated. There are 18 complex queries that can be run in parallel or one at a time.
Our findings were that, on POWER7 and AIX V6.1, you can achieve equal or better performance for the 18 queries in parallel, if you allocate +1 virtual processors to the target dedicated cores and:
•Give the IQ LPAR slightly higher entitlement than its neighbors
or
•Give the IQ LPAR a higher weight setting than its neighbors
or
•Arrange the shared pool so that neighbor LPARs have lower peak demand
The reason for allocating +1 virtual processor in relation to the target dedicated CPU count is that the shared processor allocation algorithm is continually recalculating load and thus averages down slightly from the number of virtual processors. Thus, if you allocate four virtual processors and sustain a 100% load, the actual consumption is about 3.9 processors on average. Thus, to attain a peak allocation of the power of four processors, five virtual processors must be assigned, and so on.
You can achieve equal or better performance of each query that is run sequentially by using the configurations that are outlined in “Implementing Sybase IQ to POWER7” on page 195) and by turning off virtual processor folding. Turning off virtual processor folding is not standard practice, but it leads to improved performance in some cases.
Finally, our results suggest that IQ 15.2 can attain dedicated-level performance with a 1:4 entitlement to virtual processor ratio, providing that the other configuration suggestions
are followed.
Migrating SAS to POWER7
This section provides a starting point for performance optimization from a system-wide perspective to create an enhanced environment for SAS 9 on IBM POWER processor-based servers that run AIX V6.1 and AIX V7.1. To verify that the version of SAS and the product suites you are running are supported at the version of AIX you are running, review the information found at:
The preferred performance settings are as follows:
•Technology levels: Use the latest version of AIX V7 (currently Version 7.1) with the latest maintenance levels and fix packs, or AIX 6 V6.1. For more information, go to:
As of the publication of this guide, Version 7.1 TL01 with fix pack or Version 6.1 TL07 are considered to be the latest technology levels with fix packs.
•IBM HMC: Keep the HMC and microcode up to date.
 – Search for HMC update bulletins and microcode at:
 – VMM: Both the AIX V6.1 and AIX V7.1 default VMM parameters are mostly tuned for optimal SAS performance, except for one parameter. If needed, you can change the other values by using the AIX vmo command. Start with the following parameter values:
 • nokilluid=10.
 • minfree=use greater of 960 or (128 * number of logical processors).
 • maxfree=(minfree + j2_maxPageReadAhead * number of logical processors).
 – The following values are designated as restricted values in AIX V7, and you should not change them unless directed to do so by AIX support. These values are already in line with SAS recommendations.
 • lru_file_repage (Version 6.1 only).
 • minperm% (Version 6.1 only).
 • maxperm%.
 • maxclient%.
 • strict_maxclient.
 • strict_maxperm.
 – I/O: Tune I/O at the file system layer by using the AIX ioo command to enable Enhanced Journaled File System (JFS2) to perform efficient caching. Start with the following parameter values:
 • j2_dynamicBufferPreallocation=256.
 • j2_nBufferPerPagerDevice=2048.
 • j2_maxPageReadAhead=1024.
 • j2_minPageReadAhead=16.
 • j2_nPagesPerWriteBehindCluster=64.
For higher workloads, increase the value of j2_maxPageReadAhead up to 2048.
 – Network: Tune the network parameters by using the no command. If SAS applications (such as Scalable Performance Data Server (SPDS), Scalable Performance Data Engine (SPDE), or SAS/CONNECT) are being used, set the following parameter value:
tcp_nodelayack=1
 – Maximum user process: If the maximum number of processes for a single user exceeds 2000, increase the value of maxuproc to prevent SAS processes from abnormal shutdown or delay. Increase the maxuproc setting by using the AIX smit or chdev commands, for example:
chdev –l sys0 –a maxuproc=<new value>
 – User limits: Increase user-process resource limits for SAS users and database instances as appropriate (for example, unlimited or some tuned value for all resources. In the /etc/security/limits file, set -1 for all resources.
 
Default AIX resource limits: The default AIX user-process resource limits might be too low for SAS power users or large enterprise-class deployments of SAS. When SAS processes end because of attempts to exceed these resource limits, the system administrator typically sets all of the user process resource limits to unlimited (a numeric value of -1) for users who run SAS. The problem with this approach is that the increased multi-threading and scalability support in newer versions of SAS, coupled with an unlimited setting for user-process resource limits, allows other users to potentially exhaust system resources, such as processor, memory I/O, and paging space. Before you increase the user-process resource limits, such as memory, to high values, consider the potential consequences.
 – Paging space: Configure the paging space to include at least the following items:
 • Place paging spaces on dedicated disks to eliminate I/O contention.
 • Use multiple paging spaces that are spread across multiple disks.
 • Make the primary paging space hd6 a little bigger than the secondary
paging spaces.
 • Ensure that the paging space is sufficient to support the number of concurrent SAS processes, because the number of SAS processes can be dynamic, depending on application workload.
 – Volume groups (VGs): Use the AIX Scalable or Big volume group.
The scalable VG implementation provides configuration flexibility regarding the number of physical volumes (PVs) and logical volumes (LVs) that an instance of the new VG type can accommodate. The configuration options allow any scalable VG to contain 32, 64, 128, 256, 512, 768, or 1024 disks and 256, 512, 1024, 2048, or 4096 LVs. You do not need to configure the maximum values of 1024 PVs and 4096 LVs when you create the VG to account for potential future growth. You can increase the initial settings later, as required.
 – Disk layout: Minimize disk contention between SAS temporary space and data spaces by considering the following items:
 • Avoid disk contention by placing SAS temporary-space file systems and SAS data file systems on physically separate disks.
 • Use multiple storage-server controllers to further separate and isolate the I/O traffic between SAS temporary and data spaces.
 • Use multiple mount points for SAS file systems. Place the operating system, SAS installation, SAS user, SAS temporary space, and SAS data file systems on separate physical disks.
 • Consider creating multiple SAS WORK areas that are used by groups of
SAS users.
 • Create separate JFS2 log files on separate physical disks for each SAS file system.
 • Spread the I/O workload across many physical disk spindles rather than across fewer, larger-capacity disks. Determine the sizing, based on the quantity of disks rather than on disk capacity. Do not wrap logical unit numbers (LUNs) around the same spindle sets.
 • Do not share disk spindles with a relational database management system (RDBMS).
 – Release-behind mechanism for JFS2: This feature allows the file system to release the file pages from file system buffer cache when an application reads or writes the file pages. This feature helps when the SAS application performs a great deal of sequential reads or writes, and after the file pages are accessed, they are not accessed again soon. This feature can be configured on a file system basis. When you use the mount command, enable release-behind by specifying one of the
following flags:
 • Release-behind sequential read flag (-rbr),
 • Release-behind sequential write flag (-rbw),
 • Release-behind sequential read and write flag (-rbrw).
 – Host bus adapters (HBAs): Use enough HBAs from storage to the host server to provide the required application bandwidth.
 • Consider high-performance storage channels, such as Fibre Channel (FC) technology instead of slower mediums.
 • If possible, use dynamic multipathing to spread the I/O load across
multiple adapters.
 – Redundant Array of Independent Disks (RAID): Implement storage system RAID striping across multiple physical disks.
 • Use RAID10 or RAID5, depending on the level of redundancy and total capacity instead of the usable capacity that is needed for each file system type.
 • Use Logical Volume Manager (LVM) striping instead of concatenation.
 – LVM striping: When you choose the disk stripe or segment size, or array stripe size, AIX file systems are aligned on a 16 KB boundary.
 • A strip is the size of data to be written to each physical disk in the array. A stripe is the size of the full write across all the physical disks in the array. For example: strip size x number of disks = stripe size.
 • The AIX LVM stripe size that you can select from the smit lv create panel is the single strip size (not stripe). It is the size of data to be written to each of the array disks; it is not the full stripe size across all the physical disks. Consider using an LVM stripe size of 64 KB or 128 KB. Stripe sizes of 256 KB or 512 KB show better I/O performance in the case of SAS 9 workloads.
 • Synchronize SAS BUFSIZE with the storage-system stripe size and the AIX LVM stripe size (if you are using LVM striping) and VMM read-ahead increments.
 • Synchronizing I/O sizes streamlines I/O processing and reduces the number of I/O requests to the storage subsystem.
Disk storage tuning
From a high level, the AIX I/O stack contains several layers that an I/O must traverse. At each layer, AIX tracks the I/O. Some of the layers have specific queues that are useful to consider tuning. The I/O stack layers are:
•Application
•File system (optional)
•LVM (optional)
•Subsystem device driver (SDD) or SDD Path Control Module (SDDPCM) (if used)
•hdisk device driver
•Adapter device driver
•Interconnect to the disk
•Disk subsystem
•Disk
In this section, the focus is on tuning the middle layers, which consist of SDD, hdisk, and adapter device drivers. The goal is to improve simultaneous I/O capability and realize efficient queue handling. See Table C-3 for some of the parameters that can affect disk and adapter performance. In general, SAS applications benefit from careful consideration and tuning of these parameters.
Table C-3 Disk and adapter I/O tuning parameters
Parameter
Description
max_xfer_size
FC adapter maximum I/O that is issued.
max_transfer
Disk maximum I/O that is issued.
queue_depth
Disk maximum number of simultaneous I/Os.
The default is 20 but can be set as high as 256 for IBM Enterprise Storage Server® (ESS), IBM System Storage® DS6000™, and IBM System Storage DS8000®.
num_cmd_elems
FC adapter maximum number of simultaneous I/Os. The default is 200 per adapter but can be set up to 2048.
qdepth_enable
Subsystem Device Driver (SDD) data path optimizer (dpo) device queuing parameter.
The default is yes; setting this parameter to no disables
SDD queuing.
Use this parameter with IBM Enterprise System Storage, IBM System Storage DS6000, and IBM System Storage DS8000 storage.
lg_term_dma
Long-term DMA - Memory area the FC adapter uses to store I/O commands and data.
LTG
AIX volume group Logical Track Group parameter.
LTG specifies the largest I/O the LVM issues to the device driver.
In AIX 5.3, the LTG dynamically matches the disk maximum transfer parameter.
Both the disk and adapter have maximum transfer parameters that can be adjusted to handle larger I/O, reduce I/O splitting, and coalesce I/O as it moves up and down the stack. In addition, both have I/O queues that can be adjusted to accept more I/Os.
If SDD is used (IBM System Storage DS6000 or IBM System Storage DS8000), evaluate the data path optimizer (dpo) device I/O queue. SDD provides a virtual path (vpath) to the storage subsystem LUN and logical disk and provides several hdisk devices through the physical paths (such as FC adapters). So, with SDD you can issue queue_depth times the number of paths to a LUN.
However, when the dpo device queue is enabled (the default is yes), any excess I/Os that cannot be serviced in the disk queues go into the single wait queue of the dpo device. The benefit of this situation is that the dpo device provides fault-tolerant error handling. This situation might be wanted for high availability applications, but for other applications, there are advantages to disabling the dpo device queue and using multiple hdisk wait queues for each SDD vpath device.
 
AIX limitations for I/Os: This is not an exhaustive description and does not detail all possible AIX limitations for total number of I/Os. Also, carefully evaluate the queue parameters before you implement any changes. For tuning guides specific to a particular IBM storage system such as the IBM System Storage DS4000®, DS6000, or DS8000, see Appendix B, “Performance tooling and empirical performance analysis” on page 155.
Queue information can be monitored in AIX 5L V 5.3 and later by running iostat –D. For AIX 5L V5.1 and AIX 5L V5.2, SAR can be used. Run qdepth_enable=no to use the hdisk wait queue rather than the dpo device wait queue.
You should increase num_cmd_elems for the FC adapter from the default (initially starts at 400). Some of these parameters require a system reboot to take effect.
Run the following commands to display and modify disk and adapter parameters
and settings:
•Disk: max_transfer and queue_depth
 – ‘lquerypv –M hdisk#’ displays the maximum I/O size a disk supports.
 – ‘lsattr –El hdisk#’ displays the current disk values.
 – ‘lsattr –R -l hdisk# -a max_transfer hdisk#” displays the allowable values.
 – ‘chdev –l hdisk# -a max_transfer=value –P’ modifies the current disk values.
 
Device state: The device must be in an offline or disabled state before you change any parameters, and then you must run cfgmgr.
 
•Adapter: max_xfer_size, lg_term_DMA, and num_cmd_elems
 – ‘lsattr –El fcs#’ displays the current value.
 – ‘chdev –l fcs# -a max_xfer_size=value -P’ modifies the current value.
 
Device state: The device must be in an offline or disabled state before you change any parameters, and then you must run cfgmgr.
•SDD/DPO: qdepth_enable
 – ‘lsattr –El dpo’ displays the current value.
 – Run datapath to change the parameters if at SDD 1.6 or greater. Otherwise, run chdev. For example:
‘datapath set qdepth disable’
Available documentation
For detailed tuning information, see the following publications:
•Best Practices for Configuring your IO Subsystem for SAS9 Applications;
•How to Maintain Happy SAS Users:
•AIX V6 best practices for SAS Enterprise Business Intelligence (SAS eBI) users on IBM POWER6:
•IBM General Parallel File System (GPFS) wiki for SAS:
•Understanding Processor Utilization on Power Systems - AIX:
Migrating SAP BusinessObjects Business Intelligence platform with POWER7
The SAP BusinessObjects Business Intelligence (SBOP BI) platform enables enterprises to realize key insights about their corporate data. By delivering interactive business intelligence (BI) reports to users using any web application (that is, Internet or intranet), SAP BusinessObjects provides the information necessary to make more informed business decisions. As an integrated suite for reporting, analysis, and information delivery, SAP BusinessObjects BI provides a solution for increasing user productivity and reducing administrative efforts. You can analyze data from any of the SAP systems and from many supported database systems (including text or multi-dimensional online analytical processing (OLAP) systems). You can publish in many different formats to many different
publishing systems.
Platform support
Although the front-end reporting tools are mostly web browser or Windows application based, the server components of the SAP BusinessObjects BI platform can run on POWER servers using the AIX operating system.
The currently supported AIX versions for SBOP BI are:
•AIX 5L V5.3 TL3 SP3
•AIX V6.1 TL5
•AIX V7.1 TL1 SP1
The supported operating systems for SBOP BI components are listed in Platform Availability Matrix (PAM) at the SAP Service Marketplace, which is available at http://service.sap.com/pam (registration required; search for SBOP BI platform).
SBOP BI itself requires a database for its Central Management Service (CMS). For larger SBOP BI installations, run this database on a dedicated server. The supported databases for CMS are listed in an embedded Excel spreadsheet in the PAM document, and include the most common databases, such as IBM DB2.
SBOP BI can use various data sources for analysis and reporting. Besides SAP systems in general, and SAP Business Warehouse systems specifically, SBOP BI can also directly access database systems. The types of database systems that are supported as data sources are also listed in the embedded Excel spreadsheet of the PAM document.
Although the PAM document is the official source for any SAP-related platform support, the SAP Community Network topic, Supported Platforms/PARS for SAP Business Objects (http://www.sdn.sap.com/irj/boc/articles?rid=/webcontent/uuid/e01d4e05-6ea5-2c10-1bb6-a8904ca76411) provides a good overview of SAP BusinessObjects releases and supported platforms.
Sizing for optimum performance
Adequate system sizing is essential for successfully running an SBOP BI solution. Therefore, SAP includes SBOP BI in its Quick Sizer tool. Based on the number of users who are planning to use the SBOP BI applications, the Quick Sizer tool provides an SAP Application Performance Standard (SAPS) number and the memory necessary to run the applications. IBM can then provide the correct system configuration that is based on these numbers. The Quick Sizer tool is available at http://service.sap.com/quicksizer (registration required).
Also, the SAP BusinessObjects BI 4 - Companion Guide is available on the SAP Quick Sizer landing page at http://service.sap.com/quicksizer (registration required). To download the guide, open the web page and click Sizing Guidelines → Solutions & Platforms → SAP BusiessObjects. This guide explains the sizing process in detail using a
sizing example.
Landscape design
For general considerations about how to design an SBOP BI landscape, see the Master Guide (registration required), available at:
There are six different reference architecture landscapes available that demonstrate how to implement an SBOP BI solution that is based on the type of enterprise resource planning (ERP) and data warehouse solution you are using.
The architecture documents are in the Getting Started with SAP BusinessObjects BI Solutions topic on the SAP Community Network (http://scn.sap.com/docs/DOC-27193) in the Implementation and Upgrade section.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset