数据库性能测试实战

sysbench是一个开源的、模块化的、跨平台的多线程性能测试工具，
可以用来进行CPU、内存、磁盘I/O、线程、数据库的性能测试，
目前支持的数据库包括MySQL、Oracle和PostgreSQL。

[root@wallet01~]#yuminstall-ymakeautomakelibtoollibaio-develpkgconfigmysql-devel

[root@wallet01~]#gitclonehttps://github.com/akopytov/sysbench.git
InitializedemptyGitrepositoryin/root/sysbench/.git/
remote:Enumeratingobjects:131,done.
remote:Countingobjects:100%(131/131),done.
remote:Compressingobjects:100%(95/95),done.
remote:Total9597(delta71),reused70(delta32),pack-reused9466
Receivingobjects:100%(9597/9597),4.13MiB|61KiB/s,done.
Resolvingdeltas:100%(6842/6842),done.

[root@wallet01~]#cdcdsysbench
[root@wallet01sysbench]#./autogen.sh
[root@wallet01sysbench]#./configure
[root@wallet01sysbench]#make
[root@wallet01sysbench]#makeinstall

[root@wallet01~]#sysbench--help
Usage:
sysbench[options]...[testname][command]
Commandsimplementedbymosttests:prepareruncleanuphelp
Generaloptions:
--threads=Nnumberofthreadstouse[1]
--events=Nlimitfortotalnumberofevents[0]
--time=Nlimitfortotalexecutiontimeinseconds[10]
--forced-shutdown=STRINGnumberofsecondstowaitafterthe--timelimitbeforeforcingshutdown,or'off'todisable[off]
--thread-stack-size=SIZEsizeofstackperthread[64K]
--rate=Naveragetransactionsrate.0forunlimitedrate[0]
--report-interval=Nperiodicallyreportintermediatestatisticswithaspecifiedintervalinseconds.0disablesintermediatereports[0]
--report-checkpoints=[LIST,...]dumpfullstatisticsandresetallcountersatspecifiedpointsintime.Theargumentisalistofcomma-separatedvaluesrepresentingtheamountoftimeinsecondselapsedfromstartoftestwhenreportcheckpoint(s)mustbeperformed.Reportcheckpointsareoffbydefault.[]
--debug[=on|off]printmoredebugginginfo[off]
--validate[=on|off]performvalidationcheckswherepossible[off]
--help[=on|off]printhelpandexit[off]
--version[=on|off]printversionandexit[off]
--config-file=FILENAMEFilecontainingcommandlineoptions
--tx-rate=Ndeprecatedaliasfor--rate[0]
--max-requests=Ndeprecatedaliasfor--events[0]
--max-time=Ndeprecatedaliasfor--time[0]
--num-threads=Ndeprecatedaliasfor--threads[1]

Pseudo-RandomNumbersGeneratoroptions:
--rand-type=STRINGrandomnumbersdistribution{uniform,gaussian,special,pareto}[special]
--rand-spec-iter=Nnumberofiterationsusedfornumbersgeneration[12]
--rand-spec-pct=Npercentageofvaluestobetreatedas'special'(forspecialdistribution)[1]
--rand-spec-res=Npercentageof'special'valuestouse(forspecialdistribution)[75]
--rand-seed=Nseedforrandomnumbergenerator.When0,thecurrenttimeisusedasaRNGseed.[0]
--rand-pareto-h=Nparameterhforparetodistribution[0.2]

Logoptions:
--verbosity=Nverbositylevel{5-debug,0-onlycriticalmessages}[3]
--percentile=Npercentiletocalculateinlatencystatistics(1-100).Usethespecialvalueof0todisablepercentilecalculations[95]
--histogram[=on|off]printlatencyhistograminreport[off]

Generaldatabaseoptions:
--db-driver=STRINGspecifiesdatabasedrivertouse('help'togetlistofavailabledrivers)[mysql]
--db-ps-mode=STRINGpreparedstatementsusagemode{auto,disable}[auto]
--db-debug[=on|off]printdatabase-specificdebuginformation[off]

Compiled-indatabasedrivers:
mysql-MySQLdriver

mysqloptions:
--mysql-host=[LIST,...]MySQLserverhost[localhost]
--mysql-port=[LIST,...]MySQLserverport[3306]
--mysql-socket=[LIST,...]MySQLsocket
--mysql-user=STRINGMySQLuser[sbtest]
--mysql-password=STRINGMySQLpassword[]
--mysql-db=STRINGMySQLdatabasename[sbtest]
--mysql-ssl[=on|off]useSSLconnections,ifavailableintheclientlibrary[off]
--mysql-ssl-cipher=STRINGusespecificcipherforSSLconnections[]
--mysql-compression[=on|off]usecompression,ifavailableintheclientlibrary[off]
--mysql-debug[=on|off]traceallclientlibrarycalls[off]
--mysql-ignore-errors=[LIST,...]listoferrorstoignore,or"all"[1213,1020,1205]
--mysql-dry-run[=on|off]Dryrun,pretendthatallMySQLclientAPIcallsaresuccessfulwithoutexecutingthem[off]

