Shulou(Shulou.com)06/02 Report--

With the growth of the number of users, the pressure on the database is also increasing exponentially. In the face of high traffic and high concurrency, UCloud MongoDB is efficient and shows a better performance experience.

-- excellent volunteer CTO Zhang Haipeng

Background

The period from late June to late August is the peak period for college entrance examination volunteers, and it is also the peak period for the back-end of excellent volunteers to face large traffic and high concurrency requests. The Outstanding Volunteer APP aims to provide professional and independent enrollment, voluntary application, study abroad, travel and other further study planning services for 30 million high school students. Through four years of continuous technological innovation and research and development, with patent-level core algorithms such as the Provincial Control Line difference method, it has served more than 4 million candidates, achieving an admission success rate of 99.2% and a maximum waste score of only 6 points. In order to provide services for so many candidates, it is not easy to maintain stability in the face of hundreds of millions of searches.

Cdn.xitu.io/2019/8/23/16cbd46be1cb5656?w=202&h=88&f=jpeg&s=4121 ">

The pain point faced by

Youyi was originally based on the physical machine self-built MSSQL (Microsoft SQL Sever) database, in the face of high traffic and high concurrency business peak, exposed the following problems:

1. Unable to keep up with the pace of flexible expansion of business

Self-built MSSQL is very cumbersome in doing read-write separation, AlwaysOn configuration, and configuring domain name accounts for each windows client. With the growth of business traffic, servers and application configurations need to be increased irregularly, and the whole cycle from hardware procurement, machine deployment to application configuration is also long.

two。 Performance encounters bottleneck, DBA consumes time and effort

In addition, because Youyi's business scenarios are mainly based on query and index operations, with a read-write ratio of about 7:3, the retrieval speed of the previously self-built MSSQL dropped significantly when the data reached tens of millions of items, so the index catalogue needs to be updated in a timely manner, which not only affects the examinee's user experience, but also consumes a lot of DBA manpower costs.

After some research and comparison, you volunteer chose to use UCloud MongoDB products to solve the above problems.

Why choose UCloud MongoDB?

UCloud MongoDB is a highly available and high-performance database service based on mature cloud computing technology, which is seamlessly compatible with the open source version of MongoDB. In addition to the high-availability master-slave architecture at the bottom, it also provides configuration nodes and routing node functions that meet data sharding scenarios. At the same time, it provides a full range of solutions such as disaster recovery, backup, monitoring and so on.

(figure: you can choose to create a MongoDB instance in the UDB product interface of the UCloud console)

● creates replica set with one click to achieve read-write separation

By default, UCloud MongoDB builds a replica set of three nodes: Primary node + one Secondary node + one Arbiter node. The create node operation through the replica set supports the extension of a replica set with more nodes (for example, five nodes, seven nodes, or more nodes). For scenarios with excellent voluntary reading and writing less and their business peak, users can add and delete Secondary nodes as needed to better achieve the expansion of read performance. The use of UCloud MongoDB replica set read-write separation to solve most of the read requests, even if the data reaches hundreds of millions of items, the retrieval speed is still very fast. The replica set architecture is shown in the figure:

● flexible and highly available cluster architecture

In order to solve the performance bottleneck caused by the increase in the amount of data, MongoDB also supports sharding clusters, that is, multiple shards can form a cluster to provide services. Sharding cluster (MongoDB3.4 or above) is composed of Config Server triplicate + N Mongos + N shard data shards. The MongoDB cluster sets sharding rules, and the corresponding data operation requests can be automatically forwarded to the corresponding sharding machine through mongos operation of the database. MongoDB uses "sharding" to quickly build a highly available and scalable distributed cluster, with the characteristics of large amount of data, high scalability, high performance, flexible data model, high availability and so on. The sharding cluster architecture is shown in the figure:

Flexible expansion and reduction of ● to reduce cost

Users can flexibly expand MongoDB resources according to the actual business traffic to meet the needs of database performance and storage space in different business stages. The required database resources can be opened immediately at the peak of the business, and there is no need to purchase high-cost hardware at the initial stage of the business, so as to effectively reduce the hardware cost. It can be deleted and released at any time during the business trough to avoid idle waste of database resources. As you volunteer CTO Zhang Haipeng said: "after using UCloud MongoDB database, operation and maintenance costs have been saved by 80%, and hardware procurement costs have been reduced by 55%."

Query index optimization to break the performance bottleneck

Catton phenomenon

On the eve of this year's college entrance examination, on the first day after the launch of the new version of Youyi's business, users reported that their APP login interface was very stuttered. After operation and maintenance investigation, it was found that the bottleneck appeared in the database layer, and invited the UCloud cloud database team to intervene to solve the relevant performance problems.

