#頭條創作挑戰賽#
之前已經在微信公衆号分享了資料庫優化的方法，連結為https://mp.weixin.qq.com/s/6Atzk9UKPJRxxAs0nsKBXg 。 其中作業系統部分介紹了IO排程算法的優化，本文将通過壓力測試的方式來對比不同的排程算法下磁盤IO的表現。
1 準備工作
1.1 安裝sysbench
本次采用sysbench進行壓測，先安裝sysbench，步驟如下：
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench
sysbench --version 1.2 準備測試檔案
生成後續需要使用的測試檔案，block大小為16k（MySQL DBA都懂的，哈哈），建立4個檔案，合計20G
[root@mha1 ~]# sysbench fileio --file-num=4 --file-block-size=16384 --file-total-size=20G prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
4 files, 5242880Kb each, 20480Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
21474836480 bytes written in 47.94 seconds (427.24 MiB/sec). 1.3 準備測試表
因為也要進行資料庫讀寫方面的測試，是以需要先建立相關表及資料
[root@mha1 ~]# sysbench fileio --file-num=4 --file-block-size=16384 --file-total-size=20G prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
4 files, 5242880Kb each, 20480Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
21474836480 bytes written in 47.94 seconds (427.24 MiB/sec). 2 檢視支援的排程算法
本次磁盤為SSD硬碟，作業系統版本文Centos7.8 。下面将排程算法修改為三種不同的值來進行随機讀與随機寫的壓力測試
本系統為Centos7.8,需要檢視支援的IO排程算法，然後再進行修改測試。
[root@mha1 ~]# dmesg | grep -i scheduler
[ 4.885816] io scheduler noop registered
[ 4.885820] io scheduler deadline registered (default)
[ 4.885867] io scheduler cfq registered
[ 4.885870] io scheduler mq-deadline registered
[ 4.885872] io scheduler kyber registered 可見，再本系統中，預設的排程算法為 deadline。
也可以通過如下指令檢視目前的排程算法，其中中括号裡代表目前使用的排程算法。
[root@mha1 ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq 3 deadline算法
Deadline在機械盤的情況下對資料庫環境(ORACLE RAC,MySQL等)是最好的選擇。下面将進行随機寫與随機讀的壓力測試
3.1 随機寫
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndwr \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...
Threads started!
File operations:
reads/s: 0.00
writes/s: 6935.37
fsyncs/s: 0.53
Throughput:
read, MiB/s: 0.00
written, MiB/s: 108.37
General statistics:
total time: 180.0138s
total number of events: 1248484
Latency (ms):
min: 0.10
avg: 3.46
max: 107.39
95th percentile: 14.73
sum: 4317610.93
Threads fairness:
events (avg/stddev): 52020.1667/426.95
execution time (avg/stddev): 179.9005/0.01 随機寫的iops為6935.37，磁盤寫入速度是108.37MiB/s
3.2 随機讀
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndrd \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
File operations:
reads/s: 7956.88
writes/s: 0.00
fsyncs/s: 0.00
Throughput:
read, MiB/s: 124.33
written, MiB/s: 0.00
General statistics:
total time: 180.0075s
total number of events: 1432313
Latency (ms):
min: 0.10
avg: 3.01
max: 322.24
95th percentile: 5.47
sum: 4309094.67
Threads fairness:
events (avg/stddev): 59679.7083/2688.56
execution time (avg/stddev): 179.5456/0.18 随機讀的iops為7956.88，磁盤讀取速度是124.33MiB/s
3.3 測試資料庫寫
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_write_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 0
write: 589934
other: 294968
total: 884902
transactions: 147483 (491.43 per sec.)
queries: 884902 (2948.62 per sec.)
ignored errors: 2 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1050s
total number of events: 147483
Latency (ms):
min: 2.58
avg: 16.27
max: 2608.34
95th percentile: 35.59
sum: 2399415.58
Threads fairness:
events (avg/stddev): 18435.3750/90.33
execution time (avg/stddev): 299.9269/0.04 可見，随機寫入的TPS為491.43 ,查詢次數為2948.62
3.4 測試資料庫讀
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 1651692
write: 0
other: 235956
total: 1887648
transactions: 117978 (393.13 per sec.)
queries: 1887648 (6290.13 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0949s
total number of events: 117978
Latency (ms):
min: 3.08
avg: 20.34
max: 170.48
95th percentile: 29.19
sum: 2399636.31
Threads fairness:
events (avg/stddev): 14747.2500/1513.84
execution time (avg/stddev): 299.9545/0.04 可見，随機讀時的TPS為393.13 ,查詢次數為6290.13
4 noop算法
4.1 修改為noop算法
noop稱為電梯排程算法，是基于FIFO隊列實作的。所有的請求都是先進先出的，因為SSD的随機讀、随機寫速度快，是以該算法适合SSD硬碟。
[root@mha1 ~]# echo 'noop' >/sys/block/sda/queue/scheduler
[root@mha1 ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq 4.2 随機寫
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndwr \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...
Threads started!
File operations:
reads/s: 0.00
writes/s: 7057.60
fsyncs/s: 0.53
Throughput:
read, MiB/s: 0.00
written, MiB/s: 110.27
General statistics:
total time: 180.0136s
total number of events: 1270481
Latency (ms):
min: 0.10
avg: 3.40
max: 240.39
95th percentile: 14.46
sum: 4317435.99
Threads fairness:
events (avg/stddev): 52936.7083/487.57
execution time (avg/stddev): 179.8932/0.02 随機寫的iops為7057.60，磁盤寫入速度是110.27MiB/s
4.3 随機讀
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndrd \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
File operations:
reads/s: 8399.89
writes/s: 0.00
fsyncs/s: 0.00
Throughput:
read, MiB/s: 131.25
written, MiB/s: 0.00
General statistics:
total time: 180.0100s
total number of events: 1512081
Latency (ms):
min: 0.10
avg: 2.85
max: 315.77
95th percentile: 5.00
sum: 4312384.33
Threads fairness:
events (avg/stddev): 63003.3750/10086.77
execution time (avg/stddev): 179.6827/0.12 随機讀的iops為8399.89，磁盤讀取速度是131.25MiB/s
4.4 資料庫寫入
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_write_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 0
write: 653457
other: 326730
total: 980187
transactions: 163364 (544.38 per sec.)
queries: 980187 (3266.28 per sec.)
ignored errors: 2 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0903s
total number of events: 163364
Latency (ms):
min: 2.62
avg: 14.69
max: 220.12
95th percentile: 32.53
sum: 2399040.57
Threads fairness:
events (avg/stddev): 20420.5000/112.69
execution time (avg/stddev): 299.8801/0.04 可見，随機寫入的TPS為 544.38 ,查詢次數為3266.28
4.5 資料庫隻讀
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 1596364
write: 0
other: 228052
total: 1824416
transactions: 114026 (379.97 per sec.)
queries: 1824416 (6079.59 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0869s
total number of events: 114026
Latency (ms):
min: 3.08
avg: 21.04
max: 321.03
95th percentile: 31.37
sum: 2399600.56
Threads fairness:
events (avg/stddev): 14253.2500/1475.71
execution time (avg/stddev): 299.9501/0.02 可見，隻讀時的TPS為 379.97,查詢次數為6079.59
5 cfq算法
5.1 修改為cfq算法
cfq稱為絕對公平排程算法，為每個程序及線程單獨建立一個隊列來管理IO請求，起到每個程序和線程均勻分布IO的效果。此算法适用于通用伺服器，centos6中為預設的IO排程算法。
[root@mha1 ~]# echo 'cfq' >/sys/block/sda/queue/scheduler
[root@mha1 ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq] 5.2 随機寫
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndwr \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...
Threads started!
File operations:
reads/s: 0.00
writes/s: 6614.37
fsyncs/s: 0.53
Throughput:
read, MiB/s: 0.00
written, MiB/s: 103.35
General statistics:
total time: 180.0118s
total number of events: 1190677
Latency (ms):
min: 0.10
avg: 3.63
max: 348.78
95th percentile: 15.27
sum: 4317092.54
Threads fairness:
events (avg/stddev): 49611.5417/517.80
execution time (avg/stddev): 179.8789/0.03 随機寫的iops為6614.37，磁盤寫入速度是103.35MiB/s
5.3 随機讀
[root@mha1 ~]# sysbench fileio \
> --time=180 \
> --threads=24 \
> --file-total-size=20G \
> --file-test-mode=rndrd \
> --file-num=4 \
> --file-extra-flags=direct \
> --file-fsync-freq=0 \
> --file-block-size=16384 \
> run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: directio
4 files, 5GiB each
20GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
File operations:
reads/s: 7481.39
writes/s: 0.00
fsyncs/s: 0.00
Throughput:
read, MiB/s: 116.90
written, MiB/s: 0.00
General statistics:
total time: 180.0086s
total number of events: 1346731
Latency (ms):
min: 0.10
avg: 3.20
max: 374.49
95th percentile: 5.77
sum: 4312382.07
Threads fairness:
events (avg/stddev): 56113.7917/3058.00
execution time (avg/stddev): 179.6826/0.17 随機讀的iops為7481.39，磁盤讀取速度是116.90MiB/s
5.4 資料庫寫
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_write_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 0
write: 598765
other: 299384
total: 898149
transactions: 149691 (498.54 per sec.)
queries: 898149 (2991.25 per sec.)
ignored errors: 2 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.2552s
total number of events: 149691
Latency (ms):
min: 2.55
avg: 16.02
max: 779.62
95th percentile: 35.59
sum: 2397311.08
Threads fairness:
events (avg/stddev): 18711.3750/132.24
execution time (avg/stddev): 299.6639/0.38 可見，随機寫入的TPS為498.54 ,查詢次數為2991.25
5.5 資料庫讀
sysbench --db-driver=mysql --time=300 --threads=8 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=10 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
SQL statistics:
queries performed:
read: 1448342
write: 0
other: 206906
total: 1655248
transactions: 103453 (344.66 per sec.)
queries: 1655248 (5514.58 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1562s
total number of events: 103453
Latency (ms):
min: 3.11
avg: 23.19
max: 222.31
95th percentile: 38.25
sum: 2399486.55
Threads fairness:
events (avg/stddev): 12931.6250/1278.72
execution time (avg/stddev): 299.9358/0.01 可見，隻讀時的TPS為 344.66,查詢次數為5514.58
6 小結
根據測試結果對比一下三種排程算法的讀寫速度
算法IOPS磁盤寫速度IOPS磁盤讀速度oltp_write_only oltp_read_only
deadline6935.37118.37MiB/s7956.88124.33MiB/sTPS為491.43 ,查詢次數為2948.62 TPS為393.13 ,查詢次數為6290.13
noop7057.60110.27MiB/s8399.89131.25MiB/sTPS為 544.38 ,查詢次數為3266.28TPS為 379.97,查詢次數為6079.59
cfq6614.37103.35MiB/s7481.39116.90MiB/sTPS為498.54 ,查詢次數為2991.25 TPS為 344.66,查詢次數為5514.58
因為本次測試環境為SSD硬碟，是以，在此情況下建議選擇noop磁盤IO排程算法，此結論也符合我們的預期。
特别注意：磁盤IO的排程算法還需要根據磁盤情況、資料庫類型、資料庫架構、業務場景（OLTP、OLAP等）等各種場景進行區分，不同的場景排程算法也要調整，不可一概而論。如果不确定的話，建議進行壓測來判斷，選擇符合對應場景下最合适的算法。
想了解更多内容或參與技術交流可以關注微信公衆号【資料庫幹貨鋪】或進技術交流群溝通。
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)