In this paper, the method of download, installation and use of SOAR is briefly introduced, and four scenarios of basic setup and application of SOAR are introduced.
Review of previous article:
Failure recovery time prediction based on linear regression
At the Open Source Pioneer Day (OSCAR) on October 20, Xiaomi opened source its own SOAR (SQL Optimizer And Rewriter), click on the “Open Source Address”. This is an intelligent SQL optimization and rewriting tool that allows developers to quickly check the quality of their SQL directly, generate evaluation reports, and prevent problems with SQL from being brought online and resulting in a deterioration of service quality. It not only improves the quality of online code as much as possible, but also avoids some of the pitfalls of human oversight.
Download and install
SOAR is written in Go language, which naturally has the cross-platform properties of operating system. You can download the compiled binary files for use in the Release version on Github according to the corresponding operating system. If you are an experienced custom user, you can also “click here” to download the code and compile and install as shown below.
Binary installation
The source code to install
Use guide
Quick learning
After the installation is complete, no configuration is required. You can immediately generate a Markdown format SQL review report and modify the SQL according to the recommendations in the report by executing the following command.
Using a configuration
SOAR provides rich configuration parameters that allow you to customize private review recommendations by specifying different configuration items for different usage scenarios. Most of the configuration options are for users with special needs who can experience SOAR’s basic capabilities without having to do any configuration.
The SOAR configuration file is in YAML[1] format. In general, you only need to configure online-DSn, test-dsn, log-output and a few other parameters to experience most of the functions provided by SOAR.
Below I will cover some of the basic configurations commonly used in SOAR.
1. Configure the environment
It is highly recommended that you specify the relevant database environment in the configuration file because many functions require database metadata for judgment purposes, so many functions will be degraded if you do not specify the database environment. To protect the security of online databases, we have divided the database environment into online and test environments, but this does not mean that you need two database environments, but it is recommended that you specify at least one test environment.
It is important to note that since we will do some relatively risky operations in the test environment, to prevent users from losing online data due to careless configuration, we added the allow-online-as-test parameter to ensure that you really want to use the online environment as a test environment. So when the two integration environments you specify are the same, please specify allow-online-as-test as true, otherwise SOAR will not be evaluated.
SOAR itself supports very rich output formats, in addition to the default Markdown format, SOAR also supports output in HTML or JSON format.
Some of SOAR’s built-in tools will also be available as report-type. For example, there are 15 output formats such as SQL rewrite, EXPLAIN analysis, SQL fingerprint and SQL beautification, and each report-type has a different function. You can view the output types supported by SOAR by using the following command.
3. Get help
In addition to the capabilities mentioned above, SOAR provides many other configuration items that you can find in the SOAR project documentation due to space constraints. If you have any questions, please submit an Issue here [2] or contact us in Gitter[3].
The scene is introduced
| scenario | The online environment | The test environment | The application type |
| The scene of a | There are | There are | Daily optimizations, complete recommendations, recommendations |
| Scenario 2 | There is no | There are | New resources are applied for and the environment is initialized |
| Scenario 3 | There is no | There is no | Blind test, trial, no EXPLAN and index recommendations |
| Scenario 4 | There are | There is no | Using the online environment as the test environment is not recommended |
The scene of a
This scenario is the most common scenario in our daily work, it is mainly applied to online services daily create table or table structure table more.
When SOAR analyzes SQL, it first fetchs basic information from THE SQL statement, then fetchs all related library table structure metadata from the online environment and synchronizes it to the test environment for further analysis. In this scenario, SOAR provides the most comprehensive functional support. It can not only output more than 110 SQL checks, but also index the library tables involved. It can also output EXPLAIN, PROFILE and other information to assist DBAs or developers in performance tuning and problem location. SQL rewriting also relies on metadata support, which is most fully supported in this scenario.
Scenario 2
This scenario applies when you apply for database resources for the first time without an existing online environment.
In this scenario, THE SQL file mainly contains library building table sentences. SOAR generates the corresponding test environment based on DDL and obtains metadata from this environment for optimization analysis.
Scenario 3
In this scenario because there is no configuration online environment and test environment, the lack of a lot of analysis the necessary metadata, and unable to get the EXPLAIN information, such as in this case SOAR execution will do functional degradation, only give part doesn’t depend on the database environment to give advice (most of the heuristic Suggestions and a small number of SQL rewrite).
While the service is not fully functional, it is also the fastest to execute in this scenario, which still provides more than 110 heuristic suggestions and is particularly suitable for detecting SQL itself, such as as a localized SQL Linter IDE plug-in.
Scenario 4
This scenario is the same as scenario 2. In this scenario, only the online environment is configured and the online environment is enabled as the test environment. SOAR points the test environment to the online environment without specifying the test environment.
SOAR creates a temporary database in the test environment to ensure that multiple clients can be evaluated in the same environment at the same time. The temporary database will be deleted after the evaluation is completed. Therefore, it is highly recommended not to use the online database environment as the test environment, in case the database is severely affected in some unknown extreme conditions.
reference
[1]https://en.wikipedia.org/wiki/YAML
[2]https://github.com/XiaoMi/soar/issues
[3]https://gitter.im/xiaomi-dba/soar
This article was first published on the public account “Mi Operation and Maintenance”. Click to view the original article