Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the performance test of sysbench0.4.12 database in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. Common option

Run the following command in Shell to get information about common options for sysbench:

Sysbench-help

The output of the above command is shown in the following figure:

1.1 use syntax

When using sysbench, you can benchmark with the following syntax:

Sysbench [General options]... -- test= [Test options]... Command

1.2 Common options

-- num-threads=N: the total number of threads that need to be used (default is 1).

-- max-requests=N: the upper limit of the total number of requests (default is 10000).

-- max-time=N: the upper limit of the total execution time in seconds (the default value is 0, which means the execution time is unlimited).

-- forced-shutdown=STRING: the total amount of time to wait after-max-time before forcing it to stop (default is off).

-- thread-stack-size=SIZE: the size of the stack space for each thread (the default is 32KB).

-- init-rng= [on | off]: whether to initialize the random number generator (default is off).

-- seed-rng=N: the seed of the random number generator, ignored when 0 (default is 0).

-- tx-rate=N: target transaction rate (TPS) (default is 0).

-- tx-jitter=N: target transaction change rate in milliseconds (default is 0).

-- report-interval=N: specify an interval and sysbench will periodically report the intermediate statistical results during the test in seconds. A value of 0 disables the intermediate reporting feature (the default is 0).

-- report-checkpoints= [LIST,...]: at the specified point in time, dump the complete statistics and reset all counters. The parameter to this option is a comma-separated value that represents the time, in seconds, from the start of the test to the time the report checkpoint must be performed. Report checkpoints are disabled by default.

-- test=STRING: the test item you need to run.

-- debug= [on | off]: outputs more debugging information (default is off).

-- validate= [on | off]: perform a validity check if possible.

-- help= [on | off]: output help information, and then exit.

-- version= [on | off]: output version information, and then exit.

1.3 logging options

-- verbosity=N: the level of detail of the log. If the value is 5, the debug log is output; if the value is 0, only important information is output (the default is 4).

-- percentile=N: calculates the percentage level of query response time (the default value is 95, which means that sysbench will discard the longest request of 5%, and then select the longest request from the remaining requests).

1.4 built-in test items

Fileio: file I DUBO test.

Cpu:CPU performance testing.

Memory: memory speed test.

Threads: thread subsystem performance testing.

Mutex: mutex performance testing.

Oltp:OLTP (online transaction processing) test.

1.5 Command

Prepare: perform preparatory work for some tests, such as creating the necessary files on disk for fileio tests, populating the test database for OLTP tests, and so on.

Run: run the test item executed by the-- test option.

Cleanup: after the test ends, delete the temporary data and files generated by the test.

Help: displays help information for the test item specified by-- test.

Version: outputs the version information of sysbench.

two。 File Icano test

Run the following command in Shell, and you can see the test options for the file Igamot O Test (fileio):

Sysbench-test=fileio help

The test options for fileio are shown in the following figure:

-- file-num=N: the number of files to be created (the default is 128files).

-- file-block-size=N: the size of the data block used in all IO operations (default is 16384).

-- file-total-size=SIZE: the total size of the file to be created (default is 2G).

-- file-test-mode=STRING: test mode. Available values include seqwr (sequential write), seqrewr (sequential rewrite), seqrd (sequential read), rndrd (random read), rndwr (random write), rndrw (random read and write).

-- file-io-mode=STRING: file operation mode. Optional values include sync, async, and mmap (default is sync).

-- file-extra-flags=STRING: extra flags used on open files. Optional values are sync, dsync, direct.

-- file-fsync-freq=N: after issuing the number of requests specified by this parameter, execute fsync () to synchronize the modified file data in memory to the storage device. A value of 0 means that fsync () is not used (the default is 100).

-- file-fsync-all= [on | off]: after each write operation, execute fsync () (default is off).

-- file-fsync-end= [on | off]: at the end of the test, execute fsync () (default is on).

-- file-fsync-mode=STRING: which method to use when synchronizing data? the optional values are fsync and fdatasync (default is fsync).

