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

Due to the update of the sysbench version, some command options have changed. Query help to change as appropriate.

Https://yq.aliyun.com/articles/66651?spm=5176.100239.blogcont66870.15.0ouYNm

Http://imysql.cn/2014/10/17/sysbench-full-user-manual.shtml

Abstract: why do you want to test and test what? There are many kinds of testing, and the purpose of testing is also very many. My main purpose here is to compare the limit IO of two testing MySQL. Why do different versions of MySQL, different parameters, different hardware, and different systems affect the performance of MySQL? because the official test of MySQL is to use sysbench to choose the latest version as far as possible.

Why are you testing? what are you testing?

There are many kinds of tests, and there are many purposes of testing. I have two main purposes here.

Test the limit IO of MySQL

Compare the performance effects of different versions of MySQL, different parameters, different hardware and different systems on MySQL

Why choose sysbench? because the official test of MySQL is to use sysbench, oh, try to choose the latest version of sysbench, oh, versions greater than 0.4 have real-time display function on how to download sysbench.

Http://github.com/akopytov/sysbench

Where is the document?

Http://github.com/akopytov/sysbench

How to install * basic steps cd sysbench-1.0;./autogen.sh;./configure-- with-mysql-includes=/usr/local/mysql/include-- with-mysql-libs=/usr/local/mysql/lib/;make;make install; * faults that may be encountered in the process sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory * solution export LD_LIBRARY_PATH=/usr/local/mysql/lib/ * Test whether shell has been successfully installed > sysbench-- version sysbench 1.0 introduces the core usage of sysbench, which can be used to test a lot of things, test io,cpu,mem,mysql,oracle,pg, and so on. Here I mainly introduce two concerns, IO & MySQL below the first half is the use of version 0.4, more than 0.4 version of the usage is different, will be noted. 1. General grammar sysbench [common-options]-- test=name [test-options] command command* prepare preparation phase, that is, loading data. In filo test: create a file of specified size in oltp test: create a table of specified size * actual test phase of run * final phase of cleanup to clear the data from previous tests. Common-options

Only common options are introduced.

Option describes the default value-how many threads of num-threads, how many requests, 0 means unlimited 1000--max-time testing time, 0 means unlimited 0--test testing what modules must be required-report-interval periodically reports test statistics, added above version 0.4-options for test=fileio modules

Note in advance:-- file-test-mode

The * seqwrsequential write* seqrewrsequential rewrite* seqrdsequential read * rndrd random read * rndwr random write* rndrwcombined random read/write test option for fileio option describes the default value-- the number of files created by file-num, the size of the 128--file-block-sizeIO operation, the total size of all files, 2G, file-io-modei/O, rndrd, rndwr, rndwr (described above) must-- file-io-modei/O mode Sync, async, fastmmap, slowmmapsync--file-extra-flags open with extra tags-file-fsync-freq number of requests after using fsync100--file-fsync-all each time you write IO must fsyncno--file-fsync-mode synchronize files fsync, fdatasync (see above) fsync--file-rw-ratio proportion of random read and write requests

For example:

Sysbench-- num-threads=16-- test=fileio-- file-total-size=3G-- file-test-mode=rndrw prepare$ sysbench-- num-threads=16-- test=fileio-- file-total-size=3G-- file-test-mode=rndrw run $sysbench-- num-threads=16-- test=fileio-- file-total-size=3G-- file-test-mode=rndrw cleanup OLTP-MySQL

This mode is used to test the performance of a real database. Create tables in prepare phase. Sbtest defaults to

CREATE TABLE `sbtest` (`id` int (10) unsigned NOT NULL auto_increment, `k` int (10) unsigned NOT NULL default '0mm, `c` char (120) NOT NULL default'', `pad` char (60) NOT NULL default'', PRIMARY KEY (`id`), KEY `k` (`k`))

In the run phase

Simple mode SELECT c FROM sbtest WHERE id=N Point queriesSELECT c FROM sbtest WHERE id=N Range queries:SELECT c FROM sbtest WHERE id BETWEEN N AND M Range SUM () queriesSELECT SUM (K) FROM sbtest WHERE id BETWEEN N and M Range ORDER BY queriesSELECT c FROM sbtest WHERE id between N and M ORDER BY c Range DISTINCT queriesSELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c UPDATEs on index columnUPDATE sbtest SET k=k+1 WHERE id=N UPDATEs on non-index column:UPDATE sbtest SET centering N WHERE id=M DELETE queriesDELETE FROM sbtest WHERE id=N INSERT queriesINSERT INTO sbtest VALUES (...) The oltp test schema common parameter option describes the default value-- the name of the oltp-table-name table, the number of rows of the 10000--oltp-tables-count table, the number of 1--oltp-dist-type hot spots data distribution {uniform (uniform distribution), Gaussian (Gaussian distribution), special (spatial distribution)}. Default is specialspecial--oltp-dist-pctspecial: ratio of hotspot data generated 1--oltp-dist-resspecial: access frequency of hotspot data 75--oltp-test-modesimple Complex (described above) complex--oltp-read-only only select requests off--oltp-skip-trx without transaction off--oltp-point-selects number of simple select queries in a transaction 10--oltp-simple-ranges number of simple range queries in a transaction number of 1--oltp-sum-rangessum range number of 1--oltp-order=rangesorder range parameter-- mysql-host= [LIST,...] MySQL server host [localhost]-- mysql-port= [LIST,...] MySQL server port [3306]-- mysql-socket= [LIST,...] MySQL socket-- mysql-user=STRING MySQL user [sbtest]-- mysql-password=STRING MySQL password []-- mysql-db=STRING MySQL database name [sbtest]-- mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]-- mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]-- mysql-ssl= [on | off] use SSL connections If available in the client library [off]-- mysql-ssl-cipher=STRING use specific cipher for SSL connections []-- mysql-compression= [on | off] use compression, if available in the client library [off]-- myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]-- mysql-debug= [on | off] dump all client library calls [off]-- mysql-ignore-errors= [LIST,...] list of errors to ignore Or "all" [1213J 1020J 1205]-- mysql-dry-run= [on | off] Dry run, pretent that all MySQL client API calls are successful without executing them [off]

