環境

mysql5.7+centos6+sysbench

常用命令

[root@centos1 ~]# uname -a

Linux 內核名稱

centos1 主機名稱

2.6.32-431.el6.x86_64 內核版本號

#1 SMP Fri Nov 22 03:15:09 UTC 2013

x86_64 處理器

x86_64 硬件平臺類型

x86_64

GNU/Linux 操作系統名稱

[root@centos1 ~]# head -n 1 /etc/issue

CentOS release 6.5 (Final) 系統版本

//我的只是一個虛擬機,有些參數可能不正常

[root@centos1 ~]# cat /proc/cpuinfo

processor : 0

vendor_id : GenuineIntel

cpu family : 6

model : 60

model name : Intel(R) Core(TM) i3-4160 CPU @ 3.60GHz

stepping : 3

cpu MHz : 3591.769

cache size : 3072 KB

fpu : yes

fpu_exception : yes

cpuid level : 13

wp : yes

flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc up arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm arat epb xsaveopt pln pts dts fsgsbase bmi1 avx2 smep bmi2 invpcid

bogomips : 7183.53

clflush size : 64

cache_alignment : 64

address sizes : 42 bits physical, 48 bits virtual

power management:

查看系統磁盤空間

參數 -b、-k、-m、-g 分別代表以 字節、K字節、M字節、G字節為單位.

[root@centos1 ~]# free -m

total used free shared buffers cached

Mem: 980 349 631 0 23 132

-/+ buffers/cache: 193 787

Linux下清理內存和Cache辦法 /proc/sys/vm/drop_caches,默認值是0

To free pagecache:

* echo 1 > /proc/sys/vm/drop_caches

To free dentries and inodes:

* echo 2 > /proc/sys/vm/drop_caches

To free pagecache, dentries and inodes:

* echo 3 > /proc/sys/vm/drop_caches

Swap: 1983 0 1983

SWAP便是LINUX下的虛擬內存分區,它的作用是在物理內存使用完之后,將磁盤空間(也便是SWAP分區)虛擬成內存來使用.它和Windows系統的交換文件作用類似,但是它是一段連續的磁盤空間,并且對用戶不可見.

需要注意的是,雖然這個SWAP分區能夠作為"虛擬"的內存,但它的速度比物理內存可是慢多了,因此如果需要更快的速度的話,并不能寄厚望于SWAP,最好的方法仍然是加大物理內存.SWAP分區只是臨時的解決方法.

交換分區(swap)的合理值一般在內存的2 倍左右,可以適當加大.實際上具體還是以實際應用為準

df -h 和df -i顯示的占用率差別大

