MySQL Database benchmark stress test tool MYSQLSLAP Use example

Source: Internet
Author: User
Tags benchmark mixed mysql in switches time 0

Mysqlslap Introduction Mysqlslap is MySQL5.1 after the benchmark benchmark tool, similar to Apache bench load generation tool, generate schema, load data, execute Benckmark and query data, simple syntax, flexible , easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server, giving performance test data and providing performance comparisons for multiple engines. Mysqlslap for MySQL performance optimization before and after providing an intuitive verification basis, the author suggested that the system operators should master some common pressure testing tools, so as to more accurately grasp the online system can support the user flow limit and the pressure resistance and so on.  www.2cto.com   Method Introduction You can use the Mysqlslap--help to show how to use: 1)--concurrency represents the number of concurrent, multiple can be separated by commas, for example: concurrency= 10,50,100, the number of concurrent connection threads is 10, 50, and 100 concurrency respectively. 2)--engines represents the engine to be tested, can have multiple, separated by separators. 3)--iterations represents how many times to run these tests. 4)--auto-generate-sql represents the SQL script generated by the system itself. 5) Whether the--auto-generate-sql-load-type represents a read or write or a mix of both (read,write,update,mixed) 6)--number-of-queries represents the total number of queries to run. The number of queries that are run per client can be calculated using the total number of queries/concurrency. 7)--debug-info represents the additional output of CPU and memory related information. 8)--number-int-cols: Number of int fields to create test table 9)--auto-generate-sql-add-autoincrement: Represents the automatic addition of auto_increment columns to the generated table. Starting from version 5.1.18)--number-char-cols The number of char fields that created the test table. One) the--create-schema test Schema,mysql in the schema is the database. --query   Perform tests using custom scripts, such as a custom stored procedure or SQL statement that can be invoked to perform the test. --only-print If you just want to print a look at what the SQL statement is, you can use this option. Three, demo example below we use several demo instances to test 1, demo1:[[email protected] ~]# mysqlslap-uroot-p123abc--concurrency=100-- Iterations=1--auto-generate-sql--auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement-- Engine=myisam--number-of-queries=10--debug-info# Note This test takes 100 concurrent threads, tests 1 times, automatically generates SQL test scripts, reads, writes, updates hybrid tests, self-growing fields, test engine for MyISAM, Run a total of 10 queries, the output CPU resource information return information as follows: Rement--engine=myisam--number-of-queries=10--debug-infobenchmark        Running for engine myisam        Average number of seconds to run all queries:0.129 SECONDS&NB Sp       Minimum number of seconds to run all queries:0.107 seconds        Maximum number of seconds to run all queries:0.264 seconds        number of clients running queries:100  &nbs P     Average number of queries per Client:0user time 0.16, System time 0.25Maximum resident set size 4624, Integ RAL resident set Size 0non-physical pagefaults 7346, physical PageFaults 0, Swaps 0Blocks in 0 off 0, Messages in 0 out 0, signals 0Voluntary context switches 27221, involuntary conte XT Switches 42412, Demo2: Specifying database and SQL statements mysqlslap-h192.168.202.84-p3309--concurrency=100--iterations=1--create-schema = ' MMS_SDMTV '--query= ' select * from role; '--number-of-queries=10--debug-info-uroot-p123abc# Note Use MYSQLSLAP to specify SQL statements for testing 3, DEMO3: Test Cases [[email protected]/]# mysqlslap--concurrency=50,100,200--iterations=20 --number-int-cols=4--number-char-cols=35--auto-generate-sql--auto-generate-sql-add-autoincrement-- Auto-generate-sql-load-type=read--engine=myisam,innodb--number-of-queries=200--verbose--socket=/var/lib/mysql/ mysql.sock-uroot-p123abc# System script test, add int type 4 column char 35 column, Test 2 engine Myisam,innodb read performance, 50,100,200 client to test the server for a total of 200 query statements Perform 20 queries benchmark        Running for engine myisam        Average number of seconds To run all queries:0.666 seconds        Minimum number of seconds to run all Queries:0.223 seconds        Maximum number of seconds to run all queries:4.889 seconds        Number of clients running queries:50        Average number of queries per Client:4benc hmark        Running for engine myisam        Average number of seconds-run all queries:0.620 seconds        Minimum number of seconds to run all queries:0.231 seconds  &nbsp ;     Maximum number of seconds to run all queries:4.898 seconds        number of clients run Ning queries:100        Average number of queries per client:2benchmark        R Unning for engine myisam        Average number of seconds to run all queries:0.503 seconds  &nbs P     Minimum number of seconds to run all queries:0.257 seconds        Maximum number of sec Onds to run all queries:4.269 seconds        number of clients running queries:200        Average N Umber of queries per client:1benchmark        Running for engine innodb        AV Erage number of seconds to run all queries:1.049 seconds        Minimum number of seconds to run all queries:0.244 seconds        Maximum number of seconds to run all queries:5.292 seconds        Number of clients running queries:50        Average number of queries per Client:4ben chmark        Running for engine innodb        Average number of seconds to run Al L queries:0.712 seconds        Minimum number of seconds to run all queries:0.246 seconds  &nbs P     Maximum number of seconds to run all queries:6.585 seconds        number of clients run Ning Queries:100&nbsP       Average number of queries per client:2benchmark        Running for engine InnoDB         Average number of seconds to run all queries:0.269 seconds        Minimum Number of seconds to run all queries:0.175 seconds        Maximum number of seconds to run all Queri es:0.328 seconds        number of clients running queries:200        Average num ber of queries per client:14, self-built SQL test Case Mysqlslap--create=/yourpath/test1.sql--query=/yourpath/test2.sql-- concurrency=50,100,200--iterations=20--engine=myisam,innodb  -u root-p123abc# Create your test SQL text Test1 and Test2 in the set Yourpath directory and perform 50, 100, and 200 simulated concurrency tests

Mysqlslap Use instance of the MySQL database benchmark stress test tool

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.