Optimizing Oracle Server Performance on LINUX

Tips for Maximizing Toad Productivity OOUG 2009

Columbus, OH July 16th
2008 Quest Software, Inc. ALL RIGHTS RESERVED.

Agenda
Apply low hanging fruit fixes to speed up Oracle performance on Linux

Well look at both database and operating system level modifications

Goal is simple how can we maximize Oracle

performance on Linux servers!

Bert Scalzo
Database Expert & Product Architect for Quest Software
Oracle Background: Worked with Oracle databases for over two decades (starting with version 4) Work history includes time at both Oracle Education and Oracle Consulting Academic Background: Several Oracle Masters certifications BS, MS and PhD in Computer Science MBA (general business) Several insurance industry designations Key Interests: Data Modeling Database Benchmarking Database Tuning & Optimization "Star Schema" Data Warehouses Oracle on Linux and specifically: RAC on Linux Articles for: Oracles Technology Network (OTN) Oracle Magazine, Oracle Informant PC Week (eWeek) Articles for: Dell Power Solutions Magazine The Linux Journal www.linux.com www.orafaq.com

Books by Bert

Coming in 2009

Couple of Questions (for both our benefit)

How many people using

Where using Linux

Production Development Personal Education

Which Linux Distribution

Oracle Enterprise Linux Redhat Enterprise Server SUSE Linux Enterprise Other ???

LINUX Server Popularity

IDC Linux Servers:

Posted 12th consecutive quarter of double-digit growth Year-over-year revenue growth of 45.1% Unit shipments up 32.1% Revenue exceeded $1.4 billion quarterly (will reach 9.1 billion by 2008) HP was first with 24.3% market share IBM was second with 20.3% market share Customers continue to expand role of Linux servers into an ever increasing array of workloads in both commercial and technical segments of the market
Gartner Linux Servers:

One of hottest applications for Linux is on RDBMS servers Linux was the fastest growing platform for RDBMS past year Enterprises turning to Linux as an alternative for older Unixs

LINUX Web Popularity

In Netcrafts July 2000 survey of 18,169,498 web sites.

#1 OS !!!

www.netcraft.com/survey/index-200007.html

LINUX Web Popularity

http://survey.netcraft.com/index-200106.html

LINUX Web Popularity

http://news.netcraft.com/archives/2003/07/index.html

LINUX Gaining Momentum

LINUX Gaining Momentum

http://www.gartner.com/DisplayDocument?ref=g_search&id=414985

Popularity != Performance

Some plausible reasons for this:

Relative newness of the LINUX OS in general RISC UNIX sys admin unfamiliar with INTEL

INTEL UNIX sys admin unfamiliar with LINUX

Windows based sys admin unfamiliar with UNIX Oracle, Oracle, Oracle

Where Can We Look

What is Tunable: Hardware

Operating System
Database Network

Linux Tuning

Application

SQL Tuning

SQL Tuning Advice focus on application first, and then database and possibly network. Linux Tuning Advice focus on hardware, LINUX and Oracle database configurations.

Performance Pyramid

Network Hardware OS DBMS Application

Our Main Goal

To squeeze all the blood out of our LINUX turnip

Which LINUX Distribution

OS SLES-9 Red Hat Enterprise AS/ES 4 Red Hat Enterprise AS/ES 3 SLES-9 SLES-8 Red Hat Enterprise Linux AS/ES 4 Red Hat Enterprise Linux AS/ES 3 Red Hat Enterprise Linux AS/ES 2.1 Red Flag DC Server 4.0 (China Only) Monta Vista Carrier Grade Linux 3.1 (Embedded) Miracle Linux Standard Edition 2.1 (Japan only) Asianux 2.0 Asianux 1.0 UnitedLinux 1.0 SLES-9 SLES-8 Red Hat Enterprise Linux AS/ES 4 Product 10gR2 10gR2 10gR2 10g 10g 10g 10g 10g 10g 10g 10g 10g 10g 9.2 9.2 9.2 9.2 Status Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified Certified

Red Hat Enterprise Linux AS/ES 3

Red Hat Enterprise Linux AS/ES 2.1 Red Flag DC Server 4.0 (China Only) Monta Vista Carrier Grade Linux 3.0 (Embedded) Miracle Linux Standard Edition 2.1 (Japan only) Asianux 1.0

9.2
9.2 9.2 9.2 9.2 9.2

Certified
Certified Certified Certified Certified Certified

Which LINUX Distribution

http://news.netcraft.com/archives/2005/12/05/strong_growth_for_debian.html

