Inconsistent Connection Status Between Server and Client When Sysbench Is Used to Test MySQL

Symptom

Sysbench is used to test MySQL. When the value of num-threads in the stress test is greater than 500, the connection status of the server and client may be inconsistent, and many processes in the TIME_WAIT state exist on the server.

Key Process and Cause Analysis

Resources are used up in the continuous high-concurrency scenario.

Conclusion and Solution

1. Modify the maximum number of connections in the MySQL configuration by adding the following content under the [mysqld] field in the /etc/my.cnf file:

   max_connections=2000

2. Adjust network parameters.
   1. Run the following command to modify the maximum number of clients that can receive SYN (synchronization) packets:

      echo 8192 >/proc/sys/net/ipv4/tcp_max_syn_backlog

   2. Run the following command to change the maximum number of clients whose data can be processed by the server.

      echo 1024 >/proc/sys/net/core/somaxconn

3. Open the /etc/security/limits.conf configuration file that limits resource usage to configure the maximum numbers of files and processes that can be opened. Change the values of the following parameters to 65535:

   * soft nofile 65535
   * hard nofile 65535
   * soft nproc 65535
   * hard nproc 65535

4. Modify the /etc/security/limits.d/90-nproc.conf file and set nproc (number of processes) to 65535.

   * soft nproc 65535

5. Run the sysbench test command again.

   ./src/sysbench --num-threads=500 --test=./tests/include/oltp_legacy/update_non_index.lua --oltp-table-size=2180000 --oltp-tables-count=10 --percentile=99 --report-interval=1 --mysql-host=172.253.33.194 --mysql-user=szjr --mysql-password=szjr --mysql-port=3306  --mysql-db=summertest --max-time=300 --max-requests=0  run