Sysbench MySQL performance test compare on SSD & SATA

Machine Hardware Environment:

Cpu:intel (R) Xeon (r) CPU e3-1230 v3 @ 3.30GHz 4 Core

mem:32g

Ssd:intel Ssdsc2bp480g4

SATA hdd:2*500g LVM

Installation of Sysbench 0.5:

LP: ~sysbench-developers/sysbench/0.5  sysbench#cd sysbench/#. /configure--prefix=/usr/local/sysbench0. 5 #  Make  Make Install  /usr/local/src directory #cp -DPRF sysbench/usr/local/src/

Note here that the Sysbench 0.5--help option does not display the help information for OLTP, as follows:

[email protected] bin]#/usr/local/sysbench0.5/bin/sysbench--helpmissing required command argument. Usage:sysbench [general-options] ...--test=<Test-name> [Test-options] ... commandgeneral options:--num-threads=n number 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 before forcing shutdown [off]--thread-stack-siz E=size SIZE of stack per thread [64K]--tx-rate=n target transaction rate (TPS) [0]--report-interval =n periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]--report-checkpoints=[list,...] Dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test whe N Report checkpoint (s) must is performed. Report checkpoints is off by default. []--test=string test to run--debug=[on|off] print more debugging 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 [off]--rand-init=[on|off] in itialize random number generator [off]--rand-type=string random numbers distribution {uniform,gaussian,special,           Pareto} [Special]--rand-spec-iter=n number of iterations used for numbers generation []--rand-spec-pct=n Percentage of values to being treated as ' special ' (for special distribution) [1]--rand-spec-res=n Percen Tage of ' special ' values to use (for special distribution) [all]--rand-seed=n seed for random number genera      Tor, ignored when 0 [0]--rand-pareto-h=n parameter h for Pareto distibution [0.2]log options:--verbosity=n verbosity level {5-debug, 0-only critical Messages} [3]--percentile=n percentile rank of query response Tim Es to Count [95]compiled-in tests:fileio-file I/O test cpu-cpu performance test memory-memory functions speed test threads-threads su bSYSTEM Performance Test Mutex-mutex performance Testcommands:prepare run cleanup help Versionsee ' Sysbench--test=<name>Help ' for a list of options for each test.

Also need to see the help of sysben0.4, as follows:

