Give us the complexity and return the simplicity to you丨TiVP visualizes SQL execution plans

Rediscovered my potential through the TiDB Hackathon. When you want to do something, you will find yourself focusing on it, and then something that you thought would be impossible and impossible becomes possible.

——TiVP team

In the just-concluded TiDB Hackathon 2021, TiDB Visual Plan, the work of the TiVP team, realized the visualization of the SQL execution plan, which is conducive to quickly locating and solving various problems related to the execution plan. Due to the practicality of the project and from the perspective of users (whether external TiDB users or PingCAP internal R&D engineers), it won the "Third Prize" and "Users' Choice Award".

The project is very focused, which effectively improves the readability of the execution plan and has certain index recommendation capabilities.

——Judge Feng Guangpu

When I finally saw the visualized execution plan, I almost shed tears of excitement. After all, it was really hard for us to diagnose slow SQL before, so the execution plan on a large screen is almost impossible to see, and if we want to compare the similarities and differences between the two execution plans, it will be even more broken. With visualization, at least the efficiency of analyzing where is slow will be much improved.

——Tang Liu, Vice President of R&D at PingCAP

Why did you do this project?

TiVP is a temporary team composed of PingCAP employees and community partners. Yves, who had been employed at PingCAP for less than half a year, saw the promotion of Hackathon, and signed up with an experience mentality. As a member of the production and research fire brigade, Yves often looks at the customer's execution plan in his daily work. At first, he only had a simple idea: the demand for TiDB SQL tuning is great, and in the original factory support applied by the customer, More than half of the problems related to the execution plan (Plan); SQL is a declarative language, and observing the execution plan is the only way to check the execution efficiency. The execution plan obtained by slow SQL Explain is extremely complicated and difficult to understand, which affects the analysis efficiency. In today's emphasis on software "observability", is it possible to visualize the SQL execution plan?

So he privately found the optimizer boss Chrysan beside him to talk about this idea. He didn't expect to hit it off after just two minutes of chatting, and then the boss brought QA Tammyxia, so the lineup suddenly became huge. But the three of them are all working on the backend of the database, and visualization is not possible without the frontend. With the mentality of trying it out, Yves posted a teammate recruitment post on AskTug.

The strong appeal of TiDB's open source ecosystem made several friends approach Yves to exchange contact information the next day. Among them, 92hacker (Chen Yuan, a front-end developer in Shenzhen, although his work content is not strongly related to the database, But I have been paying attention to the PingCAP public account to understand the progress of TiDB) and had the first friendly and in-depth communication with him, so the team was pulled out like this.

The name of the team TiVP is the abbreviation of TiDB Visual Plan. Everyone thinks VP is also very domineering, and PingCAP has many tools starting with Ti, so the team unanimously adopted the team name. After forming the team, there is still some time before submitting the RFC. The team browsed the award-winning projects of Hackathon 2020, and also exchanged experience with the previous players. As the TiVP team said, dream big, champion heart, won the award for the first time in the competition, one The reason is that the goal is set high.

What problem does the

TiDB Visual Plan, by collecting the execution plan and runtime information of SQL on the TiDB database side, develops a display interface based on the open source component dalibo/pev2 to visualize the execution plan of SQL, sort out the information output from the complex database, and help Technicians can more easily understand and analyze SQL statements and their execution logic, and can highlight key problems, so as to quickly locate and solve various problems related to execution plans, such as slow SQL optimization and wrong execution plan selection.

As shown in the figure below, TiDB Visual Plan will convert the SQL statement executed on the left to JSON conversion and statistics of corresponding operators, and clearly display the execution plan in a tree structure. After each operator is clicked to expand, its specific time-consuming, number of records and resources used will be displayed. The upper right corner of the operator will also use various icons to prompt some problems, such as relatively long time consumption, relatively high resource consumption, large deviation between the estimated execution plan and the actual number of executions, etc. Through these prompts, the DBA can easily Easily locate problem modules for detailed troubleshooting.

For complex SQL execution plans, TiDB Visual Plan will also provide the Advisor function, which can compare the changes before and after optimization through Hint prompts (use_index as shown in the figure below) and background calculations, and can apply optimization through one-click “Apply” to achieve intelligent assistance Tuning [Note: This function is a blueprint plan, this Hackathon only demonstrates the interaction process].

In addition, TiDB Visual Plan can also visualize the SQL execution plan through different dimensions such as time-consuming analysis and cost analysis, and provide tree diagrams and classic horizontal expansion modes. In short, you can find everything you want.

Challenges and fun in the

The four members of the TiVP team participated in the TiDB Hackathon for the first time. As a mixed team, the entire competition process was full of challenges and joy. As 92hacker mentioned, it is a great honor to find three Ping partners through the community, so that he has a better impression of PingCAP. Before the finals, I thought that regardless of whether the TiDB Visual Plan project could win the prize or not, I would try my best to put it after the competition. It is integrated into TiDB's Dashboard to complete the technology migration.

Challenge

TiDB Dashboard is a graphical interface provided by TiDB since version 4.0 for monitoring and diagnosing TiDB clusters. At first, the team wanted to integrate the visualization of TiDB Visual Plan into Dashboard, but after the front-end selection, they found a big problem, that is, the two technology stacks are incompatible, and the cost of the entire migration will be very high. Since the team members were very busy with their daily work, the time left for the front-end development side was only a day or two, but if the migration was completed, it would take at least a week. In the end, the team discussed and decided that instead of focusing on integrating into the Dashboard, it is better to change the direction and optimize the visualization of the execution plan itself, such as running the most complex test SQL, integrating index recommendation, etc. Judging from the final result, it was quite successful to make such a decision at that time.

Fun

During the preliminary round, only the team leader Yves could hear the sound in the video demonstrated by the Demo, and no one else could hear the sound, and the team members sat there very calmly without interruption (Yves wore noise-cancelling headphones during the defense, and the defense After that, without pulling out the headphones, I confidently played the demo video to the judges until the end (as shown in the figure below), so that the online judges watched a silent version of the demo (92hacker in Shenzhen thought that this video was originally silent), but the video is actually a well-prepared voice explanation. However, everyone can understand it without explaining it, and it also verifies the team's product philosophy, which is simple.

Outlook for future work

In addition to winning the third prize, the TiVP team was very surprised to be awarded the "Users' Choice" award. When working on this project, the team did not start with a large scope, but discovered the pain points of the current execution plan. While solving the visualization of the execution plan, we found that there are many areas for improvement, including ease of use, interactivity, and some future intelligence. The idea of the whole project is to think about the problem from the user's point of view and hit the user's pain point. The team guessed that this may be an important reason to impress the judges on behalf of TiDB users.

Although the Hackathon 2021 competition has ended, the TiVP team is still making continuous efforts to migrate the technology stack of TiDB Visual Plan to Dashboard, promoting TiDB to integrate this new function as soon as possible, and freeing DBAs from the dense execution plan code on the screen.

At present, TiDB Visual Plan is mainly for visualization of execution plans. In the future, it not only hopes to further improve the diagnosability of the kernel, such as the visualization of the optimization process of the optimizer, but also hopes to do more exploration in terms of depth and breadth compatibility: for example, giving the optimizer With the thinking ability of whatif, it provides users with an excellent experience of one-click tuning; for example, by defining the interface protocol, the SQL tuning tool is extended from TiDB to MySQL, PostgreSQL, Oracle and SQL Server, etc., to make a more general one. Cloud service version. Just like the declaration of the TiVP team, "Give the complexity to us and return the simplicity to you", the visualization service of SQL execution plan with ease of use, scalability and intelligence after cloudification will be a sea of stars.