Improve database performance with connection pooling and load balancing

 🏗️Improve database performance with connection pooling and load balancing 🎢

We tend to rely on caching solutions to improve database performance. Caching frequently-accessed queries in memory or via a database can optimize write/read performance and reduce network latency, especially for heavy-workload applications, such as gaming services and Q&A portals. But you can further improve performance by pooling users' connections to a database.

Client users need to create a connection to a web service before they can perform CRUD operations. Most web services are backed by relational database servers such as Postgres or MySQL. With PostgreSQL, each new connection can take up to 1.3MB in memory. In a production environment where we expect to receive thousands or millions of concurrent connections to the backend service, this can quickly exceed your memory resources (or if you have a scalable cloud, it can get very expensive very quickly).

Because each time a client attempts to access a backend service, it requires OS resources to create, maintain, and close connections to the datastore. This creates a large amount of overhead causing database performance to deteriorate.

Consumers of your service expect fast response times. If that performance deteriorates, it can lead to poor user experiences, revenue losses, and even unscheduled downtime. If you expose your backend service as an API, repeated slowdowns and failures could cause cascading problems and lose you customers.

Instead of opening and closing connections for every request, connection pooling uses a cache of database connections that can be reused when future requests to the database are required. It lets your database scale effectively as the data stored there and the number of clients accessing it grow. Traffic is never constant, so pooling can better manage traffic peaks without causing outages. Your production database shouldn’t be your bottleneck.

Oracle load balancing & Pooling solutions:

Oracle Real application clustering (RAC)

When using RAC you can configure SCAN-IP to achieve load balancing. But cause of some applications behavior and existing limitation, you can not always using Scan ip, so you have to create services to minimize traffic on interconnection, in this case you lost load balancing feature of Rac and use scalability and 

high availability of Rac.

1-Oracle Traffic Director Manager(TDM)

Oracle Traffic Director is a fast, reliable, and scalable layer-7 software load balancer. You can set up Oracle Traffic Director to serve as the reliable entry point for all HTTP, HTTPS and TCP traffic to application servers and web servers in the back end. Depending on the needs of your IT environment, you can configure Oracle Traffic Director to apply multiple, complex rules when distributing requests to the back-end servers and when forwarding responses to clients.

Oracle Traffic Director distributes the requests that it receives from clients to servers in the back end based on the specified load-balancing method, routes the requests based on specified rules, caches frequently accessed data, prioritizes traffic, and controls the quality of service.

On engineered systems platforms, you can set up pairs of Oracle Traffic Director instances and leverage its built-in High Availability capability to setup either Active-Passive or Active-Active failover. As the volume of traffic to your network grows, you can easily scale the environment by reconfiguring Oracle Traffic Director with additional back-end servers to which it can route requests.

Oracle Traffic Director provides the following features:

• Advanced methods for load distribution
  Configure Oracle Traffic Director to distribute client requests to servers in the back-end using one of these methods:
• Round robin
• Least connection count
• Least response time
• Weighted round robin
• Weighted least connection count
• Flexible routing and load control on back-end servers
• Request-based routing
• Content-based routing
• Request rate acceleration
• Connection limiting
  
• Controlling the request load and quality of service
• Request rate limiting
• Quality of service tuning
  
• Support for WebSocket connections
• Integration with Oracle Fusion Middleware
• Easy-to-use administration interfaces
  
• Security
  Oracle Traffic Director enables and enhances security for your IT infrastructure in the following ways:
• Reverse proxy
• Support for TLS 1.0, 1.1, and 1.2
• Web Application Firewall
• HTTP Forward Proxy Support in Origin Server Pools
  
• High availability
  Oracle Traffic Director provides high availability for your enterprise applications and services through the following mechanisms:
• Health checks for the back end
• Backup servers in the back end
• Failover for load balancing
• Dynamic reconfiguration
• Monitoring statistics
  Administrators can monitor a wide range of statistics pertaining to the performance of Oracle Traffic Director instances through several methods: the administration console, the command-line interface, and a report in XML format.
