Choosing your primary datastore

6 min readOct 14, 2018

--

When developing a new service, one of the critical decisions that the team has to make, is the choice of primary data store. We had to do that too. We all have had good and bad experience with different data persistence technologies and the landscape is constantly evolving. While many good articles exist (see References), I wasn't able to find a survey of these technologies in one place. This article presents a comparison of three main-stream technologies - RDBMS (most popular choice MySQL) and NoSQL (Cassandra, DynamoDB) .

MySQL

Used by some of the largest content services in the world today e.g.
Facebook, Quora, You Tube, Twitter and GitHub to name a few
https://www.mysql.com/customers/
It’s robust, replication works and it’s easy to use and run
Aurora by AWS is a MySQL managed service which takes this one step further by providing many goodies OOTB
— Redundant storage across AZ, multiple Read replicas with load balancing
— Autoscaling storage, SSD based virtualized storage
— Automated routine tasks like provisioning, patching, backup, failover, recovery etc.
— Better performance characteristics than standard MySQL
— Disaster Recovery : Supports replicas in different region
MySQL scalability isn’t an issue, if you partition the data at the application level. And there are many ways to do it — Using simple application based routing where you can direct traffic based on some application identifier (e.g. tenant identifier in a multi-tenant system) or Using an off-the-shelf frameworks like MaxScale, ProxySQL, MySQLRouter, MySQL Fabric, etc.
In fact, one can go a long way by simply “scaling up” the instance with cpu cores and tons of ram + multiple read replicas. Reads can be fronted by a distributed caching like Memcache and DB has to deal mostly with writes.
Tonnes of tribal knowledge available. Easy to find expert DBAs
Plenty of good examples on how to design and scale with flexible schema. e.g. Uber Schema-less Design and Friendfeed’s
Huge community, continuous improvement of the tech stack with contributions coming in from large adopters e.g. New features like JSON type to support document style storage, index-able virtual generated columns

Cassandra

Although not as common as MySQL, Cassandra is being used at large scale by few customers .

Netflix run hundreds of clusters with over 10000 nodes and Petabytes of Data serving millions of transactions per second. Cassandra is used to store customer details, viewing history, bookmarks, even billing and payment
Apple reported to have 75000 Nodes with 10 PB of data (they may have moved to different database now)
Capacity scales linearly, can use commodity hardware
Fault tolerant
No single point of failure (master free architecture)
Automatically replicated (cross AZ,DC,Region)
Data Modeling — Support for collection data types like Map, Set, List as well as User Defined Type
High performance for Writes
— Works great for append only, relatively immutable data with minimal updates and deletions
— Tombstones(special deletion records), cause heap pressure and other related issues
— Row and Batch level atomicity for writes. Lightweight transaction support using conditional writes
Table per Query model. Data duplication is okay, optimizing for data is anti pattern.
— Create the same data in multiple tables to get secondary index.(Cassandra 3+ supports materialized views now which does the same job OOTB)
— Proliferation of materialized views can become very common (In one of my services I have 6 different Materialized views for some tables).
High Operational Complexity
— Monitoring the ring. Manual recovery and repair operations, understanding processes such as compaction, memtables, sstables etc.
— Most companies running this at scale have build custom tools to manage Cassandra clusters
— None of Big 3 AWS,GCP or Azure provide Cassandra as managed Service
— Other providers : DataStax Enterprise provides Cassandra as a service with administration, monitoring, developer tooling etc.

AWS Dynamo DB

Fully managed by AWS, scales horizontally and built for denormalized views of data
— Essentially a Key value store that also supports document attribute type
— Schema-less Data Modeling with support for Scalar, Document (JSON) and Set types
— HTTP(S) API , no persistent connection
Proven to work at scale, Over 100K customers with large enterprise adoption - Lyft, AirBnB, Expedia, Duolingo to name a few
— DynamoDB served over 12.9 million requests per second on AWS Prime day
— Case studies
Table-per-Query model, similar to Cassandra
— Aggregate and keep instantiated views in table which can be read in one shot (e.g. customer and orders together)
— Create Global and local secondary indexes for native filtering on non-key attributes
— GSI is similar to Cassandra Materialized views, a copy of original table. LSI is per partition
Row(Item) level atomic writes. Lightweight Transaction support using conditional writes.
Throughput provisioning
— DynamoDB provides two independent throughput control/knobs - Read capacity (RCU) and Write capacity (WCU)
— Provisioned on per Table/GSI basis
— RCU and WCU are table data are uniformly spread across partitions of the Table. See partitioning math in Appendix
— If sustained throughput goes beyond provisioned throughput then throttling is done. Most common cause is non-uniform workloads which cause "hot keys" i.e. bulk of traffic going to one partition. Adaptive Capacity can address this to some extent.
GSI provisioned throughput is separate from the provisioned throughput settings of the table.
Important Limits
— Item(Row) size, including LSI size, is limited to 400KB
— 5 global secondary indexes and 5 local secondary indexes per table
DynamoDB Accelerator(DAX)
— Managed, clustered in-memory cache for DynamoDB for read heavy and burst workloads.
— Write through cache, can reduce RCU requirements for read heavy workloads
DynamoDB Streams + Lambda
— Powerful execution framework to build real time aggregation or other views of your data
— Much superior to Stored procedures in RDBMS

Conclusion

Cost
DynamoDB charging is based on provisioned IO unlike Aurora which is based on used IO. Note that GSI provisioned IO is separate from main table. Large AWS Customers have reported a decrease in operational cost of up to 40–50% by moving from Dynamo to Aurora. Its hard to quantify without actual data and workload but in general Aurora is ~20% more expensive than MySQL RDS.
Scaling
All three solutions can scale linearly and provide HA when designed right.
Efficient Querying & Flexible Data Modeling
MySQL would lead the pack being most flexible in terms how and what you want to index. Both Cassandra and DynamoDB indexes are materialized views (copy of main table). Note that primary goal for DynamoDB is low latency key based queries with high throughput and fast ingestion of data. DynamoDB Schema-less nature provides more flexibility than MySQL but improvements like JSON type support in MySQL have bridged that gap. BTW, Don't consider Schemas to be all evil. Schemas enforce type on the persisted data of your application, they serve as documentation, and in many case would prevent bugs.
Operational Complexity
DynamoDB and Aurora are AWS managed solutions with DynamoDB being fully managed (no need to choose instance types tc.). Most companies running Cassandra at scale have devised their own tools to manage the clusters. DataStax provides managed Cassandra but with none of Big 3 (Azure, AWS, GCP) providing a managed solution, its hard to see its adoption growing.
Trust and experience
Can you trust the system with your mission critical data? Do you have enough experience in your teams with this technology? Is it easy or hard to find solution to the problems you encounter when using this system?
MySQL wins hands down on these parameters. DynamoDB is a solid solution too but its proprietary in nature (and don't forget the cost :P)
Appendix
AWS DynamoDB Partitioning Math
- By Capacity = (Total RCU)/3000 + (Total WCU)/1000
- By Size = Total Size/10 GB
- Total partitions = MAX(By Capacity, By Size)
- Example:
- Table Size = 8GB, RCU=5K, WCU=0.5K
- = 3 Partition. RCU =5K/3= 1.7K per partition and WCU=0.5/3=166.7 and data per partition = 10/3= 3.33 GB

Choosing your primary datastore

MySQL

Cassandra

AWS Dynamo DB

Conclusion

References

Written by msingh

No responses yet