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
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
#2
楼上说的很全了.主要是在性能和安全中做平衡.
#3
sync_binlog=1
会使用分布式事务(mysql和存储引擎之间)来刷写日志到磁盘,之后才正式commit事务,所以比较慢,但非常安全!
会使用分布式事务(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最好了.
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
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
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
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没区别
是不是这样呢?那这样的话,象网站高并发,同时向一个表中插入数据,怎么办?
我用mysqlslap做压力测试,并发1个连接和20个连接,相同的执行数量,并没有变快
我关了log_bin,后并发就明显会快很多,但并发不能太大,50和100没区别
是不是这样呢?那这样的话,象网站高并发,同时向一个表中插入数据,怎么办?
#10
这方面还得做个均衡呀!
#1
任何一种数据库,包括ORACLE,SQL SERVER, 打开归档日志后都会产生额外的开销,MYSQL也不例外。 你需要的是在性能和数据安全中做平衡。 不写日志,INSERT、UPDATE、DELETE的速度会快一些,但一旦数据库有问题,你无法恢复。比如有人误删了一张表,你只能恢复到最新的备份,之的的数据就无法恢复了。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
#2
楼上说的很全了.主要是在性能和安全中做平衡.
#3
sync_binlog=1
会使用分布式事务(mysql和存储引擎之间)来刷写日志到磁盘,之后才正式commit事务,所以比较慢,但非常安全!
会使用分布式事务(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最好了.
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
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
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
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没区别
是不是这样呢?那这样的话,象网站高并发,同时向一个表中插入数据,怎么办?
我用mysqlslap做压力测试,并发1个连接和20个连接,相同的执行数量,并没有变快
我关了log_bin,后并发就明显会快很多,但并发不能太大,50和100没区别
是不是这样呢?那这样的话,象网站高并发,同时向一个表中插入数据,怎么办?
#10
这方面还得做个均衡呀!