Compiled-intests:
fileio-FileI/Otest
cpu-CPUperformancetest
memory-Memoryfunctionsspeedtest
threads-Threadssubsystemperformancetest
mutex-Mutexperformancetest
See'sysbenchhelp'foralistofoptionsforeachtest.

Lua是一种强大、高效、轻量级、可嵌入的脚本语言。
支持过程编程、面向对象编程、功能编程、数据驱动编程和数据描述。

[root@wallet01~]#cd/usr/local/share/sysbench
[root@wallet01sysbench]#ls-l
total64
-rwxr-xr-x1rootroot1452Jan1008:47bulk_insert.lua
-rw-r--r--1rootroot14369Jan1008:47oltp_common.lua
-rwxr-xr-x1rootroot1290Jan1008:47oltp_delete.lua
-rwxr-xr-x1rootroot2415Jan1008:47oltp_insert.lua
-rwxr-xr-x1rootroot1265Jan1008:47oltp_point_select.lua
-rwxr-xr-x1rootroot1649Jan1008:47oltp_read_only.lua
-rwxr-xr-x1rootroot1824Jan1008:47oltp_read_write.lua
-rwxr-xr-x1rootroot1118Jan1008:47oltp_update_index.lua
-rwxr-xr-x1rootroot1127Jan1008:47oltp_update_non_index.lua
-rwxr-xr-x1rootroot1440Jan1008:47oltp_write_only.lua
-rwxr-xr-x1rootroot1919Jan1008:47select_random_points.lua
-rwxr-xr-x1rootroot2118Jan1008:47select_random_ranges.lua
drwxr-xr-x4rootroot4096Jan1008:47tests

[root@gscf01sysbench]#./oltp_read_write.luahelp
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)

oltp_read_write.luaoptions:
--auto_inc[=on|off]UseAUTO_INCREMENTcolumnasPrimaryKey(forMySQL),oritsalternativesinotherDBMS.Whendisabled,useclient-generatedIDs[on]
--create_secondary[=on|off]CreateasecondaryindexinadditiontothePRIMARYKEY[on]
--delete_inserts=NNumberofDELETE/INSERTcombinationspertransaction[1]
--distinct_ranges=NNumberofSELECTDISTINCTqueriespertransaction[1]
--index_updates=NNumberofUPDATEindexqueriespertransaction[1]
--mysql_storage_engine=STRINGStorageengine,ifMySQLisused[innodb]
--non_index_updates=NNumberofUPDATEnon-indexqueriespertransaction[1]
--order_ranges=NNumberofSELECTORDERBYqueriespertransaction[1]
--pgsql_variant=STRINGUsethisPostgreSQLvariantwhenrunningwiththePostgreSQLdriver.Theonlycurrentlysupportedvariantis'redshift'.Whenenabled,create_secondaryisautomaticallydisabled,anddelete_insertsissetto0
--point_selects=NNumberofpointSELECTqueriespertransaction[10]
--range_selects[=on|off]Enable/disableallrangeSELECTqueries[on]
--range_size=NRangesizeforrangeSELECTqueries[100]
--secondary[=on|off]UseasecondaryindexinplaceofthePRIMARYKEY[off]
--simple_ranges=NNumberofsimplerangeSELECTqueriespertransaction[1]
--skip_trx[=on|off]Don'tstartexplicittransactionsandexecuteallqueriesintheAUTOCOMMITmode[off]
--sum_ranges=NNumberofSELECTSUM()queriespertransaction[1]
--table_size=NNumberofrowspertable[10000]
--tables=NNumberoftables[1]

[root@wallet01~]#mysql-uroot-pabcd.1234
mysql>createdatabasesbtest;
QueryOK,1rowaffected(0.02sec)

mysql>grantallonsbtest.*to'sbtest'@'%'identifiedby'sbtest';
QueryOK,0rowsaffected(0.06sec)

mysql>flushprivileges;
QueryOK,0rowsaffected(0.02sec)

[root@wallet01~]#sysbench--mysql-host=localhost--mysql-port=3306--mysql-user=sbtest--mysql-password=sbtest\
--threads=10\
/usr/local/share/sysbench/oltp_read_write.lua--tables=10--table_size=100000prepare

sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)

Initializingworkerthreads...

