Guest post from Bjørn Kisbye Engsig, Oracle Real World Performance team
Does your database perform as expected? Can it deal with a two fold increase in load? Where are the bottlenecks you haven’t considered?
Measuring how your database performs with a given application has always been a tradeoff between reality and complexity. A new tool from Oracle's Real World Performance team – RWP*Load Simulator – “rwloadsim” – can assist in closing this gap. It gives the performance engineer a simple way to write a simulation of the important core of an application, and measure the performance impacts of various types of changes at both application and database side. The rwloadsim tool has a programming language that allows you to quickly prototype some business processes and execute these at your application server with connections to your database, thereby simulating the real application workload. The tool is multi-threaded and allows you to run your simulation much like the complete application would, possibly using connection pools such as those available in Java, python or C. Using rwloadsim, you can control settings such as transaction arrival rates, mix of transactions, and number of processes and threads. Lots of execution statistics such as timing, counts and histograms are saved and used to create various graphs, awr and other reports, etc. As examples, the two graphs on the left side below show spikes of wait events that would be worth investigating further and the two graphs on the right side visualize the famous “hockey stick curve” that tells how far you can increase the load on your system.
In addition to simulations, the RWP*Load Simulator can be very useful in benchmarks, proof-of-concepts and similar cases, where application and database performance is investigated. Additionally, the scripting features of rwloadsim gives you an ease of use integration between the standard Linux shell and the Oracle Database, which can be very useful in areas such as database administration. As such a scripting tool, rwloadsim is a very nice supplement to SQL*Plus.
If you think of a bit of bash and SQL, a dose of PL/SQL, a nip of C or Java, a fragment of Oracle Call Interface, a dash of awk, a grain of sed plus a few drops of secret sauce and put it all into one tool, you will have an idea about what rwloadsim is. In its core, it is a programming language that takes a bit of each of these known tools and programming languages and integrates them into one.
The sample code on the left shows how the RWP*Load Simulator language can be used to run a simulation and create data for graphs like those seen in the image above. The code sample includes three procedures that implement simulations of three different “business transactions”. They are being executed in 20 worker threads, which with a simulated arrival rate of 30 per second randomly execute one of the declared procedures for a total period of 600s. During execution, a session will automatically be acquired and released as necessary, so the 20 worker threads will share the pool of five sessions. With the data saved by rwloadsim, the throughput of each can be plotted together with database CPU and time as shown in the upper left graph in the previous image. The graph below shows a plot of active session history that has also been saved during the execution, and combined they give the performance engineer suggestions for what to investigate next.
The two graphs on the right in the image above show a representation of database performance when load on the database increases; the load is shown on the x-axis with some arbitrary unit. In the top right graph the typical “hockey stick curve” tells that the system does not scale well beyond a load of around 250. The graph below shows percentiles of execution time of one specific business transaction, which can be compared to a service level agreement. As the example shows, the service level agreement specifies that 95% of the executions must be 0.01s or faster, and you can see that this is the case for loads up to 260. Consequently if your normal load were 150 you could not safely double your load.
The rwloadsim tool is provided as Oracle Open Source, and source code as well as pre-compiled binaries for Linux and Solaris are available at https://github.com/oracle/rwloadsim. In addition to documentation with many samples, it comes with a complete oltp style workload that is ready to use. All graphs above are generated using this sample workload.
Bjørn Kisbye Engsig has been working with the Oracle Database throughout his entire career starting in the mid-1980 and has been an employee of Oracle Corporation for a total of almost 30 years. His primary focus has always been database performance or – to put it more precisely – how proper application design can ensure the best overall system performance. For more than a decade, he has been a member of Oracle's Real World Performance team that often has been given the difficult task of finding the proper balance between complexity and reality. In this role, Mr. Engsig has been using many different methods and tools to measure and simulate database performance, and experience from years of performance engineering has been put into the development of the RWP*Load Simulator.