[ email protected] bin]#/usr/local/sysbench0.4/bin/sysbench--helpusage:sysbench [general-options] ...--test= <Test-name> [Test-options] ... commandgeneral options:--num-threads=n number 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 before forcing shutdown [off]--thread-stack-size=si              ZE size of stack per thread [32K]--init-rng=[on|off] Initialize random number generator [off]--test=string Test to run--debug=[on|off] print more debugging info [off]--validate=[on|off] perform Val Idation checks where possible [off]--help=[on|off] Print help and exit--version=[on|off] print vers  Ion and exitcompiled-in tests:fileio-file I/O test cpu-cpu performance test memory-memory functions Speed test Threads-threads Subsystem Performance Test Mutex-mutex performance test OLTP-OLTP Testcommands:prepare run Cleanu P Help Versionsee ' SysbeNch--test= <name>Help ' for a list of options for each test.[email protected] bin]#./sysbench--TEST=OLTP HelpSysbench 0.4.12:multi-threaded System Evaluation BENCHMARKOLTP options:--oltp-test-mode=string test Type to us e {SIMPLE,COMPLEX,NONTRX,SP} [complex]--oltp-reconnect-mode=string reconnect mode {Session,transaction,query,random        } [Session]--oltp-sp-name=string name of store procedure to call in SP test mode []--oltp-read-only=[on|off] Generate only ' read ' queries (does not modify database) [off]--oltp-skip-trx=[on|off] Skip Begin/commit stat ements [off]--oltp-range-size=n range size for range queries [+]--oltp-point-selects=n number O F Point selects [ten]--oltp-simple-ranges=n number of simple ranges [1]--oltp-sum-ranges=n number  of sum ranges [1]--oltp-order-ranges=n number of ordered ranges [1]--oltp-distinct-ranges=n number of Distinct ranges [1]--oltp-index-updates=n number of index Update [1]--oltp-non-index-updates=n number O f non-index Updates [1]--oltp-nontrx-mode=string mode for non-transactional test {Select, Update_key, Update_nokey, INSERT, delete} [select] --oltp-auto-inc=[on|off] whether auto_increment (or equivalent) should be used on ID column [on]--oltp-connect- Delay=n time in microseconds to sleep after connection to database [10000]--oltp-user-delay-min=n Minim Um time in microseconds-to-sleep after each request [0]--oltp-user-delay-max=n maximum time in microseconds to S             Leep after each request [0]--oltp-table-name=string name of test table [Sbtest]--oltp-table-size=n Number of records in Test table [10000]--oltp-dist-type=string random numbers distribution {Uniform,gaussian,spe               cial} [Special]--oltp-dist-iter=n number of iterations used for numbers generation []--oltp-dist-pct=n               Percentage of values to being treated as ' special ' (for special distribution) [1]--oltp-dist-res=n Percentage of ' SpeciAl ' Values to use (for special distribution) [75]general database options:--db-driver=string Specifies database driver To use ("Help" to get list of available drivers)--db-ps-mode=string prepared statements usage mode {auto, disable} [auto       ]compiled-in database Drivers:mysql-mysql drivermysql options:--mysql-host=[list,...]  MySQL server host [localhost]--mysql-port=n mysql server port [3306]--mysql-socket=string MySQL Socket--mysql-user=string MySQL user [sbtest]--mysql-password=string mysql password []--mysql-db=st RING MySQL database name [sbtest]--mysql-table-engine=string storage engine to use for the test table {myi sam,innodb,bdb,heap,ndbcluster,federated} [InnoDB]--mysql-engine-trx=string whether storage engine used is TRANSACTI Onal or not {yes,no,auto} [auto]--mysql-ssl=[on|off] With SSL connections, if available in the client library [o FF]--myisam-max-rows=n max-rows paraMeter for MyISAM tables [1000000]--mysql-create-options=string additional options passed to create TABLE [] 

Sysbench test on SSD first:

The MY.CNF configuration is as follows:

[Client]Port= 3306Socket=/ssd/mysql5.5/mysql.sock[mysqld]Port= 3306Socket=/ssd/mysql5.5/mysql.sockdatadir=/ssd/mysql5.5/databack_log= 50Character-set-filesystem=Utf8character-set-server=Utf8collation-server=utf8_general_cimax_connections= 100max_connect_errors= 10Table_open_cache= 2048Max_allowed_packet=16mbinlog_cache_size=1mmax_heap_table_size=64mread_buffer_size=2mread_rnd_buffer_size=16msort_buffer_size=8mjoin_buffer_size=8mthread_cache_size= 8thread_concurrency= 8query_cache_size=64mquery_cache_limit=2mft_min_word_len= 4Default-storage-engine=Innodbthread_stack=192ktransaction_isolation=repeatable-readtmp_table_size=64mserver-id= 1key_buffer_size=32mbulk_insert_buffer_size=64mmyisam_sort_buffer_size=128mmyisam_max_sort_file_size=10gmyisam_repair_threads= 1myisam_recoverinnodb_additional_mem_pool_size=16minnodb_buffer_pool_size=20ginnodb_file_per_table=Trueinnodb_data_file_path=Ibdata1:10m:autoextendinnodb_data_ssd_dir=/ssd/mysql5.5/datainnodb_write_io_threads= 16innodb_read_io_threads= 16innodb_thread_concurrency= 16Innodb_flush_log_at_trx_commit= 1innodb_log_buffer_size=256minnodb_log_file_size=512minnodb_log_files_in_group= 3Innodb_log_group_ssd_dir=/ssd/mysql5.5/datainnodb_max_dirty_pages_pct= 90Innodb_lock_wait_timeout= 120[mysqldump]Quickmax_allowed_packet=16M[MySQL]No-auto-rehash[Myisamchk]key_buffer_size=512msort_buffer_size=512mread_buffer=8mwrite_buffer=8M[mysqlhotcopy]Interactive-timeout[Mysqld_safe]Open-files-limit= 8192