Creatingtable'sbtest10'...
Creatingtable'sbtest6'...
Creatingtable'sbtest7'...
Creatingtable'sbtest8'...
Creatingtable'sbtest9'...
Creatingtable'sbtest4'...
Creatingtable'sbtest3'...
Creatingtable'sbtest5'...
Creatingtable'sbtest2'...
Creatingtable'sbtest1'...
Inserting100000recordsinto'sbtest1'
Inserting100000recordsinto'sbtest6'
Inserting100000recordsinto'sbtest9'
Inserting100000recordsinto'sbtest10'
Inserting100000recordsinto'sbtest4'
Inserting100000recordsinto'sbtest3'
Inserting100000recordsinto'sbtest2'
Inserting100000recordsinto'sbtest7'
Inserting100000recordsinto'sbtest5'
Inserting100000recordsinto'sbtest8'
Creatingasecondaryindexon'sbtest1'...
Creatingasecondaryindexon'sbtest6'...
Creatingasecondaryindexon'sbtest10'...
Creatingasecondaryindexon'sbtest9'...
Creatingasecondaryindexon'sbtest5'...
Creatingasecondaryindexon'sbtest2'...
Creatingasecondaryindexon'sbtest7'...
Creatingasecondaryindexon'sbtest3'...
Creatingasecondaryindexon'sbtest4'...
Creatingasecondaryindexon'sbtest8'...

[root@wallet01~]#mysql-uroot-pabcd.1234
mysql>usesbtest
Databasechanged

mysql>selectcount(*)fromsbtest1;
+----------+
|count(*)|
+----------+
|100000|
+----------+
1rowinset(0.04sec)

[root@wallet01~]#sysbench--mysql-host=localhost--mysql-port=3306--mysql-user=sbtest--mysql-password=sbtest\
--threads=10--time=120--report-interval=10\
/usr/local/share/sysbench/oltp_read_write.lua--tables=10--table_size=100000run
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)

Runningthetestwithfollowingoptions:
Numberofthreads:10
Reportintermediateresultsevery10second(s)
Initializingrandomnumbergeneratorfromcurrenttime


Initializingworkerthreads...

Threadsstarted!

[10s]thds:10tps:53.79qps:1092.88(r/w/o:765.65/218.76/108.48)lat(ms,95%):331.91err/s:0.00reconn/s:0.00
[20s]thds:10tps:54.90qps:1099.92(r/w/o:770.01/220.00/109.90)lat(ms,95%):320.17err/s:0.00reconn/s:0.00
[30s]thds:10tps:60.00qps:1198.19(r/w/o:838.60/239.60/120.00)lat(ms,95%):277.21err/s:0.00reconn/s:0.00
[40s]thds:10tps:55.90qps:1119.41(r/w/o:784.00/223.60/111.80)lat(ms,95%):308.84err/s:0.00reconn/s:0.00
[50s]thds:10tps:57.60qps:1138.70(r/w/o:796.60/227.60/114.50)lat(ms,95%):248.83err/s:0.00reconn/s:0.00
[60s]thds:10tps:64.70qps:1306.90(r/w/o:915.60/261.20/130.10)lat(ms,95%):231.53err/s:0.00reconn/s:0.00
[70s]thds:10tps:62.30qps:1243.40(r/w/o:869.60/249.20/124.60)lat(ms,95%):240.02err/s:0.00reconn/s:0.00
[80s]thds:10tps:63.20qps:1267.40(r/w/o:887.40/253.60/126.40)lat(ms,95%):219.36err/s:0.00reconn/s:0.00
[90s]thds:10tps:60.70qps:1214.00(r/w/o:849.80/242.80/121.40)lat(ms,95%):287.38err/s:0.00reconn/s:0.00
[100s]thds:10tps:59.10qps:1180.10(r/w/o:826.00/236.00/118.10)lat(ms,95%):257.95err/s:0.00reconn/s:0.00
[110s]thds:10tps:57.40qps:1149.70(r/w/o:805.00/229.80/114.90)lat(ms,95%):277.21err/s:0.00reconn/s:0.00
[120s]thds:10tps:61.00qps:1218.29(r/w/o:852.59/243.80/121.90)lat(ms,95%):244.38err/s:0.00reconn/s:0.00
SQLstatistics:
queriesperformed:
read:99624
write:28464
other:14232
total:142320
transactions:7116(59.21persec.)
queries:142320(1184.27persec.)
ignorederrors:0(0.00persec.)
reconnects:0(0.00persec.)

Generalstatistics:
totaltime:120.1739s
totalnumberofevents:7116

Latency(ms):
min:50.46
avg:168.79
max:1260.52
95thpercentile:262.64
sum:1201077.94

Threadsfairness:
events(avg/stddev):711.6000/5.77
executiontime(avg/stddev):120.1078/0.04

