测试环境说明:
云平台:腾讯云
服务器配置: 4Core 16GB 500Gdisk(普通云盘)
OS:centos 7.3镜像(因为各家都有一定调整,所以特别说明是镜像来源于公共镜像)
OS kernel: 3.10.0-514.21.1.el7.x86_64
mysql数据目录: 采用XFS文件系统。
测试工具:
sysbench 1.0.6 (采用yum安装,即 " yum install sysbench -y ",可能存在小版本差异)
安装完成后,可以在 “/usr/share/sysbench目录下看到很多的Lua写的测试脚本”
比如:
bulk_insert.lua oltp_common.lua oltp_point_select.lua oltp_update_index.lua select.lua update_index.lua
common.lua oltp_delete.lua oltp_read_only.lua oltp_update_non_index.lua select_random_points.lua update_non_index.lua
delete.lua oltp_insert.lua oltp_read_write.lua oltp_write_only.lua select_random_ranges.lua
insert.lua oltp.lua oltp_simple.lua parallel_prepare.lua tests
其中tests目录。
内核参数(采用镜像已经设定的值,没有调整),sysctl输出如下:
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
net.ipv6.conf.lo.disable_ipv6 = 1
net.ipv4.conf.all.promote_secondaries = 1
net.ipv4.conf.default.promote_secondaries = 1
net.ipv6.neigh.default.gc_thresh3 = 4096
net.ipv4.neigh.default.gc_thresh3 = 4096
kernel.softlockup_panic = 1
kernel.sysrq = 1
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
vm.overcommit_memory = 1
kernel.shmmax = 68719476736
系统ulimit(此也是系统镜像已经设定好的)输出如下:
* soft nofile 100001
* hard nofile 100002
root soft nofile 100001
root hard nofile 100002
mysql软件版本信息如下:
mysql57-community-release-el7-11.noarch
mysql-community-client-5.7.19-1.el7.x86_64
mysql-community-libs-5.7.19-1.el7.x86_64
mysql-community-devel-5.7.19-1.el7.x86_64
mysql-community-common-5.7.19-1.el7.x86_64
mysql-community-server-5.7.19-1.el7.x86_64
mysql-community-libs-compat-5.7.19-1.el7.x86_64
mysql运行参数如下:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/mysqldata
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1
event_scheduler=ON
character_set_server=utf8
init_connect='SET NAMES utf8'
max_allowed_packet=256M
max_connections=500
skip-name-resolve
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
innodb_buffer_pool_size=8589934592
key_buffer_size=200M
low_priority_updates=1
table_open_cache = 8000
back_log=1500
query_cache_type=0
table_open_cache_instances=16
# files
innodb_file_per_table
innodb_log_file_size=1024M
innodb_log_files_in_group=3
innodb_open_files=4000
# buffers
#innodb_buffer_pool_size=32000M
innodb_buffer_pool_instances=1
innodb_log_buffer_size=64M
join_buffer_size=32K
sort_buffer_size=32K
# innodb
innodb_checksums=0
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=50
innodb_use_native_aio=1
innodb_stats_persistent=1
innodb_spin_wait_delay=6
# perf special
innodb_adaptive_flushing=1
innodb_flush_neighbors=0
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=4000
innodb_purge_threads=1
innodb_adaptive_hash_index=0
# monitoring
innodb_monitor_enable='%'
performance_schema=ON
测试过程说明:
从应用服务器访问数据库服务器的mysql服务。
测试参数:
在测试前当然是需要先在测试库创建sbtest数据库,并创建相关数据,也就是将最后的“run”命令替换为“prepare”去生产相应的测试数据。最后也别忘记清除测试数据,只要将“run”替换成“cleanup”即可。
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=192.168.1.12 --mysql-port=3306 \
--mysql-user=root \
--mysql-password=PPPPPPPWWWWDDD \
--mysql-db=sbtest \
--report-interval=5 \
--time=120 \
--threads=12 \
--percentile=99 \
--db-driver=mysql \
run
最后是测试结果截图:
屏幕快照 2017-08-30 下午6.28.05.png
下图为未优化mysql的参数的测试结果:
sysbench-mysql-centos73-xfs-未优化mysql参数.png
原先的系统采用的是centos 6.8 x64,同样的测试过程和参数,但是结果不太理想。现在的结果是原先的两倍差距。原先的系统测试结果如下: