Explanation of factors affecting database performance in MySQL

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

You may also be interested in:
  • Usage and performance optimization techniques of aggregate function count in MySQL
  • Detailed explanation of the reasons and optimizations for the large offset affecting performance during MySQL query
  • Example of how to optimize MySQL insert performance
  • MySQL performance comprehensive optimization method reference, from CPU, file system selection to mysql.cnf parameter optimization
  • A brief discussion on the difference between MySQL and MariaDB (performance comparison between mariadb and mysql)
  • Optimizing the performance of paging query for MySQL with tens of millions of data
  • MySQL paging performance exploration
  • Detailed explanation of MySQL batch SQL insert performance optimization
  • Summary of several important performance index calculation and optimization methods for MySQL

<<:  Vue implements DingTalk's attendance calendar

>>:  Keepalived+Nginx+Tomcat sample code to implement high-availability Web cluster

Recommend

Detailed steps for Python script self-start and scheduled start under Linux

1. Python automatically runs at startup Suppose t...

Ubuntu 18.04 installs mysql 5.7.23

I installed MySQL smoothly in Ubuntu 16.04 before...

Introduction to several ways to introduce CSS in HTML

Table of contents 1. Embed CSS styles directly in...

Jenkins builds Docker images and pushes them to Harbor warehouse

Table of contents Dockerfile pom.xml Jenkins Conf...

How to redirect to other pages in html page within two seconds

Copy code The code is as follows: <!DOCTYPE ht...

Solve the problem of secure_file_priv null

Add secure_file_priv = ' '; then run cmd ...

Summarize the common properties of BigIn functions in JavaScript

Table of contents 1. Overview 2. Attributes 1. Ma...

Vue routing returns the operation method of restoring page status

Route parameters, route navigation guards: retain...

React's method of realizing secondary linkage

This article shares the specific code of React to...

Use Docker to build a Redis master-slave replication cluster

In a cluster with master-slave replication mode, ...

How are spaces represented in HTML (what do they mean)?

In web development, you often encounter characters...

Summary of several principles that should be followed in HTML page output

1. DOCTYPE is indispensable. The browser determin...