Improve Application Performance with SQL Auto-Caching, No Code - PowerPoint PPT Presentation

Improve Application Performance with SQL Auto-Caching, No Code changes Roland Lee Heimdall Data

Agenda 1) Database Proxy Introduction 2) Demo 2

Web scale Challenges More users and applications implies • Higher data volumes • More tables, changes to schema • Higher latency responses 3

Database Proxy Vendors Feature ProxySQL ✓ ✓ ✓ ✓ Automated Failover ✓ ✓ ✓ ✓ SQL Read/Write Splitting ✓ Automated Cache invalidation ✓ Reduces network latency 4

IMDG or Database Proxy Amazon ElastiCache • Good scalability & performance • Best scale & performance • Existing applications, small dev • Greenfield applications • No code changes • Requires code changes 5

Heimdall Data Software Options Application Server Application Server Application Application Vendor Database Heimdall Data JDBC driver, Driver JDBC .jar file Vendor JDBC Driver Runs as an Heimdall Data Proxy agent 6 Any JDBC data source

Heimdall Data Distributed Proxy Auto-caching Auto-invalidation Removes network latency 1. Heimdall is installed on each application instance 2. Direct Heimdall to the grid-cache of your choice (e.g. Redis, Hazelcast) 7 3. Heimdall will auto-cache and auto-invalidate SQL results to the look-aside cache

Heimdall Data Uses Cases Use case Customer Benefit • Auto-caching / Auto-invalidate SQL Results Caching • NO code changes • Faster failover for MySQL & SQL Server AlwaysOn Automated failover • PGPool-II Replacement • Improves write performance • Batches singleton DML operations Batch Processing • Removes Commit overhead • Logs data access: Who, what, when Auditing for Privacy • GDPR, SOX, PCI, HIPPA Compliance 8

Heimdall Data Architecture Heimdall Application Servers Application Heimdall Data Automated Application Server Application Heimdall DB Failover Data Application Server Application Data Application Server • You choose the storage Heimdall Application Server • Heimdall auto-caches Central Console • Heimdall auto-invalidates SQL Analytics 9 Audit Logging ElastiCache

Heimdall Data for Microservices Microservice Application Hosts Heimdall Heimdall Application Data Micro Micro Application Micro Heimdall Data service service service Data Heimdall Heimdall Micro Micro Micro Heimdall Application service service service Data Data Data Micro Micro Micro service service service Heimdall Data Solution DB Failover Connection Pooling Caching SQL Analytics 10 Audit logging

Application Side – SQL Caching Application Server Removes network latency SQL SQL Heimdall Application DB Proxy Local Cache • Heimdall auto-caches • 2-Tier Look-aside SQL cache L2 Cache • Not a write-through cache • Not a read-through cache • Auto-invalidation Elasticache 11

What to Cache? Uses real-time analysis and statistics on: • Query frequency and variability • Relative performance of Cache vs. Database Provides: • Auto-cache only if there is a performance benefit • Cache recommendations and benefits 12

Heimdall Query Analytics Very cacheable. 700 µ s per query 13

Heimdall Cache Rules Analytics Before: Query response time 700 µ s per query. Significant repetitive database load. After: Cached response in 70 µ s per query. Reduced database load. 14

Other use cases

Other use cases

Use Case #1: Read / Write Splitting Application Server Read 1 Heimdall Application Data Application Server Read 2 1. Automate use of read replicas (read/write splits) Read 3 1. Replication lag detection to ensure data freshness 2. Scale out the database with no application changes! 17

Use Case #2: Async DML Batching DML Data Generator Request START TRANSACTION; #1 DML 1; DML 2; DML 3; #3 DML 4; #5 COMMIT; #2 Queue Spoofed Success on 8 7 6 5 4 3 2 1 Queue Insert #4 4 3 2 1 Exceptions are logged, Batch Size 4 removed from batch, and transaction restarted Benefits: •Lower CPU overhead due to fewer commits •Improved application response time 18 •Improved DML scale

Use Case #3: SQL Analytics Application Server SQL Heimdall Application DB Proxy Identifies performance bottleneck Heimdall • Application? Central • Database? Console • Network? 19

Demo 20

Thank you Roland Lee Heimdall Data