1 / 44

Optimizing Oracle Server Performance on LINUX

Optimizing Oracle Server Performance on LINUX. Tips for Maximizing Toad Productivity OOUG 2009 Columbus, OH July 16 th. Agenda. Apply “low hanging fruit” fixes to speed up Oracle performance on Linux We’ll look at both database and operating system level modifications …

pink
Download Presentation

Optimizing Oracle Server Performance on LINUX

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optimizing Oracle Server Performance on LINUX • Tips for Maximizing Toad Productivity • OOUG 2009 • Columbus, OH • July 16th

  2. Agenda • Apply “low hanging fruit” fixes to speed up Oracle performance on Linux • We’ll look at both database and operating system level modifications … • Goal is simple – how can we maximize Oracle performance on Linux servers!

  3. 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: • Oracle’s 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 3

  4. Books by Bert … Coming in 2009 … 4

  5. 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 ???

  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 Unix’s

  7. LINUX Web Popularity In Netcraft’s 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.html

  10. LINUX Gaining Momentum

  11. LINUX Gaining Momentum http://www.gartner.com/DisplayDocument?ref=g_search&id=414985

  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/strong_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:Don’t rely on free tools only!

  21. Tools Used This is not a sales pitch – I’m 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 PC’s • 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. LINUX cmd tools sar –r 01:00:00 PM kbmemfree kbmemused %memused kbmemshrd kbbuffers kbcached kbswpfree kbswpused %swpused 01:10:00 PM 465132 48128 9.37 0 2708 26136 525288 0 0.00 01:20:00 PM 463352 49908 9.72 0 2784 26732 525288 0 0.00 01:30:00 PM 463356 49904 9.72 0 2784 26736 525288 0 0.00 01:40:03 PM 1652 511608 99.67 0 532 18216 447360 77928 14.83 01:50:01 PM 1604 511656 99.68 0 768 17228 369024 156264 29.74 02:00:00 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

  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 Oracle’s “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 tool’s 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 Be careful – for example on RAC setup choosing larger block size may cause interconnect overload / hot-spots 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_cursors=2 db_block_lru_latches=8 dbwr_io_slaves=4 lgwr_io_slaves=4

  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/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 Monolithic Kernel - If you compile everything into the kernel to exactly match your hardware and thus make minimal use of modules.

  36. 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

  37. OS3 – Newer Major Kernel Update 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 (SEMMNI*SEMMSL) #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 Minor Kernel Update After Major Kernel = 2.4.17smp 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 • 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 don’t 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 gid=5,mode=620 0 0 /dev/sda5 swap swap defaults,noatime 0 0 Actually, this can be done on Windows server as well! HKEY_LOCAL_MACHINE\System\CurrentControlSet\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 3000500 1884 2" Redhat 6.1 echo “100 1200 128 512 15 500 1884 2” > /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 TAR’s 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 (don’t 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

  44. Questions and Answers … Thank You Presenters: Bert Scalzo: Bert.Scalzo@Quest.com Note: these slides should be available on OOUG web site, but we’ll also make sure to post them on our company’s web site: www.toadworld.com/Experts/BertScalzosToadFanaticism/tabid/318/Default.aspx

More Related