Tuning the QuerySurge Agent

Versions: 8.0+

QuerySurge Technical Whitepaper No. 1

Introduction
Your QuerySurge Agent installs with default settings that should give you reasonable throughput and performance under most conditions. As you gain experience with QuerySurge, or if you need to deploy Agents to a variety of platforms with differing hardware, you may want to optimize Agent behavior.

In general, the major consideration when it comes to Agent performance is the amount of heap (memory) available to the Agent. More available heap means more flexibility in handling larger Source or Target query ResultSets. However, in cases where heap is limited relative to desired ResultSet size, tuning the Agent can provide more data throughput than you might otherwise be able to obtain.

The QuerySurge Agent and Your JDBC Driver
When we consider how to tune a QuerySurge Agent, we are really considering how to tune two pieces of software: the Agent and the JDBC driver(s) that the Agent calls. The JDBC driver has the responsibility to communicate with your Source or Target data source, and the QuerySurge Agent handles the conversation between the driver and the QuerySurge app server. The major tasks handled by the QuerySurge Agent include: fielding assigned queries, loading the proper driver for each query, receiving each ResultSet, packaging each ResultSet for delivery to QuerySurge, and sending each ResultSet to QuerySurge. All tuning tasks therefore reflect the interplay between Agent and driver. Whenever we speak about how the Agent uses resources, we really mean how the Agent uses memory in conjunction with a JDBC driver.

Tuning Tradeoffs
The major tuning tradeoff is between the heap available to the Agent and driver and the message volume desired. Drivers are often optimized to utilize heap in order to buy better performance. If you have lots of RAM available, increasing the heap size available to the Agent provides an easy route to handling larger query ResultSets. However, in many cases, Agents are deployed to platforms that have heap limitations relative to ResultSet size. By tuning the Agent, you can in effect trade increased message volume for lower heap sizes. The basic idea is that if you are heap-limited, you can try to trade a larger volume of smaller messages for a smaller memory footprint, or if you are heap-rich and can handle larger query ResultSets, you can have the Agent process your data with a smaller number of larger messages.

Memory and 32-bit vs. 64-bit Operating Systems
A quick note about operating systems and Java – since the QuerySurge Agent is a Java application. An important limitation concerns the maximum theoretical amount of memory that Java can request from the host Operating System. On 32-bit Operating Systems, Java can request a theoretical maximum of 4 GB of heap space; however, this number is strongly dependent on JVM and Operating System. For non-server Windows OS’s, this value is < 2 GB. For some Windows servers and many Linux flavors, the maximum value is around 3 GB. On 64-bit operating systems, this limitation is relaxed considerably, since the theoretical maximum is quite large by today’s standards (16 exabytes), and well beyond the physical memory that any box is likely to have. Of course, as with 32-bit Operating Systems, not all of this memory would be available to Java – but it is likely that a great deal of the available memory on a 64-bit system would be available for Java.

Another significant issue is related to Java’s requirement for contiguous memory. The Java heap consumes memory, but in addition, the memory it consumes must be contiguous memory. This means that if you deploy an Agent to a box with 2 GB of available RAM, but an unrelated piece of software loads into an address in the middle of that available memory (on Windows, a Windows dll, for example), Java will only have approximately 1 GB of contiguous memory to load into in this case. This is a significant limitation on the memory available to Java and therefore to your QuerySurge Agent. It means not only that the actual maximum amount of memory that Java can request is dependent on the other software installed on the Agent box; it also means that the maximum amount of memory may vary from machine to machine.

Note: Some JVMs do support non-contiguous heap.

The Major Tuning Parameters
The QuerySurge Agent has three major tuning parameters:

• Agent Heap Size
• Fetch Size
• Agent Message Size