Test Method
TPC benchmark (www.tpc.org)
TPC Benchmark C (TPC-C) is an OLTP workload. It is a mixture of read-only and update intensive transactions that simulate the activities found in complex OLTP application environments. It does so by exercising a breadth of system components associated with such environments, which are characterized by: The simultaneous execution of multiple transaction types that span a breadth of complexity On-line and deferred transaction execution modes Multiple on-line terminal sessions Moderate system and application execution time Significant disk input/output Transaction integrity (ACID properties) Non-uniform distribution of data access through primary and secondary keys Databases consisting of many tables with a wide variety of sizes, attributes, and relationships Contention on data access and update Excerpt from TPC BENCHMARK C: Standard Specification, Revision 3.5

Test Platform

Simulate 200 Users

Carpenter Needs Tools

Between the hardware, LINUX and Oracle there are far too many dependent variables for tuning to rely merely on human intuition and experience. Free is nice but you often get what you pay for!

If your customer or management are willing to buy more hardware for better performance, then there is obviously budget for tuning tools press the issue! Golden Rule #1: Use OS & DB tuning tools! Golden Rule #2: Dont rely on free tools only!

Tools Used
This is not a sales pitch Im a DBA (a.k.a techno-nerd)!
Benchmark Factory Create, populate and index the test database (200 megs) Simulate 200 concurrent users via a single or many PCs LINUX Freebies Command line utilities: X-Windows utilities:

sar, mpstat, iostat, vmstat, linmon, ipcs, top, free, hdparm, linuxconf, slmon, gtop, ktop, xload, xosview, kperfmeter, gkrellmm, procmeter, gpowertweak,

Benchmark Factory - GUI

Benchmark Factory - Agent

LINUX cmd tools

sar r 01:00:00 01:10:00 01:20:00 01:30:00 01:40:03 01:50:01 02:00:00 Average: vmstat procs r b 0 5 0 1 0 3 0 1 0 1 0 0 0 0 0 5 1 2 1 0 0 1 1 0 1 1 0 3 1 2 1 2 1 1 0 2 0 5 0 2 0 0 memory buff cache 440 17492 440 16992 432 17140 440 17344 468 17024 460 16940 444 16508 452 16596 448 16500 432 16576 468 16516 456 16680 444 16756 456 16516 464 16468 464 16320 468 16588 460 16416 440 16284 444 15776 448 15480 swap si so 0 124 0 230 0 253 0 171 0 159 2 164 1 120 0 168 12 190 0 160 0 136 5 102 0 120 0 106 0 90 2 100 0 115 18 73 2 76 0 56 0 51 io bi 145 194 164 172 189 181 166 138 184 158 183 126 157 185 138 179 147 135 125 134 109 bo 81 97 155 88 143 131 124 122 104 128 135 65 93 137 115 133 82 111 84 89 47 system in cs 906 338 1114 435 992 373 987 394 1104 426 1059 407 1006 394 870 344 1074 419 991 394 1086 433 815 332 957 388 1097 441 899 348 1099 442 910 367 882 338 841 343 890 361 767 316 cpu sy id 5 90 6 86 5 84 5 87 7 82 5 87 5 87 7 87 7 86 6 86 6 84 8 83 9 81 9 81 7 82 8 84 8 84 7 86 6 87 5 89 6 87 PM kbmemfree kbmemused PM 465132 48128 PM 463352 49908 PM 463356 49904 PM 1652 511608 PM 1604 511656 PM 376852 136408 295324 217936 %memused kbmemshrd kbbuffers 9.37 0 2708 9.72 0 2784 9.72 0 2784 99.67 0 532 99.68 0 768 26.57 0 1120 42.46 0 1782 kbcached kbswpfree kbswpused 26136 525288 0 26732 525288 0 26736 525288 0 18216 447360 77928 17228 369024 156264 25692 503344 21944 23456 482598 42690 %swpused 0.00 0.00 0.00 14.83 29.74 4.17 8.12

w 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

swpd 122308 124608 127132 128836 130428 132052 133240 134920 136800 138400 139764 140780 141984 143044 143944 144940 146092 146820 147580 148144 148652

free 1588 1588 1588 1588 1592 1596 1600 1588 1592 1600 1588 1596 1588 1588 1588 1588 1596 1588 1588 1596 1588

us 5 8 11 8 11 7 8 6 7 9 9 9 10 10 12 8 8 7 8 6 7

LINUX gui tools

Here We Go
Work up Performance Pyramid:

