一、磁盘测试

首先利用FIO对我本机的磁盘性能做了下简单的测试，测试过程及结果如下：

  1. [root@localhost3 storage]# /usr/local/bin/fio -filename=/storage/test_randread -direct=1 -iodepth 1 -thread -rw=randrw -rwmixread=70 -ioengine=psync -bs=16k -size=2G -numjobs=30 -runtime=120 -group_reporting -name=mytest
  2. mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K/16K-16K, ioengine=psync, iodepth=1
  3. ...
  4. fio-2.1.10
  5. Starting 30 threads
  6. mytest: Laying out IO file(s) (1 file(s) / 2048MB)
  7. Jobs: 30 (f=30): [mmmmmmmmmmmmmmmmmmmmmmmmmmmmmm] [100.0% done] [77046KB/36086KB/0KB /s] [4815/2255/0 iops] [eta 00m:00s]
  8. mytest: (groupid=0, jobs=30): err= 0: pid=6739: Sun May 20 03:43:17 2018
  9.   read : io=13007MB, bw=110989KB/s, iops=6936, runt=120002msec
  10.     clat (usec): min=0, max=213826, avg=3933.92, stdev=4254.00
  11.      lat (usec): min=0, max=213826, avg=3934.57, stdev=4254.30
  12.     clat percentiles (usec):
  13.      |  1.00th=[  604],  5.00th=[  980], 10.00th=[ 1304], 20.00th=[ 1784],
  14.      | 30.00th=[ 2160], 40.00th=[ 2544], 50.00th=[ 2928], 60.00th=[ 3376],
  15.      | 70.00th=[ 3984], 80.00th=[ 5152], 90.00th=[ 7776], 95.00th=[10944],
  16.      | 99.00th=[15552], 99.50th=[17792], 99.90th=[54528], 99.95th=[74240],
  17.      | 99.99th=[148480]
  18.     bw (KB  /s): min= 1145, max= 5461, per=3.34%, avg=3703.50, stdev=550.43
  19.   write: io=5616.6MB, bw=47927KB/s, iops=2995, runt=120002msec
  20.     clat (usec): min=0, max=128681, avg=886.32, stdev=1759.41
  21.      lat (usec): min=0, max=128683, avg=887.74, stdev=1760.38
  22.     clat percentiles (usec):
  23.      |  1.00th=[   58],  5.00th=[   73], 10.00th=[   76], 20.00th=[   89],
  24.      | 30.00th=[  131], 40.00th=[  189], 50.00th=[  318], 60.00th=[  502],
  25.      | 70.00th=[  788], 80.00th=[ 1272], 90.00th=[ 2288], 95.00th=[ 3440],
  26.      | 99.00th=[ 7264], 99.50th=[ 9920], 99.90th=[17536], 99.95th=[22656],
  27.      | 99.99th=[46848]
  28.     bw (KB  /s): min=  475, max= 2682, per=3.34%, avg=1599.02, stdev=311.41
  29.     lat (usec) : 2=0.04%, 4=0.01%, 10=0.01%, 20=0.02%, 50=0.19%
  30.     lat (usec) : 100=7.21%, 250=6.27%, 500=4.69%, 750=3.92%, 1000=4.04%
  31.     lat (msec) : 2=18.10%, 4=33.47%, 10=17.54%, 20=4.27%, 50=0.15%
  32.     lat (msec) : 100=0.07%, 250=0.01%
  33.   cpu          : usr=0.31%, sys=1.88%, ctx=1152784, majf=0, minf=6
  34.   IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
  35.      submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  36.      complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  37.      issued    : total=r=832428/w=359459/d=0, short=r=0/w=0/d=0
  38.      latency   : target=0, window=0, percentile=100.00%, depth=1
  40. Run status group 0 (all jobs):
  41.    READ: io=13007MB, aggrb=110988KB/s, minb=110988KB/s, maxb=110988KB/s, mint=120002msec, maxt=120002msec
  42.   WRITE: io=5616.6MB, aggrb=47927KB/s, minb=47927KB/s, maxb=47927KB/s, mint=120002msec, maxt=120002msec
  44. Disk stats (read/write):
  45.     dm-0: ios=831233/358981, merge=0/0, ticks=2685947/797509, in_queue=3483558, util=100.00%, aggrios=832476/359519, aggrmerge=0/1, aggrticks=2690623/727120, aggrin_queue=3415102, aggrutil=100.00%
  46.   sda: ios=832476/359519, merge=0/1, ticks=2690623/727120, in_queue=3415102, util=100.00%

从上面结果看到，我本机笔记本上的虚拟机，读IOPS可达到6936，写的IOPS却只有2995。

二、OLTP测试

对OLTP测试我设了一下几组对照测试

对照组 参数
对照组1 Innodb_flush_log_at_trx_commit=0
sync_binlog=1
对照组2 Innodb_flush_log_at_trx_commit=1
sync_binlog=1
对照组3 Innodb_flush_log_at_trx_commit=2
sync_binlog=1
对照组4 Innodb_flush_log_at_trx_commit=0
sync_binlog=100
对照组5 Innodb_flush_log_at_trx_commit=1
sync_binlog=100
对照组6 Innodb_flush_log_at_trx_commit=2
sync_binlog=100
对照组7 Innodb_flush_log_at_trx_commit=0
sync_binlog=0
对照组8 Innodb_flush_log_at_trx_commit=1
sync_binlog=0
对照组9 Innodb_flush_log_at_trx_commit=2
sync_binlog=0