View Code

Next, use Sysbench to generate the initialization data:

/usr/local/sysbench0.5/bin/Sysbench--test=/usr/local/src/sysbench/sysbench/Sysbench--test=/usr/local/src/sysbench/sysbench/tests/db/Parallel_prepare.lua--mysql-socket=/ssd/mysql5.5/Mysql.sock--mysql-user=Root--mysql-table-engine=InnoDB--oltp-table-size=25000000 --oltp-tables-count= - --rand-init= on--num-threads= - --oltp-read-only=off--report-interval=Ten --rand-type=Special--rand-spec-pct=5 --max-requests=0 --percentile= About --max- Time=6000Run

Next Sysbench test the OLTP application:

/usr/local/sysbench0.5/bin/Sysbench--test=/usr/local/src/sysbench/sysbench/Sysbench--test=/usr/local/src/sysbench/sysbench/tests/db/Oltp.lua--mysql-socket=/ssd/mysql5.5/Mysql.sock--mysql-user=Root--mysql-table-engine=InnoDB--oltp-table-size=25000000 --oltp-tables-count= - --rand-init= on--num-threads= - --oltp-read-only=off--report-interval=Ten --rand-type=Special--rand-spec-pct=5 --max-requests=0 --percentile= About --max- Time=6000Run

The test results are as follows:

Sysbench 0.5:multi-threaded System Evaluation Benchmark

Running the test with following options:
Number of Threads:16
Report intermediate results every second (s)
Initializing random number generator from timer.

Random number generator seed is 0 and would be ignored

Threads started!

[10s] threads:16, tps:431.35, reads/s: 6056.98, writes/s: 1727.62, Response time:77.86ms (99%)
[20s] Threads:1 6, tps:671.30, reads/s: 9394.20, writes/s: 2684.20, Response time:53.66ms (99%)
[30s] threads:16, tps:889.20, read S/S: 12454.40, writes/s: 3557.70, Response time:42.33ms (99%)
[40s] threads:16, tps:970.80, reads/s: 13589.70, writ ES/S: 3883.40, Response time:39.56ms (99%)
[50s] threads:16, tps:1024.20, reads/s: 14342.20, writes/s: 4098.50, Res Ponse time:38.07ms (99%)
[60s] threads:16, tps:1034.90, reads/s: 14480.88, writes/s: 4136.40, Response time:37.70m S (99%)
[70s] threads:16, tps:947.10, reads/s: 13266.32, writes/s: 3793.20, Response time:122.98ms (99%)
[80s] Threads:16, tps:1006.20, reads/s: 14081.49, writes/s: 4020.40, Response time:77.28ms (99%)
[90s] threads:16, TPS: 938.30, reads/s: 13133.50, writes/s: 3753.10, Response time:129.52ms (99%)
.... Omit

OLTP Test Statistics:
Queries performed:
read:80147774
write:22899364
other:11449682
total:114496820
transactions:5724841 (954.14 per sec.) (on behalf of TPs)
Deadlocks:0 (0.00 per Sec.)
read/write requests:103047138 (17174.45 per sec) (on behalf of QPS)
Other operations:11449682 (1908.27 per sec.)

General Statistics:
Total time:6000.0260s
Total number of events:5724841
Total time taken by event execution:95991.9466s
Response Time:
Min:2.11ms
Avg:16.77ms
Max:75652.05ms
Approx. percentile:74.13ms

Threads Fairness:
Events (Avg/stddev): 357802.5625/970.53
Execution Time (Avg/stddev): 5999.4967/0.01

The same method for testing MySQL on SATA results, to be followed:

Sysbench MySQL performance test compare on SSD & SATA