Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve the separation of reading and writing in Oneproxy in mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Basic architecture: all write requests are directed to the main database, data is asynchronously copied to the secondary database through logs, and read requests can be routed to the primary or secondary database according to the situation. The current reading pressure is averagely distributed to three servers, and the performance is improved by about 20-30%.

The middleware connection pool buffers the connection according to the AUTOCOMMIT status of the CONNECTION. If the value of AUTOCOMMIT changes during the process, the previously cached database connection will fail. Leads to a decline in the performance of re-establishing connections. The JDBC transaction control takes the initiative to set the AUTOCOMMIT property of CONNECTION to FALSE (default is TRUE). If transactions are disabled, data integrity is not guaranteed and other errors are raised. Fortunately, the latest version of ONEPROXY has solved this problem (beside the point, it seems that the design idea of MYCAT connection pool is similar, whether there are any holes to be verified when using it)

The connection pool size of ONEPROXY middleware should be adjusted under high concurrency.

The character set of ONEPROXY middleware should be set to UTF8, and the default is GBK. If it is not set correctly, the object serialized BLOB stored in the MYSQL database will be corrupted and cannot be deserialized.

Under the architecture of writing the primary database and reading multiple secondary databases, the most common problem is that the data replication is not timely, resulting in the read request directed to the secondary database not reading the data. ONEPROXY solves this problem through transaction control. If the read and write operations are in the same transaction, then they will be directed to the primary database, so the transaction boundary control is very important, but now the application code is very chaotic. Making it difficult to control transactions, ONEPROXY provides a compromise that specifies routing to the primary library for certain tables, shopping carts, orders, order lines, promotions, payment records, etc. for tables, shopping carts, orders, order lines, promotions, payment records, etc. currently the error rate is basically controlled at zero. We have tried to use the parameter WSREP_CAUSAL_READS of PERCONA to solve the above problems, but to no avail

This is the end of the article on "how to achieve the separation of reading and writing in Oneproxy in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.