Sysbench MYSQL-SSD and HDD performance test

Test environment

Operating system: CentOS7.2.1511 64-bit

mysql:mysql5.7.12 64-bit

HDD: SSD, HDD

Test tool: sysbench0.5

Installing sysbench0.5

Updating and installing dependent packages

git clone https://github.com/akopytov/sysbench cd sysbench./autogen.sh

# with-mysql-includes Specify MySQL installation directory

# WITH-MYSQL-LIBS Specifies the directory where MySQL Lib resides

./configure--with-mysql-includes=/usr/local/mysql/include/--with-mysql-libs=/usr/local/mysql/lib/make & & Make Install

The following error occurred

/bin/sh. /libtool--tag=cc--mode=link gcc-pthread-g-o2-o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o Db_dri VER.O tests/fileio/libsbfileio.a TESTS/THREADS/LIBSBTHREADS.A tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests /oltp/libsboltp.a tests/mutex/libsbmutex.a Drivers/mysql/libsbmysql.a-l/usr/mysql/lib-lmysqlclient_r not found

Cd/usr/local/mysql/lib/mysql/ln-s libmysqlclient.so.15.0.0 Libmysqlclient_r.socp/usr/bin/libtool Libtool

The second problem is to execute the Times error after installing Sysbench:

This means that Sysbench cannot find the MySQL library file, which is most likely the environment variable ld_library_path is not set and can be set to resolve the problem:

Export Ld_library_path=/usr/local/mysql/lib

I. Data preparation

[Email protected] sysbench_0.5]# sysbench--mysql-db=iotest--num-threads=24--mysql-engine-trx=yes-- Mysql-table-engine=innodb--rand-type=special--mysql-host=localhost--mysql-port=3306--mysql-user=root-- mysql-password=123123--mysql-socket=/tmp/mysql.sock--test=sysbench/tests/db/oltp.lua--oltp_tables_count=50-- oltp-table-size=10000--rand-init=on Prepare

Two. INNODB Test commands

[[email protected] sysbench] #sysbench--mysql-db=iotest--mysql-engine-trx=yes--mysql-table-engine=innodb-- Mysql-host=localhost--mysql-port=3306--mysql-user=root--mysql-password=123123--mysql-socket=/tmp/mysql.sock-- Test=sysbench/tests/db/oltp.lua--oltp_tables_count=10--oltp-table-size=10000--num-threads=10--oltp-read-only= Off--report-interval=10--rand-type=uniform--max-time=600--max-requests=0--percentile=95 Run

Parameter description

--MYSQL-DB: Specifies the database in which to create the test table, the default is the Iotest library, which needs to be created in advance.

--num-threads: Use multithreading to create multiple tables, saving preparation time

--mysql-engine-trx=yes Support Transactions

--mysql-table-engine: Specifies the storage engine, such as myisam,innodb,heap,ndbcluster,bdb,maria,falcon,pbxt default InnoDB

--test=tests/db/oltp.lua means calling the Tests/db/oltp.lua script for OLTP mode testing

--OLTP_TABLES_COUNT=10 indicates that 10 test tables are generated

--oltp-table-size=10000 indicates that the amount of data to be populated for each test table is 10000

--oltp-read-only=off indicates that read-only testing is not performed, that is, a read-write mixed mode test is used

--REPORT-INTERVAL=10 indicates a test progress report is output every 10 seconds

--rand-type=uniform: Data distribution mode, special indicates presence of hotspot data, uniform represents non-hotspot data mode: uniform (fixed), Gaussian (Gaussian), special (specific), Pareto (Pareto)

--max-time=600 indicates a maximum execution duration of 600 seconds

--max-requests=0 indicates that the total number of requests is 0 because the total execution time has been defined above, so the total number of requests can be set to 0, or only the total number of requests, not the maximum execution duration is set.

--PERCENTILE=95 indicates the set sampling scale, default is 95%, that is, discard 5% long request, in the remaining 95% to take the maximum value

Three. Test results

Ssds. TPS=495.30K/S HDD. tps=83.06k/s

SSD.READS=6934.34K/S hdd.reads=1163.4k/s

SSD.WRITES=1981.18K/S hdd.writes=332.347k/s

Ssds. Response_time=37.08ms HDD. Response_time=420.25ms

Four. Test conclusion

1.TPS thing handling: MySQL runs on SSDs 5.9631 times times faster on HDD

2.reads:mysql running on SSDs is 5.9604 times times the size of the HDD

3.writes:mysql running on SSDs is 5.9611 times times the size of the HDD

4.response_time:mysql runs 11.33 times times faster on SSDs than on HDD

5.errors:mysql running on SSD is twice times higher error rate on HDD and there are some discrepancies in expectations

This article from "Running" blog, declined reprint!

Sysbench MYSQL-SSD and HDD performance test