Onesql Wing FAI case

Database cluster query reached 10w/s, update operation 5K/S, normal!
Application concurrent connection reached historical peak 4 times times, normal!
Business smooth operation, Normal!

Yong-hui Micro Shop 527 Big Promotion, complete success! This marks a few months of tuning work by civilian software database engineers, Accenture Consulting practitioners, SAP support staff, wing FAI Research and other staff, which has allowed the system capacity of the Yonghui Micro store to rise to a new height.
Yong-Hui micro-store is a typical e-commerce, its products are from the store of Yonghui supermarket, and the price of fresh products with different stores, dates, different preferential dynamics and every few hours will change. Therefore, the complexity of single orders is more complex than the general traditional e-commerce.
527 early stage, according to multi-staff assessment, the system capacity can not meet the promotion day demand, one of the core bottleneck is that the database can not bear the high concurrency of the application system pressure. At this time, Yong-Hui supermarket through the sky-wing technology (Hangzhou civilian Software parent company) invited us to help solve the data layer problem.
Civilian software has the industry's best-in-class database experts, and its tuning practices in the data layer are very rich. Data-tier tuning includes, but is not limited to, the following sections
1. Parameter tuning
2. SQL optimization
3, Hardware resources (CPU, memory, disk, network) performance bottleneck location
4. Rational diagnosis of interaction between application layer and data layer

Following the identification and resolution of the above four issues,

First, using the official version of MySQL5.6, the system's CPU has been found at 100%, where the kernel state accounts for more than 80% of the CPU. Considering the high concurrency of the system, too many connections will cause MySQL to create too many threads, resulting in the operating system of the database system, frequent context switching, resulting in low system performance.

Second, we tested the Percona5.6 with thread pool functionality. The test verifies that the CPU utilization can be reduced by setting the number of threads, but the corresponding TPS is also significantly reduced. Increasing the number of threads will be the same as the official version of MySQL5.6, with a CPU share of nearly 100%.

Finally, by analyzing the performance degradation of the database system in the case of large concurrency, we decided to test the product onesql of the civilian software. After the test, the effect is fully satisfied with the customer's expectations, so only the article at the beginning of the 527 major promotion success.

Why does Onesql have such excellent performance in large concurrent e-commerce systems? I hope the following introduction will make you understand how he works.

Onesql originated in e-commerce, is currently the best for the e-commerce platform to build a MySQL database branch. Onesql's exclusive multi-queue thread pool Escorts your e-commerce business, making your business system more than 10 times times more capable.

Why can a multi-queue thread pool have this effect? Today I try to explain it in a popular way. We liken the database to a four-lane highway. The load on the database is likened to a variety of vehicles

1, the big truck runs slowly, corresponds to the big table statistic query in the database, the correlation query
2, the car run fast, corresponding to the automatic commit transaction in the database
3, the bus speed between the big truck and the car, corresponding to the explicit transaction in the database
4, the fastest motorcycle speed, corresponding to the simple query in the database

In the e-commerce system, the line of business is usually composed of cars, buses and motorcycles corresponding to the query, some background statistics business is usually a large truck corresponding query. In order to smooth the highway, prevent the big trucks occupy all the lanes, affect other types of vehicle operation, the different types of vehicles must be placed in different lanes.

Onesql borrowed the same idea, put different types of SQL into different queues, combined with queuing thinking, so that the performance of the database system in the e-commerce environment, even more than 10 times times the promotion.

In addition, Onesql in the second Kill, log asynchronous refresh, master-Slave synchronization speed improvement has been optimized, if interested please visit www.onexsoft.com

If you have any questions, please contact onesoft007

Onesql Wing FAI case