Real Application Testing - some notes

This post is a mixture of experiences from a recent project and an overview on how to use RAT in a project.

Real Application Testing (RAT) is a licensed feature for the Enterprise Edition (EE) of the Oracle database. It was released on 11g, but can be used to test migration of databases from 9i and 10g to 11g (assuming that the source databases have a required version/patch). The term Real is, I reckon, used to hint at a more authentic testing process. The tests are executed based on a capture from a real production system.

RAT can be used to:

• Evaluate new hardware, including migration from one platform to something completely different.
• Verify execution of SQL from today’s production on a newer version of the database
• Analyze the effect of changes in configuration
• Scaling up, see how current system works with higher load; this works best for queries / reporting and not so well for data manipulation (DML).

A project involving RAT can be divided into these activities:

1. Capture workload from a production system
2. Create a clone of production system with flashback database enabled and a restore point created.
3. Prepare for replay on test database and with agents on application servers or other client machines.
4. Execute replay
5. Generate reports (RAT and AWR)
6. Flashback database
7. If you haven’t reached a conclusion yet, repeat from 4.

In two projects I was asked to do step 2 - 6 and figured out that I should write down what I learned for posterity. (The fact that I have a friend in Belgium that reminded me about blogging more is a mere coincidence.) I will not put many details on step 1 and 2 here.

Some think it is a good idea with a long capture, but keep the replay part in mind and the time it takes to actually execute the replay. Though the flashback of the database is impressively fast, a longer replay (with much updates) will lead to more time spent on setting the database back with flashback. If you can limit the capture to say two hours that is a time saver later, but if you need to confirm say scalability for one night's ETL jobs, you may need to capture several hours. Before you start with capture, make sure you have all the backups you need to duplicate the database with point in time recovery as close to the starting point as possible. This should not be an issue since all productions system (IMHO) should run in archiving mode and being backed up. This is an opportunity to verify your backups.

For large databases it may take quite some time to transport the backup (and capture files) to new platform and have it restored to a clone. Flashback database is a wonderful feature since it takes litle time to flashback a database compared to a full restore for large databases. Flashback is turned on with command:

ALTER DATABASE FLASHBACK ON;

A restore point is created with:

CREATE RESTORE POINT B4_REPLAY GUARANTEE FLASHBACK DATABASE;

The name of the restore point is your decision (within limits). You will need some space for flashback logs, depending of the amount of changes to the database during replay.

In a bypass; yes, there are as usual two ways to do this. Enterprise Manager for the young and aspiring DBAs, or you can use the PL/SQL packages. When I have the routines ready for using the API I tend to favour them, so I did most of the work with the packages DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY together with the packages for creating AWR reports DBMS_WORKLOAD_REPOSITORY. Sure, I had some looks at nice graphs and reports in EM too.

Going into more details on step 3; the result of the capture is a collection of many files that needs to be transported to the new platform. On the test database create a database DIRECTORY object that points to the file directory where they are stored. First step is to process the capture with:

exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('REPLAY')

where REPLAY is the name for the directory. The result of this is a new directory with a name that starts with pp followed by a version number. Copy it all to the machine(s) that will host the replay agents (actually replay the workload and send it to database.)  If you are doing this on a RAC-database, copy the same stuff to other nodes with same path for the directory object.

In each installation of the Oracle database and client you have the workload replay client (wrc) included under $ORACLE_HOME/bin. It’s task is to send SQL from the captured workload to the database.  Before you start the actual replay a calibration should be run first. With ORACLE_HOME and PATH set, execute:

wrc mode=calibrate replaydir=/tmp/replay

This will report the recommended minimum number of clients. Too few means that the replay clients cannot replay the workload fast enough. The same will happen if the servers where the wrc binaries are overloaded, or the network between them and the database have any issues.

So if you have to execute 10 clients, put 10 copies of this in a script:

#!/bin/bash
wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay 
sleep 1
wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay
sleep 1

wrc system/welcome1@clonedb mode=replay replaydir=/tmp/replay 
sleep 1

Don’t run it yet.

Now we’re onto step 4. You repeat the following sequence if you decide to repeat a replay, but the preparation hitherto is only done once. Initialize the replay with the following command, using a distinct name for the replay each time:

exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name=>’run_1’, replay_dir=>’REPLAY’)

