Download as PDF, PPTX
Uploaded byScyllaDB
Dissecting Real-World Database Performance Dilemmas
The document discusses database performance challenges and solutions, particularly with ScyllaDB, highlighting its impressive throughput and low latency for various applications. It covers real-world use cases, such as managing latency in adtech and optimizing data retention strategies, while also addressing the complexities of scaling database operations. Key takeaways include the importance of reviewing third-party integrations and maintaining a holistic view of system performance.
More Related Content
Similar to Dissecting Real-World Database Performance Dilemmas
Dissecting Real-World Database Performance Dilemmas
- 1. Dissecting Real-World Database Performance Dilemmas FelipeMendes, Solution Architect Noelly Medina, Technical Support Team Lead Guilherme da Silva Nogueira, Senior Solutions Architect
- 2. + For data-intensiveapplications that require high throughput and predictable low latencies + Close-to-the-metal design takes full advantage of modern infrastructure + >5x higher throughput + >20x lower latency + >75% TCO savings + Compatible with Apache Cassandra and Amazon DynamoDB + DBaaS/Cloud, Enterprise and Open Source solutions The Database for Gamechangers 2 “ScyllaDB stands apart...It’s the rare product that exceeds my expectations.” – Martin Heller, InfoWorld contributing editor and reviewer “For 99.9% of applications, ScyllaDB delivers all the power a customer will ever need, on workloads that other databases can’t touch – and at a fraction of the cost of an in-memory solution.” – Adrian Bridgewater, Forbes senior contributor
- 3. 3 +400 Gamechangers LeverageScyllaDB Seamless experiences across content + devices Digital experiences at massive scale Corporate fleet management Real-time analytics 2,000,000 SKU -commerce management Video recommendation management Threat intelligence service using JanusGraph Real time fraud detection across 6M transactions/day Uber scale, mission critical chat & messaging app Network security threat detection Power ~50M X1 DVRs with billions of reqs/day Precision healthcare via Edison AI Inventory hub for retail operations Property listings and updates Unified ML feature store across the business Cryptocurrency exchange app Geography-based recommendations Global operations- Avon, Body Shop + more Predictable performance for on sale surges GPS-based exercise tracking Serving dynamic live streams at scale Powering India's top social media platform Personalized advertising to players Distribution of game assets in Unreal Engine
- 4. Introductions Felipe Mendes, SolutionArchitect + Helps teams solve their most challenging problems + Author of Database Performance at Scale Noelly Medina, Technical Support Team Lead + Years of experience in customer-facing roles + Always seeking for new challenges Guilherme da Silva Nogueira, Senior Solutions Architect + Years of experience with Linux and distributed systems + Just got a new puppy
- 5. Puppy time! Meet Baunilha (Portuguesefor Vanilla)
- 6. Agenda + Huntingdown a Latency Problem + Ticking Time-Series Bomb + Scale, scale… SCALE! + Hot Facts, Cold Insights 6
- 7. Hunting Down aLatency Problem Uncovering a Multi-Region Performance Challenge 7
- 8. Customer evaluated ScyllaDBand was happy with the results + Initial testing: + 3 node cluster (AWS us-east1) + P99 < 15ms + Using gocql driver: + Following query best-practices + Making use of gocql.DataCentreHostFilter + All application queries using a LOCAL_* ConsistencyLevel 8 A Latency Sensitive Workload for AdTech
- 9. Final production requirementswere multi-region (AWS us-west-1) + Followed the Adding a New DC to an ScyllaDB Cluster procedure + Latency went through the roof! 9 The PROBLEM
- 10.
- 11. + We realizedthat latencies only affected a specific ScyllaDB scheduling class 11 A Few Data Points + A path for resolution: main query class P99 driven by the Network RTT
- 12. + nodetool setlogginglevelquery_processing trace: 12 Whoops! Tracking down individual queries + Seems like we are getting close. Ideas? for i in *; do egrep -Hi "SELECT|UPDATE|INSERT|DELETE" $i | awk -F':' '{ print $1, $NF }'; done | sort -V | uniq -c 215 (cached) "SELECT * FROM system_auth.roles WHERE role = ?" (cassandra) 1 (cached) "SELECT * FROM system_distributed.service_levels;" () 304 (cached) "SELECT * FROM system_auth.roles WHERE role = ?" (cassandra) 3 (cached) "SELECT * FROM system_distributed.service_levels;" () 323 (cached) "SELECT * FROM system_auth.roles WHERE role = ?" (cassandra) 2 (cached) "SELECT * FROM system_distributed.service_levels;" () 1 (cached) "SELECT id, data, written_at, version FROM system.batchlog LIMIT 128" () 7 (cached) "SELECT * FROM system_distributed.service_levels;" () 1 (cached) "SELECT id, data, written_at, version FROM system.batchlog LIMIT 128" () 6 (cached) "SELECT * FROM system_distributed.service_levels;" () 1 (cached) "SELECT id, data, written_at, version FROM system.batchlog LIMIT 128" () 6 (cached) "SELECT * FROM system_distributed.service_levels;" ()
- 13. + From ScyllaDB: 13 Theanswer lies within the code (and in our docs!) + From Enable Authorization: db::consistency_level password_authenticator::consistency_for_user(std::string_view role_name) { if (role_name == DEFAULT_USER_NAME) { return db::consistency_level::QUORUM; } return db::consistency_level::LOCAL_ONE; }
- 14. Ticking Time-series bomb Rushingagainst time 14
- 15. Log retention usecase in production + ScyllaDB Cloud cluster in GCP + 6 node cluster + Latency within bounds, except during repairs 15 Major Streaming Company
- 16. Cluster repairs weretaking long to complete + Some nodes started to run out of disk space + Customer was under a time-sensitive "freeze" period 16 The PROBLEM 4% free space!
- 17. + Compaction wasunable to keep up with the rate of incoming files: 17 An interesting symptom
- 18.
- 19. + What weknow thus far: + Nodes are running out of disk space + Compaction is unable to catch up + Repair is taking long 19 Data Modeling Review
- 20. + Making useJaeger as an integration: + Business-critical application metrics + Time to Live (TTL) for automatic data expiration + Spreads data using a "bucket" technique, sorted by time + Circling back to the basics: 20 Data Modeling Review $ cqlsh -e "DESC SCHEMA" | egrep -i "compaction =" | sort -h | uniq -c 43 AND compaction = {'class': 'IncrementalCompactionStrategy'} 33 AND compaction = {'class': 'SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'} 55 AND compaction = {'class': 'TimeWindowCompactionStrategy', 'compaction_window_size': '1', 'compaction_window_unit': 'HOURS'} + 131 tables to run through … 😢 + … but TWCS tables seem interesting 💪
- 21. + TTL 259200(in seconds) == 30 days + Split in 1 hour buckets + 30 * 24 buckets = 720 windows! 21 Diving Deeper CREATE TABLE x.y ( <...> ) WITH crc_check_chance = 1.0 AND compaction = {'class': 'TimeWindowCompactionStrategy', 'compaction_window_size': '1', 'compaction_window_unit': 'HOURS'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND default_time_to_live = 2592000 AND gc_grace_seconds = 10800
- 22. 22 Understanding the problem TWCS Picturefrom: https://www.pythian.com/blog/proposal-for-a-new-cassandra-cluster-key-compaction-strategy
- 23. 23 After schema changes +Repair completed, memory utilization reduced, performance improved!
- 24. 24 + Customer explainedthey were using Jaeger integration "stock" settings + We reported and addressed jaegertracing/jaeger/4561 upstream: + "With ScyllaDB (and likely Cassandra too), increasing TTL leads to large numbers of sstables" + Felipe introduced the "twcs_max_window_count" tunable in ScyllaDB: + Safemode - Introduce TimeWindowCompactionStrategy Guardrails + Available starting at ScyllaDB Open Source 5.2 & Enterprise 2023.1 + Takeaways + Be suspicious of activities taking a long time to complete + Review how third-party integrations works under the hood + Rest assured with ScyllaDB Cloud expertise to back you up! Post event findings (and diligence)
- 25. Scale, scale… SCALE! Shouldthe sky really be the limit? 25
- 26. + This isa fairly common scenario: + Decades of user data stored to be retrieved live + Long-term Cassandra deployment + Massive data growth YoY, dozens of Terabytes per cluster + Multiple clusters per geo + Some part of the dataset replicated EU/Asia + Rest of the dataset siloed per region + ~260TB raw dataset + This is a Cassandra cluster, so we should just scale horizontally, right? 🚀 26 Use Case Review
- 27. + Multiple clustersscaling horizontally to keep up with data growth + From 9 nodes, to 12, 18, 30, 45, 90, … 200, 400!? 😱 + Managing multiple clusters of 100's of nodes is challenging + From technical and cost perspective 💸 + Each node has CPU and RAM, that sums up to a stratospherical amount 🛰 + Assuming 64 vCPUs and 512GB RAM, 400 nodes = 25K vCPUs and 2TB RAM 27 Node sprawl
- 28.
- 29. + Current technologyrestricted storage density per node + Does not make efficient use of the hardware + Cannot tackle nodes denser in storage 29 Look for alternatives
- 30. + From: 64vCPUs x 400 nodes = 25K vCPUs + To: 21 x i3en.24xlarge (96 vCPUs, 60TB storage) = 2K vCPUs + ~12x reduction in vCPUs + ~20x reduction in node count 30 How can ScyllaDB help?
- 31. Hot Facts, ColdInsights Data Thermodynamics & ScyllaDB 31
- 32. Engaged with usafter losing data in HBase + On-prem bare-metal deployment + HUGE storage footprint – Petabyte range + Tiered storage or similar requirement: + "hot" – frequently accessed data or; + "cold" – least accessed data + No retention periods, data is forever stored 32 Worldwide Feed Aggregator
- 33. Which deployment andreplication strategies to follow? + Single cluster, dual hot/cold DC? + Separate clusters, observable replication? + Should we use CDC for replication? + To dual-write or not? + How to evict data from the "hot" cluster? 33 Challenges
- 34.
- 35. Reasons were plenty: +An on-premise deployment makes it fair difficult to effectively leverage it + Decommissioning the previous HBase infrastructure wasn't an option + Performance: + "Local" Object Storage latencies were suboptimal + Cloud ones would result in: + Network RTT penalty + Internet traffic ($$) + Need to come up with data tiering/replication strategies anyway… + … and still figure out how to enable their applications to work with an Object Store 35 Why not just use Object Storage?
- 36. Plan-ahead: + ScyllaDB specializedcache fully allocates server's memory (LSA) + Important on-disk components (SSTable metadata) needs to be stored + 1:30 for performance, 1:100 as an upper bound limit 36 Memory and Storage Limits
- 37. Ultimately, they decidedto apply the following strategy: + Application primarily writes to "hot" DC; + Replication from "hot" to "cold" is asynchronously handled by ScyllaDB + After their cut-off period, simply remove all replicas from the "hot" DC: 37 ScyllaDB Replication ALTER KEYSPACE replicated_keyspace WITH replication = {'class': 'NetworkTopologyStrategy', 'hot': 0, 'cold': 3}; + Similar strategy already employed in HBase, zero friction point
- 38. Performance requires anholistic view: + An overlooked setting may be the culprit to performance problems + Integrations won't always follow best practices + Be sure to take the most of your underlying (and available) infrastructure + Context is fundamental to guide your decisions 38 Summary
- 39. Q&A ScyllaDB Cloud Start freetrial scylladb.com/cloud Feb 14-15 | VIRTUAL EVENT scylladb.com/summit Virtual Workshop January 25, 2024 scylladb.com/events
- 40. Thank you for joiningus today. @scylladb scylladb/ slack.scylladb.com @scylladb company/scylladb/ scylladb/