Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use mysqlslap, a stress testing tool for mysql database. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Mysqlslap is an official stress testing tool that has been provided since version 5.1.4 of MySQL. It simulates multiple concurrent clients to access MySQL concurrently to perform the stress test, provides a more detailed SQL execution data performance report, and can well compare the performance differences of multiple storage engines (MyISAM,InnoDB, etc.) under the same concurrent pressure in the same environment.

Mysqlslap official document: https://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

Table 4.14 mysqlslap Options

FormatDescriptionIntroduced--auto-generate-sqlGenerate SQL statements automatically when they are not supplied in files or using command options--auto-generate-sql-add-autoincrementAdd AUTO_INCREMENT column to automatically generated tables--auto-generate-sql-execute-numberSpecify how many queries to generate automatically--auto-generate-sql-guid-primaryAdd a GUID-based primary key to automatically generated tables--auto-generate-sql-load-typeSpecify the test load type--auto-generate-sql-secondary-indexesSpecify how many secondary indexes to add to automatically generated tables--auto-generate -sql-unique-query-numberHow many different queries to generate for automatic tests.--auto-generate-sql-unique-write-numberHow many different queries to generate for-- auto-generate-sql-write-number--auto-generate-sql-write-numberHow many row inserts to perform on each thread--commitHow many statements to execute before committing.--compressCompress all information sent between client and server--concurrencyNumber of clients to simulate when issuing the SELECT statement--createFile or string containing the statement to use for creating the table--create-schemaSchema in which to Run the tests--csvGenerate output in comma-separated values format--debugWrite debugging log--debug-checkPrint debugging information when program exits--debug-infoPrint debugging information Memory And CPU statistics when program exits--default-authAuthentication plugin to use5.6.2--defaults-extra-fileRead named option file in addition to usual option files--defaults-fileRead only named option file--defaults-group-suffixOption group suffix value--delimiterDelimiter to use in SQL statements--detachDetach (close and reopen) each connection after each N statements--enable-cleartext-pluginEnable cleartext authentication plugin5.6.7--engineStorage engine to use for creating the table--helpDisplay help message and exit--hostConnect to MySQL server on given host--iterationsNumber of times To run the tests--login-pathRead login path options from. Mylogin.cnf5.6.6-no-defaultsRead no option files--no-dropDo not drop any schema created during the test run5.6.3--number-char-colsNumber of VARCHAR columns to use if-- auto-generate-sql is specified--number-int-colsNumber of INT columns to use if-- auto-generate-sql is specified--number-of-queriesLimit each client to approximately this number of queries--only-printDo not connect to databases. Mysqlslap only prints what it would have done--passwordPassword to use when connecting to server--pipeOn Windows Connect to server using named pipe--plugin-dirDirectory where plugins are installed5.6.2--portTCP/IP port number to use for connection--post-queryFile or string containing the statement to execute after the tests have completed--post-systemString to execute using system () after the tests have completed--pre-queryFile or string containing the statement to execute before running the tests--pre-systemString to execute using system () before running the tests--print-defaultsPrint default options--protocolConnection protocol to use--queryFile or string containing the SELECT statement to use for retrieving Data--secure-authDo not send passwords to server in old (pre-4.1) format5.6.17--shared-memory-base-nameThe name of shared memory to use for shared-memory connections--silentSilent mode--socketFor connections to localhost The Unix socket file to use--sslEnable secure connection--ssl-caPath of file that contains list of trusted SSL CAs--ssl-capathPath of directory that contains trusted SSL CA certificates in PEM format--ssl-certPath of file that contains X509 certificate in PEM format--ssl-cipherList of permitted ciphers to use for connection encryption--ssl-crlPath of file that contains certificate revocation lists5.6.3--ssl-crlpathPath of directory that contains certificate revocation list files5.6.3--ssl-keyPath of file that contains X509 key in PEM format--ssl-modeSecurity State of connection to server5.6.30--ssl-verify-server-certVerify server certificate Common Name value against host name used when connecting to server--userMySQL user name to use when connecting to server--verboseVerbose mode--versionDisplay version information and exit parameter reference table:-- host=host_name -h hostname (or IP address) of the MySQL server to which host_name is connected The default is native localhost--user=user_name, the user name u user_name uses when connecting to the MySQL service-- password [= password],-p [password] the password used to connect to the MySQL service-- create-schema represents the custom test library name, and the schema in the tested schema,MySQL is database. (you may encounter an error mysqlslap: Error when connecting to server: 1049 Unknown database 'mysqlslap' when you do not specify which database to use)-- query=name,-q uses custom scripts to execute tests (which can be SQL strings or scripts), such as calling a custom stored procedure or sql statement to execute the test. -- the SQL (which can be a SQL string or script) required by create to create the table-- concurrency=N, and-c N represents the concurrency, that is, how many clients are simulated to execute query simultaneously. Multiple values can be specified with a comma or the value specified by the-- delimiter parameter as the delimiter. For example:-- concurrency=100200500 (100,200,500 concurrency respectively). The iterations of iterations=N,-I N test execution represent how many times you want to run the test in different concurrency environments, and run it many times to make the results more accurate. -- the total number of number-of-queries=N test queries (concurrent customers × queries per customer)-- engine=engine_name,-e engine_name represents the engine to be tested. There can be multiple, separated by delimiters. For example:-- engine=myisam,innodb,memory. -- auto-generate-sql,-an automatically generates test tables and data, indicating the concurrent stress of testing with the SQL script generated by the mysqlslap tool itself. -- the type of auto-generate-sql-load-type=type test statement. Represents whether the environment to be tested is a read or write operation or a mixture of both. Values include: read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects). The default value is mixed. -- auto-generate-sql-add-auto-increment represents the automatic addition of auto_increment columns to the generated table, which is supported from version 5.1.18. -- number-char-cols=N,-x N automatically generated test table contains how many character type columns, default 1-- number-int-cols=N,-y N automatically generated test table contains how many numeric type columns, default 1-- commint=N how many DML and then submit. -- compress,-C if both the server and client support compression, the compression information is passed. -- only-print only prints test statements and does not actually execute them. -- detach=N disconnects the reconnection after executing N statements. -- debug-info,-T prints information about memory and CPU. There are three steps to run mysqlslap:

1. Create schema, table, test data, etc. (in MySQL, schema is database)

two。 Run load tests that can use multiple concurrent client connections

3. Test environment cleanup (delete created data, tables, etc.). Case study:

[root@mysql] # mysqlslap-a-concurrency=50100-number-of-queries 1000-auto-generate-sql-add-autoincrement-number-char-cols=10-number-int-cols=5-poracle

Warning: Using a password on the command line interface can be insecure.

Benchmark

Average number of seconds to run all queries: 0.775 seconds

Minimum number of seconds to run all queries: 0.775 seconds

Maximum number of seconds to run all queries: 0.775 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Average number of seconds to run all queries: 0.984 seconds

Minimum number of seconds to run all queries: 0.984 seconds

Maximum number of seconds to run all queries: 0.984 seconds

Number of clients running queries: 100

Average number of queries per client: 10

[root@mysql] # mysqlslap-a-concurrency=50100-number-of-queries 1000-auto-generate-sql-add-autoincrement-number-char-cols=10-number-int-cols=5-engine=myisam,innodb-poracle

Warning: Using a password on the command line interface can be insecure.

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.117 seconds

Minimum number of seconds to run all queries: 0.117 seconds

Maximum number of seconds to run all queries: 0.117 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.129 seconds

Minimum number of seconds to run all queries: 0.129 seconds

Maximum number of seconds to run all queries: 0.129 seconds

Number of clients running queries: 100

Average number of queries per client: 10

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.665 seconds

Minimum number of seconds to run all queries: 1.665 seconds

Maximum number of seconds to run all queries: 1.665 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 1.480 seconds

Minimum number of seconds to run all queries: 1.480 seconds

Maximum number of seconds to run all queries: 1.480 seconds

Number of clients running queries: 100

Average number of queries per client: 10

This is the end of the article on "how to use mysqlslap, a stress testing tool for mysql database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.