GreatSQL茶话会——分享一下你的五一假期

GreatSQL社区

搜索

[已解决] mysql slow log 问题排查

571 5 2023-2-8 13:35
系统版本: CentOS Linux release 7.7.1908 (Core)
MySQL版本: Percona-Server-5.7.40-43-Linux.x86_64.glibc2.17
服务器配置:  
CPU: 2 x AMD EPYC 7742 64-Core Processor
内存: 2T
硬盘: 6 x Intel DC P4610 7.68TB NVMe 组软raid10
压测命令: sysbench 1.0.20
  1. sysbench /usr/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench2023 --mysql-db=sbtest --tables=30 --table-size=1000000 --time=1800 --report-interval=1 --threads=512 run
复制代码

先上图: binlog 切换时 出现 slow log ,大部分为 update insert 语句.


这个现象持续了好久,一直没有解决, 最近几天做sysbench压测 也发现了类似的问题, binlog切换时 sysbench tps掉到0.


oltp_write_only
  1. [ 8s ] thds: 512 tps: 20572.90 qps: 123480.43 (r/w/o: 0.00/82320.62/41159.81) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
  2. [ 9s ] thds: 512 tps: 20702.38 qps: 124304.26 (r/w/o: 0.00/82893.51/41410.75) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  3. [ 10s ] thds: 512 tps: 0.00 qps: 318.87 (r/w/o: 0.00/280.88/37.98) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  4. [ 11s ] thds: 512 tps: 5223.13 qps: 30825.66 (r/w/o: 0.00/20427.41/10398.25) lat (ms,95%): 1803.47 err/s: 0.00 reconn/s: 0.00
  5. [ 12s ] thds: 512 tps: 21712.28 qps: 130219.67 (r/w/o: 0.00/86806.11/43413.56) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  6. [ 13s ] thds: 512 tps: 21231.51 qps: 127492.07 (r/w/o: 0.00/85014.05/42478.02) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  7. [ 14s ] thds: 512 tps: 21348.92 qps: 128019.51 (r/w/o: 0.00/85313.67/42705.84) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  8. [ 15s ] thds: 512 tps: 21158.23 qps: 127125.39 (r/w/o: 0.00/84814.93/42310.46) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  9. [ 16s ] thds: 512 tps: 21136.05 qps: 126572.27 (r/w/o: 0.00/84301.18/42271.09) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  10. [ 17s ] thds: 512 tps: 21046.11 qps: 126507.69 (r/w/o: 0.00/84420.46/42087.23) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  11. [ 18s ] thds: 512 tps: 21037.55 qps: 126228.31 (r/w/o: 0.00/84142.21/42086.10) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  12. [ 19s ] thds: 512 tps: 21149.28 qps: 126773.70 (r/w/o: 0.00/84494.14/42279.56) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  13. [ 20s ] thds: 512 tps: 20885.42 qps: 125334.54 (r/w/o: 0.00/83556.69/41777.84) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  14. [ 21s ] thds: 512 tps: 21163.80 qps: 126879.83 (r/w/o: 0.00/84553.23/42326.61) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  15. [ 22s ] thds: 512 tps: 21068.88 qps: 126649.30 (r/w/o: 0.00/84508.54/42140.76) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  16. [ 23s ] thds: 512 tps: 21237.77 qps: 127448.64 (r/w/o: 0.00/84958.10/42490.55) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  17. [ 24s ] thds: 512 tps: 21097.83 qps: 126392.00 (r/w/o: 0.00/84210.34/42181.67) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  18. [ 25s ] thds: 512 tps: 21110.70 qps: 126649.17 (r/w/o: 0.00/84435.78/42213.39) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  19. [ 26s ] thds: 512 tps: 21075.62 qps: 126665.75 (r/w/o: 0.00/84491.50/42174.25) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  20. [ 27s ] thds: 512 tps: 21021.28 qps: 125964.68 (r/w/o: 0.00/83939.12/42025.56) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  21. [ 28s ] thds: 512 tps: 21048.98 qps: 126397.90 (r/w/o: 0.00/84287.93/42109.97) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  22. [ 29s ] thds: 512 tps: 21077.88 qps: 126320.29 (r/w/o: 0.00/84177.53/42142.76) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  23. [ 30s ] thds: 512 tps: 5470.12 qps: 33261.68 (r/w/o: 0.00/22287.43/10974.24) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  24. [ 31s ] thds: 512 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  25. [ 32s ] thds: 512 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  26. [ 33s ] thds: 512 tps: 19456.78 qps: 116481.78 (r/w/o: 0.00/77590.21/38891.57) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
  27. [ 34s ] thds: 512 tps: 21481.80 qps: 128830.77 (r/w/o: 0.00/85860.17/42970.60) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  28. [ 35s ] thds: 512 tps: 21277.20 qps: 127554.22 (r/w/o: 0.00/85013.82/42540.41) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  29. [ 36s ] thds: 512 tps: 20266.93 qps: 121969.62 (r/w/o: 0.00/81393.75/40575.87) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  30. [ 37s ] thds: 512 tps: 795.00 qps: 4412.99 (r/w/o: 0.00/2868.99/1544.00) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
  31. [ 38s ] thds: 512 tps: 22659.98 qps: 136055.89 (r/w/o: 0.00/90740.92/45314.96) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  32. [ 39s ] thds: 512 tps: 21444.98 qps: 128531.87 (r/w/o: 0.00/85648.92/42882.95) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  33. [ 40s ] thds: 512 tps: 21363.77 qps: 128127.63 (r/w/o: 0.00/85385.08/42742.54) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  34. [ 41s ] thds: 512 tps: 21492.14 qps: 129114.83 (r/w/o: 0.00/86132.55/42982.28) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  35. [ 42s ] thds: 512 tps: 21605.65 qps: 129612.91 (r/w/o: 0.00/86399.60/43213.30) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  36. [ 43s ] thds: 512 tps: 21400.11 qps: 128459.63 (r/w/o: 0.00/85656.42/42803.21) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  37. [ 44s ] thds: 512 tps: 21457.30 qps: 128626.79 (r/w/o: 0.00/85721.19/42905.60) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  38. [ 45s ] thds: 512 tps: 21163.81 qps: 127080.84 (r/w/o: 0.00/84747.23/42333.61) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  39. [ 46s ] thds: 512 tps: 21196.93 qps: 127265.59 (r/w/o: 0.00/84862.73/42402.86) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  40. [ 47s ] thds: 512 tps: 21182.23 qps: 127103.39 (r/w/o: 0.00/84744.92/42358.46) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  41. [ 48s ] thds: 512 tps: 21324.91 qps: 127814.48 (r/w/o: 0.00/85166.67/42647.82) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  42. [ 49s ] thds: 512 tps: 21035.57 qps: 126202.41 (r/w/o: 0.00/84136.27/42066.14) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  43. [ 50s ] thds: 512 tps: 20946.67 qps: 125730.01 (r/w/o: 0.00/83834.68/41895.33) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  44. [ 51s ] thds: 512 tps: 21238.73 qps: 127513.37 (r/w/o: 0.00/85032.91/42480.45) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  45. [ 52s ] thds: 512 tps: 21237.70 qps: 127416.23 (r/w/o: 0.00/84933.82/42482.41) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  46. [ 53s ] thds: 512 tps: 20929.03 qps: 125552.16 (r/w/o: 0.00/83698.11/41854.05) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
  47. [ 54s ] thds: 512 tps: 21047.94 qps: 126225.64 (r/w/o: 0.00/84130.76/42094.88) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  48. [ 55s ] thds: 512 tps: 21058.31 qps: 126387.87 (r/w/o: 0.00/84274.24/42113.62) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  49. [ 56s ] thds: 512 tps: 7304.41 qps: 44178.50 (r/w/o: 0.00/29528.67/14649.83) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  50. [ 57s ] thds: 512 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  51. [ 58s ] thds: 512 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  52. [ 59s ] thds: 512 tps: 19748.83 qps: 118069.98 (r/w/o: 0.00/78624.33/39445.65) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
  53. [ 60s ] thds: 512 tps: 21747.03 qps: 130541.17 (r/w/o: 0.00/87030.11/43511.06) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