[root@centos1 ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda2 28G 4.3G 22G 17% /

tmpfs 491M 0 491M 0% /dev/shm

/dev/sda1 291M 34M 242M 13% /boot

[root@centos1 ~]# df -i

Filesystem Inodes IUsed IFree IUse% Mounted on

/dev/sda2 1823248 109709 1713539 7% /

tmpfs 125551 1 125550 1% /dev/shm

/dev/sda1 76912 38 76874 1% /boot

原因：刪除了一些文件,但是運行的程序仍然占用著過期的文件句柄,導致不一樣

[root@centos1 ~]# lsof / | grep deleted

mysqld 3123 mysql 4u REG 8,2 0 1446090 /tmp/ibaboxht (deleted)

mysqld 3123 mysql 5u REG 8,2 0 1446092 /tmp/iba9jBR9 (deleted)

mysqld 3123 mysql 6u REG 8,2 0 1446093 /tmp/ib6oOFrQ (deleted)

mysqld 3123 mysql 7u REG 8,2 0 1446095 /tmp/ibbF8cDd (deleted)

mysqld 3123 mysql 11u REG 8,2 0 1446096 /tmp/ibzrMOhU (deleted)

查看指定文件大小

[root@centos1 ~]# du -sh /usr/local/mysql/

816M /usr/local/mysql/

查看鏈接的用戶數量

[root@centos1 ~]# uptime

05:55:49 up 3:04, 3 users, load average: 0.00, 0.00, 0.00

[root@centos1 ~]# date -s '2016-11-09 13:56:00'

Wed Nov 9 13:56:00 PST 2016

[root@centos1 ~]# clock -w

定時任務組件安裝

[root@CentOS ~]# yum -y install vixie-cron

[root@CentOS ~]# yum -y install crontabs

說明：

vixie-cron 軟件包是 cron 的主程序；

crontabs 軟件包是用來安裝、卸裝、或列舉用來驅動 cron 守護進程的表格的程序.

sysbench使用

sysbench是一個模塊化的、跨平臺、多線程基準測試工具,主要用于評估測試各種不同系統參數下的數據庫負載情況.關于這個項目的詳細介紹請看：http://sysbench.sourceforge.net.

它主要包含以下幾種方式的測試：

1、cpu性能

2、磁盤io性能

3、調度程序性能

4、內存分配及傳輸速度

5、POSIX線程性能

6、數據庫性能(OLTP基準測試)

安裝

文件傳輸組件安裝

sz/rz

yum install lrzsz

下載sysbench

yum install -y automake

//下載[libtool](ftp://ftp.gnu.org/gnu/libtool/libtool-1.4.3.tar.gz)

tar -zvxf libtool-1.4.3.tar.gz

./configure --prefix=/usr/local/libtool && make && make install

安裝sysbench

./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

make && make install

使用

CPU測試

測試素數的加法,所有計算都會采用64位整數

//增加sysbench環境變量

vim /etc/profile

[root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run

libmysqlclient.so.18 not found

可是我在mysql/lib下面明明看到這個文件為什么不行呢?詳見壓測問題動態鏈接庫問題

[root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 4

Random number generator seed is 0 and will be ignored

Doing CPU performance benchmark

Primer numbers limit: 20000

Threads started!

Done.

General statistics:

total time: 20.2200s

total number of events: 10000

total time taken by event execution: 80.8197

response time:

min: 1.88ms

avg: 8.08ms

max: 48.07ms

approx. 95 percentile: 11.54ms

Threads fairness:

events (avg/stddev): 2500.0000/3.39

execution time (avg/stddev): 20.2049/0.01

注意: 服務器類型,有偏運算型的,有偏存儲, 所必要的指標不一樣.偏運算的(如視頻轉碼服務器)要求CPU強,而存儲則優先選擇大容量和快速存儲備.

測試的數據,孤立起來看,是沒有意義的.數據要有比擬才有意義, 比如多臺服務器的測試數據,比擬CPU性能.

IO性能測試

測試文件讀取速度,這個速度跟mysql數據查詢直接掛鉤

解釋:針對1G文件,做隨機讀寫,測試IO

–file-test-mode 還可以為

seqwr：順序寫入

seqrewq：順序重寫

seqrd：順序讀取

rndrd：隨機讀取

rndwr：隨機寫入

rndrw：混合隨機讀寫

[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G prepare

Creating file test_file.127

1073741824 bytes written in 46.61 seconds (21.97 MB/sec).

//給我創建了127個8M的小文件在sysbench目錄下 ls -h

//順序讀

[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=seqrd run

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Random number generator seed is 0 and will be ignored

Extra file open flags: 0

128 files, 8Mb each

1Gb total file size

Block size 16Kb

Periodic FSYNC enabled, calling fsync() each 100 requests.

Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing sequential read test

Threads started!

Done.

Operations performed: 65536 reads, 0 writes, 0 Other = 65536 Total

Read 1Gb Written 0b Total transferred 1Gb (261.94Mb/sec)

16764.17 Requests/sec executed

General statistics:

total time: 3.9093s

total number of events: 65536

total time taken by event execution: 3.8426

response time:

min: 0.00ms

avg: 0.06ms

max: 35.02ms

approx. 95 percentile: 0.46ms

Threads fairness:

events (avg/stddev): 65536.0000/0.00

execution time (avg/stddev): 3.8426/0.00

//隨機讀

[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=rndrd run

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Random number generator seed is 0 and will be ignored

Extra file open flags: 0

128 files, 8Mb each

1Gb total file size

Block size 16Kb

Number of random requests for random IO: 10000

Read/Write ratio for combined random IO test: 1.50

Periodic FSYNC enabled, calling fsync() each 100 requests.

Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing random read test

Threads started!

Done.

Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total

Read 156.25Mb Written 0b Total transferred 156.25Mb (8.1738Mb/sec)

523.13 Requests/sec executed

General statistics:

total time: 19.1159s

total number of events: 10000

total time taken by event execution: 19.0684

response time:

min: 0.00ms

avg: 1.91ms

max: 57.75ms

approx. 95 percentile: 9.42ms

Threads fairness:

events (avg/stddev): 10000.0000/0.00

execution time (avg/stddev): 19.0684/0.00

順序讀取262M/S隨機讀取8M/S,相差32倍!!mysql中合理的索引(后面文章將會跟上解釋)加上where語句會盡量達成順序讀.

mysql事務測試

mysql配置文件

[root@centos1 sysbench]# grep -v "^#" /etc/my.cnf | grep -v "^$"

[client]

port=3306

[mysql]

default-character-set=utf8

[mysqld]

server-id=2

port=3306

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

character-set-server=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100

query_cache_size=0

table_cache=256

tmp_table_size=34M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=67M

key_buffer_size=54M

read_buffer_size=64K

read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_additional_mem_pool_size=3M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=2M

innodb_buffer_pool_size=105M

innodb_log_file_size=53M

innodb_thread_concurrency=10

準備數據,創建測試庫test

//sbtest表會自動創建,我準備了500W的測試數據,1G左右數據

[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

FATAL: unable to connect to MySQL server, aborting...

FATAL: error 1045: Access denied for user 'root'@'localhost' (using password: YES)

FATAL: failed to connect to database server!

FATAL: Failed to create test tables

[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Creating table 'sbtest'...

Creating 5000000 records in table 'sbtest'...

測試mysql事務性能

[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 run

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Random number generator seed is 0 and will be ignored

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 10000

Using 1 test tables

Threads started!

Done.

OLTP test statistics:

queries performed:

read: 140000

write: 50000

other: 20000

total: 210000

transactions: 10000 (36.33 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 190000 (690.24 per sec.)

other operations: 20000 (72.66 per sec.)

General statistics:

total time: 275.2683s

total number of events: 10000

total time taken by event execution: 275.0810

response time:

min: 1.90ms

avg: 27.51ms

max: 581.77ms

approx. 95 percentile: 77.63ms

Threads fairness:

events (avg/stddev): 10000.0000/0.00

execution time (avg/stddev): 275.0810/0.00

第二波

–num-threads=1 表示發起 1個并發連接

–oltp-read-only=off 表示不要進行只讀測試,也便是會采用讀寫混合模式測試

–report-interval=10 表示每10秒輸出一次測試進度申報

–percentile=99 表示設定采樣比例,默認是 95%,即丟棄1%的長哀求,在剩余的99%里取最大值

真實測試場景中,建議持續壓測時長不小于1個小時,根據線上環境而論,否則測試數據可能不具參考意義.

[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 --num-threads=1 --oltp-read-only=off --report-interval=10 --percentile=99 run

sysbench 0.4.12.10: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Report intermediate results every 10 second(s)

Random number generator seed is 0 and will be ignored

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 10000

Using 1 test tables

Threads started!

--每10秒鐘申報一次測試結果,tps、每秒讀、每秒寫、99%以上的響應時長統計

[ 10s] Intermediate results: 1 threads, tps: 245.887991, reads/s: 3442.431872, writes/s: 1229.439954 response time: 27.147781ms (99%)

[ 20s] Intermediate results: 1 threads, tps: 188.598939, reads/s: 2640.385141, writes/s: 942.994693 response time: 63.163764ms (99%)

[ 30s] Intermediate results: 1 threads, tps: 195.203516, reads/s: 2732.849229, writes/s: 976.017582 response time: 58.050724ms (99%)

[ 40s] Intermediate results: 1 threads, tps: 174.599703, reads/s: 2444.395837, writes/s: 872.998513 response time: 74.445546ms (99%)

Done.

OLTP test statistics:

queries performed:

read: 140000

write: 50000

other: 20000 --其他操作總數(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等)

total: 210000

transactions: 10000 (201.29 per sec.) -- 總事務數(每秒事務數)

deadlocks: 0 (0.00 per sec.)--死鎖數

read/write requests: 190000 (3824.60 per sec.)-- 讀寫總數(每秒讀寫次數)

other operations: 20000 (402.59 per sec.)

General statistics:

total time: 49.6784s

total number of events: 10000 -- 共發生多少事務數

total time taken by event execution: 49.5798 -- 所有事務耗時相加(不考慮并行因素)

response time: --響應統計

min: 1.94ms

avg: 4.96ms

max: 698.99ms

approx. 99 percentile: 47.36ms

Threads fairness:

events (avg/stddev): 10000.0000/0.00

execution time (avg/stddev): 49.5798/0.00

個人認為,一個數據庫服務器好不好,跟系統要求有直接的關系,能符合業務需求的便是合格的服務器,基準測試只是看看服務器能承受的極限是多少,供系統上線后調優方向參考.