Load testing benchmarks on Oracle RAC Romain Basset

Load testing & benchmarks on Oracle RAC Romain Basset – IT PSS DP CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it

Outline • Motivations • DB load testing & benchmarks – Existing solutions • The application – Description – Examples • Conclusion CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 2

Motivations • Database workload is rising until LHC startup. → Database architecture should be tested to study performance and availability issues. • Internal software (COOL, CORAL) has to be tested in production-like environment. CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 3

Atlas example CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 4

DB load testing & benchmarks • To ensure the quality of service, three main aspects should be looked at : – Performance – Scalability – Reliability • Benchmarking is focusing on the first two. • Load testing aims at proving the last one. CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 5

TPC • TPC : Transaction Processing Performance Council • Benchmark specifications – TPC-C : Order-Entry system (OLTP) • select, update, insert & delete – TPC-H : Decision support system • complex select queries & few insert/delete statements • No out-of-the-box implementation • Simulated behaviors do not match with our use cases CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 6

Hammer. ORA • Tcl/Tk open source software • Possibility to replay a workload from Oracle trace files (10046) • Load testing • Implementation of TPC-like benchmarks – TPC-C and TPC-H • Master/Slave module out of order • No real monitoring / reporting feature • No C++ support CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 7

Swingbench • Free load generator & benchmarks • Implementation of OLTP-like benchmarks • Comprehensive graphical toolset for performance metrics and monitoring • Distributed execution • Not open source • No C++ support CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 8

Swing. Bench CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 9

Requirements • Possibility to plug new tests (C++) • Distributed execution • Monitoring feature • Performance report for analysis CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 10

Technologies • • CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Python AFS SSH XML HTML, PHP, JPGraph CORAL LEMON Oracle Load testing & Benchmarking Framework for Oracle RAC - 11

Typical scenario 1. Spawn the clients 2. Query the DB 3. Write results on files (on AFS) 4. Gather results 5. Query the DB for statistics CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 12

Configuration • Configuration file : <? xml version="1. 0" ? > - <config> - <machines> <client>node 1</client> <client>node 2</client> <client>node 3</client> <server>server 1</server> <server>server 2</server> </machines> <statistic cpu="1" wait. Events="1" /> <execution num. Cli. Node="1" num. Sess. Per. Cli. Node="2" num. Sess. Ini="1" num. Sess. Inc="1" step. Duration="20" max. Test. Duration="3600" /> <locations base. Dir="/afs/cern. ch/user/j/jsmith/Perf. Tests/" client. Log. Dir="/afs/cern. ch/user/j/jsmith/Perf. Tests/logs/" /> - <metrics> <dynamic>Object inserted</dynamic> <static>Memory usage in MB</static> </metrics> <client. Conf db. User="ORACLE_USER" client. Cmd="client. py" client. Exe="python" report. Period="10" report. Db. Connect="/dbdev/Oracle. Stress" /> • Authentication. xml (CORAL): <? xml version="1. 0" ? > - <connectionslist> - <connection name="oracle: //service_name/monitoring_username"> <parameter name="user" value="monitoring_username" /> <parameter name="password" value="monitoring_password" /> </connection> - <connection name="oracle: //service_name_node 1/client_username"> <parameter name="user" value="client_username" /> <parameter name="password" value="client_password" /> </connection> - <connection name="oracle: //service_name_node 2/client_username"> <parameter name="user" value="client_username" /> <parameter name="password" value="client_password" /> </connectionslist> • Dblookup. xml (CORAL): <? xml version="1. 0" ? > - <servicelist> - <logicalservice name="/Oracle. Stress. Report"> <service name="oracle: //service_name/monitoring_username" access. Mode="readonly" authentication="password" /> </logicalservice> </servicelist> - <file. To. Save> <file name="Client execution script">client. py</file> <file name="Configuration file">test. Conf. xml</file> </file. To. Save> </config> CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 13

Monitoring (RAC CPU) • RAC CPU monitoring • Get the values from Oracle : GV$SYSMETRIC CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 14

Monitoring (wait events) • Average active sessions (OEM style) • Get the values from Oracle : GV$WAITCLASSMETRIC CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 15

LEMON • LEMON plots for client and server nodes – Cpu – Load – Network (in & out) • Linked or downloaded from LEMON web pages CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 16

Performance (client) • Metric measured by the clients • Get the values from the log files CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 17

Performance (aggregated) CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • Aggregated measures from the clients • Get the values from the log files • Number of sessions (GV$SESSION) Load testing & Benchmarking Framework for Oracle RAC - 18

Possibility to write new tests • No specific language • No specific metrics – Dynamic, time dependant (throughput) – Static, time independant (response time) • Specific input – Client ID – Result file path – Result output frequency • Specific output (CSV file) – Timestamp – Time elapsed since last result – Values for the different metrics CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 19

Examples • CORAL : generic relational database access API for Physics applications • COOL based on CORAL, defines a schema and provides C++ API to store/retrieve contitions data CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 20

Examples • COOL performance tests – – Insertions : store. Object() Retrievals : find. Objects(), browse. Objects() Benchmarking (using different COOL versions) Tests on production sized dataset (using 1 TB database ~1 -year Atlas PVSS data) • Stress testing using COOL insertion test – 10 clients – 4 -node RAC – 1 TB of data in ~10 hours CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 21

Conlusion • Tests already conducted : – Performance when changing Oracle configuration – Performance in COOL package – Stress testing on 11 g (by Balys) • Tutorial available (on PSS wiki) • Future work – Comparison of 2 test runs (new report) – Integration with COOL performance suite – Automatic checking of performance issues before a software release CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 22

References • Pro Oracle Database 10 g RAC on Linux, J. Dyke and S. Shaw, chap. 5 & 23. • TPC, http: //tpc. org/ • Hammer. ORA, http: //hammerora. sourceforge. net/ • Swing. Bench, http: //www. dominicgiles. com/swingbench. html • CORAL, http: //pool. cern. ch/coral • COOL, http: //cool. web. cern. ch/cool • Oracle Load Testing Tools Documentation, https: //twiki. cern. ch/twiki/bin/view/PSSGroup/Oracle. RACLoad. Testing • Tutorial, https: //twiki. cern. ch/twiki/bin/view/PSSGroup/Oracle. RACLoad. Testing. Tuto CERN IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it Load testing & Benchmarking Framework for Oracle RAC - 23