Explanation of factors affecting performance in mysql database (with database architecture case)

This article brings you an explanation of factors affecting performance in the MySQL database (with database architecture cases). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Story about database performance

During the interview, things about the database will more or less be talked about, "How well do you master the database?", when is the performance of the database most tested, and the promise is important On the one hand, it is a time for reading and writing large amounts of data, and e-commerce promotions are a time to test the performance of their respective databases.

For web servers, when the amount of data is large, we can simply reduce the burden on a single server through horizontal expansion, but for database servers, it is not that simple. They cannot do it easily. Horizontal expansion also violates the principles of database integrity and consistency. So how should our database architecture be built?

For major promotion activities, no matter how good the product is or how successful the planning is, if there is no stable database and server environment, all this so-called will be in vain.

Database architecture case

As shown in the figure, there is no master-slave replication component between the master and slave servers, that is, when the master server In the event of a failure, it is difficult to switch the master server. This requires the DBA to select the slave server with the latest data, promote it to the master server and synchronize other slave servers. The time cost of this process is also very heavy.

And too many slave servers will also pose a certain challenge to the network card of the master server when the business volume is large.

We can understand what affects database performance through cluster monitoring information.

Yes, yes. Generally speaking, the main reasons are QPS and TPS, concurrency (the number of requests processed at the same time, to avoid confusion with the number of simultaneous connections), disk IO, and read operations that are too high

Here is a suggestion: It is best not to back up data on the main database. At least cancel such plans before large-scale events.

Factors affecting the database

sql query speed
Server Hardware
Network Card Traffic
Disk IO

• Ultra-high QPS and TPS

Risk: SQL with low efficiency ( QPS: Queries processed per second)

• A large amount of concurrency and ultra-high CPU usage

Risk: A large amount of concurrency ( The number of database connections is full (max_connections defaults to 100))
Risk: Ultra-high CPU usage (downtime due to exhaustion of CPU resources)

• Disk IO

Risk: Sudden decline in disk IO performance (use faster disk devices)
Risk: Other scheduled tasks that consume a large amount of disk performance (adjust scheduled tasks)

• Network card traffic

Risk: The network card IO is full (1000Mb/8=100MB)

How to avoid being unable to connect to the database:
1. Reduce the number of slave servers
2. Perform hierarchical caching
3. Avoid using "select *" for query
4. Separate the business network and server network

The above is the detailed content of Explanation of factors affecting performance in mysql database (with database architecture case). For more information, please follow other related articles on the PHP Chinese website!