复制代码


状态持续时间太短,时间间隔也不太固定,不太好抓取线程信息.
设置双0, 现象依旧.
  1. sync_binlog = 0
  2. innodb_flush_log_at_trx_commit = 0
复制代码


查看binlog cache 没有使用disk的情况

  1. | Binlog_cache_disk_use                           | 0     |
  2. | Binlog_stmt_cache_disk_use                      | 0     |
复制代码


排查binlog配置参数(只列了跟这个现象有关的两个参数)
初始binlog配置参数 其他略.

  1. | binlog_transaction_dependency_history_size | 1000000              |
  2. | binlog_transaction_dependency_tracking     | WRITESET             |
复制代码

尝试将 binlog_transaction_dependency_tracking 设置为COMMIT_ORDER, binlog文件切换时的 tps 掉0的现象居然消失了.

随后又将binlog_transaction_dependency_tracking 设置为WRITESET,降低binlog_transaction_dependency_history_size,
发现 当WRITESET时,binlog_transaction_dependency_history_size 超过一定的值(60万)后 tps都有掉0 的现象.
将binlog_transaction_dependency_history_size 设置为 官方25000默认值后, tps峰值较 设置 100万后 tps降低约10%.


binlog_transaction_dependency_history_size=25000:
  1. [ 60s ] thds: 512 tps: 20646.73 qps: 123952.40 (r/w/o: 0.00/82655.94/41296.46) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
  2. [ 61s ] thds: 512 tps: 20576.84 qps: 123327.05 (r/w/o: 0.00/82174.37/41152.68) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
  3. [ 62s ] thds: 512 tps: 21052.21 qps: 126296.25 (r/w/o: 0.00/84190.83/42105.41) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
  4. [ 63s ] thds: 512 tps: 21011.72 qps: 126210.38 (r/w/o: 0.00/84178.93/42031.45) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
  5. [ 64s ] thds: 512 tps: 20569.04 qps: 123433.24 (r/w/o: 0.00/82301.16/41132.08) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
  6. [ 65s ] thds: 512 tps: 20822.29 qps: 124793.69 (r/w/o: 0.00/83164.11/41629.58) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00
  7. [ 66s ] thds: 512 tps: 20918.61 qps: 125303.67 (r/w/o: 0.00/83462.45/41841.22) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
  8. [ 67s ] thds: 512 tps: 20935.83 qps: 125993.01 (r/w/o: 0.00/84101.34/41891.67) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
  9. [ 68s ] thds: 512 tps: 20984.10 qps: 125600.69 (r/w/o: 0.00/83652.48/41948.21) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
  10. [ 69s ] thds: 512 tps: 21127.67 qps: 126910.06 (r/w/o: 0.00/84650.72/42259.34) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
  11. [ 70s ] thds: 512 tps: 21556.35 qps: 129454.09 (r/w/o: 0.00/86336.38/43117.71) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  12. [ 71s ] thds: 512 tps: 21379.91 qps: 128149.39 (r/w/o: 0.00/85392.58/42756.81) lat (ms,95%): 34.95 err/s: 0.00 reconn/s: 0.00
  13. [ 72s ] thds: 512 tps: 20585.14 qps: 123370.83 (r/w/o: 0.00/82218.55/41152.28) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
  14. [ 73s ] thds: 512 tps: 20864.69 qps: 125297.09 (r/w/o: 0.00/83555.72/41741.37) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
  15. [ 74s ] thds: 512 tps: 21028.86 qps: 126272.17 (r/w/o: 0.00/84210.45/42061.72) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00
  16. [ 75s ] thds: 512 tps: 21143.42 qps: 126861.53 (r/w/o: 0.00/84558.68/42302.85) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00