[root@wallet01~]#sysbench--mysql-host=localhost--mysql-port=3306--mysql-user=sbtest--mysql-password=sbtest\
--threads=10\
/usr/local/share/sysbench/oltp_read_write.lua--tables=10--table_size=100000cleanup
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)

Droppingtable'sbtest1'...
Droppingtable'sbtest2'...
Droppingtable'sbtest3'...
Droppingtable'sbtest4'...
Droppingtable'sbtest5'...
Droppingtable'sbtest6'...
Droppingtable'sbtest7'...
Droppingtable'sbtest8'...
Droppingtable'sbtest9'...
Droppingtable'sbtest10'...

[root@wallet01~]#sysbenchcpuhelp
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)
cpuoptions:
--cpu-max-prime=Nupperlimitforprimesgenerator[10000]

[root@cacti~]#sysbenchcpu--cpu-max-prime=20000--threads=2run
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)
Runningthetestwithfollowingoptions:
Numberofthreads:2
Initializingrandomnumbergeneratorfromcurrenttime
Primenumberslimit:20000
Initializingworkerthreads...
Threadsstarted!
CPUspeed:
eventspersecond:965.95
Generalstatistics:
totaltime:10.0015s
totalnumberofevents:9663
Latency(ms):
min:2.07
avg:2.07
max:3.00
95thpercentile:2.07
sum:19998.19
Threadsfairness:
events(avg/stddev):4831.5000/0.50
executiontime(avg/stddev):9.9991/0.00

[root@wallet01~]#sysbenchfileiohelp
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)
fileiooptions:
--file-num=Nnumberoffilestocreate[128]
--file-block-size=NblocksizetouseinallIOoperations[16384]
--file-total-size=SIZEtotalsizeoffilestocreate[2G]
--file-test-mode=STRINGtestmode{seqwr,seqrewr,seqrd,rndrd,rndwr,rndrw}
--file-io-mode=STRINGfileoperationsmode{sync,async,mmap}[sync]
--file-async-backlog=Nnumberofasynchronousoperatonstoqueueperthread[128]
--file-extra-flags=[LIST,...]listofadditionalflagstousetoopenfiles{sync,dsync,direct}[]
--file-fsync-freq=Ndofsync()afterthisnumberofrequests(0-don'tusefsync())[100]
--file-fsync-all[=on|off]dofsync()aftereachwriteoperation[off]
--file-fsync-end[=on|off]dofsync()attheendoftest[on]
--file-fsync-mode=STRINGwhichmethodtouseforsynchronization{fsync,fdatasync}[fsync]
--file-merged-requests=NmergeatmostthisnumberofIOrequestsifpossible(0-don'tmerge)[0]
--file-rw-ratio=Nreads/writesratioforcombinedtest[1.5]

[root@wallet01~]#sysbenchfileio--file-num=10--file-total-size=2Gprepare
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)
10files,209715Kbeach,2047Mbtotal
Creatingfilesforthetest...
Extrafileopenflags:(none)
Creatingfiletest_file.0
Creatingfiletest_file.1
Creatingfiletest_file.2
Creatingfiletest_file.3
Creatingfiletest_file.4
Creatingfiletest_file.5
Creatingfiletest_file.6
Creatingfiletest_file.7
Creatingfiletest_file.8
Creatingfiletest_file.9
2147614720byteswrittenin20.04seconds(102.18MiB/sec).

[root@wallet01~]#sysbenchfileio--file-num=10--file-total-size=2G--file-test-mode=rndrw--time=120run
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)
Runningthetestwithfollowingoptions:
Numberofthreads:1
Initializingrandomnumbergeneratorfromcurrenttime
Extrafileopenflags:(none)
10files,204.8MiBeach
2GiBtotalfilesize
Blocksize16KiB
NumberofIOrequests:0
Read/WriteratioforcombinedrandomIOtest:1.50
PeriodicFSYNCenabled,callingfsync()each100requests.
Callingfsync()attheendoftest,Enabled.
UsingsynchronousI/Omode
Doingrandomr/wtest
Initializingworkerthreads...
Threadsstarted!
Fileoperations:
reads/s:166.43
writes/s:110.95
fsyncs/s:27.81
Throughput:
read,MiB/s:2.60
written,MiB/s:1.73
Generalstatistics:
totaltime:120.0488s
totalnumberofevents:36629
Latency(ms):
min:0.00
avg:3.28
max:262.28
95thpercentile:28.16
sum:120010.69
Threadsfairness:
events(avg/stddev):36629.0000/0.00
executiontime(avg/stddev):120.0107/0.00

[root@wallet01~]#sysbenchfileio--file-num=10--file-total-size=2Gcleanup
sysbench1.0.16(usingbundledLuaJIT2.1.0-beta2)

Removingtestfiles...