Prerequisites for a Successful Evaluation

1. Please ensure your cluster is installed according to documented Best Practices, and that sufficient resources have been allocated for your tests.  Note that the minimum supported hardware configuration is adequate for testing compatibility, but is not suitable for load or performance testing. Instead, please see Recommended Hardware Configurations.
2. Check Clustrix and system logs for any recurring errors. 
   1. This could include environmental issues, such as transient network errors or storage subsystem issues, could introduce inconsistencies in performance or cause test failures.  
3. As a general rule, it is best to perform one aspect of performance testing at a time, in order to obtain consistent, comparable numbers. For example, unless you are specifically measuring mixed workload behavior, perform analytic query tests separately from OLTP tests. 

Testing OLTP Performance

This section describes considerations when testing OLTP performance, where we are focused on finding the maximum throughput of the system, while maintaining a reasonable response time (query latency).  Given an appropriate test harness and set of queries as described below, you can determine whether the TPS/latency figures of a given configuration are suitable for your application, or directly compare performance of Clustrix and an alternative database using the same workload at varying concurrency.  

Test Harnesses

Since Clustrix is optimized for high concurrency OLTP workloads, a test to exhibit this capability must simulate many users accessing the database at once. A single-threaded test (for example feeding mysql client a list of queries) will fail to utilize the distributed resources of a Clustrix cluster, and obtain results no better than a single-instance database.  A suitable performance test utilizing tens or hundreds of threads, however, will allow Clustrix to leverage its distributed architecture.

 It is important to have some kind of test harness to run load against the cluster.  A suitable test harness for high-throughput transaction testing should have the following capabilities:

• ability to run a number of queries against the cluster, measuring throughput (TPS) and latency (average query time, in ms)
• multi-threaded; ability to increase concurrency of the workload 
• light-weight and/or distributed across multiple clients

As with any performance exercise, it is important to identify and eliminate any bottlenecks.  If your test harness is itself the bottleneck, you will not be able to differentiate the performance characteristics of the database under test.  Make sure that your test tool is efficient enough that it can drive high load without itself requiring lots of CPU or memory; this is a particularly important consideration if you are building a test with a robust application server framework, where you may find you need to allocate a large number of servers to adequately drive the database backend.  To avoid this expense and complexity, you may prefer to use one of the methods of query replay suggested below.  

We have worked with customers who have successfully utilized existing load test frameworks such as YCSB and jmeter, both of which are designed to scale load.  We have also assisted users in utilizing two tools from Clustrix, stest and aleqload, in order to build a model of their workload from queries extracted from their current production database.  

stest and aleqload

stest and aleqload are tools from Clustrix for generating query workload with varying concurrency in an efficient manner, such that a single thread can saturate even a large cluster with query load. stest simply re-plays queries from a specified set of queries and allows you to specify number of threads and a duration or number of iterations. The output of stest includes metrics for throughput and latency measurements.  The set of queries is typically obtained by extraction from a tcpdump collected on the production database; Clustrix Sales can assist in the process of extracting queries and executing these with stest.

aleqload is a more sophisticated tool, similar to MySQL's randgen utility, but with considerably more functionality.  Rather than replaying a given set of queries, it takes a configuration file which specifies a grammar for generating queries, including random generation of values.  The distribution of each query type can be fine-tuned to match production workload.  The tool also allows setting a maximum QPS (query per second) rate, in addition to driving maximum load, which is useful for measuring system response to ad hoc queries while the system is under some baseline workload.  

For more information on stest or aleqload, contact Clustrix Sales or Support.

Distributing Client Load

As noted above, it is important that your test include sufficient concurrency (multiple threads) to engage all cluster resources and adequately model production load.  Further, clients should distribute their thread connections evenly across all the nodes in a cluster.  Some test harnesses (including stest and aleqload) allow specification of a pool of hostnames/IPs to facilitate this.  Otherwise, a front-end load balancer can be used, as described in  Load Balancing Clustrix with HAProxy and Configure EC2 Elastic Load Balancer for ClustrixDB AWS.  You can confirm that load is evenly distributed during your test run by checking the output of clx stat, which shows TPS for each node, or with a query such as:

SELECT nodeid, count(1) FROM system.sessions GROUP BY nodeid, ORDER BY nodeid;

To optimally balance load across the cluster, consider using a thread count which is an even multiple of the number of nodes. However, for sufficiently high thread counts (greater than total number of cores in the cluster), exactly even distribution of threads will matter much less. 

Test Data Set

For an existing application, a dump and restore of the current production data set provides the simplest data set for testing. For very large data sets, it may be desirable to utilize a subset of production data, in which case:

• mysqldump's --where flag can be used to extract a subset of production data, including the neat trick --where "1 LIMIT 1000"
• Take care that any query set is also tweaked to ensure it operates on this subset of data (avoid do-nothing queries which operate on 0 rows)

When creating a data set from scratch, be careful to avoid problems arising from synthetic data. One particular problem to avoid is repeating one or very few values in an indexed column; this leads to queries over the index either processing most rows or none at all, but additionally the imbalanced distribution of values will trigger the Rebalancer redistribution mechanism to kick in during data population. Consider using a data generation tool which is specifically designed to create meaningful distribution of values, such as spawner or benerator. 

