Mysqlslap stress test mysqlSLAP

Mysqlslap is a pressure test tool that simulates multiple clients making simultaneous requests to the server.

• All the conclusions of this paper are derived from the author’s practice. Thank you for correcting any mistakes you have made.
• 【MySQL】 mysqlSLAP — juejin slap
• The article is still unfinished and will be improved gradually

Command options:

• --concurrencyConcurrency number, multiple use.Separated.
• --enginesUse multiple engines to test.Separated.
• --iterationsNumber of tests.
• --auto-generate-sqlTest with the SQL scripts generated by the system itself.
• --auto-generate-sql-add-autoincrementUse self-increment fields.
• --auto-generate-sql-load-typeTest modes (read,write, Update,mixed).
• --number-of-queriesTotal number of queries.
• --debug-infoTo output additional CPU and memory information, enable the Debug mode.
• --number-int-colsTest table int number of columns.
• --number-char-colsChar number of columns in table char.
• --create-schemaTest db.
• --queryUse your own SQL scripts to perform the tests.
• --no-defaultsDisable default Settings.
• --only-printOnly query statements are printed.

You can use –only-print to see what the automatically generated SQL looks like, and if you’re not satisfied you can use your own SQL script.

Single-machine read concurrent test

The MySQL server in the native WSL2-based Docker is used here, so the performance will be worse than the host running MySQL.

• 100 concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=100 --number-of-queries=2000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 10.662 seconds
          Minimum number of seconds to run all queries: 10.141 seconds
          Maximum number of seconds to run all queries: 11.484 seconds
          Number of clients running queries: 100
          Average number of queries per client: 20
  Copy the code

• 500 concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=500 --number-of-queries=10000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 51.981 seconds
          Minimum number of seconds to run all queries: 51.454 seconds
          Maximum number of seconds to run all queries: 52.500 seconds
          Number of clients running queries: 500
          Average number of queries per client: 20
  Copy the code

• 1000 concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=1000 --number-of-queries=20000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 102.453 seconds
          Minimum number of seconds to run all queries: 102.016 seconds
          Maximum number of seconds to run all queries: 102.797 seconds
          Number of clients running queries: 1000
          Average number of queries per client: 20
  Copy the code

• 1500 concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=1500 --number-of-queries=30000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 157.340 seconds
          Minimum number of seconds to run all queries: 155.532 seconds
          Maximum number of seconds to run all queries: 163.485 seconds
          Number of clients running queries: 1500
          Average number of queries per client: 20
  Copy the code

• 2000 concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=2000 --number-of-queries=40000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 213.437 seconds
          Minimum number of seconds to run all queries: 208.406 seconds
          Maximum number of seconds to run all queries: 222.453 seconds
          Number of clients running queries: 2000
          Average number of queries per client: 20
  Copy the code

The performance of each concurrency degree is as follows:

Max_connections sets the maximum number of MySQL connections to 16384. Therefore, the maximum number of concurrent MySQL connections on a single server is 16384. The Docker I was running crashed at 8000.

Single-node write concurrent test

• 100 Insert concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=100 --number-of-queries=2000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 2.034 seconds
          Minimum number of seconds to run all queries: 1.969 seconds
          Maximum number of seconds to run all queries: 2.094 seconds
          Number of clients running queries: 100
          Average number of queries per client: 20
  Copy the code

• 500 Insert concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=500 --number-of-queries=10000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 6.562 seconds
          Minimum number of seconds to run all queries: 6.297 seconds
          Maximum number of seconds to run all queries: 6.672 seconds
          Number of clients running queries: 500
          Average number of queries per client: 20
  Copy the code

• 1000 Insert concurrent:

  mysqlslap --no-defaults --iterations=5 --concurrency=1000 --number-of-queries=20000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 12.643 seconds
          Minimum number of seconds to run all queries: 12.172 seconds
          Maximum number of seconds to run all queries: 13.875 seconds
          Number of clients running queries: 1000
          Average number of queries per client: 20
  Copy the code

• 1500 Insert concurrency:

  mysqlslap --no-defaults --iterations=5 --concurrency=1500 --number-of-queries=30000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 20.003 seconds
          Minimum number of seconds to run all queries: 19.781 seconds
          Maximum number of seconds to run all queries: 20.328 seconds
          Number of clients running queries: 1500
          Average number of queries per client: 20
  Copy the code

• 2000 Insert concurrency:

  mysqlslap --no-defaults --iterations=5 --concurrency=2000 --number-of-queries=40000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456
  mysqlslap: [Warning] Using a password on the command line interface can be insecure.
  Benchmark
          Average number of seconds to run all queries: 35.859 seconds
          Minimum number of seconds to run all queries: 30.281 seconds
          Maximum number of seconds to run all queries: 43.938 seconds
          Number of clients running queries: 2000
          Average number of queries per client: 20
  Copy the code

The performance of each concurrency degree is as follows: