长春公司网站推广,从哪里找网站,怎样创建自己的电商平台,wordpress国外模板下载测试mysql5.7和mysql8.0分别在读写#xff0c;选定#xff0c;只写模式下不同并发时的性能(tps#xff0c;qps)最早测试使用版本为mysql5.7.22和mysql8.0.15sysbench测试前先重启mysql服务#xff0c;并清除os的缓存(避免多次测试时命中缓存)每次进行测试都是新生成测试数据…测试mysql5.7和mysql8.0分别在读写选定只写模式下不同并发时的性能(tpsqps)最早测试使用版本为mysql5.7.22和mysql8.0.15sysbench测试前先重启mysql服务并清除os的缓存(避免多次测试时命中缓存)每次进行测试都是新生成测试数据后再进行mysql5.7和mysql8.0的测试每次测试时保证mysql5.7和mysql8.0的配置参数一致环境机器cat / etc / redhat-release | xargs echo版本 dmidecode -s系统产品名称| xargs echo是否虚拟化 cat / proc / cpuinfo | grep“ processor” | wc -l | xargs echocpu核数版本CentOS Linux版本7.5.1804(核心)是否虚拟化KVM cpu核数4myql5.7.225.7.22-loginnodb_buffer_pool_size 128Minnodb_log_buffer_size 64Minnodb_log_file_size 48Mbinlog_format ROWlog_bin ONtransaction_isolation REPEATABLE-READmysql8.0.158.0.15innodb_buffer_pool_size 128Minnodb_log_buffer_size 64Minnodb_log_file_size 48Mbinlog_format ROWlog_bin ONtransaction_isolation REPEATABLE-READ系统平台sysbench -Vsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)测试在不同的持久化策略下(binlog重做日志持久化)mysql5.7和mysql8.0在读写模式引用模式只写模式(oltp_read_writeoltp_read_onlyoltp_write_only)下的性能表现sysbench测试时间为60s测试的表数量为20测试分别在双1模式(安全性)和0 2模式(高级)下进行参数任选值意味着sync_binlog0binlog刷盘持久化由操作系统完成性能好存在丢失binlog的风险sync_binlog1个事务提交后刷盘持久化最安全sync_binlogñ在每N个事务提交后进行刷盘持久化innodb_flush_log_at_trx_commit0每秒钟写redo log并刷盘持久化innodb_flush_log_at_trx_commit1个事务提交后写redo log并刷盘持久化最安全innodb_flush_log_at_trx_commit2事务提交后写redo log两次刷盘持久化双1模式下SHOW GLOBAL VARIABLES WHERE Variable_name IN(sync_binlog,innodb_flush_log_at_trx_commit);---------------------------------------| Variable_name | Value |---------------------------------------| innodb_flush_log_at_trx_commit | 1 || sync_binlog | 1 |---------------------------------------mysql5.7和mysql8.0在读写模式下的表现双1配置读写模式下mysql5.7.22和mysql8.0.15 tpsqps性能差不多mysql8.0.15在120线程并发时性能出现了下降幅度mysql5.7和mysql8.0在预期模式下的表现双1配置预期模式下mysql5.7.22的tpsqps比mysql8.0.15好1/3左右并发线程数增加后tpsqps并没有增加反而出现了下降的趋势mysql5.7和mysql8.0在只写模式下的表现双1配置只写模式下转换并发数的上升mysql5.7.22的性能比mysql8.0.15好1/4左右0 2模式下SHOW GLOBAL VARIABLES WHERE Variable_name IN(sync_binlog,innodb_flush_log_at_trx_commit);---------------------------------------| Variable_name | Value |---------------------------------------| innodb_flush_log_at_trx_commit | 2 || sync_binlog | 0 |---------------------------------------mysql5.7和mysql8.0在读写模式下的表现0 2配置读写模式下并发数低时mysql5.7.22性能好于mysql8.0.15; 并发数比较高时mysql8.0.15性能好于mysql5.7.22在80线程的并发以上时性能开始下降mysql5.7和mysql8.0在预期模式下的表现0 2配置预期模式下mysql5.7.22性能比mysql8.0.15好1/3左右转换并发数的上升性能也没有上升反而有下降的趋势mysql5.7和mysql8.0在只写模式下的表现0 2配置只写模式下mysql5.7.22的tps顶点比较大mysql5.7.22的qps比mysql8.0.15好1/3左右摘要整体来看mysql5.7.22在读写模式扩展模式只写模式下的表现是mysql8.0.15的随着并行数的增加性能表现不会也跟着增加将会出现下降本次测试结果是在配置很低的情况下进行的不代表绝对注意sysbench需要设置--db-ps-mode disable禁用预编译语句不然并发测试线程多时会报下面的错误。致命mysql_stmt_prepare()失败致命MySQL错误1461“不能创建超过max_prepared_stmt_count语句(当前值16382)“致命mysql_stmt_prepare()失败致命MySQL错误1461”不能创建超过max_prepared_stmt_count语句(当前值16382)“致命thread_init function failed: /usr/local/share/sysbench/oltp_common.lua:288: SQL API error FATAL: mysql_stmt_prepare() failed FATAL: MySQL error: 1461 Cant create more than max_prepared_stmt_count statements (current value: 16382) FATAL:thread_init函数失败/ usr / local / share / sysbench / oltp_common.lua288SQL API错误致命mysql_stmt_prepare()失败使用脚本cat sysbench_test_mysql5.7_8.0_tps_qps.sh#!/bin/bash#用于sysbench 测试在读写模式、只读模式、只写模式下 mysql5.7和mysql8.0 的tps,qps#nohup bash $0 /tmp/sysbench_test 2 1 #useradminpasswdadminports8015 57222host127.0.0.1sysbench_test_modeoltp_read_write oltp_read_only oltp_write_onlysysbench_test_info_path/tmp/sysbench-testfunction red_echo (){local what$*echo -e $(date %F-%T) \e[1;31m ${what} \e[0m}function check_las_comm(){if [ $1 -ne 0 ];thenred_echo $2exit 1fi}function restart_mysqld(){service mysqld${1} restartsleep 2}function purge_binlog(){port$1mysql -u$user -p$passwd -P$port -h$hostpurge binary logs before now();EOF}function clean_os_cache(){echo 3 /proc/sys/vm/drop_caches}function sysbench_with_diff_thread(){thread_num$1port$2order$3test_mode$4sysbench /usr/local/share/sysbench/${test_mode}.lua --mysql_storage_engineinnodb --table-size100000 --tables20 --mysql-dbtest_1 --mysql-user$user --mysql-password$passwd --mysql-port$port --mysql-host$host --threads$thread_num --time60 --report-interval2 --db-ps-modedisable --events0 --db-drivermysql $order}function main(){for test_mode in $sysbench_test_mode;dofor port in $ports;dofor thread_num in {5,10,20,30,40,80,120,200};dorestart_mysqld $portcheck_las_comm $? restart mysqld${port} failed clean_os_cachepurge_binlog $portred_echo sysbench $thread_num threads cleanup mysqld${port}sysbench_with_diff_thread $thread_num $port cleanup $test_mode/dev/nullred_echo sysbench $thread_num threads prepare mysqld${port}sysbench_with_diff_thread $thread_num $port prepare $test_mode/dev/nullmkdir -p $sysbench_test_info_pathred_echo sysbench $thread_num threads run mysqld${port} $test_modesysbench_with_diff_thread $thread_num $port run $test_mode $sysbench_test_info_path/${test_mode}_${thread_num}_$port# service mysqld{port} stopdonedonedone}main来源r6d.cn/8cw
