Sysbench Introduction
Sysbench is a modular, cross-platform, multithreaded benchmarking tool that is used to assess the load of databases under various system parameters. It mainly includes the following methods of testing: CPU Performance Disk IO Performance Scheduler performance memory allocation and transmission speed POSIX thread performance database performance (OLTP benchmark)
At present, the Sysbench mainly support mysql,pgsql,oracle 3 kinds of databases. Sysbench Installation
MySQL is supported by default, and if you need to test oracle/postgresql, you need to add –with-oracle or –with-pgsql parameters to the Configure.
Sysbench is installed by default:/usr/local/bin/sysbench
shell> tar-zxvf sysbench-0.5.tar.gz-c/db/tool/shell> cd/db/tool/sysbench-0.5 shell> chmod +x autogen.sh She
ll>./autogen.sh automake 1.10.x (aclocal) wasn ' t found, exiting shell> yum install automake shell>./autogen.sh Libtoolize 1.4+ wasn ' t found, exiting-shell> yum install Libtool shell>./autogen.sh: Running ' aclocal-i M4 './autogen.sh:running ' libtoolize--copy---force ' libtoolize:putting auxiliary files in AC_CON
Fig_aux_dir, ' config '.
libtoolize:copying file ' config/ltmain.sh ' libtoolize:putting macros in Ac_config_macro_dir, ' M4 '. libtoolize:copying file ' m4/libtool.m4 ' libtoolize:copying file ' m4/ltoptions.m4 ' libtoolize:copying file ' M4/ltsugar. M4 ' libtoolize:copying file ' m4/ltversion.m4 ' libtoolize:copying file ' m4/lt~obsolete.m4 './autogen.sh:running ' Autohe Ader './autogen.sh:running ' automake-c--foreign--add-missing ' configure.ac:23:installing ' config/compile ' . ac:11:installing ' confIg/config.guess ' configure.ac:11:installing ' config/config.sub ' configure.ac:16:installing ' config/install-sh ' Configure.ac:16:installing ' config/missing ' sysbench/makefile.am:installing ' config/depcomp './autogen.sh:running ' Autoconf ' libtoolized with:libtoolize (GNU libtool) 2.2.6b automade with:automake (GNU Automake) 1.11.1 configured with : autoconf (GNU autoconf) 2.63 shell>./configure--prefix=/db/sysbench--with-mysql-includes=/db/mysql/include-- With-mysql-libs=/db/mysql/lib shell> make && make install test installation shell> Cp/db/sysbench/bin/sysbench/usr/lo Cal/bin/shell> sysbench--help sysbench:error while loading shared libraries:libmysqlclient.so.18:cannot open Shar Ed object File:no Such file or directory problem reason: Sysbench could not find the MySQL library file, possibly the environment variable Ld_library_path not set, the problem can be resolved after setting: Add Export Ld_ Library_path=/db/mysql/lib can
sysbench Parameters
shell> sysbench--help Missing required command argument. Usage:sysbench [general-options] ...--test= [test-options] ... command general options:--num-threads=n nu Mber of threads to use [1]--max-requests=n limit for total number of requests [10000]--max-time=n Limit for total execution time in seconds [0]--forced-shutdown=string amount of time to wait after--max-time B Efore forcing shutdown [off]--thread-stack-size=size size of stack/per thread [64K]--tx-rate=n Targ ET transaction rate (TPS) [0]--tx-jitter=n target transaction variation, in microseconds [0]--report-in Terval=n periodically intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]--test=string test to run--debug=[on|off] print more Debu gging info [off]--validate=[on|off] Perform validation checks where possible [off]--help=[on|off] Print help and exit--version=[on|off] print version and exit [out]--rand-init=[on|off] in itialize random number generator [off]--rand-type=string random numbers distribution {uniform,gaussian,special} [Special]--rand-spec-iter=n number of iterations used for numbers generation [a]--rand-spec-pct=n Percentage of values to is treated as ' special ' (for special distribution) [1]--rand-spec-res=n Percentage of ' Special ' values to use (for special distribution) [$]--rand-seed=n seed for random number generator, IG Nored 0 [0] Log options:--verbosity=n verbosity level {5-debug, 0-only critical Messages} [3]--perce Ntile=n percentile rank of query response to count [] compiled-in tests:fileio-file I/O test CPU-CP U performance test memory-memory functions Speed test threads-threads subsystem performance test Mutex-mutex P Erformance Test CommandS:prepare Run Cleanup help version "Sysbench--test= help" for a list of the options for each test.
CPU Test
Sysbench CPU test uses a 64-bit integer to test the time it takes to calculate a prime to a maximum value
shell> sysbench--test=cpu--cpu-max-prime=2000 run sysbench 0.5:multi-threaded System evaluation Benchmark Running The test with following options:number of threads:1 Random number generator seed is 0 and would be ignored Primer
RS limit:2000 Threads started!
Test execution Summary:total time:2.2452s total number of events:10000 Total time taken by event execution:2.2347s Per-request Statistics:min:
0.20ms avg:0.22ms max:3.35ms Approx. Percentile:0.27ms Threads fairness:events (avg/stddev): 10000.0000/0.00 execution Ti Me (Avg/stddev): 2.2347/0.00 add: View CPU Information Methods view the number of physical CPUs grep "Physical ID"/proc/cpuinfo | Sort-u | Wc-l View core number grep "Core ID"/proc/cpuinfo | Sort-u | Wc-l view number of threads grep "Processor"/proc/cpuinfo | Sort-u | Wc-l inIn the Sysbench test, the--num-threads value is "Number of threads"
Thread (threads) test
Test the performance of the thread scheduler. Useful for testing the behavior of the thread scheduler in high load situations
shell> sysbench--test=threads--num-threads=64--thread-yields=100--thread-locks=2 run
sysbench: Multi-threaded System Evaluation Benchmark
Running the test with following options: Number of
threads:64
Ran Dom number generator seed is 0 and would be ignored
Threads started!
Test Execution Summary: Total time: 1.9581s Total number of
events: 10000 Total time
taken by Event execution:124.8938s
per-request Statistics:
min: 0.05ms
avg: 12.49ms
Max: 151.15ms
approx. Percentile: 50.83ms
Threads Fairness:
Events (Avg/stddev): 156.2500/14.48
execution Time (Avg/stddev): 1.9515/0.00
file IO performance testGenerate the required test files, the total size of the file 5g,16 a concurrent thread. After execution, a bunch of small files are generated in the current directory.
shell> sysbench--test=fileio--num-threads=16--file-total-size=5g Prepare
Executing tests, specifying random read-write mode
Specify read-write mode: SEQWR sequential write seqrewr sequential rewrite seqrd sequential read RNDRD Random read Rndwr random write RNDRW mixed random Read/write
shell> sysbench--test=fileio--num-threads=16--init-rng=on--file-total-size=5g Run
Sysbench 0.5:multi-threaded System Evaluation benchmark Running the test with following options:number of Threads:16
Random number generator seed is 0 and would be ignored Threads started! Operations performed:5999 reads, 4001 writes, 12800 other = 22800 Total Read 93.734Mb written 62.516Mb total Transferr Ed 156.25Mb (9.2561mb/sec) # #吞吐量 592.39 requests/sec executed Test execution summary:total time:
16.8808s Total number of events:10000 total time taken by event Execution:176.1816s Per-request statistics:min:0.01ms avg: 17.62ms max:416.73ms approx. Percentile:104.82ms Threads fairness:events (avg/stddev): 625.0000/62.39 execution TimE (Avg/stddev): 11.0114/0.67
Clear test File
shell> sysbench--test=fileio--num-threads=16--file-total-size=5g cleanup
Mutex (mutex) test
Test the performance of the mutex by simulating that all threads are running concurrently at the same time and are briefly requesting mutexes.
shell> sysbench--test=mutex--num-threads=16--mutex-num=1024--mutex-locks=10000 Run
Sysbench 0.5: multi-threaded system Evaluation benchmark
Running the test with following options: Number of
t Hreads:16
Random number generator seed is 0 and'll be ignored
Threads
started! Test Execution Summary: Total time: 0.0135s Total number of events: Total time taken by
event ex Ecution:0.0411s
per-request Statistics:
min: 0.70ms
avg: 2.57ms
Max: 9.19ms
approx. Percentile: 9.16ms
Threads Fairness:
Events (Avg/stddev): 1.0000/0.00 Execution Time
( Avg/stddev): 0.0026/0.00
Memory Test
The memory test tests the memory for continuous read-write performance.
shell> sysbench--test=memory--num-threads=16--memory-block-size=8192--memory-total-size=1g run Sysbench
0.5: multi-threaded system Evaluation benchmark
Running the test with following options: Number of
threads:1 6
Random number generator seed is 0 and would be ignored
Threads started!
Operations performed:131072 (381158.38 ops/sec)
1024.00 MB transferred (2977.80 mb/sec) # #吞吐量
Test Execution Summary: Total time: 0.3439s Total number of
events: 131072 Total time
taken by Event execution:3.9915s
per-request Statistics:
min: 0.00ms
avg: 0.03ms
Max: 51.02ms
approx. Percentile: 0.00ms # #大约95% Time distribution
Threads Fairness:
Events (Avg/stddev): 8192.0000/ 1166.77
Execution Time (avg/stddev): 0.2495/0.02
MySQL Database test
Sysbench 0.5 replaces the previous OLTP with a series of LUA scripts to simulate a closer to the real benchmark environment. These test scripts include: Insert.lua, Oltp.lua, Parallel_prepare.lua, Select_random_points.lua, Update_index.lua, Delete.lua oltp_ Simple.lua, Select.lua, Select_random_ranges.lua, Update_non_index.lua, script usage is basically similar.
Sysbench 0.5 uses the Sbtest library by default, but you need to create it yourself manually, or you can use--MYSQL-DB to specify options for other Non-default items:-mysql-host-mysql-port-mysql-socket- Mysql-user-mysql-password-mysql-db-mysql-ssl Prepare
To generate a table and insert data, you can use the Parallel_prepare.lua script to prepare the data in parallel. –db-driver Server Type MySQL | Drizzle, default is Mysql–mysql-table-engine table storage engine –myisam-max-rows MyISAM table max_rows option (for large tables) –oltp-table-count generate table number [Sbtest1, Sbtest2 ...] –oltp-table-size the row number of the generated table –oltp-secondary ID column generates a level two index instead of a primary key –oltp-auto-inc set the ID column to be self added on | Off, default to On
shell> cd/db/tool/sysbench-0.5/sysbench shell> sysbench--test=./tests/db/oltp.lua--mysql-table-engine= MyISAM--oltp-table-size=100000--mysql-db=test--mysql-user=root--oltp-tables-count=10--mysql-password=oracle-- Mysql-socket=/tmp/mysql.sock Prepare Sysbench 0.5:multi-threaded System Evaluation benchmark table ' creating '.
.
Inserting 100000 records into ' sbtest1 ' creating table ' Sbtest2 ' ...
Inserting 100000 records into ' sbtest2 ' creating table ' Sbtest3 ' ...
Inserting 100000 records into ' sbtest3 ' creating table ' Sbtest4 ' ...
Inserting 100000 records into ' sbtest4 ' creating table ' sbtest5 ' ...
Inserting 100000 records into ' sbtest5 ' creating table ' Sbtest6 ' ...
Inserting 100000 records into ' sbtest6 ' creating table ' sbtest7 ' ...
Inserting 100000 records into ' sbtest7 ' creating table ' Sbtest8 ' ...
Inserting 100000 records into ' sbtest8 ' creating table ' Sbtest9 ' ...
Inserting 100000 records into ' sbtest9 ' creating table ' sbtest10 ' ... Inserting 100000 records into ' SbtesT10 ' can also use Parallel_prepare.lua script to prepare test data in parallel, and the number of threads should be a multiple of the run table shell> sysbench--test=./tests/db/parallel_prepare.lua-- Mysql-table-engine=myisam--oltp-table-size=100000--num-threads=10--mysql-db=test--mysql-user=root-- oltp-tables-count=10--mysql-password=oracle--mysql-socket=/tmp/mysql.sock Run sysbench 0.5:multi-threaded system ev Aluation benchmark Running The test with following options:number of Threads:10 Random # Generator seed is 0 and W
Ill be ignored Threads started! Thread Prepare8 Creating table ' Sbtest9 ' ... thread prepare1 creating table ' Sbtest2 ' ... thread prepare0 creating table ' SB Test1 ' ... thread prepare5 creating table ' Sbtest6 ' ... thread prepare3 creating table ' Sbtest4 ' ... thread prepare6 creating Table ' sbtest7 ' ... thread prepare9 creating table ' sbtest10 ' ... thread prepare4 creating table ' sbtest5 ' ... thread prepar
E2 creating table ' Sbtest3 ' ... thread prepare7 creating table ' Sbtest8 ' ... Inserting 100000 records into ' sbtest1 ' inserting 100000 REcords into the ' sbtest9 ' inserting 100000 records into ' sbtest8 ' inserting 100000 to records into ' sbtest6 ' inserting 100000 rec Ords into the ' sbtest7 ' inserting 100000 records into ' sbtest2 ' inserting, 100000 records into ' sbtest10 ' inserting 100000
RDS into ' sbtest4 ' inserting 100000 records into ' sbtest5 ' inserting 100000 ' records into ' sbtest3 ' OLTP test statistics: Queries performed:read:0 write:380 othe R:20 total:400 transactions:0
(0.00 per Sec.)
Deadlocks:0 (0.00 per Sec.)
Read/write requests:380 (32.82 per sec.)
Other operations:20 (1.73 per sec.)
Test execution Summary:total time:11.5785s total number of events:10000 Total time taken by event execution:0.1422s per-request statistics:min:0.00ms avg: 0.01ms max:19.55ms approx.
percentile:0.00ms http://ww3.sinaimg.cn/bmiddle/671c4d8egw1enjimkujv2g20b40b4b29.gif Threads Fairness: Events (Avg/stddev): 1000.0000/2912.74 Execution Time (Avg/stddev): 0.0142/0.04
Run–oltp-tables-count–oltp-read-only only execute select test on | Off, the default is the Off–oltp-dist-type random number distribution state. Uniform (uniform distribution), Gauss (Gaussian distribution), special (special distribution) –oltp-dist-pct percentage value of special distribution –oltp-dist-res percentage of special distributions –oltp-point-selects The number of SELEC queries specified in a single transaction –oltp-range-size the scope of the range query, which should be the number of simple range queries specified in a single transaction oltp-table-size small –oltp-simple-ranges Oltp-sum-ranges the number of sum range queries specified in a single transaction –oltp-order-ranges the number of order range queries specified in a single transaction –oltp-distinct-ranges Number of distinct range queries specified in a single transaction –oltp-index-updates the number of use index updates specified in a single transaction –oltp-non-index-updates the number of unused index updates specified in a single transaction
shell> sysbench--test=./tests/db/oltp.lua--num_threads=10--oltp-table-size=100000--mysql-db=test =root--oltp-tables-count=10--mysql-password=oracle Run sysbench 0.5:multi-threaded System evaluation Benchmark Runni Ng the test with following options:number of threads:10 Random number generator seed is 0 and'll be ignored threads
started! OLTP Test Statistics:queries performed:read:140028--Total Read write: 40008-Write Total other:20004-Total number of other operations (SELECT, INSERT, UPDATE, delete External operations, such as commit, etc) total:200040--Total totals transactions:10002 ( 234.44 per sec.) --Total transaction count (transactions per second) deadlocks:0 (0.00/sec.) --Total number of deadlocks Read/write requests:180036 (4219.99 per Sec.)--Total reads and writes (read/write times) Other operations: 20004 (468.89 per sec.) -- Total number of other operations (other operations per second) Test execution Summary:total time:42.6626s--Total time consuming sum of E VENTS:10002--How many transactions occur total times taken by event execution:426.3020s--all transactions are added together (regardless of parallelism) per- Request Statistics:min:5.36ms--Minimum time-consuming avg: 42.62ms--Average time consuming max:183.44ms--the longest time approx.
Percentile:59.81ms--More than 99% of the average time consuming Threads fairness:events (avg/stddev): 1000.2000/23.87 Execution Time (Avg/stddev): 42.6302/0.01
Clearnup
shell> sysbench--test=./tests/db/oltp.lua--oltp-table-size=100000--mysql-db=test--mysql-user=root
-- oltp-tables-count=10--mysql-password=oracle Cleanup
sysbench 0.5: multi-threaded system evaluation Benchmark
dropping table ' Sbtest1 '
... Dropping table ' Sbtest2 ' ...
Dropping table ' Sbtest3 ' ...
Dropping table ' Sbtest4 ' ...
Dropping table ' Sbtest5 ' ...
Dropping table ' Sbtest6 ' ...
Dropping table ' sbtest7 ' ...
Dropping table ' Sbtest8 ' ...
Dropping table ' Sbtest9 ' ...
Dropping table ' sbtest10 ' ...
After tuning the database, you can use Sysbench to test the OLTP to see if the TPS will improve.
Note: The Sysbench test is only a benchmark and does not represent performance metrics in the actual enterprise environment.
See also: Http://www.percona.com/docs/wiki/benchmark:sysbench:olpt.lua
Sorting from NetworkSvoid 2014-12-24
--This article from: http://blog.itpub.net/29733787/viewspace-1383197/