binary log对mysql性能的影响

时间：2022-09-25 15:43:33
通过这些天的测试(高并发同时insert/update,小事务)， 我总感觉mysql在提交commit的时候， 如果sync_binlog=1 这个thread不但需要执行commit操作， 还需要负责将binary log从cache写到disk. 由于sync_binlog=1所以每个事务thread都需要做这一步，并且由于所有的thread操作的是同一个文件， 也就是同一个disk, 导致了mysql的提交性能急剧下降,就想我测试的能达到:
sync_binlog=0的性能大概是sync_binlog=1时的5倍。
如果干脆不设置log-bin, 性能是sync_binlog=1时的将近8倍。
这是不是很恐怖呢?
我是在一个disk的情况下测试的，
大家能谈谈你们在实际应用的时候， 开binary log和不开binary log的性能差别吗?
另外sync_binlog通常设置成多少呢? 如果不是1,那么又是如何保证数据的复制能尽可能的实时呢?

10 个解决方案

#1

任何一种数据库，包括ORACLE，SQL SERVER， 打开归档日志后都会产生额外的开销，MYSQL也不例外。 你需要的是在性能和数据安全中做平衡。 不写日志，INSERT、UPDATE、DELETE的速度会快一些，但一旦数据库有问题，你无法恢复。比如有人误删了一张表，你只能恢复到最新的备份，之的的数据就无法恢复了。


MySQL官方文档  http://dev.mysql.com/doc/refman/5.1/zh/index.html

#2

楼上说的很全了.主要是在性能和安全中做平衡.

#3

sync_binlog=1

会使用分布式事务（mysql和存储引擎之间）来刷写日志到磁盘，之后才正式commit事务，所以比较慢，但非常安全！

#4

sync_binlog=1

为了安全，还是这样设置的好。

#5

谢谢大家的回答, 不过我觉得如果开了binary log就会导致mysql 性能直线下降这是不对的.
binary log是可以保持数据复制,oracel也可以,可是并不会导致oracle性能急剧下降. 当然其实mysql也不会导致性能急剧下降了. 我昨天把mysql的data file, log file, bianry log file放到内存mount成的目录里, 性能就比较稳定, 只有很少的降低. 但不管怎么样, mysql的binary log置成1会很多的碰盘写操作. 事务越小, 写操作越频繁,影响越大. 所以强烈建议在slave端不要开binary log, 当然如果slave不需要事务保证,那myisam最好了.

#6

mysql-> set global innodb_flush_log_at_trx_commit=1;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
sysbench 0.4.10:  multi-threaded system evaluation benchmark

OLTP test statistics:
    queries performed:
        read:                            572222
        write:                           204362
        other:                           81745
        total:                           858329
    transactions:                        40872  (680.90 per sec.)
    deadlocks:                           1      (0.02 per sec.)
    read/write requests:                 776584 (12937.27 per sec.)
    other operations:                    81745  (1361.81 per sec.)

Test execution summary:
    total time:                          60.0269s
    total number of events:              40872
    total time taken by event execution: 959.3876
    per-request statistics:
         min:                                 12.71ms
         avg:                                 23.47ms
         max:                                512.94ms
         approx.  95 percentile:              34.24ms