-- file-merged-requests=N: merge up to the number of IO requests specified by this parameter, if possible. A value of 0 means that requests will not be merged (the default is 0).

-- file-rw-ratio=N: the ratio of read and write operations when performing a comprehensive read-write test (the default is 1.5).

3. CPU performance test

Run the following command in Shell to see the test options for the CPU performance Test (cpu):

Sysbench-test=cpu help

The test options for cpu are shown in the following figure:

-- cpu-max-prime=N: the upper limit of the prime generator (default is 10000).

4. Memory speed test

Run the following command in Shell to see the test options for the memory speed test (memory):

Sysbench-test=memory help

The test options for memory are shown in the following figure:

-- memory-block-size=SIZE: the size of the memory block used by the test (default is 1K).

-- memory-total-size=SIZE: the total size of the data to be transferred (default is 100G).

-- memory-scope=STRING: memory access range. Available values include global and local (default is global).

-- memory-hugetlb= [on | off]: allocates memory from the HugeTLB pool (default is off).

-- memory-oper=STRING: type of memory operation. Optional values are read, write, and none (default is write).

-- memory-access-mode=STRING: memory access mode. Optional values are seq (order) and rnd (random) (default is seq).

5. Thread subsystem testing

Run the following command in Shell to see the test options for Thread Subsystem Test (threads):

Sysbench-test=threads help

The test options for threads are shown in the following figure:

-- thread-yields=N: the number of thread pauses per request (default is 1000).

-- thread-locks=N: the number of locks used by each thread (the default is 8).

6. Mutex performance test

Run the following command in Shell to see the test options for the mutex performance test (mutex):

Sysbench-test=mutex help

The test options for mutex are shown in the following figure:

-- mutex-num=N: the total size of the mutex array (default is 4096).

-- mutex-locks=N: the number of mutexes used when executing each thread (the default is 50000).

-- mutex-loops=N: the number of empty loops executed inside the mutex (the default is 10000).

7. OLTP test

Run the following command in Shell to see the test options for the online transaction processing test (oltp):

Sysbench-test=oltp help

The test options for oltp are shown in the following figure:

7.1 OLTP option

-- oltp-test-mode=STRING: the types of tests that can be used. Optional values are simple (simple), complex (advanced transaction), nontrx (no transaction), sp (stored procedure) (default is complex).

-- oltp-reconnect-mode=STRING: reconnection mode. Available values are session, transaction, query, and random (default is session).

-- oltp-sp-name=STRING: then-- oltp-test-mode=sp, then this parameter can be used to specify the name of the stored procedure to be called.

-- oltp-read-only= [on | off]: only "read" queries are generated (the database is not modified) (the default is off).

-- oltp-avoid-deadlocks= [on | off]: update keywords are generated in incremental order, which avoids deadlocks (default is off).

-- oltp-skip-trx= [on | off]: skip the BEGIN/COMMIT statement (default is off).

-- oltp-range-size=N: the scope size of the range query (default is 100).

-- oltp-point-selects=N: the number of point selections in a transaction (default is 10).

-- oltp-use-in-statement=N: use the IN statement (default value is 0) in the lookup of 10 primary keys for each query.

-- oltp-simple-ranges=N: the number of simple scope queries in a transaction (the default is 1).

-- oltp-sum-ranges=N: the number of SUM range queries in a transaction (default is 1)

-- oltp-order-ranges=N: the number of sorted (ORDER) range queries in a transaction (default is 1).

-- oltp-distinct-ranges=N: the number of deduplicated (DISTINCT) range queries in a transaction (default is 1).

-- oltp-index-updates=N: the number of index update (UPDATE) queries in a transaction (default is 1).

-- oltp-non-index-updates=N: the number of non-index updates (UPDATE) in a transaction (default is 1).

-- oltp-nontrx-mode=STRING: the mode of non-transactional testing. Optional values are select, update_key, update_nokey, insert, delete (default is select).

