MySQL Automation completes SQL audits

The SQL audit mainly accomplishes two purposes.

1, to avoid poor performance of SQL into the production system, resulting in lower overall performance

2, check the development of the design of the index is reasonable, whether you need to add an index

The 1th is the core of SQL audits, to avoid a messy SQL impact on the performance of the line, and even lead to online system crashes.

The 2nd is the category of modeling, the best way to solve the modeling is the DBA to participate in the project Prophase Audit, by the DBA model, if the DBA human resources is not sufficient, then periodically by the DBA training developers. And then found that modeling is too bad to buckle KPI.

Now many companies are human flesh to complete the SQL audit, human flesh audit requirements for DBAs higher, need to understand some code, another is time-consuming and laborious, after all, the general company dozens of development, corresponding to a DBA, and the DBA has to do a lot of other things.

How do you liberate DBAs from human-SQL audits?

The idea is actually very simple:

1, get the program to execute the SQL

2, to carry out the analysis of the SQL, you can add a variety of analysis conditions to determine whether the SQL can be automatically audited through, not approved by the need for manual processing.

3, with the late Slow query log analysis system to complete long-term monitoring.

Open source solutions are mainly Taobao Dan Chen Sqlautoreview system. You can search on the github.

But this system is mainly based on Java sqlmapfile.xml solve the problem of automatic indexing, the source data requirements, and by parsing the SQL structure to assume that the SQL execution plan is not particularly accurate, and can not well distinguish between new SQL or old SQL.

So a new scheme has emerged:

1. Generate a figerprint for all executed SQL

2, based on the data provided by the slow query, plus the data provided by explain to determine whether this SQL performance is acceptable, or can be optimized.

3, automatic audit through the performance acceptable part of the DBA to show the poor performance of the SQL, and then optimize.

The advantage of the scheme is that:

Based on the SQL that the user actually executes, and can observe the SQL execution frequency.

Based on MySQL's real execution plan and execution results, the analysis is more accurate.

Each SQL has a fingerprint that requires incremental processing of the newly added SQL, efficiency and performance improvements.

Box Anemometer Two development allows slow queries and SQL audits to be on the same platform, increasing tool integration, and improving the user experience (DBAs and developers).

Programme implementation:

Now that I'm a DBA, there's definitely a more DBA mindset. Based on the existing software two times development, reduce development costs, integrated management platform.

Based on box anemometer. Install box anemometer

Box Anemometer is a b/s architecture, graphical MySQL slow query analysis tool. Powerful and easy to use, design simple and direct. Anemometer is a two-time package based on Pt-query-digest.

Core processing process:

The MySQL node–> Schedule task collects slow query information through Pt-query-digest –> results to the database –>anemometer the results of slow queries are presented on a conditional basis, and features such as graphical and trend maps are provided.

So Anemometer has helped us complete the data collection, including each SQL fingerprint information, and related information, we in the test environment, based on Anemometer, will long_query_time set to 0, The SQL and related information can be collected.

After we have collected all of the SQL, we have to analyze whether this SQL can be automatically audited. We're going to start customizing it here.

Custom content is as follows:

One

Set up a separate datasources that can be named Audit_sql.

This datasources only a slow query in the development environment or test environment (which environment you want to do the SQL Audit), set the long_query_time of this environment to 0, and receive all the SQL queries.

Ii. Modification of Anemometer

ALTER TABLE ' Global_query_review ' ADD audit_status VARCHAR (255) not NULL DEFAULT ' refuse ' comment ' SQL Audit status refuse failed pass Audit through ';

Modify the PHP code.

Add a Aduit status option box in the Where condition of the audit_status to filter the status of the

Add a Audit Status option box to the Show_query module to manually set the Audit_status state

Third, add two additional scripts, quasi real-time analysis audit_status for refuse SQL, if the SQL to meet the conditions of automatic audit, then set audit_status for pass, said automatic audit passed.

Automatically audits failed SQL, which is retrieved and processed by the DBA manually on the anemometer.

This involves an automated audit of the algorithm:

The algorithm is divided into two kinds.

The first is quasi real time, that is, it can be run once a few minutes or one hours, mainly based on the efficiency of each SQL execution to determine whether pass.

The corresponding script name is called: audit_sql.py

The second is once a day, weakening execution efficiency judgments, increasing the frequency of the day's execution.

The corresponding script name is called: audit_sql_day.py

Each family adjusts or optimizes the two scripts according to their own actual situation.

At this point, you can let more than 99% of the Code automatic audit passed, audit not through the code you can allow development of their own to tracking can also be actively pushed to development.

For the environment to be built, there may be some messy SQL, but after a period of stability, the exception of the SQL fingerprint, then the daily production of fewer SQL fingerprints, and this part of the SQL fingerprint is the programmer to write new code generated.

The two modified box anemometer code and corresponding Python scripts are on my github.

Https://github.com/ISADBA/anemometerAudit_SQL