Threads fairness:
    events (avg/stddev):           2554.5000/6.72
    execution time (avg/stddev):   59.9617/0.01

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            563430
        write:                           201225
        other:                           80490
        total:                           845145
    transactions:                        40245  (670.58 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 764655 (12741.04 per sec.)
    other operations:                    80490  (1341.16 per sec.)

Test execution summary:
    total time:                          60.0151s
    total number of events:              40245
    total time taken by event execution: 959.2476
    per-request statistics:
         min:                                  8.46ms
         avg:                                 23.84ms
         max:                                425.82ms
         approx.  95 percentile:              35.22ms

Threads fairness:
    events (avg/stddev):           2515.3125/4.70
    execution time (avg/stddev):   59.9530/0.01

#7

mysql-> set global innodb_flush_log_at_trx_commit=2;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            963018
        write:                           343935
        other:                           137574
        total:                           1444527
    transactions:                        68787  (1146.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1306953 (21773.99 per sec.)
    other operations:                    137574 (2292.00 per sec.)

Test execution summary:
    total time:                          60.0236s
    total number of events:              68787
    total time taken by event execution: 959.4940
    per-request statistics:
         min:                                  2.95ms
         avg:                                 13.95ms
         max:                               2439.00ms
         approx.  95 percentile:              21.81ms

Threads fairness:
    events (avg/stddev):           4299.1875/63.30
    execution time (avg/stddev):   59.9684/0.00

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            683760
        write:                           244200
        other:                           97680
        total:                           1025640
    transactions:                        48840  (813.91 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 927960 (15464.24 per sec.)
    other operations:                    97680  (1627.81 per sec.)

Test execution summary:
    total time:                          60.0068s
    total number of events:              48840
    total time taken by event execution: 959.4427
    per-request statistics:
         min:                                  2.79ms
         avg:                                 19.64ms
         max:                              16236.41ms
         approx.  95 percentile:              21.18ms

Threads fairness:
    events (avg/stddev):           3052.5000/249.41
    execution time (avg/stddev):   59.9652/0.00

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1065988
        write:                           380710
        other:                           152284
        total:                           1598982
    transactions:                        76142  (1268.82 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1446698 (24107.54 per sec.)
    other operations:                    152284 (2537.64 per sec.)

Test execution summary:
    total time:                          60.0102s
    total number of events:              76142
    total time taken by event execution: 959.1223
    per-request statistics:
         min:                                  2.91ms
         avg:                                 12.60ms
         max:                                684.32ms
         approx.  95 percentile:              21.16ms

Threads fairness:
    events (avg/stddev):           4758.8750/56.45
    execution time (avg/stddev):   59.9451/0.00

#8

mysql-> set global innodb_flush_log_at_trx_commit=0;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1269646
        write:                           453437
        other:                           181376
        total:                           1904459
    transactions:                        90687  (441.01 per sec.)
    deadlocks:                           2      (0.01 per sec.)
    read/write requests:                 1723083 (8379.28 per sec.)
    other operations:                    181376 (882.02 per sec.)

Test execution summary:
    total time:                          205.6362s
    total number of events:              90687
    total time taken by event execution: 3271.3306
    per-request statistics:
         min:                                  2.87ms
         avg:                                 36.07ms
         max:                             149592.95ms
         approx.  95 percentile:              20.51ms

Threads fairness:
    events (avg/stddev):           5667.9375/124.93
    execution time (avg/stddev):   204.4582/0.29

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            374150
        write:                           133625
        other:                           53450
        total:                           561225
    transactions:                        26725  (329.72 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 507775 (6264.59 per sec.)
    other operations:                    53450  (659.43 per sec.)

Test execution summary:
    total time:                          81.0548s
    total number of events:              26725
    total time taken by event execution: 1296.3940
    per-request statistics:
         min:                                  2.98ms
         avg:                                 48.51ms
         max:                              64098.27ms
         approx.  95 percentile:              22.76ms

Threads fairness:
    events (avg/stddev):           1670.3125/43.26
    execution time (avg/stddev):   81.0246/0.01

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            1262926
        write:                           451045
        other:                           180418
        total:                           1894389
    transactions:                        90209  (892.12 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1713971 (16950.30 per sec.)
    other operations:                    180418 (1784.24 per sec.)

Test execution summary:
    total time:                          101.1175s
    total number of events:              90209
    total time taken by event execution: 1596.6090
    per-request statistics:
         min:                                  2.86ms
         avg:                                 17.70ms
         max:                              46818.16ms
         approx.  95 percentile:              20.70ms

Threads fairness:
    events (avg/stddev):           5638.0625/109.61
    execution time (avg/stddev):   99.7881/0.32

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1262352
        write:                           450840
        other:                           180336
        total:                           1893528
    transactions:                        90168  (922.61 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1713192 (17529.59 per sec.)
    other operations:                    180336 (1845.22 per sec.)

Test execution summary:
    total time:                          97.7314s
    total number of events:              90168
    total time taken by event execution: 1492.0429
    per-request statistics:
         min:                                  2.62ms
         avg:                                 16.55ms
         max:                              41072.19ms
         approx.  95 percentile:              20.67ms

Threads fairness:
    events (avg/stddev):           5635.5000/125.99
    execution time (avg/stddev):   93.2527/1.13

#9

我也在测试这个，感觉打开日志log_bin后，并发变成顺序执行了
我用mysqlslap做压力测试，并发1个连接和20个连接，相同的执行数量，并没有变快

我关了log_bin，后并发就明显会快很多，但并发不能太大，50和100没区别


是不是这样呢？那这样的话，象网站高并发，同时向一个表中插入数据，怎么办？

#10

引用 1 楼  的回复:
任何一种数据库，包括ORACLE，SQL SERVER， 打开归档日志后都会产生额外的开销，MYSQL也不例外。 你需要的是在性能和数据安全中做平衡。 不写日志，INSERT、UPDATE、DELETE的速度会快一些，但一旦数据库有问题，你无法恢复。比如有人误删了一张表，你只能恢复到最新的备份，之的的数据就无法恢复了。


MySQL官方文档 http://dev.mysql.com/doc……

这方面还得做个均衡呀！

#1

任何一种数据库，包括ORACLE，SQL SERVER， 打开归档日志后都会产生额外的开销，MYSQL也不例外。 你需要的是在性能和数据安全中做平衡。 不写日志，INSERT、UPDATE、DELETE的速度会快一些，但一旦数据库有问题，你无法恢复。比如有人误删了一张表，你只能恢复到最新的备份，之的的数据就无法恢复了。


MySQL官方文档  http://dev.mysql.com/doc/refman/5.1/zh/index.html

#2

楼上说的很全了.主要是在性能和安全中做平衡.

#3

sync_binlog=1

会使用分布式事务（mysql和存储引擎之间）来刷写日志到磁盘，之后才正式commit事务，所以比较慢，但非常安全！

#4

sync_binlog=1

为了安全，还是这样设置的好。

#5

谢谢大家的回答, 不过我觉得如果开了binary log就会导致mysql 性能直线下降这是不对的.
binary log是可以保持数据复制,oracel也可以,可是并不会导致oracle性能急剧下降. 当然其实mysql也不会导致性能急剧下降了. 我昨天把mysql的data file, log file, bianry log file放到内存mount成的目录里, 性能就比较稳定, 只有很少的降低. 但不管怎么样, mysql的binary log置成1会很多的碰盘写操作. 事务越小, 写操作越频繁,影响越大. 所以强烈建议在slave端不要开binary log, 当然如果slave不需要事务保证,那myisam最好了.

#6

mysql-> set global innodb_flush_log_at_trx_commit=1;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
sysbench 0.4.10:  multi-threaded system evaluation benchmark

OLTP test statistics:
    queries performed:
        read:                            572222
        write:                           204362
        other:                           81745
        total:                           858329
    transactions:                        40872  (680.90 per sec.)
    deadlocks:                           1      (0.02 per sec.)
    read/write requests:                 776584 (12937.27 per sec.)
    other operations:                    81745  (1361.81 per sec.)

Test execution summary:
    total time:                          60.0269s
    total number of events:              40872
    total time taken by event execution: 959.3876
    per-request statistics:
         min:                                 12.71ms
         avg:                                 23.47ms
         max:                                512.94ms
         approx.  95 percentile:              34.24ms

Threads fairness:
    events (avg/stddev):           2554.5000/6.72
    execution time (avg/stddev):   59.9617/0.01

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            563430
        write:                           201225
        other:                           80490
        total:                           845145
    transactions:                        40245  (670.58 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 764655 (12741.04 per sec.)
    other operations:                    80490  (1341.16 per sec.)

Test execution summary:
    total time:                          60.0151s
    total number of events:              40245
    total time taken by event execution: 959.2476
    per-request statistics:
         min:                                  8.46ms
         avg:                                 23.84ms
         max:                                425.82ms
         approx.  95 percentile:              35.22ms

Threads fairness:
    events (avg/stddev):           2515.3125/4.70
    execution time (avg/stddev):   59.9530/0.01

#7

mysql-> set global innodb_flush_log_at_trx_commit=2;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            963018
        write:                           343935
        other:                           137574
        total:                           1444527
    transactions:                        68787  (1146.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1306953 (21773.99 per sec.)
    other operations:                    137574 (2292.00 per sec.)

Test execution summary:
    total time:                          60.0236s
    total number of events:              68787
    total time taken by event execution: 959.4940
    per-request statistics:
         min:                                  2.95ms
         avg:                                 13.95ms
         max:                               2439.00ms
         approx.  95 percentile:              21.81ms

Threads fairness:
    events (avg/stddev):           4299.1875/63.30
    execution time (avg/stddev):   59.9684/0.00

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            683760
        write:                           244200
        other:                           97680
        total:                           1025640
    transactions:                        48840  (813.91 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 927960 (15464.24 per sec.)
    other operations:                    97680  (1627.81 per sec.)

Test execution summary:
    total time:                          60.0068s
    total number of events:              48840
    total time taken by event execution: 959.4427
    per-request statistics:
         min:                                  2.79ms
         avg:                                 19.64ms
         max:                              16236.41ms
         approx.  95 percentile:              21.18ms

Threads fairness:
    events (avg/stddev):           3052.5000/249.41
    execution time (avg/stddev):   59.9652/0.00

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1065988
        write:                           380710
        other:                           152284
        total:                           1598982
    transactions:                        76142  (1268.82 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1446698 (24107.54 per sec.)
    other operations:                    152284 (2537.64 per sec.)

Test execution summary:
    total time:                          60.0102s
    total number of events:              76142
    total time taken by event execution: 959.1223
    per-request statistics:
         min:                                  2.91ms
         avg:                                 12.60ms
         max:                                684.32ms
         approx.  95 percentile:              21.16ms

Threads fairness:
    events (avg/stddev):           4758.8750/56.45
    execution time (avg/stddev):   59.9451/0.00

#8

mysql-> set global innodb_flush_log_at_trx_commit=0;
mysql-> set global innodb_thread_concurrency=0;

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1269646
        write:                           453437
        other:                           181376
        total:                           1904459
    transactions:                        90687  (441.01 per sec.)
    deadlocks:                           2      (0.01 per sec.)
    read/write requests:                 1723083 (8379.28 per sec.)
    other operations:                    181376 (882.02 per sec.)

Test execution summary:
    total time:                          205.6362s
    total number of events:              90687
    total time taken by event execution: 3271.3306
    per-request statistics:
         min:                                  2.87ms
         avg:                                 36.07ms
         max:                             149592.95ms
         approx.  95 percentile:              20.51ms

Threads fairness:
    events (avg/stddev):           5667.9375/124.93
    execution time (avg/stddev):   204.4582/0.29

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            374150
        write:                           133625
        other:                           53450
        total:                           561225
    transactions:                        26725  (329.72 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 507775 (6264.59 per sec.)
    other operations:                    53450  (659.43 per sec.)

Test execution summary:
    total time:                          81.0548s
    total number of events:              26725
    total time taken by event execution: 1296.3940
    per-request statistics:
         min:                                  2.98ms
         avg:                                 48.51ms
         max:                              64098.27ms
         approx.  95 percentile:              22.76ms

Threads fairness:
    events (avg/stddev):           1670.3125/43.26
    execution time (avg/stddev):   81.0246/0.01

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run

OLTP test statistics:
    queries performed:
        read:                            1262926
        write:                           451045
        other:                           180418
        total:                           1894389
    transactions:                        90209  (892.12 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1713971 (16950.30 per sec.)
    other operations:                    180418 (1784.24 per sec.)

Test execution summary:
    total time:                          101.1175s
    total number of events:              90209
    total time taken by event execution: 1596.6090
    per-request statistics:
         min:                                  2.86ms
         avg:                                 17.70ms
         max:                              46818.16ms
         approx.  95 percentile:              20.70ms

Threads fairness:
    events (avg/stddev):           5638.0625/109.61
    execution time (avg/stddev):   99.7881/0.32

[root@L2 3308]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3308 --db-driver=mysql --max-time=60 --oltp-read-only=off --max-requests=0 --num-threads=16 run
OLTP test statistics:
    queries performed:
        read:                            1262352
        write:                           450840
        other:                           180336
        total:                           1893528
    transactions:                        90168  (922.61 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1713192 (17529.59 per sec.)
    other operations:                    180336 (1845.22 per sec.)

Test execution summary:
    total time:                          97.7314s
    total number of events:              90168
    total time taken by event execution: 1492.0429
    per-request statistics:
         min:                                  2.62ms
         avg:                                 16.55ms
         max:                              41072.19ms
         approx.  95 percentile:              20.67ms

Threads fairness:
    events (avg/stddev):           5635.5000/125.99
    execution time (avg/stddev):   93.2527/1.13

#9

我也在测试这个，感觉打开日志log_bin后，并发变成顺序执行了
我用mysqlslap做压力测试，并发1个连接和20个连接，相同的执行数量，并没有变快

我关了log_bin，后并发就明显会快很多，但并发不能太大，50和100没区别


是不是这样呢？那这样的话，象网站高并发，同时向一个表中插入数据，怎么办？

#10

引用 1 楼  的回复:
任何一种数据库，包括ORACLE，SQL SERVER， 打开归档日志后都会产生额外的开销，MYSQL也不例外。 你需要的是在性能和数据安全中做平衡。 不写日志，INSERT、UPDATE、DELETE的速度会快一些，但一旦数据库有问题，你无法恢复。比如有人误删了一张表，你只能恢复到最新的备份，之的的数据就无法恢复了。


MySQL官方文档 http://dev.mysql.com/doc……

这方面还得做个均衡呀！
标签：

相关文章