Application (TPC no mods) Database Operating System Hardware

Other general benchmarking advice: Limit to one item per try OS Low Hanging Fruit 1st DB Low Hanging Fruit 2nd Easy items before hard stuff

DB1 - Initial Database Creation

Database Block Size = 2K SGA Buffer Cache = 64M SGA Shared Pool = 64M SGA Redo Cache = 4M Redo Log Files = 4M Tablespaces = Dictionary
Test database created via Oracles Database Configuration Assistant Prior to 9i, the tools default settings were ridiculously lower than these. A novice DBA or system admin user might use those lower default values and get much worse results!

DB2 Increase Buffer Cache & Shared Pool

Database Block Size = 2K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 4M Redo Log Files = 4M Tablespaces = Dictionary

DB3 Increase Redo Cache & Log Files

Database Block Size = 2K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 16M Redo Log Files = 16M Tablespaces = Dictionary

DB4 4K Block Size

Database Block Size = 4K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 16M Redo Log Files = 16M Tablespaces = Dictionary

DB5 Local Tablespaces

Database Block Size = 4K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 16M Redo Log Files = 4M Tablespaces = Local

DB6 8K Block Size

Database Block Size = 8K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 16M Redo Log Files = 16M Tablespaces = Local
Be careful for example on RAC setup choosing larger block size may cause interconnect overload / hot-spots

DB7 IO Slaves + Increase Redo Log Files

Database Block Size = 8K SGA Buffer Cache = 128M SGA Shared Pool = 128M SGA Redo Cache = 16M Redo Log Files = 64M Tablespaces = Local INIT.ORA session_cached_cursors=2 db_block_lru_latches=8 dbwr_io_slaves=4 lgwr_io_slaves=4

Oracle Modification Results

DB2 DB3 DB4 DB5 DB6 load time 48.57 41.39 17.35 15.07 11.42 % Improved -1.73% -17.35% -138.56% -15.13% -31.96% trans/sec 8.15 9.15 10.09 10.18 10.43 10.68 % Improved 10.88% 9.33% 0.89% 2.36% 2.42%

DB1 49.41

DB7 DB Final 10.48 10.48 -8.97% -371.47% 10.72 10.72 0.32% 23.93%

OS1 IPC + Monolithic Kernel

Kernel = 2.2.14-5smp

Linuxconf = monolithic
Shared memory
/usr/src/linux-2.2.16/include/asm/shmparam.h #define SHMMAX 0x13000000

Monolithic Kernel - If you compile everything into the kernel to exactly match your hardware and thus make minimal use of modules.

Semaphors
/usr/src/linux-2.2.16/include/linux/sem.h #define SEMMNI 100 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 100 #define SEMVMX 32767

OS2 Newer Minor Kernel Update

Kernel = 2.2.16-3smp

Linuxconf = monolithic
Shared memory
/usr/src/linux-2.2.16/include/asm/shmparam.h #define SHMMAX 0x13000000

Semaphors
/usr/src/linux-2.2.16/include/linux/sem.h #define SEMMNI 100 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 100 #define SEMVMX 32767

OS3 Newer Major Kernel Update

Kernel = 2.4.1smp
Edit /etc/sysctl.conf kernel.shmmax = 2147483647 kernel.sem = 250 32000 100 128 The sem values are: SEMMSL SEMMNS SEMOPM SEMMNI To set w/out a reboot on Redhat: sysctl -p

Linuxconf = monolithic
Shared memory
/usr/src/linux-2.4.00/include/linux/shm.h #define SHMMAX 0x13000000

Semaphors
/usr/src/linux-2.4.00/include/linux/sem.h #define SEMMNI 128 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 128 #define SEMVMX 32767

OS4 Newer Minor Kernel Update After Major

Kernel = 2.4.17smp
Edit /etc/sysctl.conf kernel.shmmax = 2147483647 kernel.sem = 250 32000 100 128 The sem values are: SEMMSL SEMMNS SEMOPM SEMMNI To set w/out a reboot on Redhat: sysctl -p

Linuxconf = monolithic
Shared memory
/usr/src/linux-2.4.00/include/linux/shm.h #define SHMMAX 0x13000000

Semaphors
/usr/src/linux-2.4.00/include/linux/sem.h #define SEMMNI 128 #define SEMMSL 512 #define SEMMNS (SEMMNI*SEMMSL) #define SEMOPM 128 #define SEMVMX 32767

OS5 noatime file attribute