复制代码


binlog_transaction_dependency_history_size=1000000
  1. [ 46s ] thds: 512 tps: 22590.58 qps: 135265.47 (r/w/o: 0.00/90113.31/45152.16) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  2. [ 47s ] thds: 512 tps: 22477.73 qps: 135000.32 (r/w/o: 0.00/90029.87/44970.45) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  3. [ 48s ] thds: 512 tps: 22425.55 qps: 134416.28 (r/w/o: 0.00/89547.17/44869.11) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  4. [ 49s ] thds: 512 tps: 21892.79 qps: 131680.76 (r/w/o: 0.00/87906.18/43774.58) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  5. [ 50s ] thds: 512 tps: 21730.83 qps: 130354.98 (r/w/o: 0.00/86899.32/43455.66) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  6. [ 51s ] thds: 512 tps: 22002.29 qps: 131937.71 (r/w/o: 0.00/87927.13/44010.57) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  7. [ 52s ] thds: 512 tps: 21931.93 qps: 131588.58 (r/w/o: 0.00/87738.72/43849.86) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  8. [ 53s ] thds: 512 tps: 21959.50 qps: 131652.04 (r/w/o: 0.00/87733.04/43919.00) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  9. [ 54s ] thds: 512 tps: 21775.29 qps: 130797.78 (r/w/o: 0.00/87237.20/43560.59) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  10. [ 55s ] thds: 512 tps: 21826.53 qps: 131027.16 (r/w/o: 0.00/87364.10/43663.06) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  11. [ 56s ] thds: 512 tps: 21868.30 qps: 131092.76 (r/w/o: 0.00/87374.17/43718.59) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  12. [ 57s ] thds: 512 tps: 21391.44 qps: 128475.66 (r/w/o: 0.00/85682.77/42792.89) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  13. [ 58s ] thds: 512 tps: 21782.64 qps: 130591.84 (r/w/o: 0.00/87031.56/43560.28) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  14. [ 59s ] thds: 512 tps: 21481.96 qps: 128849.74 (r/w/o: 0.00/85880.83/42968.91) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00
  15. [ 60s ] thds: 512 tps: 21465.05 qps: 128886.27 (r/w/o: 0.00/85963.17/42923.10) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  16. [ 61s ] thds: 512 tps: 21568.39 qps: 129337.31 (r/w/o: 0.00/86200.54/43136.77) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  17. [ 62s ] thds: 512 tps: 21639.78 qps: 129647.64 (r/w/o: 0.00/86375.09/43272.55) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00
  18. [ 63s ] thds: 512 tps: 18076.84 qps: 109062.95 (r/w/o: 0.00/72861.28/36201.67) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  19. [ 64s ] thds: 512 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
  20. [ 65s ] thds: 512 tps: 6802.24 qps: 40479.43 (r/w/o: 0.00/26910.95/13568.48) lat (ms,95%): 1938.16 err/s: 0.00 reconn/s: 0.00
  21. [ 66s ] thds: 512 tps: 22267.96 qps: 133496.77 (r/w/o: 0.00/88965.84/44530.93) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
