Optimizing Oracle Server Performance on LINUX

1
Optimizing Oracle Server Performance on LINUX
Bert Scalzo, Ph.D. Bert.Scalzo_at_Quest.com
2
Learning Objectives

• Apply low hanging fruit fixes to speed up
  Oracle performance on Linux well look at both
  database and operating system level mods
• Goal is simple how can we maximize Oracle
  performance in Linux servers!

3
Speakers Qualifications

• Oracle Solutions Product Architect for Quest
  Software
• Chief architect for Quests popular TOAD
  product
• Oracle DBA for 20 years, versions 4 through 10g
• Worked for Oracle Education Consulting
• Holds several Oracle Masters (DBA CASE)
• BS, MS, PhD in Computer Science and also an MBA
• LOMA insurance industry designations FLMI and
  ACS
• Books
• The TOAD Handbook (Feb 2003)
• Oracle DBA Guide to Data Warehousing and Star
  Schemas (Mar 2003)
• The TOAD Pocket Reference 2nd edition (June 2005)
• Articles
• Oracle Magazine
• Oracle Technology Network (OTN)
• Oracle Informant
• PC Week (now E-Magazine)
• Linux Journal
• www.Linux.com

4
New 2nd Edition June 2005
5
About Quest Software

• Quest Software (NASDAQ QSFT)
• Founded 1987
• More than 2000 employees in 40 offices North
  America, South America, Europe, Asia, Australia
• Application management leader 75 of Fortune 500
• Develop, deploy, manage and maintain enterprise
  applications without downtime or business
  interruption
• Best known in the Oracle community for TOAD,
  Spotlight, Quest Central, Shareplex, etc.

6
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

7
LINUX Web Popularity
In Netcrafts July 2000 survey of 18,169,498 web
sites.
1 OS !!!
www.netcraft.com/survey/index-200007.html
8
LINUX Web Popularity
http//survey.netcraft.com/index-200106.html
9
LINUX Web Popularity
http//news.netcraft.com/archives/2003/07/index.ht
ml
10
LINUX Gaining Momentum
11
LINUX Gaining Momentum
http//www.gartner.com/DisplayDocument?refg_searc
hid414985
12
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

13
Where Can We Look

• What is Tunable
• Hardware
• Operating System
• Database
• Network
• Application

Linux Tuning
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.
14
Performance Pyramid
Our Focus
Network
Hardware
OS
DBMS
Application
15
Our Main Goal
To squeeze all the blood out of our LINUX turnip
16
Which LINUX Distribution
17
Which LINUX Distribution
http//news.netcraft.com/archives/2005/12/05/stron
g_growth_for_debian.html
18
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
19
Test Platform
Simulate 200 Users
20
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!
21
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 sar, mpstat, iostat,
  vmstat, linmon, ipcs,
• top, free, hdparm, linuxconf, slmon,
• X-Windows utilities gtop, ktop, xload,
  xosview, kperfmeter,
• gkrellmm, procmeter, gpowertweak,

22
Benchmark Factory - GUI
23
Benchmark Factory - Agent
24
sar r 010000 PM kbmemfree kbmemused memused
kbmemshrd kbbuffers kbcached kbswpfree kbswpused
swpused 011000 PM 465132 48128
9.37 0 2708 26136 525288
0 0.00 012000 PM 463352 49908
9.72 0 2784 26732 525288
0 0.00 013000 PM 463356 49904
9.72 0 2784 26736 525288
0 0.00 014003 PM 1652
511608 99.67 0 532 18216
447360 77928 14.83 015001 PM 1604
511656 99.68 0 768 17228
369024 156264 29.74 020000 PM
376852 136408 26.57 0 1120
25692 503344 21944 4.17 Average
295324 217936 42.46 0 1782
23456 482598 42690 8.12
vmstat procs memory
swap io system cpu r b w
swpd free buff cache si so bi bo
in cs us sy id 0 5 0 122308 1588
440 17492 0 124 145 81 906 338 5 5
90 0 1 0 124608 1588 440 16992 0 230
194 97 1114 435 8 6 86 0 3 0 127132
1588 432 17140 0 253 164 155 992
373 11 5 84 0 1 0 128836 1588 440
17344 0 171 172 88 987 394 8 5 87
0 1 0 130428 1592 468 17024 0 159 189
143 1104 426 11 7 82 0 0 0 132052
1596 460 16940 2 164 181 131 1059 407
7 5 87 0 0 0 133240 1600 444 16508
1 120 166 124 1006 394 8 5 87 0 5
0 134920 1588 452 16596 0 168 138 122
870 344 6 7 87 1 2 0 136800 1592
448 16500 12 190 184 104 1074 419 7 7
86 1 0 0 138400 1600 432 16576 0 160
158 128 991 394 9 6 86 0 1 0 139764
1588 468 16516 0 136 183 135 1086
433 9 6 84 1 0 0 140780 1596 456
16680 5 102 126 65 815 332 9 8 83
1 1 0 141984 1588 444 16756 0 120 157
93 957 388 10 9 81 0 3 0 143044
1588 456 16516 0 106 185 137 1097 441
10 9 81 1 2 0 143944 1588 464 16468
0 90 138 115 899 348 12 7 82 1 2
0 144940 1588 464 16320 2 100 179 133
1099 442 8 8 84 1 1 0 146092 1596
468 16588 0 115 147 82 910 367 8 8
84 0 2 0 146820 1588 460 16416 18 73
135 111 882 338 7 7 86 0 5 0 147580
1588 440 16284 2 76 125 84 841
343 8 6 87 0 2 0 148144 1596 444
15776 0 56 134 89 890 361 6 5 89
0 0 0 148652 1588 448 15480 0 51 109
47 767 316 7 6 87
LINUX cmd tools
25
LINUX gui tools
26
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

