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

测试环境说明:

云平台:腾讯云

服务器配置: 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的参数的测试结果:

060cb1c7198ec8402ec741f999b4ed55.png

sysbench-mysql-centos73-xfs-未优化mysql参数.png

原先的系统采用的是centos 6.8 x64,同样的测试过程和参数,但是结果不太理想。现在的结果是原先的两倍差距。原先的系统测试结果如下:

8ce78a8e98d95b67112df1b9b6e2fb9a.png

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值