Transactional and Analytical Workloads

How Transactional and Analytical Performance Impacts the TCO of Cloud Databases

Table of Contents

  1. Summary
  2. Data Architecture Selection and TCO
  3. Platform Summary
  4. Performance Testing
  5. TCO Testing and Analysis
  6. Conclusion
  7. Disclaimer
  8. About SingleStore
  9. About William McKnight
  10. About Jake Dolezal

1. Summary

Competitive data-driven organizations rely on data-intensive applications to win in the digital service economy. These applications require a robust data tier that can handle the diverse workloads demands of both transactional and analytical processing while serving an interactive, immersive customer experience. The resulting database workloads demand low-latency responses, fast streaming data ingestion, complex analytic queries, high concurrency, and large data volumes.

Typically, organizations solve this problem by using several database technologies stitched together, but this increases development complexity, operational costs, and data duplication. SingleStoreDB is a cloud-native distributed relational database that unifies transactional and analytical processing in a single database technology, which simplifies the application data infrastructure and allows businesses to innovate and deliver their digital services more quickly.

Businesses need modern scalable architectures and high levels of performance and reliability to gain timely analytical insights and earn competitive advantage. They also value fully managed cloud services that can leverage powerful data platforms without the technical debt and burden of finding talent to manage the resources and architecture in-house. These as-a-service models enable users to only pay as they play and to stand up a fully functional analytical platform in the cloud with just a few clicks.

This report outlines the results from a GigaOm Field Test derived from three industry standard benchmarks—TPC Benchmark™ H (TPC-H), TPC Benchmark™ DS (TPC-DS), and TPC Benchmark™ C (TPC-C)—to compare SingleStoreDB, Amazon Redshift, and Snowflake. We found that SingleStoreDB offers benefits that include:

  • 50% Savings over three years compared to Snowflake-MySQL stack
  • 60% Savings over the same period compared to Redshift-PostgreSQL stack
  • Up to 100% Faster in TPC-H workloads compared to Redshift (with Refresh)

Note that these standard benchmarks for transactions and analytics mostly address data at rest. By contrast, the behavior of a database system under load—performing streaming data ingestion while processing transactions, computing analytics, and serving users concurrently—is often defined more by data in motion.

For operational workloads we used SingleStoreDB as a rowstore, while also using it as a columnstore for comparison across both transactional and analytic tests. Two issues are relevant here: First, columnstore is generally not recommended for transactional workloads but is applied here for comparative insight; and second, the columnstore implementation in SingleStoreDB is called Universal Storage and adds capabilities like fast point reads, updates, and deletes. It is significantly more performant and effective in analytic scenarios than traditional columnstore approaches.

These tests produced interesting results that reveal some of the performance characteristics of these platforms. The tests compared the performance of the following fully managed cloud database-as-a-service offerings:

  • A MySQL, Snowflake, ETL, Redis, and Kafka Stack
  • A PostgreSQL, Redshift, ETL, Redis, and Kafka Stack
  • A SingleStoreDB and Kafka Stack

The results of the GigaOM Analytical Field Test are valuable to all analytical functions within an organization, addressing scenarios such as data analysis, operational reporting, interactive business intelligence (BI), data science, machine learning, and more.

The results of the GigaOM Transactional Field Test, meanwhile, are valuable to all operational functions of an organization, such as human resource management, production planning, material management, financial supply chain management, sales and distribution, financial accounting and controlling, plant maintenance, and quality management.

We also tested SingleStoreDB, Redshift, and Snowflake with a 10TB TPC-H-like analytical workload. From the configurations we tested, we found that an S-48 SingleStoreDB cluster was slightly faster than a Snowflake 3X-Large cluster and more than twice as fast as a 10-node ra3.16xlarge Redshift cluster.

We then calculate the annual costs of the platform stacks and the time-effort costs (people costs, development costs and production costs) to conclude that SingleStoreDB is 2x cheaper than the Snowflake-MySQL stack and 2.5x cheaper than the Redshift-PostgreSQL stack over three years running enterprise-equivalent workloads. What’s more, as shown in Figure 1, only SingleStoreDB is able to handle both analytical and transactional workloads in the same engine.

Our final conclusion: The superiority in transactional processing and the high competitiveness in analytic processing makes SingleStoreDB worth consideration as a single database solution that addresses a spectrum of enterprise workloads.

Figure 1. Bridging Workloads: Database Performance Across Analytical and Transactional Workloads

As shown in Figure 1, the results of the TPC-DS-like and TPC-H-like tests are measured in seconds, with SingleStoreDB leading both Redshift and Snowflake in TPC-H-like performance, and edging out Snowflake in TPC-DS-like performance. The right side shows the ability of SingleStoreDB to run transactional workloads (such as those in the TPC-C-like benchmark) when other solutions cannot.

Full content available to GigaOm Subscribers.

Sign Up For Free