In the middle of this we have to do some remapping... The connections to the database used during production capture are not the same as used during replay. After the previous command has completed you can query the view DBA_WORKLOAD_CONNECTION_MAP and see what was used during capture. This view is empty before the initialize_replay procedure is executed, after it has values in the column CAPTURE_CONN, but REPLAY_CONN is empty. First time you have to look at the output and see if you have many different connections to the database and figure out how you will remap them. Often, only one connection string is used (connection string as the key in TNSNAMES.ORA) and the mapping is simply replacing it with the one you are now using to reach the new clone database. In that case you can use the following PL/SQL block to remap:

begin
  for i in (select conn_id, capture_conn 
    from dba_workload_connection_map m, dba_workload_replays r
    where replay_id = id
    and name = ‘run_1’)
  loop
    dbms_workload_replay.remap_connection(connection_id=>i.conn_id, replay_connection=>'cloneserver:1521/clonedb');
  end loop;
  commit;
end;

The name => ‘run_1’ refers to the name you gave to the replay. You can either use an entry from tnsnames.ora or connection string as shown above when setting the new connection. If you have some load balancing or application partitioning where you use different connections to the database they have to be remapped accordingly. Though you have to execute this for every replay, you only spend time on this first time and keep the PL/SQL ready for next time you do a replay.

Now after initialize, next step is prepare (!) In this step you set various parameters that how the replay is executed. In it simpliest way:

exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization => TRUE, think_time_scale=>100, scale_up_multiplier=>1)

synchronization set to ‘TRUE’ is equal to ‘SCN’, another more advanced (according to manual) is ‘OBJECT_ID’. This sometimes causes problems for the replay and testing several times with synchronization to ‘OFF’, ‘SCN’ or ‘OBJECT_ID’ may be necessary.

Later on when a normal replay has been executed it is always interesting to see what happens when you reduce think time or scale up the load. Note, that DML, when scaled up won’t actually update the same way, so this needs some testing from you. For reporting usage scaling up should work, however, note if you double the number of users, they are in realilty not likely to send the same SQL as today twice as many times, that is, users usually will run slightly different queries. But if this is a problem you have to find out by analysing the reports - if an a few statements are popular in an OLTP system you can assume they will remain so after you scale up.

Now you can add filter for what will be actually replayed, but I am going to leave it out. After you have exuted the prepare_replay procedure above, you can kick off the script on the clients, that is the script that includes the wrc commands. They will start and write out a message that they are waiting for replay to start.

You can start the replay with:

exec DBMS_WORKLOAD_REPLAY.START_REPLAY()

When this is executed the replay is running and the agents (wrc) writes out a message that replay has started.

EM has a nice page for monitoring a replay. A few times we have seen that the replay lags behind compared to capture, but catches up later on, or sometimes takes longer time than the capture. Which sometimes means an error with RAT itself and not necessarily a problem with the new platform.

In step 5, after replay is finished, you generate a RAT report and also AWR reports. In DBA_WORKLOAD_REPLAYS you’ll find the replay_id of the replay and the RAT report can be generated with:

select dbms_workload_replay.report(42,’HTML’) 
from dual;

Generate the AWR reports as usual. If you want to compare AWR report from capture with replay, you will have to import the AWR data (AWR data is automatically exported after the capture and included in the same directory). First verify that the capture is visible in DBA_WORKLOAD_CAPTURE. If not you can import all the data about captures found in the directory with:

exec dbms_workload_capture.get_info(‘REPLAY’)

where ‘REPLAY’ is the name of the DIRECTORY-object where capture is stored. The capture(s) should now be visible in DBA_WORKLOAD_CAPTURES. The AWR-data belonging to it can be imported with

select dbms_workload_capture.import_awr(42,’LUSER’) 
from dual;

Take a note of the number returned. This is a generated DBID that you will use later to refer to the database of the capture (this means that this is a made up DBID that is different than the DBID of the database where you executed the capture). 42 above is the capture_id from DBA_WORKLOAD_CAPTURES and LUSER is the name of a schema used for staging. Just create on temporary if you don’t have one ready.

The import can take some time. When it is finished you can see in DBA_HIST_SNAPSHOT what snapshots are available, check for the DBID that was returned in last command. You can now create comparsion reports with:

select * 
from table(dbms_workoad_repository.awr_diff_report_html(123456789,1,120,130,734501234,1,10,20));

