Shulou(Shulou.com)06/01 Report--

When we monitor MYSQL database in performance testing, as professional non-functional testers, we need to understand the working principle of operating system, business implementation architecture logic, application architecture implementation logic, and database working principle in order to truly do a good job in non-functional testing. Most business transaction problems are performance bottlenecks caused by database problems, and most of them are due to unclear database problems and processing ideas. Before MySQL optimization, you must understand the MySQL query process, that is, the actual front-end business operation to retrieve data from disk. If you understand these operations, understand that query optimization is run in the MySQL optimizer mode, as shown in the following figure:

According to the above figure, we know that whether it is mobile phone or PC, for example, our ordering system, through the APP client, we query the main points of food, then establish TCP connection with the database service layer through network connection.

Read and write data mode

Step 1: Establish a connection with the database:

The connection management module establishes the connection and requests a connection thread. If there is an idle connection thread in the connection pool, assign it to the connection, if not, create a new connection thread to take charge of the client without exceeding the maximum number of connections.

Step 2: Check the syntax.

Next verify that the user has permissions. For example, if you are registered, check that the user is a member user before you can provide ordering services, and the connection thread begins to receive and process SQL statements from the client. After the connection thread receives the SQL statement, the statement is handed over to the SQL statement parsing module for syntax analysis and semantic analysis.

Step 3: Check the cache

If it is a query statement, you can first see whether there are results in the query cache, and if there are results, you can return them directly to the client.

Step 4 Check engine optimization

If there are no results in the query cache, you need to really query the database engine layer, so send it to the SQL optimizer for query optimization. That is, request the database engine layer, open the table, and the next process goes to the database engine layer, such as InnoDB.

5. Step 5: Data reading method

At the database engine level, query whether the cache has data first, if there is direct return, if not read from disk, load the corresponding data found in disk into cache, and read directly from cache next time to make the subsequent query more efficient.

Step 6: Close the connection.

After obtaining the data, return it to the client, close the connection, release the connection thread, and the process ends.

Performance problem analysis:

If there is a problem with syntax writing, there will be disk IO high or CPU high when retrieving data from disk, that is, syntax writing problems or index use problems, if syntax analysis is no problem, then see whether the cache page configuration is too small, or its own physical memory is small, and the space for cache is less.

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.