Oracle load testing - part 2 ORION results

Last week I was running load tests in between other tasks on my agenda. As stated in previous post I wanted to compare performance, as measured by load testing tools, on an old SAN and a new one, EVA and 3PAR respectively. I ran two types of tests, the OLTP test and the SIMPLE test:

orion -run oltp -num_disks 50
orion -run simple -num_disks 50

Following are charts from the ORION OLTP test with "num_disks" equal to 50.

Test was running against one disk (LUN) pr execution. Only test-server has been migrated to 3PAR and ORION were run before and after. Not surprisingly the 3PAR can achieve higher number of I/Os per second as the load increases. The EVA seems to behave erratic or breaking down after some point. This is confirmed in the following chart for latency:

The wild values from one of the EVA-tests throws the chart out of proportions. But it is still possible to see that the latency is within reasonable limits. By limiting the values from 0 to 50,000 microseconds the chart looks like:

Looks like I could have expanded the range when testing the 3PAR. Latency is within 10-32 ms the whole range. Latency is the most important measure for databases IMHO, and after testing this repeatedly on 3PAR I expect to have more stable performance after migration.

Next chart is a histogram for the latency values:

The main difference between them is the outliers on the right side for the old EVA.

The following charts are from the SIMPLE test. In this test the pattern is less erratic for EVA, but still the 3PAR performs much better and seems to be very happy as the load increases.

The SIMPLE test measures throughput with MBPS. Here large I/Os are used to simulate a situation where throughput is more of a concern. The pattern resembles that of the previous picture, and the throughput of the 3PAR is amazingly stable. I'm wondering if it will be just as stable when we move production next week.

The last chart shows the latency of the SIMPLE test, small I/Os where used:

In summary: The ORION tests indicates that the 3PAR is much more stable, and by looking at the results for latency more fit for serving a database server than the EVA. The pattern on the EVA was similar on both production and testing servers, which means that the extra load from other users in production did not explain the oscillations.

Next post on Hammerora will show a similar pattern. I learned a lot by reading the documentation of Hammerora. It is very educational by giving good advices on how to plan and run tests. I felt my ORION testing was a bit inferior compared to the rounds with Hammerora after I finished them, but I still think the results from ORION are interesting. More on that in a few days.

Oracle load testing - part 1

The current customer is migrating from EVA storage to 3PAR, and inspired by some discussions on Twitter between @yvelikanov, @martinberx, @kevinclosson, @martinDBA, and others I decided to try out some free tools for load testing of Oracle servers. I can hardly tune a SQL statement without getting philosophical about it, so especially this first part is a bunch of thoughts I had when starting out.

There are a lot of discussions on the usability of such tools; are the measured performance any close to the reality? The load these tools generate is more or less synthetic. One tool that stands out from the crowd is the Real Application Tool (RAT), but RAT requires an extra license from Oracle. With RAT you can capture real load on a production system and replay it on a test system with new or upgraded hardware or configuration. The reports generated in Enterprise Manager lets you compare the performance of old and new system so clearly that even your CEO can understand it. You cannot get much closer to reality than with RAT, but as said, it costs money and time to set it up. [And for a change I will not whine much more on the fact that SMBs around here do not invest in these licensed packages that makes the life of the DBA easier.]

Back to the question on reliability of these tools; the fact that the load is often quite artificial and likely to be very homogenous (or randomized without the skew you have in your real world) means that they give an upper limit you are not likely to reach in normal production. That was one of the reasons why Kevin Closson wrote the Silly Little Oracle Benchmark (SLOB). After having played with this I have reached the preliminary conclusion that these tools are useful for comparing configurations and behavior more than ranking your system.

The last days my mind has started to compare this with the infamous buffer cache hit ratio (CBHR) - see this post over the subject on Jonathan Lewis' Blog. Though the BCHR is quite a meaningless measurement you still may know what it use to be for your most important databases since there are several monitoring systems out there that still come with an Oracle module that measure it and sends you an alert if it is too low (too low according to the assumptions Lewis and others have attacked long time ago). But what if your BCHR suddenly changes, what does that mean? It means just that: something changed. And that may be worthwhile investigating. Think of your kid that always make some noise, though silence is welcome, if he suddenly stops his rattling, you want to know why, silence is not always a good sign. Translated back to the BCHR, an increased ratio could be caused by some sessions reading the indexes en masse when a FTS would have performed better.

And what is the connection to load testing? By repeating the tests several times and seeing the same pattern again and again the tool confirmed itself and when running it against another system this pattern changed, but remained consistent on the new system. The measured limit itself is probably off the reality, but I think it is reasonable to assume that when the performance becomes erratic and unstable (that means widely varying results that are not linear or exponential linked to the increasing load, or less fancy, the graph you are looking at goes up and down on the right side) you have found some upper limit of the load the SAN can take. If the performance on the new SAN remains stable for more load compared to the old one, it will probably perform better in real world production also, just don't believe you've found the correct upper limit for your application. That was pretty much our ambitions, compare old and new SAN, and possibly discover any obvious configuration errors.

In my case I wanted to do some measurements of I/O performance so I could compare the old EVA with the new 3PAR SAN. First out was ORION (Oracle IO Numbers). ORION is included with the database software, you'll find it under $ORACLE_HOME/bin. Or you can download it from page I just linked to. The general idea with ORION is that it executes more or less the same code path as the Oracle database does when doing I/O. There is no application testing involved, just a bunch of I/O operations thrown against your storage. You can test small and large I/O to simulate the load from OLTP, DSS, or mixed environments. ORION is supported on several platforms including Windows. ORION reports latency (in microseconds), throughput (megabytes pr second), and I/O operations per second (IOPS). The results are written to text files in CSV format that can easily be imported to Excel for easy visualizations (or much better, imported to your precious database and devoured with Apex).

I tried to have SLOB work from Windows, but couldn't find an easy way to replace the semaphore mechanism Closson used on Unix to start all sessions at once. I could have written some in Java or other, but I really didn't have the time and it seemed to be an over-kill.

Next out was Hammerora. It uses Tcl/Tk to run and is also supported on various platforms, but it was very unstable on the Windows 7 32-bit I was using. It would run for a while and suddenly at a random point (sometimes after many minutes of testing) encounter an error with Tcl/Tk needing a restart. Think this was caused by the combination of the OS and Tcl/Tk.

Really wanting to test both SLOB and Hammerora I installed VirtualBox on the PC and started with Hammerora there. Graphs and numbers coming this week, but in general the results from last week was that ORION indicated stable and consistent better performance on the 3PAR with less erratic pattern. Hammerora actually reported better results from VBox than when running directly from host OS (Windows 7). Depending on how much idle time I'll have the coming weeks I'll try out SLOB, Swingbench and more trying to compare the two SANs.