This document describes the automatic read/write separation feature of the database proxy service in TencentDB for MySQL and its strengths and routing rules.
Currently, businesses of many users in the production environment have problems such as more reads and less writes and unpredictable business loads. In application scenarios with a large number of read requests, a single instance may not be able to withstand the pressure of read requests, which even may affect the businesses. To implement the auto scaling of read capabilities and mitigate the pressure on the database, you can create one or multiple read-only instances and use them to sustain high numbers of database reads. However, this solution requires that businesses can be transformed to support read/write separation, and the code robustness determines the quality of business read/write separation, which imposes high technical requirements and has low flexibility and scalability.
Therefore, after creating a read-only instance, you can purchase a database proxy, enable read/write separation, and configure the database proxy address in your application so as to automatically forward write requests to the source instance and read requests to the read-only instance. In addition, this method provides natural solutions to other business challenges as detailed below:
Applications with unpredictable workloads
Applications supporting highly variable workloads may try to open new database connections to handle traffic surges. Connection management in the dedicated database proxy allows you to appropriately scale applications that process unpredictable workloads by effectively reusing database connections.
First, this feature allows multiple application connections to share the same database connection to effectively use database resources. Second, it allows you to adjust the number of open database connections to maintain a predictable database performance. Finally, it allows you to delete unusable application requests to guarantee the overall application performance and availability.
Applications frequently opening/closing database connections
Applications built based on technologies such as serverless, PHP, or Ruby on Rails may frequently open and close database connections to process application requests.
The dedicated database proxy can help you maintain a database connection pool to prevent unnecessary pressure on data computing and the memory used to establish new connections.
Applications with open but idle connections
SaaS applications and traditional ecommerce applications may make database connections idle to minimize the response time when users reconnect. You can use the dedicated database proxy to retain idle connections and establish database connections for active requests as needed instead of excessively increasing the threshold or providing database services with higher specifications to support most idle connections.
Applications requiring highly smooth failover
With the dedicated database proxy, you can build applications that can tolerate active and passive database failures in an imperceptible manner without writing complex failure processing code. The dedicated database proxy will automatically route read traffic to new database instances while retaining the application connections.
SELECT FOR UPDATEstatement.
last_insert_id()) and all custom function calls.
KILL(SQL statement, not command)
SELECT) statements outside transactions.