Server-side high-performance database optimization Evolution details case

Server-side high-performance database optimization Evolution details case

Outline

• Business Scenarios

• The initial technology selection

• Read/write separation

• Vertical Sub-Library

• Horizontal sub-Library (sharding)

• Summarize

Business Scenarios

* * is the country's leading final three km logistics distribution platform. * * The business model is similar to Didi and Uber, using the social idle human resources in crowdsourcing to solve the problem of instant distribution of the last three km. * * Business mainly consists of two parts: merchant billing, distribution, delivery, as shown in the order.

* * Business growth is enormous, in 1 years or so from zero growth to nearly million a day, to the back end of the tremendous access pressure. There are two main types of pressure: reading pressure and writing pressure. Reading pressure from the distribution staff in the app to grab a single, high-frequency refresh query around the order, daily visits hundreds of millions of times, peak QPS up to thousands of times/second. Write pressure from the merchant billing, * * orders, pick up, complete and other operations. * * Business reading pressure is much greater than write pressure, read request volume is about 30 times times the volume of write requests.

* * * Last 6 months, daily visits and changes in the QPS trend map change trends, visible growth is very fast

We need to be fully prepared to meet the challenges of business as we grow our business with increasing demands on technology. Next, let's look at how the background architecture of * * evolves.

The initial technology selection

As a start-up company, the most important point is the agile, rapid implementation of products, external services, so we chose the public cloud services, to ensure rapid implementation and scalability, saving the self-built room and other time. In the technology selection, in order to quickly respond to business needs, the business system uses Python as the development language, the database uses MySQL. As shown, several large systems of the application tier access a database.

Read/write separation

With the development of the business, the rapid growth of traffic, the above-mentioned solution will soon not meet the performance requirements. The response time for each request is getting longer, such as when the distribution clerk refreshes the surrounding order in the app, and the response time increases from the initial 500 milliseconds to more than 2 seconds. During peak business hours, the system has even experienced outages, and some businesses and distributors even doubt the quality of our services. At this critical juncture of life and death, we discovered that Gao Yan MySQL CPU usage is close to 80%, disk IO usage is approaching 90%,slow query from 100 to 10,000 per day, and it is more severe than one day. The database has become a bottleneck, and we have to do a quick architecture upgrade.
The following is the database week of the QPS change graph, visible database pressure growth extremely fast.

When there is a performance bottleneck in the Web application service, because the service itself is stateless (stateless), we can solve it by adding the horizontal extension of the machine. The database obviously cannot be extended by simply adding machines, so we take the MySQL master-slave synchronization and the application service-side read-write separation scheme.

MySQL supports master-slave synchronization, in real-time copying the main library's data incrementally to the slave library, and a master library can connect multiple slave libraries (detail reference replication). Using this feature, we make a read-write judgment on each request at the application server, and if the request is written, all the DB operations within the request are sent to the main library, and if the request is read, all the DB operations within the request are sent to the slave library, as shown in.

After the read and write separation, the database pressure is reduced a lot, CPU usage and IO usage are down to 5%, and Slow query is approaching 0. Master-Slave synchronization, read and write separation to us mainly brings the following two benefits:

• reduced the main library (write) Pressure: * * Business mainly from the read operation, do read and write separation, read pressure transferred to the library, the main library pressure decreased by dozens of times times.

• From the library (read) can be horizontally extended (plus from the library machine): Because the system pressure is mainly read requests, and can be horizontally extended from the library, when the pressure from the library too, can be directly added from the library machine, to alleviate the read request pressure

The following is an optimized database of the QPS changes diagram:
Read and write select QPS for the main library before and after separation

Select QPS for read-write separation of the library

Of course, none of the solutions are omnipotent. The Read and write separation, temporarily solves the MySQL pressure problem, but also brings the new challenge. During peak business hours, when the merchant finishes the order, the order is not visible in my order list (typical read after write), and occasionally there are some exceptions that cannot be queried for data within the system. Through monitoring, we found that during peak business hours MySQL may have a master-slave delay, in extreme cases, with a master-slave delay of up to 10 seconds.

