Collecting and Analyzing MySQL Function Information

This section describes two methods for collecting MySQL function information. The first method starts the MySQL server and collects specified function information by loading the SO library before the application starts. The second method collects hotspot function information from the mysqld process in Attach mode while the application is already running.

Launch Mode

Figure 1 Flowchart

Upload the tool package to the server.
Extract tracelib-collector-aarch64.tar.gz to the /home/collector directory.
1
tar -zxvf tracelib-collector-aarch64.tar.gz -C /home/collector
Prepare the configuration file (by creating it by yourself or using the symbol tool).
- Create the configuration file manually.
  For example, to collect functions named mysql_parse and do_command, run the nm mysqld | grep -e mysql_parse -e do_command command to confirm the mangling name.
  1 2 3 4
  # nm Output_result ...(Omitted) 0000000000bafa04 T _Z10do_commandP3THD 0000000000bada20 T _Z11mysql_parseP3THDP12Parser_state
  Create a configuration file in the current directory.
  1
  vi /home/collector/config
  Press i to enter the insert mode and type the symbol name that is found.
```
_Z10do_commandP3THD 
_Z11mysql_parseP3THDP12Parser_state
```
  Press Esc, type :wq!, and press Enter to save the file and exit.
- Create the configuration file using the symbol tool.
  For example, collect the function JOIN::extract_func_dependent_tables:
  1 2
  cd /home/collector ./symbol mysqld -e 'JOIN&extract_func_dependent_tables' -o ./config
  A configuration file named config is automatically generated in the /home/collector directory.

Set environment variables and start the mysqld process.

LD_LIBRARY_PATH=/home/collector:$LD_LIBRARY_PATH LD_PRELOAD=/home/collector/librecord.so RECORD_CONF=/home/collector/config mysqld -uroot

Use sysbench on another terminal to run a test.

sysbench ./tests/include/oltp_legacy/select.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=1 --report-interval=1 run

After the collection is complete, run the Ctrl+C or kill command to stop the mysqld process.
The collected data is saved in the /home/collector/record.data directory.

Use the analyze tool to analyze the collected data and save the analysis result to the statistics directory.

./analyze ./record.data/ -m --demangle -o ./statistics

Command output:

[info] Report saved to ./statistics/merged_summary.txt

View the summary.

1	cat ./statistics/merged_summary.txt

File content:

   JOIN::extract_func_dependent_tables()
     count: 144878.00
     avg: 10975.76
     max: 725110.00
     min: 3310.00
     P90: 13400.00
     P75: 10860.00
     P50: 8290.00

The summary report contains data such as the average time consumption, maximum time consumption, and P90 value of the function. You can find the corresponding function for analysis based on the collected data.

Attach mode

Figure 2 Flowchart

Upload the full tool installation package to the /home directory on the server.

Extract the package and go to the tool directory.

tar -zxvf DevKit-CLI-x.x.x-Linux-Kunpeng.tar.gz
cd DevKit-CLI-x.x.x-Linux-Kunpeng

Start the MySQL server on the current terminal.
1
mysqld -uroot &
The password is required.

Obtain the mysqld process ID.

ps -ef | grep mysqld

Command output:

root     3899932 2429790  0 19:05 pts/8    00:00:00 /application/mysql/bin/mysqld -uroot

The process ID is 3899932.

Collect the hotspot functions of the mysqld process on the current terminal by using the process ID.
1
./devkit advisor knet attach -p 3899932 -o ./record.data --hotspot
The collected data is generated in /home/DevKit-CLI-x.x.x-Linux-Kunpeng/record.data/.

Use sysbench on another terminal to run a 10-second test.

sysbench ./tests/include/oltp_legacy/select.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=10 --report-interval=1 run

After the collection is complete, stop the mysqld process.
1
kill 3899932

Use the analyze tool in the standalone tool package to analyze the data.

tar -zxvf tracelib-collector-aarch64.tar.gz -C /home/collector
cd /home/collector
./analyze /home/DevKit-CLI-x.x.x-Linux-Kunpeng/record.data -m --demangle -o ./statistics -n 20

Command output:

[info] Report saved to ./statistics/merged_summary.txt

View the analysis result file.

1	vim ./statistics/merged_summary.txt

You can view the call information of hotspot functions such as net_read_packet and vio_io_wait, and the average time consumption, maximum time consumption, and P99 value of the functions.

File content:

...(Omitted)
    net_read_packet(st_net*, unsigned long*)
      count: 455322.00
      avg: 13024.40
      max: 608010.00
      min: 4310.00
      P90: 14830.00
      P75: 13690.00
      P50: 12550.00
    vio_io_wait
      count: 3177.00
      avg: 11934.38
      max: 579860.00
      min: 2080.00
      P90: 26030.00
      P75: 13030.00
      P50: 4870.00
    __poll
      count: 3154.00
      avg: 9714.93
      max: 575420.00
      min: 1500.00
      P90: 23477.00
      P75: 10760.00
      P50: 2700.00
    net_read_raw_loop(st_net*, unsigned long)
      count: 905188.00
      avg: 4441.90
      max: 593240.00
      min: 2570.00
      P90: 5330.00
      P75: 4810.00
      P50: 4230.00
    vio_read
      count: 908952.00
      avg: 2435.08
      max: 590750.00
      min: 1350.00
      P90: 3180.00
      P75: 2890.00
      P50: 2410.00

Parent topic: Link Latency Detection