The first three parameters are DBID, start snap id, end snap id; all from the first database (source/capture) and the last three the same respectively for the second database where replay took place. Creating these reports work pretty well in SQL Developer, use Ctrl-A and Ctrl-C to copy the html code and paste it into an editor and save it. You can actually create more reports than I have shown here, see the documentation for the mentioned packages to get an idea. AWR data is automatically exported to the same directory after a replay. This means you can later be import them into another incarnation of the database to compare different replays.

Step 6, flashback database is pretty easy. Shutdown the database, start it in mount, execute:

flashback database to restore point b4_replay;

Then open it with:

alter database open resetlogs;

If you are on RAC you have to shutdown the other nodes and start them after the previous command is completed.

There used to be some bugs that caused the database to crash spectacularly with an ORA-600 during flashback leaving the database useless and forcing you to do a new restore/duplicate from backup. Since I’m stressed during a project like this, and therefore I don’t have time to waste, I tend to avoid structural changes (like moving tablespaces around after creation of the restore point) that can challenge the flashback. I am not sure if the bugs are still there, though.

Step 7, are you done yet? I usually have to run the replay over and over several times trying out with different parameters for procedure dbms_workload_replay.prepare_replay(...). This takes a lot of time, don’t underestimate the project. Also remember that people will question your results.

In conclusion, try to figure out before you start what you want to prove. Are you comparing two systems, you need to agree on a common set of parameters; or are you trying to prove that a system is good enough (without comparing it to the old?) Try to lower you ambitions or scope, the time it takes with restore, replay, generating reports, looking over them, and possibly analyze further with extended trace, starting over,... it may feel like a walk in the desert sometimes.

Some thoughts from last PoC

For the second time I have been involved with a proof of concept (PoC) where testing of an alternative hardware platform was the goal. Common for both PoCs were:

1. The other option was Exadata
2. Real Application Testing (RAT) was used to replay production recordings on the new hardware.

And for the second time I have concluded that sales people take this too easy. It is not easy and assuming you will outperform previous platform just because you have a bunch of less expensive flash disks, cache cards, and more is arrogant at best.

A decent PoC requires lots of preparation and you need to have done some planning for capacity and load. If you have no idea of throughput, expected load, the nature of the applications and more, you are likely to fail.

Exadata is not the optimal solution for everybody, that is not why Oracle charges lots of money for it; it is not like they are thinking “Everybody will want this, so we can double the costs”. Not that I know from the inside, but some in Oracle probably thought they needed to create a solution for processing large amount of data fast. In order to do that a congestion of the CPU has to be avoided by filtering out data that is not needed before it reaches the database. As an added benefit of not shipping useless data the system will perform less I/O. If you don’t need that, Exadata may not be for you. On the other hand, if a customer needs this for a BI solution and you are selling what you like to call “standard commodity solution”, be prepared to have something that scales and can replace the Exadata features (if Exadata is what you are competing against.)

If you sell a solution to the 10% of the costs, it will not succeed simply because your bidding price is low; without performance it will fail. Without careful planning and analysis you will fail. Cheap food is nice, but if it makes people sick, they are not happy to spend the money on hospital bills. And they will go somewhere else.

There is no good substitute for proper understanding. DBAs have heard for many years that we will become redundant because modern systems are self-managing or something like that. Well, I gladly take up brewing if that becomes a reality, but what has changed is the amount of decibels from the CIO screaming when new and more expensive hardware does not perform after all.

The fun with Exadata does not all come from the fact that it is expensive and state of the art. Rather we have had these experiences where you combine your understanding of the platform with old wisdom like “filter early” and end up with incredible performance improvements. Like one day I asked a domain expert about the data we were querying, and after adding one redundant predicate to the SQL statement, the response time went from 90 seconds to less than 1 second. The whole improvement was due to smart scan taking place.

Back to the PoC; we spent weeks waiting for proper integration between servers (running Linux) and the storage. Wrong interface was used for the interconnect (this was RAC), RAID 5 was used because after all, they hadn’t provided enough disks. The devices that would provide the caching features was not used, but the low-level devices was discovered by ASM instead due to wrong configuration (parameter ASM_DISKSTRING was unchanged leading to the sd* devices being discovered). After a long time the RAT replay showed that for a small number of users the performance was acceptable, but as load increased the throughput stalled and we concluded that the new solution was not dimensioned properly. The vendor suggested a new and even better storage solution with even more flash…