How to monitor the master-slave synchronization status? From the library machine, perform show slave status, view the Seconds_behind_master value, representing the master-slave synchronization from the library behind the main library time, in seconds, if the master-slave synchronization without delay, this value is 0. One important reason for MySQL master-slave delay is that master-slave replication is single-threaded serial execution.

How to avoid or solve the master-slave delay? We have done some optimizations as follows:

• Optimize MySQL parameters, such as increasing innodb_buffer_pool_size, allowing more operations to be done in MySQL memory, reducing disk operations.

• Using high-performance CPU hosts

• Database uses physical hosts to avoid using virtual cloud hosts to improve IO performance

• Use SSD disks to improve IO performance. The SSD's random IO performance is about 10 times times that of a SATA drive.

• Business code optimization, some operations that require high real-time performance, read operations using the main library

Vertical Sub-Library

Read-write separation is a good solution to the problem of reading pressure, each reading pressure increases, can be added from the library to scale horizontally. However, the pressure of writing operations with the growth of the business has not been very effective mitigation methods, such as the slower the merchant bills, seriously affecting the use of the business experience. We monitor that database write operations are getting slower, a normal insert operation, and may even execute more than 1 seconds.

Is the database main library pressure, visible disk IO utilization is very high, peak IO response time of up to 636 milliseconds, IO utilization of up to 100%.

