Shulou(Shulou.com)05/31 Report--

This article will explain in detail what factors affect the performance of the mysql database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Stories about database performance

During the interview, you will talk more or less about the database, "how well do you know the database?" When to test the performance of the database, the main aspect is to read and write a large amount of data, and the great promotion activities of e-commerce is the time to test the performance of their respective databases.

For the web server, when the amount of data is large, we can simply reduce the burden of a single server through horizontal expansion, but for the database server, it is not so simple, they can not easily scale out, which also violates the principle of database integrity and consistency, so how to build our database architecture?

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

Database architecture case

As shown in the figure, there are no master-slave replication components between master and slave servers, that is, when the master server fails, it is difficult to switch between the master server. This requires DBA to select the latest slave server from the slave server to 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, when the volume of business is large, it is also a certain challenge to the network card of the master server.

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

Generally speaking, it is mainly 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's a suggestion: it's best not to back up data on the main database, at least before large-scale events.

Factors affecting database

Sql query speed

Server hardware

Network card traffic

Disk IO

Ultra-high QPS and TPS

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

Massive concurrency and ultra-high CPU utilization

Risk: high concurrency (database connections are full (max_connections default 100))

Risk: ultra-high CPU utilization (downtime due to CPU resource exhaustion)

Disk IO

Risk: sudden degradation of disk IO performance (using 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. Hierarchical caching

3. Avoid using "select *" for query

4. Separate business network from server network

This is the end of this article on "what factors affect performance in mysql database". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.