复制代码


设置 COMMIT_ORDER
  1. [ 10s ] thds: 512 tps: 23018.33 qps: 138294.97 (r/w/o: 0.00/92242.31/46052.66) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
  2. [ 11s ] thds: 512 tps: 22656.12 qps: 135803.72 (r/w/o: 0.00/90503.49/45300.23) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  3. [ 12s ] thds: 512 tps: 22877.32 qps: 137427.91 (r/w/o: 0.00/91672.27/45755.64) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  4. [ 13s ] thds: 512 tps: 23038.96 qps: 138151.72 (r/w/o: 0.00/92061.80/46089.92) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  5. [ 14s ] thds: 512 tps: 22736.00 qps: 136624.93 (r/w/o: 0.00/91160.92/45464.01) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  6. [ 15s ] thds: 512 tps: 22677.40 qps: 135900.34 (r/w/o: 0.00/90555.55/45344.79) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  7. [ 16s ] thds: 512 tps: 22626.94 qps: 135702.69 (r/w/o: 0.00/90454.80/45247.89) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  8. [ 17s ] thds: 512 tps: 22683.85 qps: 136091.12 (r/w/o: 0.00/90724.41/45366.71) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  9. [ 18s ] thds: 512 tps: 22606.85 qps: 135892.06 (r/w/o: 0.00/90653.36/45238.70) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  10. [ 19s ] thds: 512 tps: 22754.76 qps: 136322.57 (r/w/o: 0.00/90818.06/45504.51) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  11. [ 20s ] thds: 512 tps: 22811.08 qps: 136796.48 (r/w/o: 0.00/91168.32/45628.16) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  12. [ 21s ] thds: 512 tps: 22823.40 qps: 137039.41 (r/w/o: 0.00/91401.60/45637.81) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
  13. [ 22s ] thds: 512 tps: 22499.57 qps: 135136.48 (r/w/o: 0.00/90136.33/45000.15) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  14. [ 23s ] thds: 512 tps: 22716.25 qps: 136329.53 (r/w/o: 0.00/90880.02/45449.51) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  15. [ 24s ] thds: 512 tps: 22829.82 qps: 136892.92 (r/w/o: 0.00/91242.27/45650.64) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  16. [ 25s ] thds: 512 tps: 22830.43 qps: 137030.59 (r/w/o: 0.00/91359.73/45670.86) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  17. [ 26s ] thds: 512 tps: 22774.84 qps: 136468.07 (r/w/o: 0.00/90942.39/45525.69) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  18. [ 27s ] thds: 512 tps: 22860.03 qps: 137412.15 (r/w/o: 0.00/91677.09/45735.07) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  19. [ 28s ] thds: 512 tps: 23044.77 qps: 138146.62 (r/w/o: 0.00/92071.07/46075.55) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  20. [ 29s ] thds: 512 tps: 22897.55 qps: 137395.27 (r/w/o: 0.00/91602.18/45793.09) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  21. [ 30s ] thds: 512 tps: 22865.77 qps: 136929.58 (r/w/o: 0.00/91204.03/45725.55) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  22. [ 31s ] thds: 512 tps: 22663.76 qps: 135986.54 (r/w/o: 0.00/90670.02/45316.51) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  23. [ 32s ] thds: 512 tps: 22771.24 qps: 136903.47 (r/w/o: 0.00/91316.98/45586.49) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  24. [ 33s ] thds: 512 tps: 22866.68 qps: 136895.05 (r/w/o: 0.00/91203.70/45691.35) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  25. [ 34s ] thds: 512 tps: 22515.25 qps: 135417.49 (r/w/o: 0.00/90367.00/45050.49) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
  26. [ 35s ] thds: 512 tps: 22983.92 qps: 137892.50 (r/w/o: 0.00/91909.66/45982.84) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  27. [ 36s ] thds: 512 tps: 22696.28 qps: 136038.69 (r/w/o: 0.00/90656.13/45382.56) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  28. [ 37s ] thds: 512 tps: 23033.25 qps: 138131.55 (r/w/o: 0.00/92082.03/46049.51) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  29. [ 38s ] thds: 512 tps: 22791.82 qps: 136720.94 (r/w/o: 0.00/91143.29/45577.65) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  30. [ 39s ] thds: 512 tps: 22948.64 qps: 137634.85 (r/w/o: 0.00/91726.56/45908.29) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  31. [ 40s ] thds: 512 tps: 23022.88 qps: 138344.18 (r/w/o: 0.00/92276.44/46067.74) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  32. [ 41s ] thds: 512 tps: 22807.62 qps: 136793.74 (r/w/o: 0.00/91203.49/45590.25) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  33. [ 42s ] thds: 512 tps: 22657.43 qps: 136061.61 (r/w/o: 0.00/90728.74/45332.87) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  34. [ 43s ] thds: 512 tps: 22369.98 qps: 134267.89 (r/w/o: 0.00/89525.93/44741.96) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  35. [ 44s ] thds: 512 tps: 22890.10 qps: 137088.72 (r/w/o: 0.00/91321.51/45767.21) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  36. [ 45s ] thds: 512 tps: 22758.04 qps: 136613.25 (r/w/o: 0.00/91097.17/45516.08) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  37. [ 46s ] thds: 512 tps: 22698.40 qps: 136225.37 (r/w/o: 0.00/90825.58/45399.79) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  38. [ 47s ] thds: 512 tps: 22901.04 qps: 137422.25 (r/w/o: 0.00/91622.17/45800.09) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  39. [ 48s ] thds: 512 tps: 22822.11 qps: 136961.65 (r/w/o: 0.00/91311.44/45650.22) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  40. [ 49s ] thds: 512 tps: 22783.13 qps: 136721.80 (r/w/o: 0.00/91164.54/45557.26) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  41. [ 50s ] thds: 512 tps: 22706.03 qps: 136130.21 (r/w/o: 0.00/90711.15/45419.06) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  42. [ 51s ] thds: 512 tps: 23039.00 qps: 138170.00 (r/w/o: 0.00/92100.00/46070.00) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  43. [ 52s ] thds: 512 tps: 22921.20 qps: 137703.22 (r/w/o: 0.00/91850.81/45852.41) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  44. [ 53s ] thds: 512 tps: 23222.52 qps: 139205.11 (r/w/o: 0.00/92774.07/46431.03) lat (ms,95%): 29.72 err/s: 0.00 reconn/s: 0.00
  45. [ 54s ] thds: 512 tps: 22864.60 qps: 137273.60 (r/w/o: 0.00/91546.40/45727.20) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  46. [ 55s ] thds: 512 tps: 22765.40 qps: 136582.37 (r/w/o: 0.00/91030.58/45551.79) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
  47. [ 56s ] thds: 512 tps: 22655.41 qps: 136055.43 (r/w/o: 0.00/90755.62/45299.81) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  48. [ 57s ] thds: 512 tps: 22764.14 qps: 136476.81 (r/w/o: 0.00/90938.53/45538.28) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
  49. [ 58s ] thds: 512 tps: 22898.63 qps: 137412.76 (r/w/o: 0.00/91621.50/45791.25) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  50. [ 59s ] thds: 512 tps: 22938.99 qps: 137764.89 (r/w/o: 0.00/91868.92/45895.97) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
  51. [ 60s ] thds: 512 tps: 22811.09 qps: 136847.53 (r/w/o: 0.00/91236.35/45611.18) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