The ext2 file system normally records when a file was last modified and last accessed. We dont need to know access time for Oracle files as background programs open and access the files until shutdown.
chattr +A file_name chattr R +A directory_name Actually, this can be done on Windows server as well!

HKEY_LOCAL_MACHINE\Sy stem\CurrentControlSet\Control\ FileSystem

Edit /etc/fstab
/dev/sda6 /dev/sda1 /dev/cdrom /dev/md0 /dev/fd0 none none /dev/sda5 / /boot /mnt/cdrom /u01 /mnt/floppy /proc /dev/pts swap ext2 ext2 iso9660 ext2 auto proc devpts swap defaults,noatime defaults,noatime noauto,owner,ro defaults,noatime noauto,owner defaults,noatime gid=5,mode=620 defaults,noatime 1 1 0 1 0 0 0 0 1 2 0 2 0 0 0 0

NtfsDisableLastAccessUpdate=1

OS6 bdflush rate for VM

The bdflush file is closely related to the operation of the virtual memory (VM) subsystem of the Linux kernel and also has a little influence on disk usage. Default = "40 500 64 256 500 3000 500 1884 2" Redhat 6.1
echo 100 1200 128 512 15 500 1884 2 > /proc/sys/vm/bdflush

1st parm is max # of dirty buffers in cache. Higher = delayed disk writes.

2nd parm is max # of dirty buffers per write. Higher = delayed, bursty I/O.
3rd parm is # of buffers added to list of free buffers by refill_freelist. 4th parm refill_freelist comes across more than nref_dirt dirty buffers, it will wake up bdflush. 5th parm is max time Linux waits before writing dirty buffers to disk for data blocks.

Redhat 6.2 (edit /etc/sysctl.conf)

vm.bdflush = 100 1200 128 512 15 5000 500 1884 2

Restart daemon
/etc/rc.d/init.d/network restart

6th parm is max time Linux waits before writing dirty buffers to disk for file system metadata.

OS7 Ext3 File System

Ext3 is a journaling file system based upon ext2 benefits include: both backward and forward compatible with ext2 easy to change from ext2 to ext3 and gain the benefits without reformatting does not require a file system check even after an unclean system shutdown recovery time does not depend on the size of the file system or the number of files provide stronger guarantees about data integrity in case of an unclean system shutdown faster (higher throughput) than ext2 as ext3's journaling optimizes hard drive head motion

Create TPC-C Database EXT2 348 EXT3 228 Reiser 378 IBM JFS 351 RAW 396 RAW X 2 396

Load Tables 295 158 297 277 290 240

Create Indexes 234 122 537 231 225 263

Total Time 877 508 1,212 859 911 899

LINUX Modification Results

DB1 DB Final OS1 OS2 OS3 OS4 OS5 OS6 OS7 OS Final Total load time 49.41 10.48 9.54 9.40 8.32 8.20 5.58 4.43 3.80 3.80 3.80 % Improved -371.47% -9.85% -1.49% -12.98% -1.46% -46.95% -25.96% -16.58% -151.05% -1200.26% trans/sec 8.15 10.72 11.51 11.52 12.82 12.90 13.88 14.99 20.51 20.51 20.51 % Improved 23.93% 6.90% 0.10% 10.09% 0.66% 7.09% 7.37% 26.92% 43.88% 60.25%

Some Final Thoughts

IDE Drives Default IDE settings stink they must be changed and generally yield 500% improvement!!! Test speed via hdparm Tt /dev/hda Tune spped via hdparm X66 d1 u1 m16 c3 /dev/hda MTS Problematic under LINUX, more than a few TARs registered with Oracle technical support Hardware RAID LINUX driver support generally tier 2 and often provided by someone other than the vendor 4K File System mke2fs b 4096 mount_point LINUX default file system block size = 1K, but max is 4K Typical improvement of 5% for each increase (i.e. 5% for 1k>2k and 5% for 2k>4k) PGCC Compiler www.goof.com/pcg PGCC is a version of the GNU C compiler (gcc) with special optimizations for Pentium CPU The 30% improvement achieved by the Intel engineers is rare in the real world (dont bank on it) Typical improvement of 5% using intermediate optimization level like -O3 and no fine-tuning Recompile kernel only small improvement since Oracle makes few calls to LINUX kernel Recompile glibc bigger improvement possible, but headache unless already know process

Questions and Answers

Thank You
Presenters: Bert Scalzo: [email protected]

Note: these slides should be available on OOUG web site, but well also make sure to post them on our companys web site:
www.toadworld.com/Experts/BertScalzosToadFanaticism/tabid/318/Default.aspx