MySQL pressure test tool mysqlslap introduction and use, mysql pressure test mysqlslap

MySQL pressure test tool mysqlslap introduction and use, mysql pressure test mysqlslap

1. Introduction to Mysqlslap

Mysqlslap is a benchmark testing tool provided after MySQL5.1. It is similar to the Apache benchmark load generation tool, which generates a schema, loads data, executes benckmark and queries data. The syntax is simple, flexible, and easy to use. This tool can simulate multiple clients to concurrently send query updates to the server, and provides performance test data and Performance Comparison of multiple engines. Mysqlslap provides an intuitive verification basis before and after mysql performance optimization. The author suggests that system O & M personnel should master some common stress testing tools, in this way, the upper limit of user traffic supported by the online system and its adequacy can be grasped more accurately.

Ii. Parameter Introduction

-- Auto-generate-SQL-a automatically generates MySQL test statement -- auto-generate-SQL-add-autoincrement add AUTO_INCREMENT field -- auto-generate-SQL-execute-number = # Query count -- auto-generate-SQL-guid-primary add GUID field -- auto-generate-SQL-load-type = name mixed: hybrid, update: update, write: write, key: read primary key, read: query -- auto-generate-SQL-secondary-indexes = # set the number of index fields -- auto-generate-SQL-unique-query-number = # generate N Different query SQL statements -- auto-generate-SQL-unique-write-number = # generate N Different write SQL statements -- auto-generate-SQL-write-number = # number of write SQL statements per Thread -- commit = # set the number of SQL statements to be submitted each time -- compress-C enable the compression protocol -- concurrency = #-c client count (concurrency) -- create = name test SQL statement or file -- create-schema = name test data name -- csv = name production CSV format data file -- debug-# Write debugging log (windows unavailable) for example, mysqlslap-a-# "d: t: o, c:/debug.txt" -- print debugging information after debug-check test (windows unavailable) -- debug-info-T prints information about the memory and CPU -- default-auth = plugin verification plug-in -- delimiter = str-F statement delimiter is by default; -- detach = # Disconnect and reconnect after N statements are executed -- enable-cleartext-plugin enables the plaintext authentication plug-in -- engine = engine_name-e test engine, for example: mysqlslap-e "myisam" or-e "myisam, innodb "-- help information -- host = host_name-h Database host -- iterations = #-I number of repeated runs -- login-path = name login configuration file -- no-drop after the test delete schema -- number-char-cols = #-x set the number of VARCHAR fields -- number-int-cols = #-y set the number of INT fields -- number-of-queries = # number of SQL statements run on each client -- only-print only displays the SQL statements that will be run -- the delimiter option affects -- password = password-p Database password -- pipe enable pipeline -- plugin-dir = path plug-in directory -- port = port_num-P database port -- post-query = SQL statement executed after value test -- post-system = system statement executed after str test -- pre-query = value SQL statement executed before the test -- pre-system = str system statement executed before the test -- protocol = type connection protocol tcp, the SQL statements for the socket, pipe -- query = value-q test are as follows: mysqlslap -- query = "SELECT * FROM t1" -- secure-auth do not send password to server -- silent-s do not show test (mute test) -- socket = path-S connect to the server's socket -- ssl-ca = file_name -- ssl-capath = dir_name -- ssl-cert = file_name -- ssl-cipher = cipher_list -- ssl-crl = file_name -- ssl-crlpath = dir_name -- ssl-key = file_name -- ssl-verify-server-cert -- user = user_name-u database username -- verbose-v output more information -- version- V version information

Iii. Simple Example

1. Test 100, 200, and 400 concurrent SQL statements respectively.

mysqlslap -uroot -proot -h192.168.10.43 -P3306 --create-schema=chenmh --delimiter=";" --query="call pro_auto_select_pri" --concurrency=100,200,400 --number-of-queries=100000 

Note: The report output time is mainly reflected in the time spent in executing the query specified by-number-of-queries, here are the time spent on 100 concurrent calls, 200 concurrent calls, and 400 concurrent calls. By default, if-iterations is not specified, the operation is repeated. If N is repeated, the output time also reflects the time spent in a single loop.

2. Generate csv

mysqlslap -a -e"myisam,innodb" --csv="c:/a.csv"

Iv. Summary

Mysqlslap mainly tests database performance and has limited automatic testing functions, and the production reports are not detailed enough. Therefore, we recommend that you use sysbench for Benchmark Testing. The above is all about mysqlslap. I hope to help you in your study or work. If you have any questions, please leave a message.