复制代码

COMMIT_ORDER 比 WRITESET 在这个场景下要稳定,性能高10%左右.


关闭binlog:
  1. [ 321s ] thds: 1024 tps: 26396.05 qps: 158235.32 (r/w/o: 0.00/105474.22/52761.11) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
  2. [ 322s ] thds: 1024 tps: 26449.85 qps: 158679.07 (r/w/o: 0.00/105791.38/52887.69) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
  3. [ 323s ] thds: 1024 tps: 26532.79 qps: 159265.71 (r/w/o: 0.00/106175.14/53090.57) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
  4. [ 324s ] thds: 1024 tps: 26331.65 qps: 158103.90 (r/w/o: 0.00/105426.60/52677.30) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
  5. [ 325s ] thds: 1024 tps: 26493.45 qps: 158818.69 (r/w/o: 0.00/105842.80/52975.90) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
  6. [ 326s ] thds: 1024 tps: 26502.15 qps: 159013.92 (r/w/o: 0.00/106004.61/53009.31) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
复制代码



附mysql服务配置:
  1. [mysqld_safe]
  2. pid_file            = /data/mysql/sysbench/tmp/mysqld.pid
  3. log_error           = /data/mysql/sysbench/log/log-error.log


  6. [mysqld]
  7. #*** basic settings ***
  8. read_only = 0

  10. server_id = 127001
  11. bind_address = 0.0.0.0
  12. port = 3306
  13. report_host  = 0.0.0.0
  14. report_port  = 3306

  16. extra_port = 13306
  17. extra_max_connections = 10

  19. basedir             = /usr/local/mysql


  22. #*** data tmp socket pid dump path options ***
  23. datadir             = /data/mysql/sysbench/data
  24. tmpdir              = /data/mysql/sysbench/tmp
  25. socket              = /data/mysql/sysbench/tmp/mysql.sock
  26. pid_file            = /data/mysql/sysbench/tmp/mysqld.pid


  29. #*** log path options ***
  30. slow_query_log_file = /data/mysql/sysbench/log/mysql-slow.log
  31. general_log_file    = /data/mysql/sysbench/log/general.log
  32. log_error           = /data/mysql/sysbench/log/log-error.log


  35. #*** binlog path options ***
  36. log_bin             = /data/mysql/sysbench/binlog/mysql-bin
  37. log_bin_index       = /data/mysql/sysbench/binlog/mysql-bin.index


  40. #*** replication log path Specific options ***
  41. relay_log           = /data/mysql/sysbench/relaybin/mysql-relay-bin
  42. relay_log_index     = /data/mysql/sysbench/relaybin/mysql-relay-bin.index


  45. #*** server Specific options ***
  46. user = mysql
  47. sql_mode = "NO_ENGINE_SUBSTITUTION"
  48. autocommit = 1
  49. character_set_server = utf8mb4
  50. transaction_isolation = READ-COMMITTED
  51. explicit_defaults_for_timestamp = 1
  52. event_scheduler = 1
  53. log_timestamps = SYSTEM
  54. ssl = 0


  57. #*** performance setttings options ***
  58. open_files_limit = 1024000
  59. back_log = 8192
  60. max_length_for_sort_data = 2048
  61. range_alloc_block_size = 16384
  62. transaction_alloc_block_size = 8192
  63. transaction_prealloc_size = 4096


  66. #*** connection Specific options ***
  67. connect_timeout = 10
  68. max_connect_errors = 10000000
  69. max_connections = 100000
  70. interactive_timeout = 1800
  71. wait_timeout = 1800
  72. lock_wait_timeout = 3600
  73. skip_external_locking = 1
  74. skip_name_resolve = 1
  75. max_allowed_packet = 128M
  76. query_cache_type = 0
  77. query_cache_size = 0
  78. query_prealloc_size = 262144
  79. max_prepared_stmt_count = 262144


  82. #*** session thread memory settings ***
  83. read_buffer_size = 8M
  84. read_rnd_buffer_size = 4M
  85. sort_buffer_size = 8M
  86. join_buffer_size = 4M
  87. bulk_insert_buffer_size = 64M


  90. #*** session thread Specific options ***
  91. thread_stack = 524288
  92. thread_cache_size = 1024
  93. thread_handling = pool-of-threads
  94. thread_pool_size = 64
  95. thread_pool_idle_timeout = 60
  96. thread_pool_oversubscribe = 2
  97. thread_pool_stall_limit = 10


  100. #*** session tmp_table Specific options ***
  101. max_tmp_tables = 128
  102. tmp_table_size = 128M
  103. max_heap_table_size = 128M


  106. #*** Slow log Specific options ***
  107. slow_query_log = 1
  108. long_query_time = 0.1
  109. log_slow_admin_statements = 0
  110. log_slow_slave_statements = 0
  111. log_queries_not_using_indexes = 0
  112. log_throttle_queries_not_using_indexes = 0
  113. min_examined_row_limit = 0


  116. #*** Binlog Specific options ***
  117. expire_logs_days = 7
  118. sync_binlog = 1

  120. gtid_mode = ON
  121. enforce_gtid_consistency = ON
  122. binlog_gtid_simple_recovery = 1

  124. binlog_checksum = CRC32
  125. binlog_format = ROW
  126. binlog_row_image = FULL
  127. binlog_rows_query_log_events = 1
  128. log_bin_trust_function_creators = 1

  130. binlog_cache_size = 8M
  131. binlog_stmt_cache_size = 8M
  132. max_binlog_cache_size = 8G
  133. max_binlog_size = 1G

  135. transaction_write_set_extraction = XXHASH64
  136. binlog_transaction_dependency_history_size = 1000000
  137. binlog_transaction_dependency_tracking = WRITESET

  139. binlog_group_commit_sync_delay = 0
  140. binlog_group_commit_sync_no_delay_count = 0


  143. #*** Replication Specific options ***
  144. skip-slave-start = 1
  145. log_slave_updates = 1
  146. relay_log_recovery = 1

  148. relay_log_info_repository = TABLE
  149. master_info_repository    = TABLE

  151. slave_parallel_type = LOGICAL_CLOCK
  152. slave_parallel_workers = 32
  153. slave_pending_jobs_size_max = 256M
  154. slave_preserve_commit_order = 1
  155. slave_transaction_retries = 128

  157. #STRICT
  158. slave_exec_mode = IDEMPOTENT
  159. #all,1032,1062,1213
  160. slave_skip_errors = ddl_exist_errors
  161. slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'


  164. #*** table cache Specific options ***
  165. table_open_cache = 81920
  166. table_definition_cache = 8192
  167. table_open_cache_instances = 64


  170. #*** perforamnce_schema settings ***
  171. performance_schema = ON
  172. performance-schema-instrument = 'memory/%=COUNTED'
  173. performance_schema_instrument = '%lock%=on'
  174. performance_schema_digests_size = 40000
  175. performance_schema_max_table_instances = 40000
  176. performance_schema_max_sql_text_length = 4096
  177. performance_schema_max_digest_length = 4096


  180. #*** MyISAM Specific options ***
  181. key_buffer_size = 128M
  182. myisam_sort_buffer_size = 64M
  183. myisam_max_sort_file_size = 8G
  184. concurrent_insert = 2
  185. max_write_lock_count = 1
  186. delay_key_write = 0
  187. myisam_recover_options = 'FORCE,BACKUP'


  190. #*** INNODB Specific options ***
  191. default_storage_engine = InnoDB
  192. default_tmp_storage_engine = InnoDB
  193. innodb_adaptive_flushing = 1
  194. innodb_adaptive_flushing_lwm = 10
  195. innodb_adaptive_hash_index = 0
  196. innodb_adaptive_hash_index_parts = 64
  197. innodb_adaptive_max_sleep_delay = 50000
  198. innodb_autoinc_lock_mode = 2
  199. innodb_buffer_pool_dump_at_shutdown = 0
  200. innodb_buffer_pool_instances = 16
  201. innodb_buffer_pool_load_at_startup = 0
  202. innodb_buffer_pool_size = 128G
  203. innodb_change_buffer_max_size = 25
  204. innodb_change_buffering = all
  205. innodb_checksum_algorithm = crc32
  206. innodb_concurrency_tickets = 5000
  207. innodb_deadlock_detect = 1
  208. innodb_doublewrite = 1
  209. innodb_fast_shutdown = 0
  210. innodb_file_per_table = 1
  211. innodb_flush_log_at_timeout = 1
  212. innodb_flush_log_at_trx_commit = 1
  213. innodb_flush_method = O_DIRECT
  214. innodb_flush_neighbors = 0
  215. innodb_flush_sync = 1
  216. innodb_io_capacity = 20000
  217. innodb_io_capacity_max = 30000
  218. innodb_large_prefix = 1
  219. innodb_lock_wait_timeout = 10
  220. innodb_locks_unsafe_for_binlog = 0
  221. innodb_log_buffer_size = 128M
  222. innodb_log_file_size = 2G
  223. innodb_log_files_in_group = 32
  224. innodb_lru_scan_depth = 1024
  225. innodb_max_dirty_pages_pct = 75
  226. innodb_max_undo_log_size = 4G
  227. innodb_old_blocks_pct = 37
  228. innodb_old_blocks_time = 1000
  229. innodb_online_alter_log_max_size = 4G
  230. innodb_open_files = 1024000
  231. innodb_page_cleaners = 16
  232. innodb_page_size = 16384
  233. innodb_print_all_deadlocks = 1
  234. innodb_purge_batch_size = 1200
  235. innodb_purge_rseg_truncate_frequency = 128
  236. innodb_purge_threads = 8
  237. innodb_read_ahead_threshold = 56
  238. innodb_read_io_threads = 16
  239. innodb_rollback_segments = 128
  240. innodb_rollback_on_timeout = 1
  241. innodb_sort_buffer_size = 64M
  242. innodb_spin_wait_delay = 15
  243. innodb_stats_on_metadata = 0
  244. innodb_stats_auto_recalc = 1
  245. innodb_stats_persistent = 1
  246. innodb_stats_persistent_sample_pages = 20
  247. innodb_status_file = 1
  248. innodb_status_output = 0
  249. innodb_status_output_locks = 1
  250. innodb_strict_mode = 1
  251. innodb_support_xa = 1
  252. innodb_sync_array_size = 128
  253. innodb_sync_spin_loops = 25
  254. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:80G
  255. innodb_thread_concurrency = 0
  256. innodb_undo_log_truncate = 1
  257. innodb_undo_logs = 128
  258. innodb_undo_tablespaces = 3
  259. innodb_use_native_aio = 1
  260. innodb_write_io_threads = 16

  262. #innodb monitor settings
  263. #innodb_monitor_enable = '%'
  264. innodb_monitor_enable = "module_metadata"
  265. innodb_monitor_enable = "module_innodb"
  266. innodb_monitor_enable = "module_server"
  267. innodb_monitor_enable = "module_dml"
  268. innodb_monitor_enable = "module_ddl"
  269. innodb_monitor_enable = "module_trx"
  270. innodb_monitor_enable = "module_os"
  271. innodb_monitor_enable = "module_purge"
  272. innodb_monitor_enable = "module_log"
  273. innodb_monitor_enable = "module_lock"
  274. innodb_monitor_enable = "module_buffer"
  275. innodb_monitor_enable = "module_index"
  276. innodb_monitor_enable = "module_ibuf_system"
  277. innodb_monitor_enable = "module_buffer_page"
  278. innodb_monitor_enable = "module_adaptive_hash"