Probably without seeing it themselves, the vendors in both cases demonstrated the benefit with an engineered solution. When the customer sees that you need weeks to have the storage play with your server, they start to think that maybe Oracle has a point with their preconfigured and tested solution, ready to plug in. (Yes, we know that reality is not that simple, search Twitter for #patchmadness for an illustration.)

There are a few takeaways from the RAT testing itself, which is material for the next post. What I can say now is that restore of database, performing replays and flashback and start over again takes a long time. Keep it simple and try to keep the capture as short as possible without losing value for what you are testing.

The goal with a PoC is to qualify or disqualify a new solution. If a new suggested solution is disqualified, the PoC should be deemed a success; this was actually stated by the project manager and not by me. PoCs should not be taken too easy. Think about all the problems you can avoid by stopping the wrong solution to enter your data center.

Oracle load testing - part 3 Hammerora results

I learned something very important when doing testing with Hammerora. The documentation is quite good and has a simple, but important point, the importance of planning and preparation. To quote the documentation for Oracle OLTP testing:

Planning and Preparation is one of the most crucial stages of successful testing but is often overlooked. Firstly you should fully document the configuration of your entire load testing environment including details such as hardware, operating system versions and settings and Oracle version and parameters. Once you have fully documented your configuration you should ensure that the configuration is not changed for an entire series of measured tests. This takes discipline but is an essential component of conducting accurate and measured tests.

From this I conclude that many tests I've seen and done myself have not been accurate. As stated earlier the goal for this testing was just to compare performance before and after migration from EVA to 3PAR.

Since this customer did not have the required license to run AWR I did the following change in the driver scipt to create a Statspack snapshot in place of a AWR snapshot. Search for string containing dbms_workload, replace

set sql1 "BEGIN dbms_workload_repository.create_snapshot(); END;"

with

set sql1 "BEGIN perfstat.statspack.snap; END;"

When testing with Hammerora I decided to run each test three times to see if the numbers where consistent. I recorded the numbers of each run in an spread sheet as shown in the following table for the tests on EVA:

Vusers	Run	Report	tpm	nopm	Avg_tpm
1	1	1_2	7158	2369	7634
1	2	11_12	7874	2645
1	3	21_22	7868	2804
3	1	31_32	16478	5765	17317
3	2	41_42	17678	6256
3	3	43_44	17794	6130
10	1	45_46	27847	9959	33225
10	2	51_61	32581	11600
10	3	71_81	39248	13701
20	1	91_101	47489	17441	47075
20	2	111_121	63062	22658
20	3	131_141	30674	11116
30	1	151_161	54349	19756	44186
30	2	171_181	45628	17331
30	3	191_201	32581	12733

Vusers is the number of virtual users in Hammerora, Run is 1 - 3 for each new setting of Vusers. Report  refers to the Statspack report created on snapshots before and after. Tpm and nopm as reported from Hammerora and finally Avg_tpm is the average in each group. Compare this to the numbers for the 3PAR:

Vusers	Run	Report	tpm	nopm	Avg_tpm
1	1	9_10	8246	2815	8262
1	2	11_12	7983	2717
1	3	13_14	8556	2956
3	1	15_16	22652	7854	22881
3	2	17_18	22652	7831
3	3	19_20	23339	7994
10	1	21_22	33539	11767	33191
10	2	25_26	39054	13729
10	3	27_28	26981	9428
20	1	29_30	47134	16462	47356
20	2	31_32	46436	16330
20	3	33_34	48497	17023
30	1	35_36	53197	18902	50788
30	2	37_38	44980	15994
30	3	39_40	54187	19033

The repeated tests for the same number of virtual users do not vary as much on the 3PAR compared to the EVA. Also the numbers for the EVA seemed to improve for each run, maybe due to some caching taking place.

The 3PAR seemed to be more reliable for the same number of virtual users as can be seen in these screen captures, the first for 20 virtual users on EVA:

The peformance on the 3PAR does not change much during the test (20 virtual users):

You'll see that in one instant the EVA seems to perform better, but I rather have stable and less erratic performance with the 3PAR than a system with occasional good performance.

All in all it was very easy to play around with Hammerora, it is very easy to set up so you can spend time on planning and executing the tests. Also I like how you can observe change of performance over time. Clearly Hammerora is a tool I will use more later.

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.