In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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
Recommended reading:
The Foundation of MySQL Database
Serial number article title 01MySQL Foundation: classic practical query cases, summarize and organize 02MySQL Foundation: starting from five dimensions, View Table structure Design 03MySQL Foundation: system and Custom function Summary, trigger use to explain 04MySQL Foundation in detail: stored procedures and views, usage and Features detailed 05MySQL Foundation: logical Architecture Diagram and InnoDB Storage engine detailed 06MySQL Foundation: transaction Management, Lock Mechanism case detailed 07MySQL Foundation: user and Rights Management This is the end of the brief introduction of the log system on how to understand the performance of the server based on multi-dimensional analysis in MySQL. I hope the above content can be helpful to you and learn more. If you think the article is good, you can 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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.