mysqlslap

mysqlslap는 Mysql 서버에 간단히 부하를 줄 수 있는 유틸리티입니다.

 

아래와 같은 명령어로 사용가능합니다.

mysqlslap [option]

옵션 중 --create or --query는 SQL문법을 포함한 스트링 문자열이거나 파일입니다.

만약 특정 파일일경우 그 안에는 sql문법이 라인으로 되어 있어야합니다.

--delimeter 옵션을 사용하려면 특정 문자를 지정해서 구분자를 사용해줘야 합니다.

파일안에는 주석없이 순수 내용만 있어야 합니다.

3가지 스텝으로 진행됩니다.

1.스키마,테이블 그리고 선택사항으로 프로시저나 테스트에 필요한 데이터를 만듭니다. 이때 하나의 클라이언트를 사용합니다.

2.부하테스트를 진행합니다. 이때 많은 클라이언트가 접속을 합니다.

3.정리단계에서 하나의 접속만 남기고 접속종료와 테이블 삭제를 합니다.

 

아래는  mysqlslap 뒤에 올 수 있는 옵션들에 대한 설명입니다.

mysqlslap --help

mysqlslap  Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Copyright (c) 2005, 2020, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Run a query multiple times against the server.

 

Usage: mysqlslap [OPTIONS]

 

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

The following groups are read: mysqlslap client

The following options may be given as the first argument:

--print-defaults        Print the program argument list and exit.

--no-defaults           Don't read default options from any option file,

                        except for login file.

--defaults-file=#       Only read default options from the given file #.

--defaults-extra-file=# Read this file after the global files are read.

--defaults-group-suffix=#

                        Also read groups with concat(group, suffix)

--login-path=#          Read this path from the login file.

  -?, --help          Display this help and exit.

  -a, --auto-generate-sql

                      Generate SQL where not supplied by file or command line.

  --auto-generate-sql-add-autoincrement

                      Add an AUTO_INCREMENT column to auto-generated tables.

  --auto-generate-sql-execute-number=#

                      Set this number to generate a set number of queries to

                      run.

  --auto-generate-sql-guid-primary

                      Add GUID based primary keys to auto-generated tables.

  --auto-generate-sql-load-type=name

                      Specify test load type: mixed, update, write, key, or

                      read; default is mixed.

  --auto-generate-sql-secondary-indexes=#

                      Number of secondary indexes to add to auto-generated

                      tables.

  --auto-generate-sql-unique-query-number=#

                      Number of unique queries to generate for automatic tests.

  --auto-generate-sql-unique-write-number=#

                      Number of unique queries to generate for

                      auto-generate-sql-write-number.

  --auto-generate-sql-write-number=#

                      Number of row inserts to perform for each thread (default

                      is 100).

  --commit=#          Commit records every X number of statements.

  -C, --compress      Use compression in server/client protocol.

  -c, --concurrency=name

                      Number of clients to simulate for query to run.

  --create=name       File or string to use create tables.

  --create-schema=name

                      Schema to run tests in.

  --csv[=name]        Generate CSV output to named file or to stdout if no file

                      is named.

  -#, --debug[=#]     This is a non-debug version. Catch this and exit.

  --debug-check       This is a non-debug version. Catch this and exit.

  -T, --debug-info    This is a non-debug version. Catch this and exit.

  --default-auth=name Default authentication client-side plugin to use.

  -F, --delimiter=name

                      Delimiter to use in SQL statements supplied in file or

                      command line.

  --detach=#          Detach (close and reopen) connections after X number of

                      requests.

  --enable-cleartext-plugin

                      Enable/disable the clear text authentication plugin.

  -e, --engine=name   Storage engine to use for creating the table.

  -h, --host=name     Connect to host.

  -i, --iterations=#  Number of times to run the tests.

  --no-drop           Do not drop the schema after the test.

  -x, --number-char-cols=name

                      Number of VARCHAR columns to create in table if

                      specifying --auto-generate-sql.

  -y, --number-int-cols=name

                      Number of INT columns to create in table if specifying

                      --auto-generate-sql.

  --number-of-queries=#

                      Limit each client to this number of queries (this is not

                      exact).

  --only-print        Do not connect to the databases, but instead print out

                      what would have been done.

  -p, --password[=name]

                      Password to use when connecting to server. If password is

                      not given it's asked from the tty.

  --plugin-dir=name   Directory for client-side plugins.

  -P, --port=#        Port number to use for connection.

  --post-query=name   Query to run or file containing query to execute after

                      tests have completed.

  --post-system=name  system() string to execute after tests have completed.

  --pre-query=name    Query to run or file containing query to execute before

                      running tests.

  --pre-system=name   system() string to execute before running tests.

  --protocol=name     The protocol to use for connection (tcp, socket, pipe,

                      memory).

  -q, --query=name    Query to run or file containing query to run.

  -s, --silent        Run program in silent mode - no output.

  -S, --socket=name   The socket file to use for connection.

  --sql-mode=name     Specify sql-mode to run mysqlslap tool.

  --server-public-key-path=name

                      File path to the server public RSA key in PEM format.

  --get-server-public-key

                      Get server public key

  --ssl-mode=name     SSL connection mode.

  --ssl-ca=name       CA file in PEM format.

  --ssl-capath=name   CA directory.

  --ssl-cert=name     X509 cert in PEM format.

  --ssl-cipher=name   SSL cipher to use.

  --ssl-key=name      X509 key in PEM format.

  --ssl-crl=name      Certificate revocation list.

  --ssl-crlpath=name  Certificate revocation list path.

  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,

                      TLSv1.2, TLSv1.3

  --ssl-fips-mode=name

                      SSL FIPS mode (applies only for OpenSSL); permitted

                      values are: OFF, ON, STRICT

  --tls-ciphersuites=name

                      TLS v1.3 cipher to use.

  -u, --user=name     User for login if not current user.

  -v, --verbose       More verbose output; you can use this multiple times to

                      get even more verbose output.

  -V, --version       Output version information and exit.

  --compression-algorithms=name

                      Use compression algorithm in server/client protocol.

                      Valid values are any combination of

                      'zstd','zlib','uncompressed'.

  --zstd-compression-level=#

                      Use this compression level in the client/server protocol,

                      in case --compression-algorithms=zstd. Valid range is

                      between 1 and 22, inclusive. Default is 3.

 

