How to understand server performance analysis based on multiple dimensions in MySQL 04/20 Update SLTechnology News&Howtos

How to understand server performance analysis based on multiple dimensions in MySQL

2025-04-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article introduces how to analyze server performance based on multiple dimensions in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. Brief introduction of server performance 1. Performance definition

Server performance optimization is a very arduous task, of course, it is also a very difficult problem to deal with. When writing this article, I specifically asked the operation and maintenance boss, hardware engineer, database management, only from their own actual development experience. The point of view of this problem is at least not comprehensive.

Make up a knife: in the company is reliable, engineers this group is very good to make friends, learn from each other and progress together, promotion and raise is not good for him?

Service performance definition: the time it takes to complete a task or process an interface request, which refers to the response completion time, that is, the request is sent, to the end of the page response echo, which is the basic logic of looking at performance issues.

2. Analytical performance

The basic process of the service is generally shown in the following figure, which is the simplest process of separating the front and back end and adding a database storage process, but wants to illustrate a complex logic.

Every step of the process, from page requests to getting complete response results, can lead to performance problems. Leaving aside the core objective factors of network, hardware, server, and MySQL storage, the following line of code alone can reduce the efforts of many people.

Thread.sleep (10000); / / it seems that the whole world is quiet.

There are many factors that affect performance. Generally speaking, performance optimization will be considered from the following aspects:

Network transport, such as the interaction between private and public clouds, the interface transfer content is too large

Application service, whether the interface design is the simplest, no logical problems, and whether the architecture design is reasonable.

Storage service, query writing of MySQL, whether the table design is reasonable, whether the connection pool configuration is reasonable

Whether the hardware facilities, CPU and memory utilization are within a reasonable range, and whether the cache is reasonable.

Each of these problems is very time-consuming to deal with, and the requirements for personnel are relatively high, not to mention that they must reach the expert level, at least when performance problems arise, there should be basic awareness.

II. MySQL implementation mechanism

Based on the above flow chart, MySQL performance analysis is mainly from the following aspects, the basic direction will not be biased.

1. Connection pool configuration

View the default maximum number of connections configuration:

SHOW VARIABLES LIKE 'max_connections'

The minimum number of connections is the session connections that the connection pool maintains all the time, which is relatively easy to handle and assesses how many session connections the service needs in its normal state.

Maximum number of connections the maximum number of connections allowed by the server, the design of this parameter is relatively elegant, it needs to have control over high concurrent services, and it is necessary to analyze SQL performance and CPU utilization (basically 70% music 85%). It is not easy to obtain this set of parameters, but it is not easy to test finely, cooperate with operation and maintenance for service monitoring and recording, development is constantly optimized, and it may be necessary to divide libraries and tables, or clusters. A series of operations, such as the distribution of services, can finally get reasonable processing logic. of course, what is taken so painstakingly is the core business, and the general business is controlled by experience.

2. SQL execution process

The MySQL parser recognizes the basic syntax of SQL, generates the syntax tree, and then the optimizer outputs the SQL executable plan, a very complex process.

The client sends a request to the MySQL server

If the query is executed, it checks whether the cache is hit.

The server performs SQL parsing, preprocessing, and finally the optimizer generates the execution plan

Call the storage engine API to execute according to the execution plan

Return the client processing result

This is why interfaces now advocate the most simplified design, or interface split, step-by-step implementation, do not ask whether this will be multiple requests, put pressure on the network, this is the 5G era.

3. Logical summary

Conclusion: to analyze whether there is a performance problem of MySQL service, we need to consider whether it is the service configuration problem, or the SQL compilation process problem, which leads to a lot of waiting time, or the problem of SQL execution, or the long execution process caused by the excessive amount of query data.

In a word: the basic reason for MySQL performance problems is very simple, the amount of data is getting larger and larger, and the server cannot hold it. As a developer, this is the root cause of database optimization.

Third, execution statement analysis 1. Basic description

The above aspects are all illustrating the clear boundaries when faced with service performance problems. As developers actually have to face two direct problems: table design and SQL statement writing, most developers have been beaten by these two problems.

2. Table structure design

Table design: table design is related to all aspects of the database: data type selection, index structure, coding, storage engine and so on. It is a big proposition, but it also follows a basic norm: three paradigms.

Standardized table structure and appropriate data type can reduce the consumption of resources, index can improve query efficiency, and storage engine is related to transaction.

The logic of the structure of the table is clear, which is the basic condition for subsequent query and writing. if the structure is too large, there will be many indexes and many sub-table structures, which will bring a lot of join queries, which will also press the development feeling to the ground. This involves a metaphysics: the development should weigh the table structure design according to experience and factors.

Supplementary knife: if you ask 3.5 years of development, what business you most want to write, he will certainly say the addition, deletion, modification and query of a single table, why? Because this kind of task will not be scheduled for him.

3. Data update

Assuming that the table structure is logical, the data update (addition, deletion and modification) operation will not have major problems in general, and several basic principles will be followed.

Write with a large amount of data, perform batch operations, and take up fewer connections

Delete and update should consider the granularity of locking, do not cause large-scale locking

Delete operations are performed frequently, and memory fragmentation should be considered

Batch operations can use multithreading based on the application level

4. Data query

Query is the most common problem in development, and there are many specifications for query, and indeed query is also the most error-prone link. However, there are many factors that affect the query, and in many cases, the query is just to take the blame:

Table design specification, reducing various associations, subqueries

Column type specification, data value specification, Null and null handling

Index structure and usage specifications have the greatest impact on query performance.

The appropriate selection of storage engine directly affects the locking granularity.

Foreign key association leads to forced table coupling, one of the most annoying functions

When SQL is executed, if the performance is very poor, it also needs to be analyzed based on MySQL slow query mechanism to see if there are various problems such as disk IO, temporary table, index failure and so on.

Fourth, module summary

The above description may feel a bit messy, but on the whole, it is divided into the following three modules:

Apply service flow analysis to judge the bottleneck.

Familiar with the basic mechanism of MySQL, analyze waiting and execution time

Table structure Design and SQL execution Optimization of MySQL

This article is only a general description of the problem of service performance, the focus is to state a basic logic: have the consciousness of service performance problem analysis, and the boundary of consciousness is relatively comprehensive, do not just focus on one aspect of thinking.

One sentence: because the classification of the article is MySQL module, so the key description is also at the MySQL level. In practice, any level can cause performance problems.

Source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base