-- oltp-auto-inc= [on | off]: test whether the id column of the table should use the AUTO_INCREMENT (primary key self-growth) feature (default is on).

-- oltp-connect-delay=N: the time to go to sleep in milliseconds after connecting to the database (default is 10000).

-- oltp-user-delay-min=N: the minimum time to go to sleep after each request, in milliseconds (default is 0).

-- oltp-user-delay-max=N: the maximum time to sleep after each request, in milliseconds (default is 0).

-- oltp-table-name=STRING: the name of the test table (default is sbtest).

-- oltp-table-size=N: the number of records in the test table (default is 10000).

-- oltp-dist-type=STRING: the distribution of random numbers. Available values include uniform (uniform distribution), gaussian (Gaussian distribution), and special (special distribution) (default is special).

-- oltp-dist-iter=N: the number of iterations to use when generating numbers (the default is 12).

-- oltp-dist-pct=N: the percentage of a value that is considered a "special value" and is used only for the special distribution of random numbers (the default is 1).

-- oltp-dist-res=N: the percentage of "special values" to be used, used only for the special distribution of random numbers (the default is 1).

-- oltp-point-select-mysql-handler= [on | off]: when selecting a point, use MySQL HANDLER (default is off).

-- oltp-point-select-all-cols= [on | off]: when performing a point selection query, select all columns (default is off).

-- oltp-secondary= [on | off]: use a secondary index instead of a primary key (PRIMARY) index (default is off).

-- oltp-num-partitions=N: the number of partitions used by the test table (default is 0).

-- oltp-num-tables=N: the number of test tables (default is 1).

7.2 generic database options

-- db-driver=STRING: specify the database driver you want to use (you can see a list of available drivers through the help command in Section 1.5).

-- db-ps-mode=STRING: if the selected database driver supports preprocessing statements (Prepared Statement) API, then sysbench will use server-side preprocessing statements as much as possible in all queries. Otherwise, preprocessing statements from the client (or emulation side) are used. This option allows you to force the use of emulation even when PS API is available. The optional values are auto, disable (default is auto).

7.3 built-in database driver

Mysql:MySQL driver.

Note:

Since this article only tests OLTP for MySQL, the list of drivers here is only for MySQL. In fact, in addition to MySQL, sysbench supports databases such as Oracle, PostgreSQL, and so on.

7.4 MySQL option

-- mysql-host= [LIST,...]: the hostname or IP address of the MySQL server (default is localhost).

-- the port number of the mysql-port=N:MySQL server (default is 3306).

-- mysql-socket=STRING:MySQL 's socket file.

-- the user name of mysql-user=STRING:MySQL (default is sbtest).

-- the login password for mysql-password=STRING:MySQL.

-- the database name of mysql-db=STRING:MySQL (default is sbtest).

-- mysql-table-engine=STRING: the storage engine used by the test table. Optional values are myisam, innodb, bdb, heap, ndbcluster, federated (default is innodb).

-- mysql-engine-trx=STRING: whether to use a storage engine that supports transactions. The optional values are yes, no, and auto (default is auto).

-- mysql-ssl= [on | off]: if the client library supports SSL connection, it will be used (default is off).

-the maximum number of rows that the myisam-max-rows=N:MyISAM table can support (the default is 1000000).

-- mysql-create-options=STRING: additional options passed to the CREATE TABLE statement.

III. Examples of using sysbench

1. File Icano test

Run the following command in Shell:

# prepare stage:

Sysbench-test=fileio-num-threads=16-file-total-size=2G-file-test-mode=rndrw prepare

# run stage:

Sysbench-test=fileio-num-threads=20-file-total-size=2G-file-test-mode=rndrw run

# cleanup stage:

Sysbench-test=fileio-num-threads=20-file-total-size=2G-file-test-mode=rndrw cleanup

The above command indicates:

In the test preparation phase, 16 threads are used, the total size of the test file created is 2GB, and the file test mode is random read and write.