因为对照组较多，所以我们写个shell脚本来跑sysbench的压测，压测逻辑大概是这样

  • 冷备数据库

  • 释放OS CACHE

  • 根据参数选择不同的配置文件启动数据库

  • 开始OLTP压测

  1. #！/bin/bash
  2. #author:xucl
  3. #date:20180520
  5. function CopyData()
  6. {
  7. cp -r /vagrant/mysql3306 /storage/
  8. chown -R mysql.mysql /storage/mysql3306
  9. }
  11. function StartMySQL()
  12. {
  13. /usr/local/mysql/bin/mysqld --defaults-file=/storage/mysql3306/my.cnf --user=mysql &
  14. }
  15. function StopMySQL()
  16. {
  17. /usr/local/mysql/bin/mysqladmin -uroot -pxcl582388 -S /tmp/mysql3306.sock shutdown 
  18. }
  20. function StartTest()
  21. {
  22. sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc \
  23. --mysql-db=sbtest --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
  24. --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on prepare  --db-driver=mysql
  26. sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc \
  27. --mysql-db=sbtest --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
  28. --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on run  --db-driver=mysql > /tmp/${log_name}.log
  30. sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=tpcc --mysql-password=tpcc \
  31. --mysql-db=sbtest --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
  32. --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on cleanup  --db-driver=mysql
  33. }
  36. ##01
  37. log_name="01"
  38. rm -rf /storage/mysql3306
  39. CopyData
  40. sync
  41. echo 3 > /proc/sys/vm/drop_caches
  42. StartMySQL
  43. sleep 3
  44. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=0;set global sync_binlog=1;";
  45. StartTest
  46. StopMySQL
  48. ##11
  49. log_name="11"
  50. rm -rf /storage/mysql3306
  51. CopyData
  52. sync
  53. echo 3 > /proc/sys/vm/drop_caches
  54. StartMySQL
  55. sleep 3
  56. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=1;set global sync_binlog=1;";
  57. StartTest
  58. StopMySQL
  60. ##21
  61. log_name="21"
  62. rm -rf /storage/mysql3306
  63. CopyData
  64. sync
  65. echo 3 > /proc/sys/vm/drop_caches
  66. StartMySQL
  67. sleep 3
  68. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=2;set global sync_binlog=1;";
  69. StartTest
  70. StopMySQL
  73. ##0 100
  74. log_name="0100"
  75. rm -rf /storage/mysql3306
  76. CopyData
  77. sync
  78. echo 3 > /proc/sys/vm/drop_caches
  79. StartMySQL
  80. sleep 3
  81. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=0;set global sync_binlog=100;";
  82. StartTest
  83. StopMySQL
  86. ##1 100
  87. log_name="1100"
  88. rm -rf /storage/mysql3306
  89. CopyData
  90. sync
  91. echo 3 > /proc/sys/vm/drop_caches
  92. StartMySQL
  93. sleep 3
  94. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=1;set global sync_binlog=100;";
  95. StartTest
  96. StopMySQL
  99. ##2 100
  100. log_name="2100"
  101. rm -rf /storage/mysql3306
  102. CopyData
  103. sync
  104. echo 3 > /proc/sys/vm/drop_caches
  105. StartMySQL
  106. sleep 3
  107. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=2;set global sync_binlog=100;";
  108. StartTest
  109. StopMySQL
  112. ##0 0
  113. log_name="00"
  114. rm -rf /storage/mysql3306
  115. CopyData
  116. sync
  117. echo 3 > /proc/sys/vm/drop_caches
  118. StartMySQL
  119. sleep 3
  120. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=0;set global sync_binlog=0;";
  121. StartTest
  122. StopMySQL
  125. ##1 0
  126. log_name="10"
  127. rm -rf /storage/mysql3306
  128. CopyData
  129. sync
  130. echo 3 > /proc/sys/vm/drop_caches
  131. StartMySQL
  132. sleep 3
  133. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=1;set global sync_binlog=0;";
  134. StartTest
  135. StopMySQL
  138. ##2 0
  139. log_name="20"
  140. rm -rf /storage/mysql3306
  141. CopyData
  142. sync
  143. echo 3 > /proc/sys/vm/drop_caches
  144. StartMySQL
  145. sleep 3
  146. mysql -uroot -pxcl582388 -S /tmp/mysql3306.sock -e "set global innodb_flush_log_at_trx_commit=2;set global sync_binlog=0;";
  147. StartTest
  148. StopMySQL

结果解读

参数 TPS
Innodb_flush_log_at_trx_commit=0 120.49
sync_binlog=1
Innodb_flush_log_at_trx_commit=1 111.73
sync_binlog=1
Innodb_flush_log_at_trx_commit=2 124.25
sync_binlog=1
Innodb_flush_log_at_trx_commit=0 125.83
sync_binlog=100
Innodb_flush_log_at_trx_commit=1 122.68
sync_binlog=100
Innodb_flush_log_at_trx_commit=2 214.80
sync_binlog=100
Innodb_flush_log_at_trx_commit=0 138.05
sync_binlog=0
Innodb_flush_log_at_trx_commit=1 99.35
sync_binlog=0
Innodb_flush_log_at_trx_commit=2 113.07
sync_binlog=0

以上是本人在笔记本虚拟机上的做的一些实验，实验结果可能不准确，仅供学习参考用。