In this section, we will show you the trace function directly through the video.
Trace function on
First log in to a traffic port, and then through a
set trace=1Statement to turn the trace function on.
The benefits of trace
Select * from a table is similar to MySQL’s show profile statement. You can see how the specific execution statement takes in each link of distribution. And then through the
show traceYou can see the time-consuming situation. The front is the procedure, start and end time, followed by the execution or dependent statements, similar to an effect of explain.
You can see the time records of operations such as SQL parsing, routing, preparation for distribution, distribution and execution, collection of recycling results, including writing back to the client side, and so on. If I have multiple nodes, go through
show traceView the time of multiple SQL execution and multiple result set recycling. We can see that their start times are actually the same. When multiple nodes are distributed, they are distributed simultaneously, and when they come back, they come back separately. This is the function of our trace.
We can trace where the performance bottleneck occurs through the trace function.
Let’s simply bring back some of the graphs, not necessarily the one just mentioned, and let’s do an in-depth analysis. As a result set of trace, we can analyze which part is slow or which block is beyond our cognition to find the performance bottleneck. It is useful to locate problems with dble or mysql.
Similarly, we reinterpret such a table and draw a sequence diagram. Draw the sequence diagram. The following is the result just now. It can be divided into three layers. There is a mistake in MS SQL. It should be mysql. On the left is the client, then middleware, and finally mysql.
The first is the time when a client sends me middleware. This period of time can not be considered for me, because I don’t know how much time has been spent without our middleware layer. But we know from experience that it’s about half a RTT. If the SQL is not very large, then it goes to the middleware layer. I have time to read SQL, time to parse SQL, time to calculate routing, and some preparation work to be issued. For example, I go to the connection pool to get the connection. We call this part the front-end part. Then, we divide the middleware into front end and back end. This part of the front end is basically linear, and only after one is executed can the latter be executed. Then I started sending it to MySQL.
After giving mysql, for example, I am a select statement. Imagine a select statement with multiple lines, not one line. At the same time, for example, it is impossible for MySQL to return to the line of 1 million. Maybe the first line has arrived and the second line is still on the way. So, my next result will have a fields return. If you are familiar with the MySQL protocol, you will know that the result set will be processed one line after the column is returned.
The last one is the end of line mark, so I started to count the time when I received the first MySQL result package, until I received the last line which was my fetch result. Similarly, in our simple query, we received a line. In fact, I can write data to the client. It’s streaming and doesn’t have to wait for all the results to come out. So we can write back, and only when I receive the last line and the last line ends roweof, can we write the last package back. Our total time is actually the over all time from my entry into dble. So now all my operations are marked in red. If you find a performance problem, you can use this tool to analyze the problem.
We actually investigated a case. The problem is that three nodes are actually sent to three nodes in this part of time. After three nodes are sent, we will see that the time of two execute SQL is about half of the other time. So we found that three nodes, one MySQL itself is slow.
And we know the barrel principle. The slowest one leads to the slowest one. After troubleshooting, it is found that the parameters of the last MySQL configuration are not correct. The parameters of MySQL are tuned to the same situation as the other two mysql. Finally, the performance is adjusted. But there may be other reasons, which need to be analyzed in detail.
Trace is to show the functions of each stage. For my front end, a certain stage takes a long time. If there are not enough threads in the thread pool of dble internal processing threads, a local consumption ability can be improved by configuring a group of threads in dble. This is another adjustment direction of our trace function.
OK, let’s introduce it here.