Note: The standard installation for the Agent is to install as a service in headless mode. Tuning is therefore done by editing the Agent configuration file (//../<Agent install directory>/config/agentconfig.xml). Edit this file carefully, as any mistake may result in Agent failure.  These parameters correspond to the following tags in the Agent configuration file: Agent Heap Size, <heap>; Fetch Size, <fetchHint>; Agent Message Size, <agentMemoryMode>. However, if you are running the Agent as a service, the <heap> tag is inactive; change the heap by launching with administrative rights (in the Agent install directory) QuerySurgeAgentw.exe. Click on the Java tab, and set the heap by changing the Maximum memory pool field.

The Agent Heap Size, as noted above, is subject to limitations imposed by the Operating System, the JVM, and memory locations claimed by software unrelated to the QuerySurge Agent (other than by being installed on the same box). In general, it is good to know the maximum heap request that your Agent platforms can support – which you can find out by trial-and-error.

Note: Note that once you exceed the heap request that your Agent box can support, this value will have been written into the Agent configuration file. Your Agent will be unable to launch until you lower this value by carefully editing the agentconfig.xml. Edit the <heap> tag to contain the highest value that the Agent successfully launched with.

The second major parameter is the Fetch Size. This is actually a JDBC driver parameter – it refers to the size of the chunk of ResultSet data that the driver should fetch when more rows are needed, in units of rows of data. Note that the Fetch Size, according to the JDBC documentation, is only a hint to the database – database vendors are free to decide how to implement this, and may even decide not to implement it at all. (Consult your driver documentation for information on how the Fetch Size works.)

The Agent Message Size is an Agent parameter that controls the maximum size of the messages that the Agent can send back to QuerySurge (in MB). When the Agent processes a ResultSet for sending, it will chunk the data with the maximum default chunk configured to match the maximum message size that the QuerySurge database can accept (< 50 MB).

The Tuning Model
The basic goal of the tuning exercise is to adjust the Fetch Size and the Message Size to be in rough parity, and optimized for the heap space that is available to the Agent. Note that this is more of an art than a science, since there are critical unknowns in the tuning process:

• The Fetch Size is in rows, which are not easily convertible to MB, the units of Message Size.
• Tuning must be performed to optimize a broad range of queries with different row data types, not a single query.
• Multiple Agents deployed on different boxes may have different maximum heap sizes.
• The Message Size reflects the HTTP/SOAP communication between the Agent and QuerySurge – which contains more bytes than the data alone due to the SOAP encapsulation.

A Tuning Example
The following examples employ a QueryPair returning a ResultSet of ~850K rows and 10 columns each from Source and Target. Each of these ResultSets contains about 110 MB of data with 135 bytes/row. Trials were monitored using VisualVM (Version 1.3.3) and were executed a 64-bit Windows 7 platform with 8 GB RAM.

For our initial tuning trial, we’ll run the QueryPair “unrestricted” – using a heap well in excess of the memory needed, with the defaults for Fetch and Message sizes. We do this by setting the Agent heap to 2 GB, and by setting the Fetch Size and Agent Message Size to “Nolim” (which means “no externally imposed limitation”; the Agent will use its defaults). Figure 1 shows the setup:

Figure 1

Note: Both Source and Target databases for the trials shown here are Oracle 10g databases

Preliminary Execution with the Defaults
If we monitor an execution using these parameters, we get the following (Figure 2). Java requests heap space from the Operating System (orange area) up to the maximum heap of 2000 MB (actually 2095 MB). Java uses on average 938 MB during the run (blue area), with points of peak usage at about 1500 MB. This is fine if we have spare memory to dedicate to the heap, but in many situations, we are heap-limited, so our tuning needs to focus on how to optimize the Agent when large amounts of free RAM are not available.

Figure 2

Tuning the Heap Size
The place to start our tuning is simply to limit the heap space available to the Agent. Let’s try our run with a heap of 1100 MB, a size that might typically be available on a box equipped with 2 GB RAM (Figure 3). Java behaves as before, requesting the maximum allowed heap, but manages to limit the peak usage to ~1000 MB, and keeps the average Used heap at 513 MB. So, simply limiting the Agent Heap Size can cause Java to be more efficient – although this tactic has its limitations, because a large QueryPair may have exhaust the available memory resources and terminate in an OutOfMemoryError.

Figure 3

Tuning with the Fetch Size
For our next trial, let’s try tuning the Fetch Size from the default (a value calculated by the driver) to 10K rows, and we’ll leave the Message Size at its default value (“Nolim”). The Agent Heap Size is maintained at 1100 MB. As can be seen in Figure 4, Java is still requesting the maximum heap permitted, and the peak Usage is about the same at 1000 MB, with the average Used heap at 522 MB. In fact, it doesn’t look like we’ve made much improvement over our previous trial, which could mean that memory consumption is relatively insensitive to Fetch Size, or it could simply mean that we haven’t chosen a good Fetch Size.

Figure 4

Let’s try a 1K Fetch Size to see if this gives us the control we’re looking for. Again (Figure 5), the answer seems to be ‘no’ – if anything a smaller Fetch Size alone appears to result in the same overall Heap request but with a larger average Used heap (674 MB) and a roughly equal peak usage (1000 MB). So it looks like we are not going to be able to reach our goal of more efficient memory usage by the Fetch Size. Let’s consider the Message Size.

Figure 5

Tuning with the Agent Message Size
We’ll start out again, this time by keeping the Fetch Size at its default value (the “Nolim” setting), which, again, is a value calculated by the JDBC driver. We’ll set the Agent Message Size to 10 MB for a first trial with the Message Size. The first thing we notice when we look at Figure 6 is that Java is no longer requesting the maximum heap available – only a maximum of 836 MB has been requested out of the maximum 1100 MB. This means that we’ve been able to trade off a larger volume of messaging using smaller messages for requested heap space during the run. This is exactly the direction that we want to move in. In addition, the Used heap averages 351 MB, a nice improvement over our original trial (Figure 3), as is the peak usage at 623 MB. There is one important point that needs to be factored in when tuning the Message Size, which has to do with the Agent chunking routine. The Agent will chunk results into messages in chunks of whole ResultSet rows – so you cannot use a Message Size that is less than the storage required for a single row. This is especially important to consider if your queries involve LOB types.

Figure 6

For our next trial, since we seemed to make good progress by limiting the Agent Message Size, let’s cut it down further – we’ll use a 2 MB size. Again, we leave the Fetch Size at “Nolim”. The results of this setting (Figure 7) show continued improvement. The heap requested by Java during the run is only about 541 MB – only about half of the maximum 1100 MB. The peak Used heap is about 410 MB (also an improvement), and the average Used heap is better as well, at 289 MB.

Figure 7

Tuning with the Agent Message Size and the Fetch Size
Now, let’s see if we can determine whether we can use the Fetch Size in conjunction with the Agent Message size to wring an even more memory-efficient run out of the Agent. For this next run, we’ll hold the Agent Message Size at 2 MB, but in addition, we’ll set the Fetch Size at 1K rows. The results are shown in Figure 8 – we have indeed made some gains. The Agent requests a bit more heap from the Operating System (560 MB), but our Used heap averages 260 MB, a modest improvement over the previous run. The peak is unchanged at from the previous result
(~ 410 MB).

Figure 8

Let’s try another cycle – if we hold our Fetch Size constant at 1K rows and try a lower Message Size, can we get an even better outcome? If we lower the Message Size to 0.5 MB, Figure 9 results. Our maximum heap request has now fallen to 505 MB. The average Used heap is 265 MB, slightly higher than in our previous trial (Figure 8). So, even though we are requesting less total heap from the Operating System, we appear to be using somewhat more heap to complete the run – a potential negative since it is when Used heap approaches the Heap size that an OutOfMemoryError can occur.

Figure 9

Finally, it is worthwhile to point out that not all combinations of Agent Message Size and Fetch Size will result in good optimizations – for example, if we keep the Agent Message Size at 0.5 MB but increase the Fetch Size to 100K rows, Figure 10 is the result – not dramatically worse than our best tuning, but with the heap request rising to 672 MB and peak usage up to 423 MB and the average Used heap at 381 MB, we have clearly moved in the wrong direction.

Figure 10

Custom Tuning Parameters
In addition to the tuning parameters we have discussed here, many JDBC drivers have their own custom memory-related properties. The QuerySurge Agent can use these properties (in fact, it can use any driver properties, whether memory-related or not) in a tuning exercise in addition to the parameters discussed here. In cases where specific memory targets (or other targets such as performance targets) are a high priority, driver properties may provide the added control.

Note: Custom parameters can be set via the appropriate tags in agentconfig.xml.

Tuning for One QueryPair vs. Tuning for a Suite or Scenario
In the exercise described here, we have focused the tuning on a single QueryPair. Obviously, this is not a realistic situation for most QuerySurge use cases – in all probability, you’ll be tuning for hundreds or thousands of QueryPairs. In view of this, the best way forward is to execute your QueryPairs using the default settings in order to see where your Agents have memory-related issues. Tuning exercises can then be focused on the problem areas; it is likely that once the problem areas are resolved, full Scenario execution should proceed smoothly. The goal is to try to find an “average set” of tuning parameters that give the best throughput with the amount of Agent memory available for the queries running on the Agent. This may be different for different Agents if they are deployed on boxes with different resource levels, or if specific sets of QueryPairs are directed to specific Agents.

As a further caveat, it is worthwhile to remember that the precise mixture of queries executing simultaneously likely won’t be fully reproducible from one run to the next, because the Source and Target databases will respond differently depending of the full mix of requests that they are processing to at runtime. So, peak Agent resource needs may change from one run to the next, and it is best to err on the side of safety when you tune – so that there are enough resources for the peak consumption, even if it only occurs once every several runs.

A Note about Agent Threads
Another option that we do not explore here is the use of Agent threads. The QuerySurge Agent can execute multiple queries simultaneously using multiple threads. In the trials described in this paper, the Agent was run with 5 query threads – enough so that each query had a thread to itself. Changing the thread count will change the mix of queries that are executing simultaneously, and will likely result in changes in the heap size request to the Operating System and in the Used heap. We will discuss how the number of query threads available affects tuning in a subsequent technical whitepaper.

Summary
Table 1 below shows the main results of the tuning trials shown above. In reviewing this data, it is evident that the best tuning combinations are trials 6, 7 and 8. Comparing the initial “default” run to our fully tuned cases, we have been able to reduce the Heap Maximum to about 26% of the original (Trial 6), and the Average Used heap to about 28% of the original (Trial 7).

Table 1. Summary of QuerySurge Agent Tuning Trials

Trail	Figure	Message Size	Fetch Size	Heap Size (MB)	Peak Heap (MB)	Peak Used (MB)	Average Used (MB)
1	2	Nolim	Nolim	2000	2095	1494	938
2	3	Nolim	Nolim	1100	1126	1036	513
3	4	Nolim	10K	1100	1126	1051	522
4	5	Nolim	1K	1100	1126	1023	674
5	6	10 MB	Nolim	1100	836	623	351
6	7	2 MB	Nolim	1100	541	418	289
7	8	2 MB	1K	1100	560	463	260
8	9	0.5 MB	1K	1100	505	423	265
9	10	0.5 MB	100K	1100	672	506	381