mysql调度算法_MySQL优化--IO调度算法优化

最新推荐文章于 2022-01-25 15:41:49 发布

weixin_39633954

最新推荐文章于 2022-01-25 15:41:49 发布

阅读量174

点赞数

文章标签： mysql调度算法

本文链接：https://blog.csdn.net/weixin_39633954/article/details/113220861

版权

之前已经在微信公众号分享了数据库优化的方法，链接为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 installsysbench

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

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)4files, 5242880Kb each, 20480Mb total

Creating filesforthe test...

Extrafileopen flags: (none)

Creatingfile test_file.0Creatingfile test_file.1Creatingfile test_file.2Creatingfile test_file.3

21474836480 bytes written in 47.94 seconds (427.24 MiB/sec).

1.3 准备测试表

因为也要进行数据库读写方面的测试，因此需要先创建相关表及数据

[root@mha1 ~]# sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=testdb --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable prepare

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table'sbtest3'...

Creating table'sbtest2'...

Creating table'sbtest5'...

Creating table'sbtest6'...

Creating table'sbtest8'...

Creating table'sbtest7'...

Creating table'sbtest4'...

Creating table'sbtest1'...

Creating table'sbtest10'...

Creating table'sbtest9'...

Inserting1000000 records into 'sbtest4'Inserting1000000 records into 'sbtest7'Inserting1000000 records into 'sbtest5'Inserting1000000 records into 'sbtest3'Inserting1000000 records into 'sbtest1'Inserting1000000 records into 'sbtest2'Inserting1000000 records into 'sbtest8'Inserting1000000 records into 'sbtest6'Inserting1000000 records into 'sbtest10'Inserting1000000 records into 'sbtest9'Creating a secondary index on'sbtest7'...

Creating a secondary index on'sbtest10'...

Creating a secondary index on'sbtest8'...

Creating a secondary index on'sbtest5'...

Creating a secondary index on'sbtest2'...

Creating a secondary index on'sbtest9'...

Creating a secondary index on'sbtest1'...

Creating table'sbtest17'...

Creating a secondary index on'sbtest3'...

Inserting1000000 records into 'sbtest17'Creating a secondary index on'sbtest4'...

Creating a secondary index on'sbtest6'...

Creating table'sbtest20'...

Inserting1000000 records into 'sbtest20'Creating table'sbtest18'...

Inserting1000000 records into 'sbtest18'Creating table'sbtest15'...

Inserting1000000 records into 'sbtest15'Creating table'sbtest19'...

Inserting1000000 records into 'sbtest19'Creating table'sbtest14'...

Inserting1000000 records into 'sbtest14'Creating table'sbtest11'...

Inserting1000000 records into 'sbtest11'Creating table'sbtest13'...

Creating table'sbtest12'...

Inserting1000000 records into 'sbtest13'Inserting1000000 records into 'sbtest12'Creating table'sbtest16'...

Inserting1000000 records into 'sbtest16'Creating a secondary index on'sbtest17'...

Creating a secondary index on'sbtest20'...

Creating a secondary index on'sbtest18'...

Creating a secondary index on'sbtest19'...

Creating a secondary index on'sbtest15'...

Creating a secondary index on'sbtest11'...

Creating a secondary index on'sbtest12'...

Creating a secondary index on'sbtest13'...

Creating a secondary index on'sbtest14'...

Creating a secondary index on'sbtest16'...

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

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing randomwritetest

Initializing worker threads...

Threads started!File operations:

reads/s: 0.00writes/s: 6935.37fsyncs/s: 0.53Throughput:

read, MiB/s: 0.00written, MiB/s: 108.37General statistics:

totaltime: 180.0138s

total number of events:1248484Latency (ms):

min:0.10avg:3.46max:107.3995th percentile:14.73

sum: 4317610.93Threads fairness:

