Testing N1QL Workloads with Apache JMeter

One of the key features that Couchbase provides for querying JSON data is a N1QL language. N1QL allows users to access and modify the contents of their JSON documents with familiar SQL statements. Quite often, during development phase of a new application there is a need to simulate certain production workloads in order to determine if the new application would meet required SLAs in terms response time and concurrency in a stress-test scenario. Typically, the structure of JSON documents in the bucket is already defined and existing production N1QL queries are known up-front. As part of benchmarking a new application, we have to define both existing and newly added N1QL workloads and optionally parametrize those, then run those workloads with different levels of concurrency and measure query performance.

In this blog article I’d like to discuss Apache JMeter tool that could help in such endeavor. You can also watch the video of this demo here.

Apache JMeter

Apache JMeter is a free open-source java-based application which is used for designing and running comprehensive performance tests of web applications, databases, SOAP and REST Webservices, FTP, LDAP and mail servers, TCP and SMTP protocols. JMeter can emulate a heavy workload on the server and help analyzing performance under different test scenarios. JMeter comes with a GUI which is used for designing and running tests while measuring a variety of performance metrics such as average, min and max response time, standard deviation, throughput, and etc. Adding test plan elements (thread groups, listeners, samplers and etc.) to the test plan can be easily done by right-clicking on an element in the tree and choosing a new element from the “add” list. One of my favorite features provided by JMeter is the ability to easily change the number of concurrent threads running a particular workload. Once a test scenario has been designed in JMeter, it can be saved into the file with the .jmx extension. For load testing and getting optimal results, it is recommended to run an already created test scenario in a Non-GUI CLI mode by supplying .jmx file to the JMeter executable.

You can learn a lot more about JMeter capabilities by downloading it from https://jmeter.apache.org/ and reading tutorials/documentation.

Setting up a test scenario 

For my demo I have both JMeter and Couchbase server running on my Mac. I defined two simple workloads with N1QL statements for the “travel-sample” bucket. The first workload titled “Workload 1 – Read” runs 5 concurrent threads where each thread randomly picks up N1QL queries from the pull of 10 queries, as shown down below. For my test scenario I plan on holding the target rate of “Workload 1 – Read” for the duration of 120 secs.

Prior to running the test, a connection to Couchbase server should be authenticated via “HTTP Authorization Manager”.

To illustrate applying parameters to the WHERE clause, I parameterized “Select 3” query with Country, low and high ID ranges. Those parameters would be taken from the countries.dat file. You can parametrize N1QL queries via “CSV Data Set Config” test element, as shown below.

Each N1QL statement is submitted to Couchbase from the “HTTP Request” sampler via a POST method.

The second workload “Workload 2 – CRUD” has a simple insert, 3 selects, update and delete operations. The goal of the test is to measure performance characteristics of the second workload “Workload 2 – CRUD” when running it on behalf of 1 thread doing 10 iterations standalone (aka alone in the universe) and then with “Workload 1 – Read” running in parallel. Queries defined for each workload have different complexity and performance profiles with response time ranging from sub-second to several seconds, when ran individually.

Running tests and measuring performance 

Once the test scenario was defined, all I had to do is run a JMeter test either by pressing a green Start button at the top menu or supplying .jmx file to the jmeter executable script. To measure and visualize performance results, I added several listeners under “Workload 2 – CRUD”. The “Summary Report” listener provides summary statistics of the test and each individual N1QL statement. “View Results Tree” can be used for debugging purposes to check the status, request and response data for each sample. There are a lot of other listeners available in JMeter. “Workload 2 – CRUD” performance results when ran standalone are presented below with response times measured in milliseconds. Notice that the first workload “Workload 1 -Read” was disabled during this run. You can easily disable certain parts of your test plan by right-clicking on the test element and choosing “Disable” option.

During the next run both workloads were active and I immediately noticed the difference in response time, especially for a more complex “Select with Join” query with an average elapsed time jumping from 600ms to almost 2.6 sec.  Obviously, CPU was maxed out when multiple threads were running on my Mac, so these numbers are provided for illustration purposes only.

Test results can also be visualized with “Response Time Graph” listener, as shown below. Elapsed time of “Select with Join” query depicted by the yellow line dropped down once the first workload completed.

In Conclusion

JMeter is not the only tool available for running stress tests and benchmarking performance. Couchbase provides a couple of other tools cbc-pillowfight and cbc-n1qlback for testing performance of key/value operations and N1QL statements. YCSB is another benchmarking tool that can be used for testing various NoSQL databases. You can find several articles on YCSB and YCSB-JSON on Couchbase blogs page.