AWS-Load Testing & Sizing

This testing focused on the performance impact to scaling up with process servers for performing recalculations as this action can be one of the most intensive operations for AE requiring process servers to handle recalculating and the SQL server to handle a large amount of insert/update/delete activity from multiple processes at once. As the test scales up with more process servers SQL Server will be pushed with handling a large amount of concurrent activity which can be demanding on both IO systems and CPU.

Test Configuration

For the purpose of this test, there are three basic SQL server configurations that were tested. The environment used for assessing performance was Amazon Web Services EC2 Instances.

Common Configuration

Description	Configuration
Storage	EBS Optimized Volumes
Operating System	Windows Server 2016

Test Configuration

Description	Configuration
SQL Server	2016 Developer 2.4 GHz Intel Xeon E5-2676v3
Process & Memory Configurations Used	m4.large: 2 vCPU 8GB RAM m4.xlarge: 4 vCPU 16GB RAM m4.2xlarge: 8 vCPU 32GB RAM
SQL Server Other Configuration Options	SQL Server configured with basic best practice recommendations such as MAXDOP, Max memory setting on changing instance type. Multiple tempdb files configured.
Process Server	Windows Server 2016 2.4 GHz Intel Xeon E5-2676v3 4GB ram
Process Server Other Configuration Options	Each process server set to 6 threads. This follows the basic formula we follow of (1.5 * Number of Cores) to balance multithread improvements and CPU queue length for the best performance.

Test Data

Database populated with sample assets of each property type based on sampled average sizes. For consistency in reporting variations in performance the tests were conducted against a specific office property small sample in the larger database.

For a more accurate test, the test data population was increased to bring the database over 100GB to better reflect performance as data scales up. The approx size of the test database was 138GB.

To establish a performance baseline, a 500 property batch job was run single-threaded against a single duplicated Office property. Over the course of this job, the average time for recalculation was processed as 12.4 seconds.

Load Test Results

Test Duration Overview

Improving the time to perform the batch recalculation of a scenario requires balancing resources correctly. Adding additional process servers, for example can greatly improve the time to finish a job. However, adding too many process servers without scaling up the SQL Server resources at the same time can result in a reduction in individual calculation times and little overall performance improvement for the systems being allocated.

In the test summary provided Scaling up the SQL Server resources to handle the additional demand is critical to ensure performance degradation does not occur. For instance, 6 process servers against a lower specification SQL Server with 2 vCPU with 8GB of ram showed very little benefit in increased job completion, along with a much higher individual recalculation task time to complete.

Job Duration & Calc Timing

SQL 2 CPU + 8GB RAM

The initial SQL configuration is not designed for scaling up to 6 process servers as this shows. The addition of more process servers has a negative impact to the average time to process calculations due to to the bottlenecks created with SQL server. The total time to process the entire recalculation did not improve significantly with the addition of more process servers.

SQL 4 CPU +16GB RAM

The addition of process servers here improved the total time to process the entire job, however, this came at a high cost of contention on the SQL server, and overall increased time for each task to complete.

SQL 8 CPU + 32GB RAM

With the scaling up of the process servers with better SQL Server resources, the scaling of the tests show a marked improvement to completing the entire batch job in only 8 mins. This is a marked improvement from the 2 CPU 8 GB ram sql server configuration that took > 28 minutes to complete the same task in the first test set.

Process Server Performance

Process server performance is related to the specifications of the SQL Server as adding additional process servers may process more calculations at one time, however the increase contention with SQL server cause the total time to actually increase as concurrent connections are contending for available resources. This is a critical point to remember in scaling up. Increasing process servers may result in an improved processing time for large jobs, but only when when balanced with the appropriate resource increase for SQL Server.

SQL Server Performance

Improving the time to perform the batch recalculation of a scenario requires balancing resources correctly. Adding additional process servers, for example can greatly improve the time to finish a job. However, adding too many process servers without scaling up the SQL Server resources at the same time can result in a reduction in individual calculation times and little overall performance improvement for the systems being allocated.

In the tests performed, it pointed to the critical requirement of scaling up SQL Server to appropriately handle the increased process server load

For instance, 6 process servers against a lower specification SQL Server with 2 vCPU with 8GB of ram showed very little benefit in increased job completion, along with a much higher individual recalculation task time to complete. However, when increased resources such as the 8 vCPU + 32GB ram for SQL Server was paired with the 6 process servers, a linear reduction in the time to finish batch calculations was observed.

Calculation Times Based on Property Type

For the load test examples above, a standard Office property was utilized. The other calculation timing details are provided in the image above.

The average calculation time for an asset on an individual basis can vary. For consistency in the testing results, a single asset duplicated was utilized, ensuring a baseline to work from. If the property asset types primarily used vary then performance expectations for average calculation time, and thus the time to complete a batch calculation job of those assets will change. Additionally, calculation time will vary based on the analysis years used, complexity of the model, additional modeling features used, and other variables. For the testing a standard analysis period of 15 years was selected.