Database full SQL analysis and audit system performance optimization journey

Full SQL (all SQL that accesses the database) can effectively help secure database audits and help businesses quickly troubleshoot performance problems. Generally, it can be obtained by opening the genlog log or starting the MySQL audit plug-in. Meituan chose a non-intrusive bypass packet capture scheme, which is implemented in Go language. No matter which solution is adopted, you need to focus on its performance loss to the database. This article introduces the performance problems and optimization practices encountered in the database auditing practice of the Meituan basic R&D platform packet capture solution, hoping to be helpful or enlightening to everyone.

1 Background

Database security has always been an area that Meituan's information security team and database team pay great attention to. However, due to historical reasons, access to the database only has the capability of sampling and auditing, which makes it impossible to quickly discover, assess and optimize some attack events. Based on historical experience, the security team found that attacks on the database basically have certain characteristics, and some specific SQL is often used. We hope that through the full analysis of MySQL access traffic, we can identify the commonly used SQL and achieve targeted database security. .

2 Current situation and challenges

The following figure is the architecture diagram of the sampling MySQL audit system. The data collection end is implemented based on the pcap packet capture method, and the data processing end uses the log access solution of the Meituan Big Data Center. All MySQL instances are deployed with rds-agent for collecting MySQL-related data and log-agent for log collection. rds-agent captures MySQL access data and reports it to the log receiver through log-agent. In order to reduce the delay, scheduling optimization in the same computer room is performed between the reporting end and the receiver. The log receiver writes the data to the agreed Kafka. The security team uses Storm to consume Kafka in real time to analyze the attack events, and periodically pulls the data to Hive for persistence.

We found that it is usually a few core MySQL clusters that are attacked. According to statistics, the maximum QPS of these clusters is about 50,000 times of 9995 lines. As a parasitic process on the MySQL machine, rds-agent is also extremely important for resource control for the stability of the host. In order to evaluate the performance of rds-agent under high QPS, we use Sysbench to test MySQL, and observe the data loss rate and CPU consumption captured by rds-agent under different QPS, and compare the results from the stress test data below. Oops:

QPS	loss rate	CPU utilization
10368.72	1.03%	307.35%
17172.61	7.23%	599.90%
29005.51	28.75%	662.39%
42697.05	51.73%	622.34%
50833.50	63.95%	601.39%

How to guarantee low loss rate and CPU consumption under high QPS? It has become an urgent problem and challenge to be solved in the current system.

3 Analysis and optimization

The following mainly introduces the analysis and improvement of the data collection end in terms of process, scheduling, garbage collection and protocol around the problem of loss rate and CPU consumption.

3.1 Introduction of data collection terminal

First, briefly introduce the data collection terminal rds-agent, which is a process on a MySQL instance, written in Go language, and based on the agent transformation of the open source MysqlProbe. By monitoring the traffic of the MySQL port on the network card, it analyzes the client's access time, source IP, user name, SQL, target database and target IP and other audit information. The following is its architecture diagram, which is mainly divided into 5 major functional modules:

1. probe

Probe means probe. Gopacket is used as the packet capture solution. It is a Go packet capture library open sourced by Google and encapsulates pcap. The probe encapsulates the captured original data link layer frame into a TCP layer data packet. Through the variant Fowler-Noll-Vo algorithm hashing the source and destination IP port fields, the database connection can be quickly broken up into different workers.

2. watcher

The login user name is extremely important for auditing. Clients often access MySQL through a long connection, and the login information only appears in the authentication handshake stage of the MySQL communication protocol, and it is easy to miss only by capturing packets.

The watcher obtains all connection data of the current database by periodically executing show processlist, and compensates for the missed user name information by comparing the Host field with the client ip port of the current package.

3. worker

Different workers are responsible for managing the life cycle of different database connections, and one worker manages multiple connections. By regularly comparing the current connection list of the worker with the connection list in the watcher, it can detect expired connections in time, close and release related resources, and prevent memory leaks.

4. connStream

The core logic of the entire data collection end is responsible for parsing TCP data packets according to the MySQL protocol and identifying specific SQL. One connection corresponds to one connStream Goroutine. Because SQL may contain sensitive data, connStream is also responsible for desensitizing SQL. The specific SQL identification strategy will not be expanded here due to security reasons.

5. sender

Responsible for data reporting logic, and report audit data parsed by connStream to log-agent through the thrift protocol.

3.2 Basic performance test

The performance of the packet capture library gopacket directly determines the upper limit of the system performance. In order to explore whether the problem lies in gopacket, we have written a simple tcp-client and tcp-server, and separately analyze the first three steps involved in the data flow diagram of gopacket (As shown in the figure below), the performance test was carried out. From the test result data below, the performance bottleneck is not in gopacket.

QPS	pcap buffer	loss rate	CPU utilization
100000	100MB	0%	144.9%

3.3 CPU Profile Analysis

The loss rate and CPU consumption are inseparable. In order to explore the reasons for such high CPU consumption, we used the pprof tool that comes with Go to analyze the CPU consumption of the process. From the calling functions of the flame graph below, we can summarize several big heads. : SQL desensitization, unpacking, GC and Goroutine scheduling. The following mainly introduces the optimization work done around them.

3.4 Desensitization analysis and improvement

Because SQL may contain sensitive information, rds-agent will desensitize each SQL for security reasons.

The desensitization operation uses the SQL parser of pingcap to template the SQL: that is, replace all the values in the SQL with "?" to achieve the purpose. This operation needs to parse the abstract syntax tree of the SQL, which is expensive. Currently, there is only a need to sample and capture specific SQL, and there is no need to desensitize each SQL in the parsing phase. The process is optimized here, desensitization is sinking to the reporting module, and only the samples that are finally sent out are desensitized.

The effect of this optimization is as follows:

Contrast	QPS	loss rate	CPU utilization
before improvement	50833.50	63.95%	601.39%
After improvement	51246.47	<font color=##FF0000>31.95%</font>	<font color=##FF0000>259.59%</font>

3.5 Scheduling Analysis and Improvement

From the data flow diagram below, it can be seen that the entire link is relatively long and is prone to performance bottlenecks. At the same time, there are many Goroutines running at high frequency (the red part). Due to the large number, Go needs to frequently switch between these Goroutines. Switching is undoubtedly a burden for CPU-intensive programs like us.

For this problem, we have made the following optimizations:

1. Shorten the link : modules such as shunting, worker, and parsing SQL are combined into a Goroutine parser.
2. Reduce the switching frequency : the parser takes the network protocol packet from the queue every 5ms, which is equivalent to manually triggering the switching. (5ms is also a compromise data after multiple tests, too small will consume more CPU, too large will cause data loss)

The effect of this optimization is as follows:

Contrast	QPS	loss rate	CPU utilization
before improvement	51246.47	31.95%	259.59%
After improvement	51229.54	<font color=##FF0000>0%</font>	<font color=##FF0000>206.87%</font>

3.6 Analysis and Improvement of Garbage Collection Pressure

The following figure shows the flame graph of the allocated pointer object after rds-agent captures packets for 30 seconds. It can be seen that more than 40 million objects have been allocated, and the GC pressure can be imagined. Regarding GC, we have learned the following two optimization schemes:

1. Pooling : Go's standard library provides a sync.Pool object pool, which can reduce object allocation by reusing objects, thereby reducing GC pressure.
2. Manual memory management : Apply for memory directly to the OS through the system call mmap, bypassing the GC, and implementing manual memory management.

However, option 2 is prone to memory leaks. From the perspective of stability, we finally chose scheme 1 to manage the pointer objects created in the high-frequency calling functions. The effects of this optimization are as follows:

Contrast	QPS	loss rate	CPU utilization
before improvement	51229.54	0%	206.87%
After improvement	51275.11	0%	<font color=##FF0000>153.32%</font>

3.7 Unpacking analysis and improvement

MySQL is based on the TCP protocol. During the function debugging process, we found many empty packets. It can be seen from the interaction diagram of MySQL client-server data below: when the client sends an SQL command, the server responds with the result. Due to the message confirmation mechanism of TCP, the client sends an empty ack packet to confirm the message. Moreover, the proportion of empty packets in the whole process is large, and they will penetrate into the parsing link, which is undoubtedly a burden for Goroutine scheduling and GC under high QPS.

The figure below is the unique format of the MySQL data packet. Through analysis, we observed the following characteristics:

1. A complete MySQL packet length >= 4Byte
2. The sequence id of the new command sent by the client is either 0 or 1

And pcap supports setting filtering rules, so that we can exclude empty packets at the kernel layer. The following are two filtering rules corresponding to the above characteristics:

 特点1： ip[2:2] - ((ip[0] & 0x0f) << 2) - ((tcp[12:1] & 0xf0) >> 2) >= 4
特点2： (dst host {localIP} and dst port 3306 and (tcp[(((tcp[12:1] & 0xf0) >> 2) + 3)] <= 0x01))

The effect of this optimization is as follows:

Contrast	QPS	loss rate	CPU utilization
before improvement	51275.11	0%	153.32%
After improvement	51246.02	0%	<font color=##FF0000>142.58%</font>

Based on the above experience, we refactored the functional code of the data collection end, and also made some other optimizations.

4 Final result

The following is the data comparison before and after optimization. The loss rate has dropped from a maximum of 60% to 0%, and the CPU consumption has dropped from a maximum of 6 cores to 1 core.

In order to explore the performance loss of MySQL by the packet capture function, we used Sysbench to do a performance comparison test. From the result data below, it can be seen that the function has a maximum loss of about 6% on MySQL's TPS, QPS and response time 99-line indicators.

5 Future plans

Although we have optimized the packet capture scheme, the performance loss is still too large for some delay-sensitive services, and the scheme has poor support for some special scenarios: for example, packet loss, retransmission, randomization at the TCP protocol layer When ordering, the MySQL protocol layer uses compression to transmit large SQL. The industry generally adopts the method of directly transforming the MySQL kernel to output the full amount of SQL, and also supports the output of more indicator data. At present, the database core team has also completed the development of this solution, and is in the process of replacing the online grayscale packet capture solution. In addition, we will continue to make up for the lack of end-to-end loss rate indicators for the full online SQL.

author of this article

Su Han, from Meituan Basic R&D Platform/Basic Technology Department/Database Technology Center.

Job Offers

Meituan Basic Technology Department - Database Technology Center is looking for senior and senior technical experts, Base Shanghai and Beijing. The Meituan relational database is large in scale and grows rapidly every year, carrying hundreds of billions of access traffic every day. Here, you can experience the business challenges of high concurrency, high availability, and high scalability, keep up with and develop cutting-edge technologies in the industry, and experience the productivity improvement brought by technological progress. Welcome to send your resume to: [e-mail] .

Read more collections of technical articles from the Meituan technical team

Frontend | Algorithm | Backend | Data | Security | O&M | iOS | Android | Testing

| Reply keywords such as [2021 stock], [2020 stock], [2019 stock], [2018 stock], [2017 stock] in the public account menu bar dialog box, you can view the collection of technical articles by the Meituan technical team over the years.

| This article is produced by Meituan technical team, and the copyright belongs to Meituan. Welcome to reprint or use the content of this article for non-commercial purposes such as sharing and communication, please indicate "The content is reproduced from the Meituan technical team". This article may not be reproduced or used commercially without permission. For any commercial activities, please send an email to [e-mail] to apply for authorization.