复制代码




MySQL, 故障案例, 慢sql
binlog.jpg
全部回复(5)
只看楼主
yejr 2023-2-9 09:29:20
谢谢分享，试着把binlog直接全关了呢
回复 采纳

举报

earl86 2023-2-9 14:54:47
yejr 发表于 2023-2-9 09:29
谢谢分享，试着把binlog直接全关了呢

更新了, 关闭binlog tps又高了一点
回复 采纳

举报

yejr 2023-2-9 14:56:26
earl86 发表于 2023-2-9 14:54
更新了, 关闭binlog tps又高了一点

关闭binlog的时候，tps还会有规律的抖动吗
回复 采纳

举报

earl86 2023-2-9 15:44:09
yejr 发表于 2023-2-9 14:56
关闭binlog的时候，tps还会有规律的抖动吗

没有抖动.
回复 采纳

举报

yejr 2023-2-10 09:23:47
earl86 发表于 2023-2-9 15:44
没有抖动.

可以试试GreatSQL 5.7.36版本，或者GreatSQL 8.0.25-16版本
回复 采纳

举报

earl86

6

主题

0

博客

26

贡献

新手上路

Rank: 1

积分
44

助人为乐（铜）

发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-5-18 01:47 , Processed in 0.022965 second(s), 18 queries , Redis On.
快速回复 返回顶部 返回列表