Sharded Database Mid Tier Routing
Currently mid-tier connection pools route database requests to specific shards. This leads to a situation where each mid-tier connection pool establishes connections to each shard. This will create too many connections to the database. This problem can be solved by affinitizing application mid-tiers with shards. In addition to reducing the number of DB connections, such affinity improves mid-tier cache locality and, for geographically distributed shards, eliminates chatty mid-tier-to-database connections across data centers.
In such a scenario, it is desirable to have a dedicated mid-tier comprised of a pool of web server(s) or application server(s) for each customer application, and to have client requests routed directly to the appropriate application mid-tier which is connected to the shard(s) containing the client data, identified by client shard key(s).
This kind of a setup is commonly referred to as “Swim Lanes”, where each swim lane is a dedicated stack, from web server(s) and/or application server(s) all the way to the database shard(s).
We propose Mid-Tier routing swimlane solutions and their architectures to solve the above mentioned problem. The solution in a nutshell is as follows : An application client provides the sharding key along with the user request to retrieve pertaining user data from the sharded database. Now the API Gateway or the Web server(s) depending upon the type of swimlane architecture selected (described below) requests the Mid-tier routing services for its swim lane details by passing the sharding Key. The mid-tier routing component retrieves the routing data including shardName(s) from the shard catalog via UCP or from the UCP routing cache if previously retrieved, and then resolves those shard name(s) into application defined swim lane details from the swim lane cache, which can be either internal or external. The swimLane details returned as part of the response by the Mid-tier routing services, is used to route the user request to the appropriate mid-tier which contains the user application services as well as the user data.
- Provides Geo-affinity for application mid-tiers and their shards for either regulation purposes (for example : GDPR) or for applications that have low latency requirements.
- Applications using Mid-tier routing services to identify swimlanes for its application instances or components, benefit from faster response time to user requests, as the user requests get routed to the appropriate application instance & shard where the user's data resides.
- Helps in reducing the number of DB connections.
- Can be used as part of authentication & authorization workflows which helps isolate & secure customer application stacks and data.
- Aids in uniquely identifying a user request across different application instances or application swim lanes in a distributed application, which helps in application monitoring & troubleshooting.
- Can be used to protect against DDos attacks on a customer application by rejecting user requests and protecting downstream application resources if the shardKey is invalid or doesn't resolve to a valid swim lane.
There can be many types of swim lane architectures possible based on the application design. In this article, we present two of the most commonly used application architecture design pattern at a high level of abstraction, which suggests, as to where one might use the SDB Mid-Tier routing services in an application stack that wants to use or already uses Oracle Sharded Database and wants to design / re-design the application based on a swim lane architecture to achieve the above mentioned benefits.
In Swim Lane option 1, the App UI on the web server communicates with the SDB-Mid Tier Routing services by requesting the swim lane details (name, url, shardName) by passing the sharding key. The mid-tier routing component retrieves the routing data including shardName(s) from the shard catalog via UCP and then resolves those into application defined swim lane details from the routing cache which can be either internal (local cache) or external (redis). The swimLane details returned in response by the Mid-tier routing services, is used by the UI running on the web server(s) either directly route the user request to the appropriate mid-tier which contains the user application services as well as the user data OR to fetch the data from the appropriate mid-tier which has access to the shards on which the user data resides. Thus in swim lane option 1 architecture, the swim lane is comprised of app server(s) all the way down to the database shard(s).
In Swim Lane option 2, an API gateway communicates with the SDB-Mid Tier Routing services by requesting the swim lane details (name, url, shardName) by providing the shardKey.
The mid-tier routing component retrieves the routing data including shardName(s) from the shard catalog via UCP and then resolves those into application defined swim lane details from the routing cache which can be either internal (local cache) or external (redis).The swimLane details returned in response by the Mid-tier routing services, is used by an API Gateway to either directly route the user request to the appropriate mid-tier which contains the user application web services as well as the user data OR to fetch the data from the appropriate mid-tier which has access to the shards on which the user data resides.
Thus in swim lane option 2 architecture, the swim lane is comprised of web server(s) all the way down to the database shard(s).
The shardNames and swimLane information retrieved by the Mid-Tier routing services are always current as ONS subscriptions are used to keep the mid-tier routing UCP cache updated with any control plane events (for example : HA Failover/ Failback) to data plane events (for example : Chunk move/split between shards).
As the swim lanes mappings from shardName to swimLane details are defined by the application administrator and is application domain dependent, for shard replicas, including Master-Master setups like GoldenGate bi-directional replication and Primary-Standby setups like DataGuard based replicas, we recommend using the swim lane name for all shard replicas (list of shard names resolved from a shard key), as this will be easy to manage and provides for transparent switchovers of swimlanes during disaster recovery scenarios including Site-Wide, same data center (HA Pairs), as well as zonal and regional disaster recovery scenarios.
-
SDB Mid Tier Routing Services : Refer this article to install and consume SDB Mid-Tier routing services.
-
SDB Mid-Tier Routing Demo App : Refer this article to install a demo application (Invoice Application) that uses Mid-Tier Routing as part of its application architecture.
-
Scaling Mid-Tier routing services : Refer this article to install and consume SDB Mid-Tier routing services at scale.