• High performance
• SSL/TLS offloading
• Content caching
• HTTP compression
  

Oracle Traffic Director Terminology

An Oracle Traffic Director configuration is a collection of elements that define the run-time behavior of an Oracle Traffic Director instance. An Oracle Traffic Director configuration contains information about various elements of an Oracle Traffic Director instance such as listeners, origin servers, failover groups, and logs.

2-Orcale Connection Manager (CMAN) 

CMAN is one of the useful software that help you achieve connectivity management easily.

Oracle Connection Manager (CMAN) is a multi-purpose database connection proxy server used in Oracle deployments. CMAN is available as part of the Oracle Database Enterprise Edition (EE) client package.

CMAN in Traffic Director Mode (TDM), or CMAN-TDM, brings in intelligence to manage incoming client connections through session multiplexing and dynamic load balancing that can optimize the usage of database resources.

Session multiplexing can be handled efficiently through the Proxy Resident Connection Pooling (PRCP) feature in CMAN-TDM. When PRCP is enabled on CMAN-TDM, connection pool(s) are created on the CMAN-TDM server that enable mid-tier clients to share connections to the database.

Oracle Database 21c and beyond supports the multi-tenant architecture model with multiple Pluggable Databases (PDBs) and a single Container Database (CDB). Until recently, PRCP pools can be created for each database service. So if a PDB database instance has multiple services, then a PRCP pool is created for each of the services.

Starting from Oracle EE 23c version, CMAN-TDM provides a per-PDB PRCP option as well. With this feature, a PRCP pool will be created at the PDB level. This per-PDB PRCP pool will be shared across multiple services associated with the same PDB.

Sample per-PDB PRCP architecture

To enable per-PDB PRCP, the user has to set the PRCP connection factor (TDM_PERPDB_PRCP_CONNFACTOR) parameter to a non-zero positive value in cman.ora (The configuration file for CMAN).

The per-PDB PRCP feature dynamically configures the PRCP pool, by adjusting the maximum size based on the Oracle Compute Unit (OCPU) — an internal parameter — and the PRCP connection factor parameter at the PDB level. CMAN-TDM periodically checks the OCPU value of each PDB and reconfigures the per-PDB PRCP pool size, if required.
The user can further tune the pool sizing by tweaking the PRCP connection factor to a higher or lower value.

You can also view the per-PDB PRCP performance statistics by setting another cman.ora parameter TDM_STATS_FREQUENCYto a non-zero positive value. This will add the statistics in the newly created V$TDM_STATS dynamic view.

In essence, consolidation of PRCP pools per PDB minimizes the fragmentation of session pools across multiple services and automatically applies the sizing for session pools for the whole PDB in an effective manner, leading to better resource management on CMAN-TDM.

What’s more! you can monitor the connection performance with the per-PDB PRCP statistics available in dynamic views as well! Pretty cool!

For advanced connection pooling and security enhancements you can use F5 applience that provide a hardware load balancing with many security options.

For web applications using weblogic that can offer HA and scalability features and you can use Traffic director manager TDM also.

Conclusion

Database performance can be improved beyond connection pooling. Replication, load balancing, and in-memory caching can contribute to efficient database performance.

If a web service is designed to make a lot of read and write queries to a database, then you have multiple instances of a Postgres database in place to take care of write queries from clients through a load balancer such as pgpool-II while in-memory caching can be used to optimize read queries.

Despite the pgpool-II ability to function as a loader balancer and connection pooler, pgbouncer is the preferred middleware solution for connection pooling because it is easy to set up, not too difficult to manage, and primarily serves as a connection pooler without any other functions.

When use Oracle you have this solution:

Setup oracle RAC with config Scan-ip and using new features such as TAF, FAN, and specially TAC(Transparent Application Continuity).

Another integrated solution is using CMAN.

If thses options can not achieve your load balancing requirements then use F5 appliences that give you hardware load balancing and security options.

For Orcale environment when you are challenging on web applications, use Weblogic or traffic director software to achieve connection pooling, load balancing.

Best Regards,

Alireza Kamrani 

Senior RDBMS Consultant.