At the same time, businesses are becoming more complex, with multiple applications using the same database, and a small non-core feature slow query that often affects other core business functions on the main library. We have an application to log logs in MySQL, the log volume is very large, nearly 100 million lines of records, and this table ID is the UUID, one day peak, the whole system suddenly slowed down, which caused the outage. Monitoring found that this table insert is very slow, slow down the entire MySQL Master, and then dragged across the system. (Logging in MySQL is not a good design, of course, so we've developed a big data log system, so stay tuned for a follow-up article on this blog.) On the other hand, the UUID key is a bad choice, and in the horizontal sub-library below, there is a more in-depth narration of the ID generation.
At this point, the main library becomes a performance bottleneck, and we realize that it is necessary to do the schema upgrade again, split the main library, on the one hand to improve performance, on the other hand, reduce the interaction between the system to improve system stability. This time, we split the system vertically by business. As shown, the initial large database is split into different business databases by business, and each system accesses only the corresponding business database, avoiding or reducing cross-library access.

Is the pressure of the database main library after the vertical split, the visible disk IO utilization has been reduced a lot, peak IO response time is within 2.33 milliseconds, the IO utilization is up to 22.8%.

The future is beautiful, the road is tortuous. Vertical sub-Library process, we also encounter a number of challenges, the biggest challenge is: not cross-Library join, but also need to refactor the existing code. Library, you can simply use the Join association table query, after the library, the split database on different instances, you cannot cross the library to use the join. For example, in the CRM system, you need to search through the merchant name All orders for a merchant, before the vertical sub-Library, you can join the Merchant and order table to do the query, as shown below:

SELECT * from Tb_order where supplier_id in (select ID from supplier where name= ' Shanghai Undersea Fishing ');

After the library, you want to refactor the code, first check the merchant ID through the merchant name, and then through the Merchant ID Query Order table, as follows:

Supplier_ids = Select id from supplier where name= ' Shanghai undersea Fishing '
SELECT * from Tb_order where supplier_id in (supplier_ids)

The lessons learned from the vertical library process have led us to develop SQL best practices, one of which is to disable or less join in the program, and to assemble the data in the program to make SQL easier. On the one hand, to prepare for further vertical split business, on the other hand, it also avoids the low performance of join in MySQL.

After one weeks of intensive infrastructure tuning and business code refactoring, the vertical split of the database was finally completed. After splitting, each application only accesses the corresponding database, on the one hand the single-point database is split into multiple, the allocation of the main library write pressure, on the other hand, the separation of the database independent, to achieve business isolation, no longer affect each other.

Horizontal sub-Library (sharding)

Read-write separation, by extending from the library level, to solve the reading pressure; the vertical sub-library caches write pressure by splitting the main library by business, but the system still has the following hidden dangers:

• The volume of single-table data is getting larger. such as the order form, the number of single-table records will soon be over billion, beyond the limits of MySQL, affecting read and write performance.

• The core business Library's writing pressure is increasing, can no longer be vertically split, Mysql Master Library does not have the ability to scale horizontally

Previously, the system pressure forced us to upgrade the architecture, this time, we need to upgrade the architecture in advance to achieve the level of database expansion (sharding). The business is similar to our Uber 5 year (2014) years after the company was founded, but our business development requires that we begin to implement the level mezzanine-migration in 1 August. The logical schema diagram looks like this:

The first question that a horizontal library faces is what logic to split. One option is to divide by city, all data in one city is in one database, and the other is to split the data evenly by order ID. According to the advantages of city split is the high degree of data aggregation, to do aggregate query is relatively simple, the disadvantage is that the data distribution is not uniform, some cities have a large amount of data, the hot spots, and these hotspots may be forced to split again later. Split by order ID is the opposite, the advantage is that the data evenly distributed, does not appear a database of large or small data, the disadvantage is that the data is too scattered, not conducive to the aggregation of queries. For example, after splitting by order ID, a merchant's order may be distributed across different databases, querying all orders for a merchant, and may need to query multiple databases. For this scenario, a solution is to make redundant tables of data that require aggregate queries, redundant tables are not split, and aggregate queries are reduced during the business development process.

After weighing the pros and cons, and referring to the sub-Libraries of Uber and other companies, we finally decided to do a horizontal sub-library by order ID. From the architecture, we divide the system into three layers:

• Application layer: All kinds of business application system

• Data Access layer: A unified data access interface, to the upper layer of application layers masking read-write sub-Library, sub-Library, cache and other technical details.

• Data layer: The DB data is fragmented and shard shards can be added dynamically.

The key point of the horizontal sub-Library is the design of the data access layer, the data access layer mainly consists of three parts:

• ID Generator: Generate primary key for each table

• Data source routing: route each DB operation to a different shard data source

• Cache: Use Redis to cache data, improve performance (there will be more articles later)

The ID generator is the core of the entire horizontal library, which determines how the data is split, and the query store-retrieves the data. The ID needs to be globally unique across libraries, or it will cause a conflict in the business layer. In addition, the ID must be numeric and ascending, mainly considering that the ascending ID guarantees the performance of MySQL (such as a random string such as UUID, which is very poor in the case of high concurrency and large data volumes. Comparative performance test data are available for reference uuid-vs-int-insert-performance). At the same time, the ID generator must be very stable, because any failure can affect all database operations.

Our ID generation strategy draws on Instagram's ID generation algorithm (sharding-ids-at-instagram). The specific options are as follows:

• The binary length of the entire ID is 64 bits

• The first 36 bits use timestamps to ensure that the ID is increased in ascending order

• The middle 13 bits are the library identifier that identifies the database in which the current ID corresponds to the record

• The last 15 bits are self-increment sequences to ensure that the ID is not duplicated when concurrency is in the same second. Each shard library has a self-increment sequence table that, when generating the self-increment sequence, obtains the current self-increment sequence value from the self-increment sequence table and adds 1 to the last 15 bits of the current ID

The level of the library is a challenging project, we are also in the development of the day and night, for more information, please continue to follow this blog.

Summarize

Entrepreneurship is the process of running against time, in order to quickly meet business needs, we use simple and efficient solutions, such as the use of cloud services, application services directly access to a single point of DB, and later with system pressure increases, performance and stability are gradually taken into account, and db most prone to performance bottlenecks, we use read-write separation, vertical sub-Library, Horizontal sub-Library and other programs. In the face of high performance and high stability, the architecture upgrade needs to be completed as far ahead as possible, otherwise, the system may be slow or even downtime. Of course, the process of schema upgrade is painful and happy, every night to implement the system architecture upgrade, while enjoying the company prepared gourmet supper, while feeling the solution of technical problems, release the sense of accomplishment of the company's productivity, the exhilaration.

Server-side high-performance database optimization Evolution details case