Hekaton – (CA) OLTP / OLAP Performance Analysis

Summary

Microsoft released a whitepaper recently titled ‘In-Memory OLTP – Common Workload Patterns and Migration Considerations’. In it they show how Hekaton performance varies in comparison with traditional SQL Server tables and procedures, and the workload patterns that suit in-memory OLTP. While not disputing the Microsoft whitepaper, it doesn’t include code samples to support the points made, this article does.

This test workload pattern shows that Increasing index sizes and logical reads have a negative impact on Hekaton performance, but conversely the more procedure calls a second the better it performs in comparison with traditional SQL. It uses a Cellular Automation (CA) mathematical database application to place similar demands in Hekaton and a corresponding set of native SQL Server tables and stored procedures.

The test was performed in a single query analyser session so the Hekaton concurrency improvements aren’t considered, the CA application does support multiple concurrent sessions but they weren’t used. Even just the benefit of not having to compile and interpret queries makes a massive difference, 75 times faster in this reproducible test.

Execute the OLTP / OLAP Test Simulation

OLTP / OLAP Spectrum Analysis and Conclusions

The simulation script varies the workload type as the test progresses, initially favouring OLTP but transitioning gradually to more of an OLAP bias. The ‘OLTP / OLAP Simulator’ script initially makes 512 procedure calls each requesting the generation of 1 new pattern (OLTP Bias), this slides across the OLTP / OLAP spectrum until finally 1 procedure call is made requesting the generation of 512 new patterns (OLAP Bias). The Pie Charts show how long each set of procedures took to complete the requests at different Stress Levels, notice Hekaton performance falling away relative to the native SQL ‘IO’ procedure as the cost of each call increases and the frequency of the calls decreases.

Hekaton_Stress_Test_Trend2

The difference in total execution times between Hekaton and SQL Server stored procedures and queries varies according to the batches called and the cost per batch. When the batch costs are low and frequent (XLTP below) then there are significant gains in using Hekaton, but as cost increases and frequency decreases (OLAP below) then the gains are gradually lost.

OLTPOLAPBias10

Stress Test Description

The OLTP / OLAP bias is heavily influenced by the ‘Stress Level’ parameter in the simulation test script which can be assigned a value of either 1, 2 or 3. The Cellular Automation application generates new patterns based on applying ‘cell comes alive’ / ‘cell dies’ rules to an initial starting pattern. The more complex the starting pattern, the more intensive the subsequent workload.

@StressLevel = 1  (Heavy OLTP Bias Workload)

The initial state includes one simple, 3 Merkle Pattern that oscillates between 2 states, there is very little work involved in constructing the next new pattern.

HekatonStressLevel1

@StressLevel = 3 (Heavy OLAP Bias Workload)

Gosper Glider Gun patterns are created at Stress Level 3 which give birth to two new glider patterns once every 30th iteration, the workload and IO cost increase gradually as new patterns are created.

HekatonStressLevel3a

The Good – OLTP

Natively Compiled Stored Procedures.

When a procedure is called hundreds of times a second the savings from not having to compile or interpret T-SQL queries can be significant. The graph below shows the total duration of procedures calls for the SQL Server and Hekaton pattern generator stored procedures. Both performed exactly the same work (batch requests and batch size) using very similar queries and indexes, the results in the graph are filtered as below:

  • Stress Level = 1
  • OLTP 1 to 5 workloads

OLTPBias10

OLTP bias workloads are Hekatons strength, the total procedure execution time to perform a very similar workload is significantly lower than the corresponding interpreted SQL Server procedure calls using disk based tables. The graph below shows compilation and lock request counters during the OLTP bias test cases.

OLTP_Perfmon

The Bad – OLAP
Performance deteriorates in Hekaton as the size of the indexes, and logical reads, increases. The graph below shows the total duration of procedures calls for the SQL Server and Hekaton pattern generator stored procedures. Both performed exactly the same work (batch requests and batch size) using very similar queries and indexes, the results in the graph are filtered as below:

  • Stress Level = 3
  • OLAP 1 to 5 workloads

OLAPBias10

OLAP bias workloads are Hekatons weakness. Execution times deteriorate in comparison with SQL Server disk based tables and interpreted stored procedures performing the same workload, as the batch size increases. Memory usage also becomes an issue when processing larger indexes, ‘System out of memory’ errors become a risk.

OLAP_PERFMON

The Ugly – System Out of Memory

Request 1,000 new Hekaton Stress Level 3 patterns in 4 concurrent sessions and the error message below is typically shown after about 80 seconds. The only recovery I have found is to restart the SQL Service and the Hekaton database is in a recovery state for sometime after the instance comes back online. Data lose is typical although I haven’t researched it, an alternative table schema definition in the setup script might resolve this problem.

Crash

Performance Monitor showed ‘Memory Node – Stolen Memory, Total Memory’ and ‘Resource Pool – Used Memory’ counters all spiking just prior to the crash.

CrashPerfmon

Conclusions

In specific circumstances, Hekaton is 75 (achieved with these scripts) or more times faster than native SQL procedures. Hekaton performance degrades in comparison with native SQL procedures though as the workload cost per batch increases and the frequency of batch requests decreases. As workload types become more OLAP bias, memory becomes an issue and performance deteriorates.

References



Categories: Hekaton, Performance Benchmarking

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: