Evaluating Performance for Cloud Data Warehousing, Part 2 - Execution

As I mentioned in my previous blog, many decision makers continue to be faced with the question, is now the time to move to the cloud? In this blog entry, my second in a five part series, I’ll explore what it takes to execute performance tests during any proper cloud data warehouse evaluation.

Part 2 (of 5): How to execute a performance test

The first thing you need to do is make sure you have a good client tool, one that is able to run scripts, capture results, all under concurrency. We’ve seen SQL Workbench/J used very effectively for this testing; its a JDBC-based query tool that is easy to call from shell scripts headlessly, it can track end-to-end execution times, and it can handle multiple concurrent sessions.

The client tool you choose to use must be run on a machine with sufficient CPU and memory to handle a high level of concurrency. TG recommends that you have 16GB of memory, a minimum of 4 cores and that the tool is not run from your primary workstation/laptop. Consider using a highly reliable VM in the cloud as the machine for your client benchmarking tool.

When it comes to test execution best practices, there are few things to consider:

  1. Do not rely on the cloud provider’s service history data to measure your end-to-end query timings. Some services exclude things from their service history like the time it takes to connect the client driver, gather statistics, or the time it takes to transfer results. These can significantly contribute to the overall query timing so rely on the results of your performance test, not the provider’s service history.

  2. Be sure to run your tests multiple times. A good rule of thumb is to run your test four times, discarding the worst result, and averaging the remaining 3 timings to determine a reliable result.

  3. Some platforms offer a results caching feature; disable it, or measure results with and without it enabled then make comparisons. This will prevent queries that are re-run from finishing instantaneously, skewing your overall results.

  4. You’re going to focus a lot of time on concurrency testing, so it’s worth pointing out a few special considerations here:

    • For example, let’s say you’re asked to test 10 concurrent query streams. What I suggest is launch 9 concurrent streams that loop forever, each one using a different query order and predicate. This provides a nice, mixed workload of concurrent sessions.

    • Now, launch a single foreground query stream and measure the performance of that one stream only, and repeat this process four times using different foreground query streams each time.

    • Test concurrency 1 (single thread) thru 20 (or more), if possible. This will give you a nice set of results that show how your queries perform on an idle system, under moderate load, as well as stability under a heavy load.

All of this testing will create a broad set of results. You will be asked to compile and present these results to your decision-makers in a way that makes sense and targets the value propositions of the platform providers. In my third blog entry, I’ll share some examples of how to compile and present your results to delight your decision makers.

In case you missed it, check out Part 1 - Design.