Sysbench MySQL performance test compare on SSD & SATA

Source: Internet
Author: User
Tags generator lua mutex ranges server port

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.