Announcing ProxySQL: Query Offload for Elastic Pools in Oracle Autonomous Database
Businesses today often need cost-effective ways to isolate workloads, particularly separating high-volume transactions from analytical reporting. Handling these diverse workloads efficiently ensures consistent application performance, essential for business-critical operations such as financial or operational reporting. Oracle Autonomous Database’s new capability, ProxySQL, dramatically simplifies workload isolation, ensuring consistent and optimized query performance.
Why Businesses Need Cost-Effective Workload Isolation and Optimization?
Autoscale is a valuable capability for automatically adjusting compute resources based on workload demands. However, certain business scenarios require specialized solutions to ensure optimal performance, particularly for environments mixing intensive read (analytics) and write (transactions) workloads. ProxySQL provides targeted workload isolation and optimization, complementing Autoscale without negatively impacting its benefits.
Challenges ProxySQL Addresses:
· Efficiently Scaling Read-Intensive Workloads: Growing applications require efficient handling of analytical queries to avoid impacting transactional operations.
· Managing Peak Query Traffic: Short-term spikes, such as quarterly or annual reporting demands, require a solution beyond just scaling up temporarily, which can be costly.
· Ensuring Consistent Read/Write Performance: Write-heavy operations can degrade read performance. Separating reads and writes helps maintain optimal performance for both.
What Challenges Does ProxySQL Address?
ProxySQL addresses several challenges associated with performance of your applications that use Elastic Pools:
- Keeping consistent performance for both reads and writes: Heavy write operations can interfere with read performance. You may need to manage writes separately from reads to maintain optimal performance for both.
- Scaling out read-intensive application workloads: As your applications grow, dependent database workloads become more demanding. You need to efficiently handle analytical queries so the business operations are not interrupted.
- Managing peak workloads: Your businesses may experience short but intense spikes in query traffic, such as at the end of the quarter or fiscal year. You need a different solution than scaling up, as doing so for limited periods is costly and inefficient.
What ProxySQL Is and Why You Should Use It
So how does ProxySQL work? It dynamically routes read queries on an Elastic Pool’s Pool member to a clone of the database using Autonomous Database’s Refreshable Clones technology (refered as read replica going forward), enabling consistent query performance even during surges.
ProxySQL has the following key capabilities:
- Dynamic add and removal of clones: If additional query demands occur, you can create additional read replica, then deactivate them when traffic normalizes. This on-demand flexibility keeps costs in check without compromising performance.
- Session-Based Sticky Offload: When enabled, ProxySQL automatically redirects read queries to available rread replica, dynamically scaling as needed. Queries within the same session stick to a specific clone, improving cache efficiency and reducing unnecessary computation.
- One-to-One Service Mapping: Queries retain the same service level (e.g., MEDIUM, HIGH) across clones.
- Flexible CPU Allocation: The CPU configuration of read replica can be different from the Pool member, optimizing costs and performance based on workload needs.
ProxySQL is especially advantageous for applications that need to handle high-volume transactions and analytical queries, because maintaining performance efficiency and consistency for both is crucial. By using ProxySQL, you can separate read and write operations, achieving the data modifications happen consistently without slowing down reporting workloads. This separation is especially beneficial for businesses that generate large volumes of financial or operational reports. Figure 1 shows how ProxySQL offloads query workloads to read replica:
Figure 1: Example CPU utilization of a Pool member and refreshable clones
Getting Started with ProxySQL
Getting started with ProxySQL is extremely easy: with a single command, you can enable query offload for all sessions for the Pool member, allowing seamless scaling and performance optimization with minimal setup.
To enable query offload, simply run:
SQL> EXEC DBMS_PROXY_SQL.ENABLE_READ_ONLY_OFFLOAD;
That’s it! You’re now ready to take advantage of ProxySQL that lets you scale applications when needed, maintain performance stability, and optimize infrastructure costs, in addition to the benefits you gain from pooling your databases using Elastic Pools.
For more details, check out the official documentation.