The above 0.4 version of the grammar introduction is complete.

Next is the new syntax greater than version 0.4, especially the-- test=oltp module.

Replace it with-- test=xx.lua (passed through the full path)

[szq@upright91 local] $locate select.lua

/ home/szq/sysbench-1.0.3/src/lua/oltp_point_select.lua

/ home/szq/sysbench-1.0.3/tests/include/oltp_legacy/select.lua

/ usr/local/share/sysbench/oltp_point_select.lua

/ usr/local/share/sysbench/tests/include/oltp_legacy/select.lua

[szq@upright91 local] $ll / home/szq/sysbench-1.0.3/tests/include/oltp_legacy

Total dosage 52

-rw-r--r-- 1 szq szq 1195 February 26 05:12 bulk_insert.lua

-rw-r--r-- 1 szq szq 4696 February 26 05:12 common.lua

-rw-r--r-- 1 szq szq 366 February 26 05:12 delete.lua

-rw-r--r-- 1 szq szq 1171 February 26 05:12 insert.lua

-rw-r--r-- 1 szq szq 3004 February 26 05:12 oltp.lua

-rw-r--r-- 1 szq szq 368 February 26 05:12 oltp_simple.lua

-rw-r--r-- 1 szq szq 527 February 26 05:12 parallel_prepare.lua

-rw-r--r-- 1 szq szq 369 February 26 05:12 select.lua

-rw-r--r-- 1 szq szq 1448 February 26 05:12 select_random_points.lua

-rw-r--r-- 1 szq szq 1556 February 26 05:12 select_random_ranges.lua

-rw-r--r-- 1 szq szq 369 February 26 05:12 update_index.lua

-rw-r--r-- 1 szq szq 578 February 26 05:12 update_non_index.lua

FileIO actual combat

Disk: S3610 * 6 raid10, 128 GB of memory

Test the limit IOPS in related scenarios

Random read and write (3:2 oltp scene) * sysbench-- num-threads=16-- report-interval=3-- max-requests=0-- max-time=300-- test=fileio-- file-num=200-- file-total-size=200G\-- file-test-mode=rndrw-- file-block-size=16384-- file-extra-flags=direct run

Cdn.com/663010e67787f986aa351eef3168a3c2767eda7f.jpeg ">

Random read and write (5:1 oltp scene) * sysbench-- num-threads=16-- report-interval=3-- max-requests=0-- max-time=300-- test=fileio-- file-num=200-- file-total-size=200G\-- file-test-mode=rndrw-- file-block-size=16384-- file-extra-flags=direct-- file-rw-ratio=5 run

Randomly write * sysbench-- num-threads=16-- report-interval=3-- max-requests=0-- max-time=300-- test=fileio-- file-num=200-- file-total-size=200G\-- file-test-mode=rndwr-- file-block-size=16384-- file-extra-flags=direct run

Randomly read * sysbench-num-threads=16-- report-interval=3-- max-requests=0-- max-time=300-- test=fileio-- file-num=200-- file-total-size=200G\-- file-test-mode=rndrd-- file-block-size=16384-- file-extra-flags=direct run

MySQL5.6 vs MySQL5.7 test

Disk: S3610 * 6 raid10, 128 GB of memory

Point select* generates data sysbench--num-threads=128-report-interval=3-- max-requests=0-- max-time=300-- test=/root/sysbench-1.0/sysbench/tests/db/select.lua\-- mysql-table-engine=innodb-- oltp-table-size=50000000-- mysql-user=sysbench-- mysql-password=sysbench-- oltp-tables-count=2-- mysql-host=xx-- mysql-port=3306 prepare * execute sysbench--num-threads=128-- report-interval=3-- max-requests=0-- max-time=300-- Test=/root/sysbench-1.0/sysbench/tests/db/select.lua\-mysql-table-engine=innodb-oltp-table-size=50000000-mysql-user=sysbench-mysql-password=sysbench-oltp-tables-count=2-mysql-host=xx-mysql-port=3306 run

Point oltp* generates data sysbench--num-threads=128-report-interval=3-- max-requests=0-- max-time=300-- test=/root/sysbench-1.0/sysbench/tests/db/oltp.lua\-- mysql-table-engine=innodb-- oltp-table-size=50000000-- mysql-user=sysbench-- mysql-password=sysbench-- oltp-tables-count=2-- mysql-host=xx-- mysql-port=3306 prepare * execute sysbench--num-threads=128-- report-interval=3-- max-requests=0-- max-time=300-- Test=/root/sysbench-1.0/sysbench/tests/db/oltp.lua\-mysql-table-engine=innodb-oltp-table-size=50000000-mysql-user=sysbench-mysql-password=sysbench-oltp-tables-count=2-mysql-host=xx-mysql-port=3306 run

Conclusion in terms of performance, although the official claim that 5.7 is three times faster than 5.6, in the actual test, 5.7 is slightly worse than 5.6. Will you choose 5.7 production environment? Of course, because the new features of 5.7 are so tempting:

Https://www.percona.com/blog/2016/04/07/mysql-5-7-sysbench-oltp-read-results-really-faster/

Http://dimitrik.free.fr/blog/archives/2013/09/mysql-performance-reaching-500k-qps-with-mysql-57.html

Https://github.com/akopytov/sysbench

Http://www.mysql.com/why-mysql/benchmarks/

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.