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

This article mainly shows you "what are the factors that affect the performance of the database in mysql", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and learn about the factors that affect the performance of the database in mysql.

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

These are all the contents of the article "what are the factors that affect database performance in mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.