Explanation of factors affecting database performance in MySQL

A story about database performance

During the interview, the topic of database will be discussed to some extent, such as "How familiar are you with database?" When is the performance of the database most tested? In general, it is when reading and writing large amounts of data. Large promotional activities of e-commerce are the time to test the performance of each database.

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

For big 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 will be in vain.

Database architecture example

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

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

We can use cluster monitoring information to understand what affects database performance.

The answer is 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 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 activities.

Factors affecting the database

• SQL query speed
• Server Hardware
• Network card traffic
• Disk IO

Ultra-high QPS and TPS

Risk: Inefficient SQL (QPS: queries processed per second)

High CPU usage and high concurrency

Risk: A large number of concurrent connections (the number of database connections is full (max_connections defaults to 100))

Risk: Extremely high CPU usage (downtime due to CPU exhaustion)

Disk IO

Risk: Disk IO performance suddenly drops (use faster disk devices)

Risk: Other scheduled tasks that consume a lot of disk performance (adjust scheduled tasks)

Network card traffic

Risk: 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 queries
4. Separate business network and server network