"Original" MySQL replay online flow pressure measurement tool

A Background

Last year, a MySQL trace replay test, due to the performance schema itself to collect sample length and other limitations, the actual playback of the success rate is relatively low.

Recently found an open source tool, based on tcpcopy implementation of online traffic simulation test, this open source tool is NetEase's Wang bin development, many of the company's simulation on-line test is based on tcpcopy implementation.

Https://github.com/session-replay-tools/mysql-replay-module

1. Implementation principle

Deployment of Tcpcopy on the production server, packet capture is to add a bypass processing at the data link layer, use the socket to fetch packets on the wire at the network layer, process them, and use the socket to send packets to the test server.

The Intercept transmits the response information to tcpcopy. By crawling the response packet, intercept extracts the response header information and sends the response header information to tcpcopy using a special channel.

When Tcpcopy receives the response header information, it uses the header information to modify the properties of the online packager and continue to send another package. It should be noted that the response from the test server is routed to the secondary server acting as a black hole.

2. Usage Scenarios

Real-world traffic on the copy line for stress testing, available for new MySQL5.7 or MySQL8.0, MySQL bug detection, capacity planning, etc.

3. Playback mode

The tcpcopy-based MySQL replay tool has two playback modes that support online capture playback and offline playback. Taking into account the impact on the production server as small as possible, focus on testing the offline playback mode.

Two Deployment 1. Environment preparation

To deploy offline playback, you need to prepare the following resources to test your environment's IP as an example:

Production server: 192.168.137.110

Analog production server: 192.168.137.100

Test target server: 192.168.137.102

Secondary server: 192.168.137.101

Masquerading client ip:192.168.166.166

2. Analog production server installation offline Tcpcopy module

git clone git://github.com/session-replay-tools/tcpcopy.git

CD tcpcopy

git clone git://github.com/session-replay-tools/mysql-sgt-replay-module.git

./configure--offline--set-protocol-module=mysql-sgt-replay-module

Make

Make install

3. Secondary server installation offline intercept module

git clone git://github.com/session-replay-tools/intercept.git

CD Intercept

./configure

Make

Make install

If there is an error, you may need to install the following package

Yum Install Libpcap-devel

Three Production crawl network packet and playback offline 1. Production server uses tcpdump to crawl network packets

Tcpdump-i eth1-s 0-c TCP and Port 3306-w/data/offline.pcap

2. Test target server Add static route

1. Test target server restore DB corresponding to production server

2. Test target server starts in Skip-grant-tables mode

/usr/bin/mysqld_safe--datadir=/var/lib/mysql--socket=/var/lib/mysql/mysql.sock--pid-file=/var/run/mysqld/ Mysqld.pid--basedir=/usr--user=mysql--skip-grant-tables&

3. adding static routes

Route add-host 192.168.166.166 GW 192.168.137.101

Note: Host is a spoofed client IP,GW is the IP of the secondary server

3. Secondary server running Intercept

/usr/local/intercept/sbin/intercept-i eth2-f ' TCP and SRC Port 3306 '-D

4. Simulate production server offline playback

1. Copy the network package files crawled on the production server to the analog production server/tmp/offline.pcap

2. Running Tcpcopy parsing an offline network packet, forwarding a network packet to the test target server

/usr/local/tcpcopy/sbin/tcpcopy-i/tmp/offline.pcap-x 3306-192.168.137.102:3306-s 192.168.137.101-c 192.168.166.166-n 1

There are several options to run the Tcpcopy client:

#全流量复制

/usr/local/tcpcopy/sbin/tcpcopy-i/tmp/offline.pcap-x 3306-192.168.137.102:3306-s 192.168.137.101-c 192.168.166.166–d

#复制20% Flow

/usr/local/tcpcopy/sbin/tcpcopy-i/tmp/offline.pcap-x 3306-192.168.137.102:3306-s 192.168.137.101-c 192.168.166.166–r 20-d

#放大2倍流量

/usr/local/tcpcopy/sbin/tcpcopy-i/tmp/offline.pcap-x 3306-192.168.137.102:3306-s 192.168.137.101-c 192.168.166.166–n 2-d

5. Monitor playback status

You can see the playback situation by turning on the Performance_schema.events_statements_history_long function.

Update performance_schema.setup_consumers set enabled= ' YES ' where name= ' events_statements_history_long ';

SELECT * FROM Performance_schema.events_statements_history_long limit 10\g

Four The problems encountered

1. Production environment when grasping the package, for short connection, can catch MySQL login information, can play normal playback, for the long connection, because the lack of the first authentication login check information, can not play back, you may wait with the establishment of new connections, complete session more and more, traffic will gradually increase.

2. It is generally not recommended to re-establish the connection by restarting the online business, kill long connection.

"Original" MySQL replay online flow pressure measurement tool