mysql slow log 问题排查
系统版本: 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
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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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, 现象依旧.
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
查看binlog cache 没有使用disk的情况
| Binlog_cache_disk_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
排查binlog配置参数(只列了跟这个现象有关的两个参数)
初始binlog配置参数 其他略.
| binlog_transaction_dependency_history_size | 1000000 |
| 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:
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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:
[ 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
[ 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
[ 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
[ 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
[ 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
[ 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服务配置:
pid_file = /data/mysql/sysbench/tmp/mysqld.pid
log_error = /data/mysql/sysbench/log/log-error.log
#*** basic settings ***
read_only = 0
server_id = 127001
bind_address = 0.0.0.0
port = 3306
report_host= 0.0.0.0
report_port= 3306
extra_port = 13306
extra_max_connections = 10
basedir = /usr/local/mysql
#*** data tmp socket pid dump path options ***
datadir = /data/mysql/sysbench/data
tmpdir = /data/mysql/sysbench/tmp
socket = /data/mysql/sysbench/tmp/mysql.sock
pid_file = /data/mysql/sysbench/tmp/mysqld.pid
#*** log path options ***
slow_query_log_file = /data/mysql/sysbench/log/mysql-slow.log
general_log_file = /data/mysql/sysbench/log/general.log
log_error = /data/mysql/sysbench/log/log-error.log
#*** binlog path options ***
log_bin = /data/mysql/sysbench/binlog/mysql-bin
log_bin_index = /data/mysql/sysbench/binlog/mysql-bin.index
#*** replication log path Specific options ***
relay_log = /data/mysql/sysbench/relaybin/mysql-relay-bin
relay_log_index = /data/mysql/sysbench/relaybin/mysql-relay-bin.index
#*** server Specific options ***
user = mysql
sql_mode = "NO_ENGINE_SUBSTITUTION"
autocommit = 1
character_set_server = utf8mb4
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
event_scheduler = 1
log_timestamps = SYSTEM
ssl = 0
#*** performance setttings options ***
open_files_limit = 1024000
back_log = 8192
max_length_for_sort_data = 2048
range_alloc_block_size = 16384
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
#*** connection Specific options ***
connect_timeout = 10
max_connect_errors = 10000000
max_connections = 100000
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 3600
skip_external_locking = 1
skip_name_resolve = 1
max_allowed_packet = 128M
query_cache_type = 0
query_cache_size = 0
query_prealloc_size = 262144
max_prepared_stmt_count = 262144
#*** session thread memory settings ***
read_buffer_size = 8M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
join_buffer_size = 4M
bulk_insert_buffer_size = 64M
#*** session thread Specific options ***
thread_stack = 524288
thread_cache_size = 1024
thread_handling = pool-of-threads
thread_pool_size = 64
thread_pool_idle_timeout = 60
thread_pool_oversubscribe = 2
thread_pool_stall_limit = 10
#*** session tmp_table Specific options ***
max_tmp_tables = 128
tmp_table_size = 128M
max_heap_table_size = 128M
#*** Slow log Specific options ***
slow_query_log = 1
long_query_time = 0.1
log_slow_admin_statements = 0
log_slow_slave_statements = 0
log_queries_not_using_indexes = 0
log_throttle_queries_not_using_indexes = 0
min_examined_row_limit = 0
#*** Binlog Specific options ***
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = 1
binlog_checksum = CRC32
binlog_format = ROW
binlog_row_image = FULL
binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1
binlog_cache_size = 8M
binlog_stmt_cache_size = 8M
max_binlog_cache_size = 8G
max_binlog_size = 1G
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 1000000
binlog_transaction_dependency_tracking = WRITESET
binlog_group_commit_sync_delay = 0
binlog_group_commit_sync_no_delay_count = 0
#*** Replication Specific options ***
skip-slave-start = 1
log_slave_updates = 1
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 32
slave_pending_jobs_size_max = 256M
slave_preserve_commit_order = 1
slave_transaction_retries = 128
#STRICT
slave_exec_mode = IDEMPOTENT
#all,1032,1062,1213
slave_skip_errors = ddl_exist_errors
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
#*** table cache Specific options ***
table_open_cache = 81920
table_definition_cache = 8192
table_open_cache_instances = 64
#*** perforamnce_schema settings ***
performance_schema = ON
performance-schema-instrument = 'memory/%=COUNTED'
performance_schema_instrument = '%lock%=on'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096
#*** MyISAM Specific options ***
key_buffer_size = 128M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 8G
concurrent_insert = 2
max_write_lock_count = 1
delay_key_write = 0
myisam_recover_options = 'FORCE,BACKUP'
#*** INNODB Specific options ***
default_storage_engine = InnoDB
default_tmp_storage_engine = InnoDB
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 0
innodb_adaptive_hash_index_parts = 64
innodb_adaptive_max_sleep_delay = 50000
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_instances = 16
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_size = 128G
innodb_change_buffer_max_size = 25
innodb_change_buffering = all
innodb_checksum_algorithm = crc32
innodb_concurrency_tickets = 5000
innodb_deadlock_detect = 1
innodb_doublewrite = 1
innodb_fast_shutdown = 0
innodb_file_per_table = 1
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_flush_sync = 1
innodb_io_capacity = 20000
innodb_io_capacity_max = 30000
innodb_large_prefix = 1
innodb_lock_wait_timeout = 10
innodb_locks_unsafe_for_binlog = 0
innodb_log_buffer_size = 128M
innodb_log_file_size = 2G
innodb_log_files_in_group = 32
innodb_lru_scan_depth = 1024
innodb_max_dirty_pages_pct = 75
innodb_max_undo_log_size = 4G
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 4G
innodb_open_files = 1024000
innodb_page_cleaners = 16
innodb_page_size = 16384
innodb_print_all_deadlocks = 1
innodb_purge_batch_size = 1200
innodb_purge_rseg_truncate_frequency = 128
innodb_purge_threads = 8
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 16
innodb_rollback_segments = 128
innodb_rollback_on_timeout = 1
innodb_sort_buffer_size = 64M
innodb_spin_wait_delay = 15
innodb_stats_on_metadata = 0
innodb_stats_auto_recalc = 1
innodb_stats_persistent = 1
innodb_stats_persistent_sample_pages = 20
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_strict_mode = 1
innodb_support_xa = 1
innodb_sync_array_size = 128
innodb_sync_spin_loops = 25
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:80G
innodb_thread_concurrency = 0
innodb_undo_log_truncate = 1
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_use_native_aio = 1
innodb_write_io_threads = 16
#innodb monitor settings
#innodb_monitor_enable = '%'
innodb_monitor_enable = "module_metadata"
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
innodb_monitor_enable = "module_adaptive_hash"
谢谢分享，试着把binlog直接全关了呢 yejr 发表于 2023-2-9 09:29
谢谢分享，试着把binlog直接全关了呢
更新了, 关闭binlog tps又高了一点 earl86 发表于 2023-2-9 14:54
更新了, 关闭binlog tps又高了一点
关闭binlog的时候，tps还会有规律的抖动吗 yejr 发表于 2023-2-9 14:56
关闭binlog的时候，tps还会有规律的抖动吗
没有抖动. earl86 发表于 2023-2-9 15:44
没有抖动.
可以试试GreatSQL 5.7.36版本，或者GreatSQL 8.0.25-16版本
页:
[1]