In the presentation, we discuss some of the performance problems that exists when using an API to SQL Server integration on a high transaction systems with thousands of concurrent clients and several client tools that are used for statistical analysis.
Telemetry Data Story
Devices send telemetry data via API integration with SQL Server. These devices can send thousands of transactions every minute. There are inherit performance problems with a disk-based database when there are lots of writes and reads on the same table of a database.
To manage the performance issues, we start by moving away from a polling system into a real-time integration using Web Sockets. This enables the client application to receive events on a bidirectional channel, which in turns removes the need to have to poll the APIs at a certain frequency.
To continue to enhance the system, we introduce the concept of an enterprise in-memory cache, Redis. The in-memory cache can be used to separate the reads and writes operations from the storage engine.
At the end, we take a look at a Web farm environment with a load balancer, and we discuss the need to centralize the socket messages using Redis Publish and Subscribe feature. This enables all client with a live connection to be notified of the changes in real-time.
Database Optimization and Challenges
Slow Queries on disk-based storage
- Effort on index optimization
- Database Partition strategies
- Double-digit millisecond average speed (physics on data disks)
Simplify data access strategies
- Relational data is not optimal for high data read systems (joins?)
- Structure needs to be de-normalized
- Often views are created to shape the data, date range limit
Database Contention
- Read isolation levels (nolock)
- Reads competing with inserts
Cost to Scale
- Vertical and horizontal scaling up on resources
- Database read-replicas to separate reads and writes
- Replication workloads/tasks
- Data lakes and data warehouse
What is Socket.io, WebSockets?
Enables real-time bidirectional communication.
Push data to clients as events take place on the server
Data streaming
Connection starts as HTTP is them promoted to WebSockets
Why Use a Cache?
- Data is stored in-memory
- Sub-millisecond average speed
- Cache-Aside Pattern
- Read from cache first (cache-hit) fail over to database (cache miss)
- Update cache on cache miss
- Write-Through
- Write to cache and database
- Maintain both systems updated
- Improves app performance
- Reduces load on Database
What is Redis?
- Key-value store, keys can contain strings (JSON), hashes, lists, sets, & sorted sets
- Redis supports a set of atomic operations on these data types (available until commited)
- Other features include transactions, publish/subscribe, limited time to live -TTL
- You can use Redis from most of today's programming languages (Libs)
Code Repo