The team first looked at some common performance metrics that caused stutters in MongoDB and found that most of them were normal. With the further investigation, it is found that the locking ratio of the instance's global write lock is unusually high. This is shown in the following figure.

The percentage of locking in the figure reaches 100%, which means that all global write operations are stuck, a large number of requests are queued for locking, and the performance degrades sharply.

Sentence optimization

The UCloud cloud database team analyzed in detail the overall situation of all the slow queries of the users at that time, as well as the query plans of the relevant slow queries, and combined with the index of the users. After analysis, the team first found that some indexes in the user's related tables could be optimized to improve the efficiency of the query, so the relevant indexes were added for the user. After adding the index, the team combined with the monitoring indicators and found that the situation had improved, but still did not fundamentally solve the problem.

The team then carefully analyzed the typical query statements that took the longest time for the user and simulated the execution to view the execution plan. During execution, the team found that although the relevant collection had been indexed, the optimal index was not used in the user's query execution plan. After further analysis, the team found that the slowest statements of users are basically aggregate operations. Combined with the abnormal performance indicators of locking, the team carefully examined the contents of users' specific aggregate statements. It turns out that the grouping function ($group) is not used in these time-consuming aggregate operations. In this way, the same effect can be achieved through the most common find operation of MongoDB. For example, the original statement:

Db.this_collection.aggregate ([{$match: {UserId: 12345}}, {$match: {$or: [{FieldA: 1}, {FieldB: 2}]}, {$sort: {_ id:-1}}, {$skip: 0}, {$limit: 1}])

What this statement does is actually look for document records in the this_collection collection that have UserId = 12345 and also satisfy FieldA = 1 or FieldB = 2. After finding the record, the _ id of all the results are arranged in reverse order, and the first result is taken out. The team uses the following find statement to achieve the same effect. The statement after the transformation:

Db.this_collection.find ({UserId: 12345, {$or: [{FieldA: 1}, {FieldB: 2}]}}) .sort ({_ id:-1}) .skip (0) .limit (1)

MongoDB's find () has a higher degree of query optimization, and there is an added benefit of using the find () operation, that is, the user can attach the hint () operation at the end of find (), which can force the index to be used, resulting in a significant improvement in query performance. For example, in the above example, if there is an index on UserId in the this_collection collection, we can directly force the query to use this index. The specific statement can be:

Db.this_collection.find ({UserId: 12345, {$or: [{FieldA: 1}, {FieldB: 2}]}}) .sort ({_ id:-1}) .skip (0) .limit (1) .hint ({UserId: 1})

The team tried to modify one of the typical time-consuming statements and executed and viewed the modified execution plan in the simulated environment. The result is pleasantly surprised to find that even without using the hint operation, the find () operation has been able to use what we think is the optimal index, and the modified query performance has been greatly improved.

That night, the UCloud cloud database team immediately helped users modify all the query statements that could be modified in their slowest query, and synchronized the modification suggestions to users. After the user's R & D received the modification suggestion that night, the code was urgently modified and released.

Bottleneck relieved

On the second day after the release, users can see through the monitoring interface that slow queries are significantly reduced, locking queues are significantly reduced, database load is also significantly reduced, and the whole system can provide services more efficiently. The modified monitoring chart is as follows:

As you can see, after the transformation, the percentage of locking waiting for global write locks is greatly reduced, and the slow query is also greatly reduced. The transformation has achieved very good results, and there is no performance bottleneck at the DB level in the later stage of the user's APP.

Prometheus omni-directional real-time monitoring and alarm

In order to help users provide more real-time monitoring of the market, UCloud has built a Prometheus monitoring system in the background, which lists all the key performance indicators of all database instances. Compared with the ordinary monitoring platform, with the help of Prometheus, you can see the current situation of all database instances more clearly, grasp the current status of the database, and better find the running trend of the database.

For example, the number of connections:

For each instance, provide more detailed monitoring. For example, the status of WTcache:

Locked state:

The combination of the monitoring system and the alarm system can alarm directly when it is greater than the threshold, which is conducive to the early detection of the problem. With the Prometheus built by UCloud, users do not need to build their own monitoring system, nor do they need to care about the underlying data storage, data display, system operation and maintenance and other issues.

Write at the end

UCloud cloud database UDB has been keeping pace with the open source community since it was put into commercial use in 2013. At present, it widely supports mainstream database types in the industry, such as MySQL, PostgreSQL and SQLServer, in addition to MongoDB. On the basis of open source, the UCloud database team also developed a distributed architecture, read-write separation, storage and computing separation and other features. At the same time, in order to be closer to the user's scenario, the UCloud database team will launch a new Cloud DBA product in the near future, which provides functions such as Prometheus monitoring portal, interface management inspection and SQL analysis in the console to improve the efficiency of DBA operation and maintenance in a more intelligent way.

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.