In the test run phase, 20 threads are used, the total size of the test file is 2GB, and the file test mode is random read and write.

In the test cleanup phase, 20 threads are used, the total size of the test file is 2GB, and the file test mode is random read and write.

The test results are shown in the following figure:

In the test preparation phase, as you can see from the following figure, 128 test files are created, each with a file size of 16MB, a total file size of 2GB, and a write speed of 43.61MB/s:

In the test run phase, as can be seen from the following figure, the number of random Imax O requests is 10000, and the proportion of read and write operations is 1.50. use fsync () to synchronize memory and hard disk data every 100 requests:

During the test cleanup phase, delete all test files:

2. CPU performance test

Run the following command in Shell:

Sysbench-test=cpu-cpu-max-prime=2000 run

The above command indicates:

The upper limit of the prime generator is 2000.

The test results are shown in the following figure:

3. Memory speed test

Run the following command in Shell:

Sysbench-test=memory-memory-block-size=8K-memory-total-size=1G run

The above command indicates:

The memory block size used for testing is 8KB, and the total data size used for testing memory is 1GB.

The test results are shown in the following figure:

4. Thread subsystem testing

Run the following command in Shell:

Sysbench-test=threads-num-threads=500-thread-yields=100-thread-locks=4 run

The above command indicates:

The number of threads used for testing is 500, the number of pauses per request is 100, and each thread has 4 locks.

The test results are shown in the following figure:

5. Mutex performance test

Run the following command in Shell:

Sysbench-test=mutex-num-threads=100-mutex-num=1000-mutex-locks=100000-mutex-loops=10000 run

The above command indicates:

The number of threads used for testing is 100, the size of the mutex array is 1000, the number of mutexes executed by each thread is 100000, and the number of empty loops executed within the mutex is 10000.

The test results are shown in the following figure:

6. OLTP test

Run the following command in Shell:

# prepare stage:

Sysbench-test=oltp-mysql-table-engine=innodb-mysql-host=localhost-mysql-db=test-oltp-table-size=500000-mysql-user=root-mysql-password=password prepare

# run stage:

Sysbench-num-threads=16-test=oltp-mysql-table-engine=innodb-mysql-host=localhost-mysql-db=test-oltp-table-size=500000-mysql-user=root-mysql-password=password run

# cleanup stage:

Sysbench-num-threads=16-test=oltp-mysql-table-engine=innodb-mysql-host=localhost-mysql-db=test-oltp-table-size=500000-mysql-user=root-mysql-password=password cleanup

The above command indicates:

In the test preparation phase, the storage engine used is the InnoDB,MySQL server, the database of MySQL is named test, the total number of records of the test table is 500000, and the user name of MySQL is root,MySQL and the login password is password.

In the test run phase, the number of threads used is 16, the storage engine used is the InnoDB,MySQL server, the database of MySQL is named test, the total number of records of the test table is 500000, and the login password of MySQL user name is root,MySQL is password.

In the test cleanup phase, the number of threads used is 16, the storage engine used is the InnoDB,MySQL server, the database of MySQL is named test, the total number of records of the test table is 500000, and the login password of MySQL user name is root,MySQL is password.

The test results are shown in the following figure:

During the test preparation phase, as you can see from the following figure, a test table called sbtest is created with 500000 records:

In the test run phase, as can be seen from the following figure, 16 threads are used to generate data using a special distribution (12 iterations, returning 1% of the generated value in 75% cases), the begin statement is used at the beginning of the transaction, the primary key is in self-growing mode, and the maximum number of requests for OLTP testing is 10000:

During the test cleanup phase, clear the test table and test data:

Transferred from:

Sysbench usage tutorial-Jinri Toutiao (TouTiao.com)

Http://toutiao.com/a6319391414447046914/?tt_from=mobile_qq&utm_campaign=client_share&app=news_article&utm_source=mobile_qq&iid=5188200538&utm_medium=toutiao_ios

After reading the above, do you have any further understanding of the performance testing of sysbench0.4.12 database in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.