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

How to create a Pod in Kubernetes

Table of contents How to create a Pod? kubectl to...

Detailed explanation of MySQL backup and recovery practice of mysqlbackup

1. Introduction to mysqlbackup mysqlbackup is the...

mysql5.5.28 installation tutorial is super detailed!

mysql5.5.28 installation tutorial for your refere...

Linux 6 steps to change the default remote port number of ssh

The default ssh remote port in Linux is 22. Somet...

Docker dynamically exposes ports to containers

View the IP address of the Container docker inspe...

Installing MySQL 8.0.12 based on Windows

This tutorial is only applicable to Windows syste...

Problems encountered when uploading images using axios in Vue

Table of contents What is FormData? A practical e...

JavaScript array merging case study

Method 1: var a = [1,2,3]; var b=[4,5] a = a.conc...

Common solutions for Mysql read-write separation expiration

The pitfalls of MySQL read-write separation The m...

Super simple implementation of Docker to build a personal blog system

Install Docker Update the yum package to the late...

Example of using @media responsive CSS to adapt to various screens

Definition and Use Using @media queries, you can ...

Native JS music player

This article example shares the specific code of ...

Summary of various implementation methods of mysql database backup

This article describes various ways to implement ...

Docker deploys Laravel application to realize queue & task scheduling

In the previous article, we wrote about how to de...