Overview

CAST AI DB Optimizer proxies and caches database queries using native database wire protocols. It connects to your current database transparently and requires no code or configuration; simply update your database connection string to route database traffic through the Proxy.

Queries are inspected in real-time and reads (SQL SELECT and SHOW) are cached and served sub-millisecond from the cache, no matter the query complexity. All other traffic (INSERT, UPDATE, DELETE, etc.) seamlessly passes through to the origin database, automatically invalidating any changed data from the cache in real time. (Smart Invalidation).

Architecture

CAST AI DB Optimizer consists of two components that are hosted inside of your architecture. These are collectively referred to as the Cache:

• Proxy - A high-performance, low-latency SQL proxy that caches data and proxies queries to the origin database.
• Query Processor - An advanced, AI-driven cache intelligence engine. The Query Processor communicates in real-time with the Proxy to determine what to cache and how long to cache it for. It is also responsible for invalidating the cache in real time based on observed Data Manipulation Language (DML) queries.

Get started with Quick start guide.

Cached Data

DB Optimizer caches database responses, i.e., the payloads returned from the origin database when a query is executed. These are stored in-memory (and disk via memory-mapped files) within the Proxy.

Caching Protocol

AI-driven

DB Optimizer uses Artificial Intelligence to automate caching behavior. Caching operates at the level of unique SQL queries. Typically, an application generates thousands of unique queries with complex temporal dynamics and inter-dependencies. Managing the caching of these queries requires automation, and optimizing it requires machine learning.

Automated Caching

DB Optimizer's machine learning algorithms identify caching opportunities by recognizing and remembering patterns in query traffic. The algorithms run continuously in the background and update on every query, rapidly adapting to changes in traffic as they arise. At any moment, the most up-to-date optimal cache configurations are applied. The algorithms manage caching at a scale far beyond the abilities of a human.

Time To First Hit (TTFH)

The time to first hit is determined by multiple attributes of the particular query being processed: the arrival rate, the change rate of the query result, and the number of related queries seen, among other things. If the cache is automatically managed (default behavior) and the query being processed is new, i.e., it has never been seen, and no related queries have been seen by DB Optimizer previously, a cache hit will occur on or about the 3rd query. This will be reduced to as low as the 2nd query if the query being processed is similar to a previously processed query, i.e., the same query structure with different parameters.

Manual Caching

Sometimes, the developer knows best. It is always possible to override automated caching with manual caching rules. Time-to-live (TTL) values can be configured for query templates that span queries with a common structure or entire tables. This gives fine-grained control over what is cached and for how long.

Currently, manual caching is only accessible via our API.

Cache Invalidation

To guarantee the cache is invalidated accurately, DB Optimizer provides multiple layers of cache invalidation:

Smart Invalidation

Smart Invalidation is DB Optimizer's automated cache invalidation system. It is designed to provide 100% accurate, global eventual consistency for cached data.

At a high level, Smart Invalidation automatically detects and calculates when cached data may be stale and removes it from the cache.

Smart Invalidation has several, out of the box advantages:

• Performance - Smart Invalidation operates in real-time and invalidates the cache when a change/mutation query is detected. This can quite often mean that the Proxy & Query Processor, where a change has been detected, invalidates the cache, even before the change query has been executed on the origin database.
• Highly scalable- unlike other caching systems that require access to the Write Ahead Log (WAL) or similar, DB Optimizer inspects the wire protocol traffic to drive Smart Invalidations. Hence, an unlimited number of Proxies can be supported without degradation to the origin database.
• 100% automatic - no code or configuration is required to be developed, and any type of workload is supported.
• Global eventual consistency - Smart Invalidation is designed to provide 100% accurate, global eventual consistency for cached data.

Smart Invalidation achieves this through a multi-layer approach. The first layer involves parsing the SQL queries at a row level. It analyzes which rows are read (in the case of read queries) and which rows are written to (in the case of write queries). This analysis is performed on fully anonymized representations of the queries to maintain data security and privacy. From these query details, targeted invalidations of cached data are performed.

