通过这些天的测试(高并发同时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,那么又是如何保证数据的复制能尽可能的实时呢?
sync_binlog=0的性能大概是sync_binlog=1时的5倍。
如果干脆不设置log-bin, 性能是sync_binlog=1时的将近8倍。
这是不是很恐怖呢?
我是在一个disk的情况下测试的,
大家能谈谈你们在实际应用的时候, 开binary log和不开binary log的性能差别吗?
另外sync_binlog通常设置成多少呢? 如果不是1,那么又是如何保证数据的复制能尽可能的实时呢?
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
binary log是可以保持数据复制,oracel也可以,可是并不会导致oracle性能急剧下降. 当然其实mysql也不会导致性能急剧下降了. 我昨天把mysql的data file, log file, bianry log file放到内存mount成的目录里, 性能就比较稳定, 只有很少的降低. 但不管怎么样, mysql的binary log置成1会很多的碰盘写操作. 事务越小, 写操作越频繁,影响越大. 所以强烈建议在slave端不要开binary log, 当然如果slave不需要事务保证,那myisam最好了.
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 benchOLTP 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.24msThreads 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.22msThreads fairness:
events (avg/stddev): 2515.3125/4.70
execution time (avg/stddev): 59.9530/0.01
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.81msThreads 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.18msThreads 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.16msThreads fairness:
events (avg/stddev): 4758.8750/56.45
execution time (avg/stddev): 59.9451/0.00
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.51msThreads 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 runOLTP 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.76msThreads 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 runOLTP 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.70msThreads 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.67msThreads fairness:
events (avg/stddev): 5635.5000/125.99
execution time (avg/stddev): 93.2527/1.13
我用mysqlslap做压力测试,并发1个连接和20个连接,相同的执行数量,并没有变快我关了log_bin,后并发就明显会快很多,但并发不能太大,50和100没区别
是不是这样呢?那这样的话,象网站高并发,同时向一个表中插入数据,怎么办?