Example1)

50개의 클라이언트가  200번의 쿼리를 수행하는 예시입니다.

mysqlslap --delimiter=";"
 --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)"
 --query="SELECT * FROM a" --concurrency=50 --iterations=200

 

실제 수행 테스트입니다.

테이블을 생성하고 23이라는 데이터를 삽입 후 조회하는 mysqlslap입니다. 완료 후 테스트 했던 a 테이블은 자동으로 삭제해주기 때문에 조회가 되지 않는 모습입니다.

 

//mysqlslap 명령어 수행
mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200 -uroot -p
Enter password:
Benchmark
      Average number of seconds to run all queries: 0.026 seconds
      Minimum number of seconds to run all queries: 0.022 seconds
      Maximum number of seconds to run all queries: 0.084 seconds
      Number of clients running queries: 50
      Average number of queries per client: 1
      
      
      


//DB에서 테이블 존재하는지 확인
mysql> select * from information_schema.tables
    -> where table_name='a';
Empty set (0.00 sec)

 

Example2)

아래의 예제는 int컬럼이 2개, varchar컬럼이 3개인 테이블을 생성한 뒤, 5개의 클라이언트가 20번씩 조회를 하는 예제입니다. 이 경우에는 이미 테스트 데이터와 테이블이 생성되어있다고 가정하였기 때문에 생성문과 삽입문이 옵션에 들어가있지 않습니다.

mysqlslap --concurrency=5 --iterations=20
--number-int-cols=2 --number-char-cols=3
--auto-generate-sql

 

실제 수행테스트입니다.

 mysqlslap --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql -uroot -p
Enter password:
Benchmark
      Average number of seconds to run all queries: 0.046 seconds
      Minimum number of seconds to run all queries: 0.040 seconds
      Maximum number of seconds to run all queries: 0.065 seconds
      Number of clients running queries: 5
      Average number of queries per client: 0

 

 

출처 : Mysql 8.0 Reference Manual , mysqldba.tistory.com/93