27
DB1 - Initial Database Creation
Test database created via Oracles Database
Configuration Assistant
Database Block Size 2K SGA Buffer Cache
64M SGA Shared Pool 64M SGA Redo Cache
4M Redo Log Files 4M Tablespaces Dictionary
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!
28
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
29
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
30
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
31
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
32
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
33
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_cursors2 db_block_lru_lat
ches8 dbwr_io_slaves4 lgwr_io_slaves4
34
Oracle Modification Results
initial
cache pool
log buffer
4k block
local tsp
8k block
io slaves
35
OS1 IPC Monolithic Kernel
Kernel 2.2.14-5smp Linuxconf
monolithic Shared memory /usr/src/linux-2.2.16/in
clude/asm/shmparam.h define SHMMAX
0x13000000 Semaphors /usr/src/linux-2.2.16/includ
e/linux/sem.h define SEMMNI 100 define SEMMSL
512 define SEMMNS (SEMMNISEMMSL) define
SEMOPM 100 define SEMVMX 32767
Monolithic Kernel - If you compile everything
into the kernel to exactly match your hardware
and thus make minimal use of modules.
36
OS2 Newer 2.2 Kernel
Kernel 2.2.16-3smp Linuxconf
monolithic Shared memory /usr/src/linux-2.2.16/in
clude/asm/shmparam.h define SHMMAX
0x13000000 Semaphors /usr/src/linux-2.2.16/includ
e/linux/sem.h define SEMMNI 100 define SEMMSL
512 define SEMMNS (SEMMNISEMMSL) define
SEMOPM 100 define SEMVMX 32767
37
OS3 2.4 Kernel
Kernel 2.4.1smp 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
(SEMMNISEMMSL) define SEMOPM 128 define
SEMVMX 32767

• 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

38
OS4 Newer 2.4 Kernel
Kernel 2.4.17smp Linuxconf
monolithic Shared memory /usr/src/linux-2.4.00/in
clude/linux/shm.h define SHMMAX
0x13000000 Semaphors /usr/src/linux-2.4.00/includ
e/linux/sem.h define SEMMNI 128 define SEMMSL
512 define SEMMNS (SEMMNISEMMSL) define
SEMOPM 128 define SEMVMX 32767

• 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

39
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 Edit /etc/fstab /dev/sda6
/ ext2 defaults,noatime 1
1 /dev/sda1 /boot ext2
defaults,noatime 1 2 /dev/cdrom /mnt/cdrom
iso9660 noauto,owner,ro 0 0 /dev/md0
/u01 ext2 defaults,noatime 1
2 /dev/fd0 /mnt/floppy auto
noauto,owner 0 0 none /proc
proc defaults,noatime 0 0 none
/dev/pts devpts gid5,mode620 0
0 /dev/sda5 swap swap
defaults,noatime 0 0
Actually, this can be done on Windows server as
well! HKEY_LOCAL_MACHINE\System\CurrentControlSe
t\Control\FileSystem NtfsDisableLastAccessUpdate
1
40
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 gt /proc/sys/vm/bdflush 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
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. 6th parm is max
time Linux waits before writing dirty buffers to
disk for file system metadata.
41
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

42
LINUX Modification Results
new kernel
ipc mono
new kernel
2.4 kernel
noatime
bdflush
ext3 files
43
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 1kgt2k and 5 for 2kgt4k)
• PGCC Compiler
• www.goof.com/pcg
• PGCC is a version of the GNU C compiler (gcc)
  with special optimizations for Pentium CPU