The analysis performed by the first layer can sometimes be overwhelmed by complex queries. That is where the second layer of invalidation comes into play. A fallback to a simple but effective table-level invalidation occurs when a query is deemed too complex to determine what specific cached data may have become invalidated. In this case, all cached data coming from any affected tables is invalidated. This layer of invalidation errs on the side of caution, typically overreaching but ensuring stale cache data is not served.

The first two layers of invalidation are very effective. However, they can be thwarted in some circumstances. For example, out-of-band data changes are not visible to DB Optimizer or tightly coupled (in time) reads and writes. To address this, a third layer of invalidation exists within the cache automation itself. The automated caching layer monitors for and detects unexplained data changes. If these events are detected, it disables caching on the relevant queries. This provides an empirical safety net for the predictive actions of the first two layers.

CDC-Based Invalidation

For use cases where Smart Invalidation cannot be effective, i.e., where out-of-band changes occur on the database, DB Optimizer can be configured to use Change Data Capture (CDC) to invalidate cached data.

Storage Efficiency

The aforementioned layers invalidate entries because it is believed the underlying data has changed. Another layer of invalidation occurs for cache data that is believed to be superfluous. It is an empirical fact that most query results written to the cache will never be served as cache hits. For a cache that sees one million distinct queries daily, perhaps only one hundred thousand will have cache hits. Removing the entries from the cache that will not be needed is an important part of an efficient cache implementation. DB Optimizer uses automated machine learning algorithms (specifically, non-homogeneous Poisson process modeling) to eliminate the build-up of never-to-be-used data.

DB Optimizer Smart Invalidation provides global eventual consistency through a multi-layered approach that resolves in a few tens of milliseconds.

Multi-cache Invalidations

Smart Invalidations can be configured to trigger across two or more caches. Simply put, any invalidations to the data on cache A are repeated for cache B and vice versa.

This is useful for use cases where multiple cache configurations are required to connect to the same database. For example, an application may choose to configure one cache to have an admin interface or schema migrations for writes with uncached reads and the other to be an application channel with just cached reads. Still, changes on either must invalidate each other.

Each workspace supports a single invalidation group, and any cache within a workspace can be part of it.

Multi-cache invalidation can be configured within the user interface within each cache's Smart Invalidation section.

Cache Revalidation

🚧

Feature currently under development

This feature is under active development. Please contact us if you wish to learn more.

Cache Revalidation increases the cache hit rate and puts an upper bound on time for eventual consistency. To maximize hit rates and minimize serving potentially stale data, DB Optimizer will periodically pass through a "hit" query to the origin database. While a response is served immediately from the cache, in the background, DB Optimizer receives the response from the database and updates the cache.

If the response payload has not changed, the current TTL clock will be restarted, effectively lengthening the TTL and increasing the cache hit rate.

If the response payload has changed, a subsequent request (within the TTL window) will be served from the now-updated cache, and the future TTL will be downwardly adjusted to account for the unexplained change in the payload. This feature ensures that even if DB Optimizer is not seeing the underlying updates to your database, the amount of stale data served is limited.

In short, Cache Revalidation increases the cache hit rate. It puts an upper bound on time for eventual consistency in use cases where DB Optimizer cannot access invalidation data. It also benefits users who are interested in maximizing hit rates and are unconcerned about potentially serving a limited amount of stale data—this can be done by turning off Smart Invalidation and relying on Cache Revalidation to catch changes in the query response.

Traffic Shaping

DB Optimizer will perform various optimizations around connectivity and query behavior to reduce overall latency and increase performance. For example, eliding repetitive USE <database> or SET <setting> commands.

Database and Cache Permissions

A DB Optimizer cache identifies a single server, i.e., a server host. DB Optimizer guarantees a unique cache based on a combination of the database name and database user. This means that database and user-level cache uniqueness is guaranteed.

Transactions

DB Optimizer currently does not support transactions (does not distinguish between queries within a transaction and not).

Read-after-write Semantics

It is worth noting that when the Query Processor component detects a DML query (INSERT, UPDATE, DELETE), it will ignore subsequent reads for a brief period of time thereafter. These subsequent reads then pass through the cache directly to the database to help guarantee immediate consistency after writing.

Connection Pooling

Coming soon!