Background and Solution
Background
Data shows that 96.87% of relational databases are waiting for read I/O in OLTP services, and only 5.3% are for processor computing. This indicates that to improve the QPS performance of the database, a key point is to enhance the system's I/O capability.
Another data indicates that the majority of service access to databases is read greater than write. Typical examples are e-commerce, O2O, Internet finance, and other services, with a read/write ratio of up to 5:1 or even 10:1.
To enhance I/O capabilities, methods include upgrading hardware and increasing the disk I/O capacity of a single node. Another crucial method is read-write separation. A primary-secondary replication cluster can be deployed, distributing read requests to multiple database nodes for parallel processing. Given that most service access to databases is read-oriented, read-write separation can bring evident enhancements to database performance.
First, parallel reading across multiple nodes can provide several times the disk data reading capacity of a single node;
Second, by distributing read requests across multiple nodes, the I/O of a single node is alleviated, and the I/O waiting time is reduced.
Ultimately, the whole system's I/O capability is effectively improved.
For OLAP services, CPU and memory may become a bottleneck since they involve a large amount of memory storage and computing. Read-write separation also has a certain significance. By distributing data analysis requests to multiple nodes, different data analysis operations can be carried out in parallel on multiple nodes. Nodes do not interfere with each other, fully demonstrating the advantage of parallel processing.
Solution
UDB currently provides a complete read-write separation solution, implemented as follows:
-
On the UDB control panel, create one primary database and multiple secondary databases.
-
Go to the read-write separation page on the UDB control panel and enable read-write separation to create a read-write separation middleware. This middleware acts as a proxy between the business program and the primary-secondary replication cluster, transferring requests to the primary and secondary databases.
-
The read-write separation middleware will recognize the type of business SQL request. If it is a write request, it will be forwarded to the primary database. If it is a read request, it will be distributed to the primary or secondary databases based on a certain distribution rule (the distribution rule can be configured).
-
The read-write separation middleware has an independent IP. To use the read-write separation middleware, customers need to switch their database access address directly to this IP without modifying the business program code. It supports standard SQL, system commands, transactions, views, stored procedures, triggers, and other MySQL functions.
UDB's read-write separation middleware is permanently free. Customers only need to create the primary and secondary databases, and as long as they are running normally, they can activate and use the read-write separation middleware without any additional costs. NVMe models and SSD models MySQL (standard version / high availability version) all support the creation of read-write separation middleware.