Please see Best Practices for Loading Data Onto Clustrix for guidelines to obtain optimal import speed and data distribution.  

Query Set Used for Testing

When testing OLTP performance with a test harness such as stest or aleqload, it's important to ensure that you are testing just OLTP queries. A common pitfall is to include some non-OLTP query, such as a reporting query, which may take several seconds or even minutes to execute. If queries are randomly selected by threads of the harness, many threads will end up executing this one query, resulting in very low TPS.  Clustrix Insight's Current Workload Analysis tool is ideally suited for determining if your test workload is dominated by a single query. You can also look for such a problem by running SHOW PROCESS LIST or selecting from system.sessions while running your test, to identify any long-running queries.  

Please note that scraping queries from MySQL's slow query log will not produce a set of queries suitable for OLTP testing; see analytic query testing below.  

If you discover queries which are dominating your workload, remove them from your test if they are clearly reporting queries which are expected to be expensive, or optimize them as described in Optimizing Query Performance.

Testing Analytic Query Performance

For OLAP testing, customers are typically concerned with query response time for complex queries, rather than aggregate throughput.  In this case, a simple framework which executes queries one at a time is reasonable, but consider the following guidelines:

Always execute queries at least twice

Running the query for a second time eliminates two confounding factors, caching and query compilation. Depending on your dataset size and workload, in production most queries may execute from cache, in which case cold cache performance may not be terribly relevant. Clustrix caches the compiled program of each query, so subsequent executions need not be recompiled; for particularly complex queries (in particular, with many-way joins), compilation time may comprise a significant portion of total execution time. Running a second time will give you a good idea of how the query will execute in production, where the compiled query plan will typically be cached.  

Ensure your queries do meaningful work

This goes hand in hand with the comments above regarding use of a data set with meaningful distribution of values. Make sure that your queries match your data. One issue we have encountered is using a dump which is several months old, but queries taken from current workload; many of the queries included date ranges which matched no data in the data set, so the queries returned no rows. 

Make sure appropriate indexes are available

The indexes necessary for efficient execution of your queries are in most cases the same as needed for MySQL or other databases. However, given the distributed nature of ClustrixDB, the lack of an index can sometimes impose a more severe penalty than on a single-instance database; this is due to broadcasting among nodes, which is avoided when proper indexes (e.g. on the columns of a JOIN clause) are available. Customers sometimes use queries from their MySQL slow query log to test against ClustrixDB; in some cases we found that these queries were slow on MySQL due to lack of a proper index, and without the index, ClustrixDB was indeed slower than MySQL.  With the proper index, MySQL's performance was improved, but ClustrixDB with the index was even faster. 

For more information on identifying and correcting queries which fail to use an index, see the material in Optimizing Query Performance.

Recognizing Platform Resource Constraints

When testing performance of your ClustrixDB cluster, it is important to bear in mind the limitations imposed by your platform:  CPU, memory, storage I/O, etc. The good news here is that, as a scale-out solution, the limitations of a single server can be overcome by growing the cluster to more nodes. However, to ensure growing your cluster will improve performance of your workload, it is important to identify the limiting resource constraint.

Recognizing Platform Limits provides detailed information on identifying resource constraints, but to summarize here:

• If total CPU load for your cluster is approaching 100%, your workload is CPU bound will clearly benefit well from additional nodes
• Else, if buffer manager miss rate is significant (2% or more), and disk latency is high, your workload is likely disk bound
  
  • If your working set can be kept in cache through the additional memory made available by expanding the cluster to more nodes, adding these nodes can improve performance significantly
  • Otherwise, you may need to consider use of SSDs in your platform to reduce latency when going to disk
  • Note that bad plans due to missing indexes can also drive BM miss rates, so consider also a pass at query optimization (XXX link to query optimization material here)
• While an uncommon problem, check that the network between nodes is not becoming a bottleneck (use system.internode_latency); typically this would happen due to misconfiguration or the provisioning of cluster nodes from different subnets

Testing Scale-Out

To validate the scaling capabilities of ClustrixDB, you may wish to repeat your tests, whether OLTP, analytics, or both, with different cluster sizes. When doing such scale testing, particularly with a smaller data set, it is important to take care that your data is evenly distributed for each iteration of cluster size. There are several approaches to this:

• Re-import your data set using clustrix_import after each cluster expansion, as clustrix_import ensures each table/index has at least once slice per node
• Reslice your tables (ALTER TABLE <table_name> SLICES=<number of nodes>) after growing the cluster
• Create all tables with a slice count which is a common multiple of the cluster sizes you plan to test (e.g. 18 if you plan to test 3, 6, and 9 nodes), and then wait for the Rebalancer to finish rebalancing after expanding the cluster

Which of these strategies is appropriate depends largely on your data set size. Note that if you plan to use the rebalancer, you can greatly increase the limits on rebalancer activity to decrease the time it takes to move data to the new node(s), as described in Managing the Rebalancer.