directory

  • Flow chart of general business system operation
  • How much concurrency can a 4-core, 8-GIGAByte machine withstand?
  • Will the database be killed first when high concurrency hits?
  • How many concurrent stresses can an 8-core 16GB database withstand per second?
  • In what ways can the database architecture be optimized?
  • conclusion

Today, I would like to share a knowledge point about the evolution of MySQL database architecture. Because many brothers do system development based on MySQL every day, but the written system is the kind of low concurrency pressure and small amount of data, so even if it is online, it just runs normally. But do you know how much concurrency pressure the MySQL database you are connecting to can withstand? Do you know how the MySQL database should evolve if it is under pressure?

Flow chart of general business system operation

First of all, we first look at a most basic Java business system connected to the database running architecture, in fact, in simple terms, we usually use Spring Boot + SSM technology stack to develop a Java business system, with Spring Boot embedded Tomcat can provide external HTTP interface, Then, at most, nacOS + Dubbo will be added to call other system interfaces, and all the data will be cruD by connecting mysql database, as shown in the following figure.

The above architecture is probably the most common system architecture that many brothers do on a daily basis. Some brothers are a little bit more sophisticated. Generally speaking, they may add some MIDDLEWARE such as ES, Redis and RocketMQ for simple use, but that’s about it. Do you have any idea how much pressure his database can withstand on a system like yours?

How much concurrency can a 4-core, 8-GIGAByte machine handle?

To be honest, the solution to this problem is generally not to talk about how much stress data can withstand, because it is not the database that has to withstand high concurrency first, but the Web system that you connect to the database has to withstand high concurrency first! That is, our Spring Boot + SSM that set of business system can resist how high concurrency we have to figure out first!

So to understand the problem, it must first say a topic, in general our spring boot application system is deployed in two nuclear roughly 4 g or 4 nuclear 8 g machine, the machine configuration is very critical, so here just tell everybody a experience value, even if we if the deployment is a 4 core 8 g machine, By default, tomcat embedded in Spring Boot opens 200 threads to handle requests, and each request reads and writes to the database multiple times. At this point, your machine can handle roughly 500 to 1000 concurrent requests, depending on the complexity of your interface, as shown in the figure below.

Will the database be killed first when high concurrency hits?

So in fact, in general, when your incoming high concurrency pressure, usually is not database outweighs the first, but you resist the business system of machines, such as you deploy the 2 machine, so in fact by few thousand concurrent per second, basically CPU load the two machines have soared to more than 90%, a lot of pressure, And the interface performance will start to go down a lot, as shown below.

So what happens to our database stress at this point? In fact, when you have two machines that can handle a thousand or two thousand requests per second, there’s usually a little bit of a bottleneck, because why? The point is that your business system will read and write to the database multiple times for each business request, so the business systemOne request may result in multiple requests to the databaseBecause of this, your database concurrency may be in the thousands at this point.

How many concurrent stresses can an 8-core 16GB database withstand per second?

So the next question is, on what machine is your database typically deployed? Generally speaking, if the configuration of the database is a particularly low concurrency scenario, in fact, 2 core 4G or 4 core 8G is enough, but if it is a bit more conventional company production environment database, usually will be 8 core 16G. So how many concurrent stresses can an 8-core 16GB database withstand per second? In general, on the order of thousands.

Because the specific number of concurrent resistance depends on the amount of data in your database and the complexity of your SQL statements, so generally speaking, 8-core 16G machine is about thousands of concurrent resistance per second, no matter how large the basic can not withstand, because often to this magnitude, Database CPU, memory, network, IO load is basically high, especially CPU, probably at least 70 to 80 percent, as shown in the following figure.

In what ways can the database architecture be optimized?

1. Split multiple database machine optimization schemes according to the business system

So, how can you optimize your database architecture in general under this concurrency pressure? ** is also simple, we can add machines, the database deployed to multiple machines. Since we usually have many db and tables for business systems in one database, we can first split them by business systems, for example, add another machine, deploy another database, and then put some DB and tables for business systems. The old database machine can put db and tables on another part of the business system, which can relieve the pressure on the old database machine, as shown below.

2. Read/write separation architecture optimization solution

So then the question arises, what if the concurrency pressure continues to rise, causing the two databases to be split to become more and more stressful? At this time can be on a recruit, called separation, speaking, reading and writing, is to hang a from each database library, let master database based on synchronous replication binlog data update log from the database, keep the master-slave database data is consistent, then our system can actually written into the main database, query in from the garage, this is can alleviate the pressure of the original primary database again, The diagram below.

3. Optimization scheme of sub-database and sub-table architecture

What if, even if we suspend the slave database from the primary database, and then the concurrency pressure continues to increase, so that our primary database is overloaded with thousands of writes per second, and we can’t withstand it? At this point, we can only go to the ultimate solution, which is to split the main database into multiple libraries, put part of the data of one table in each library, and then use multiple main libraries to resist high concurrent write pressure, so that we can disperse the pressure again, as shown in the figure below.

conclusion

Ok, got here today to share knowledge, actually our database schema evolution was basically in accordance with the said today the order and the evolution of ideas gradually gradually, start your single database machine resistance to thousands of concurrent carry not to live, for split multiple database machine according to the business system, and then carry not to live, pressure on master-slave structure allocation, speaking, reading and writing. When you have multiple machines that can withstand database write pressure, you can always use a database architecture to withstand high concurrency pressure.

END

Scan code for free 600+ pages of Huisei teachers original fine articles summary PDF

Summary of original technical articles