events (avg/stddev): 52020.1667/426.95executiontime (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

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling 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.88writes/s: 0.00fsyncs/s: 0.00Throughput:

read, MiB/s: 124.33written, MiB/s: 0.00General statistics:

totaltime: 180.0075s

total number of events:1432313Latency (ms):

min:0.10avg:3.01max:322.2495th percentile:5.47

sum: 4309094.67Threads fairness:

events (avg/stddev): 59679.7083/2688.56executiontime (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:

write: 589934other:294968total:884902transactions:147483 (491.43per sec.)

queries:884902 (2948.62per sec.)

ignored errors:2 (0.01per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.1050s

total number of events:147483Latency (ms):

min:2.58avg:16.27max:2608.3495th percentile:35.59

sum: 2399415.58Threads fairness:

events (avg/stddev): 18435.3750/90.33executiontime (avg/stddev): 299.9269/0.04

可见，随机写入的TPS为491.43 ,查询次数为2948.62

3.4 测试数据库读

SQL statistics:

queries performed:

write: 0other:235956total:1887648transactions:117978 (393.13per sec.)

queries:1887648 (6290.13per sec.)

ignored errors:0 (0.00per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.0949s

total number of events:117978Latency (ms):

min:3.08avg:20.34max:170.4895th percentile:29.19

sum: 2399636.31Threads fairness:

events (avg/stddev): 14747.2500/1513.84executiontime (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 随机写

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing randomwritetest

Initializing worker threads...

Threads started!File operations:

reads/s: 0.00writes/s: 7057.60fsyncs/s: 0.53Throughput:

read, MiB/s: 0.00written, MiB/s: 110.27General statistics:

totaltime: 180.0136s

total number of events:1270481Latency (ms):

min:0.10avg:3.40max:240.3995th percentile:14.46

sum: 4317435.99Threads fairness:

events (avg/stddev): 52936.7083/487.57executiontime (avg/stddev): 179.8932/0.02

随机写的iops为7057.60，磁盘写入速度是110.27MiB/s

4.3 随机读

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling 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.89writes/s: 0.00fsyncs/s: 0.00Throughput:

read, MiB/s: 131.25written, MiB/s: 0.00General statistics:

totaltime: 180.0100s

total number of events:1512081Latency (ms):

min:0.10avg:2.85max:315.7795th percentile:5.00

sum: 4312384.33Threads fairness:

events (avg/stddev): 63003.3750/10086.77executiontime (avg/stddev): 179.6827/0.12

随机读的iops为8399.89，磁盘读取速度是131.25MiB/s

4.4 数据库写入

SQL statistics:

queries performed:

write: 653457other:326730total:980187transactions:163364 (544.38per sec.)

queries:980187 (3266.28per sec.)

ignored errors:2 (0.01per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.0903s

total number of events:163364Latency (ms):

min:2.62avg:14.69max:220.1295th percentile:32.53

sum: 2399040.57Threads fairness:

events (avg/stddev): 20420.5000/112.69executiontime (avg/stddev): 299.8801/0.04

可见，随机写入的TPS为 544.38 ,查询次数为3266.28

4.5 数据库只读

SQL statistics:

queries performed:

write: 0other:228052total:1824416transactions:114026 (379.97per sec.)

queries:1824416 (6079.59per sec.)

ignored errors:0 (0.00per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.0869s

total number of events:114026Latency (ms):

min:3.08avg:21.04max:321.0395th percentile:31.37

sum: 2399600.56Threads fairness:

events (avg/stddev): 14253.2500/1475.71executiontime (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 随机写

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing randomwritetest

Initializing worker threads...

Threads started!File operations:

reads/s: 0.00writes/s: 6614.37fsyncs/s: 0.53Throughput:

read, MiB/s: 0.00written, MiB/s: 103.35General statistics:

totaltime: 180.0118s

total number of events:1190677Latency (ms):

min:0.10avg:3.63max:348.7895th percentile:15.27

sum: 4317092.54Threads fairness:

events (avg/stddev): 49611.5417/517.80executiontime (avg/stddev): 179.8789/0.03

随机写的iops为6614.37，磁盘写入速度是103.35MiB/s

5.3 随机读

sysbench1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads:24Initializing random number generator from currenttimeExtrafileopen flags: directio4files, 5GiB each

20GiB totalfilesize

Block size 16KiB

Number of IO requests:0Read/Write ratio for combined random IO test: 1.50Calling 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.39writes/s: 0.00fsyncs/s: 0.00Throughput:

read, MiB/s: 116.90written, MiB/s: 0.00General statistics:

totaltime: 180.0086s

total number of events:1346731Latency (ms):

min:0.10avg:3.20max:374.4995th percentile:5.77

sum: 4312382.07Threads fairness:

events (avg/stddev): 56113.7917/3058.00executiontime (avg/stddev): 179.6826/0.17

随机读的iops为7481.39，磁盘读取速度是116.90MiB/s

5.4 数据库写

SQL statistics:

queries performed:

write: 598765other:299384total:898149transactions:149691 (498.54per sec.)

queries:898149 (2991.25per sec.)

ignored errors:2 (0.01per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.2552s

total number of events:149691Latency (ms):

min:2.55avg:16.02max:779.6295th percentile:35.59

sum: 2397311.08Threads fairness:

events (avg/stddev): 18711.3750/132.24executiontime (avg/stddev): 299.6639/0.38

可见，随机写入的TPS为498.54 ,查询次数为2991.25

5.5 数据库读

SQL statistics:

queries performed:

write: 0other:206906total:1655248transactions:103453 (344.66per sec.)

queries:1655248 (5514.58per sec.)

ignored errors:0 (0.00per sec.)

reconnects:0 (0.00per sec.)

General statistics:

totaltime: 300.1562s

total number of events:103453Latency (ms):

min:3.11avg:23.19max:222.3195th percentile:38.25

sum: 2399486.55Threads fairness:

events (avg/stddev): 12931.6250/1278.72executiontime (avg/stddev): 299.9358/0.01

可见，只读时的TPS为 344.66,查询次数为5514.58

6 小结

根据测试结果对比一下三种调度算法的读写速度

算法

IOPS

磁盘写速度

IOPS

磁盘读速度

oltp_write_only

oltp_read_only

deadline

6935.37

118.37MiB/s

7956.88

124.33MiB/s

TPS为491.43 ,查询次数为2948.62

TPS为393.13 ,查询次数为6290.13

noop

7057.60

110.27MiB/s

8399.89

131.25MiB/s

TPS为 544.38 ,查询次数为3266.28

TPS为 379.97,查询次数为6079.59

cfq

6614.37

103.35MiB/s

7481.39

116.90MiB/s

TPS为498.54 ,查询次数为2991.25

TPS为 344.66,查询次数为5514.58

因为本次测试环境为SSD硬盘，因此，在此情况下建议选择noop磁盘IO调度算法，此结论也符合我们的预期。

特别注意：磁盘IO的调度算法还需要根据磁盘情况、数据库类型、数据库架构、业务场景(OLTP、OLAP等)等各种场景进行区分，不同的场景调度算法也要调整，不可一概而论。如果不确定的话，建议进行压测来判断，选择符合对应场景下最合适的算法。

想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。

weixin_39633954

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql调度算法_MySQL优化--IO调度算法优化

之前已经在微信公众号分享了数据库优化的方法，链接为https://mp.weixin.qq.com/s/6Atzk9UKPJRxxAs0nsKBXg。其中操作系统部分介绍了IO调度算法的优化，本文将通过压力测试的方式来对比不同的调度算法下磁盘IO的表现。1 准备工作1.1 安装sysbench本次采用sysbench进行压测，先安装sysbench，步骤如下：curl -s https://...
复制链接

扫一扫