测试服务器运行数据库,云服务器-自建数据库之测试

测试环境说明：

云平台：腾讯云

服务器配置： 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，同样的测试过程和参数，但是结果不太理想。现在的结果是原先的两倍差距。原先的系统测试结果如下：