作者：容 易 2015-11-17
测试环境和硬件参数
数据库硬件平台,数量4台
物理cpu数量2颗,开启超线程系统显示24core
Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz
内存64G
网卡千兆网络通过交换机直连
oneproxy服务器
物理CPU数量4颗,型号Intel(R) Xeon(R) CPU E7-4870 v2 @ 2.30GHz,未启用超线程系统显示60core
网卡数量4块
网卡基本分配可以参考测试构架图
压力服务器
物理CPU数量2颗,型号Intel(R) Xeon(R) CPU E5-2650 v3 @ 2.30GHz,启用超线程系统显示40core
操作系统版本 centos6.4*64 或者centos6.6*64
内核版本升级至
2.6.32-573.8.1.el6.x86_64
压测拓扑结构
服务器相关配置
设置网卡流量队列不同的CPU
/etc/init.d/irqbalance stop
cat /proc/interrupts |grep eth0|awk -F ':' '{print $1}'
echo 0 > /proc/irq/233/smp_affinity_list
echo 1 > /proc/irq/234/smp_affinity_list
echo 2 > /proc/irq/235/smp_affinity_list
echo 3 > /proc/irq/236/smp_affinity_list
开启服务器CPU最大性能模式
/etc/init.d/cpuspeed stop
for CPUFREQ in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do [ -f $CPUFREQ ] || continue; echo -n performance > $CPUFREQ; done
服务器内核参数设置
kernel.sem = 250 32000 100 128
#fs.file-max = 6815744
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 1024 65500
kernel.shmmni = 4096
vm.zone_reclaim_mode = 0
vm.swappiness = 1
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_max_syn_backlog = 65536
net.ipv4.tcp_max_tw_buckets = 500000
net.core.netdev_max_backlog = 32768
net.core.somaxconn = 32768
net.ipv4.tcp_synack_retries = 3
数据库版本 mysql.5.9-ga版本
使用mysql用户安装部署
编译安装参数如下
CXX=g++
CXXFLAGS="-O0 -g -fno-exceptions -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
cmake -DCMAKE_INSTALL_PREFIX=/app/mysql -DMYSQL_DATADIR=/app/mysql/data -DSYSCONFDIR=/app/mysql/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/app/mysql/run/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=/app/boost/
make -j 32 && make install
mysql用户环境变量设置
[mysql@wikirac1 ~]$ cat .bashrc
# .bashrc
# Source global definitions
export PATH=/app/mysql/bin:/app/cmake/bin:$PATH
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific aliases and functions
my.cnf配置
[client]
socket = /app/mysql/run/mysql.sock
password=oracle
user=root
[mysqld]
server_id=1
user=mysql
port = 3306
bind-address = 0.0.0.0
max_connections = 12000
max_connect_errors = 30
back_log = 600
socket = /app/mysql/run/mysql.sock
pid-file = /app/mysql/run/mysql.pid
basedir = /app/mysql
datadir = /app/mysql/data
log-error = /app/mysql/log/mysql_err.log
log_error_verbosity = 1
expire_logs_days=5
long_query_time=20
slow-query-log-file = /app/mysql/log/mysql_slowquery.log
explicit_defaults_for_timestamp=true
open_files_limit = 10240
table_open_cache = 4096
max_allowed_packet = 32M
max_heap_table_size = 128M
sort_buffer_size = 16M
join_buffer_size = 16M
read_buffer_size =1M
thread_cache_size = 120
query_cache_limit = 4M
ft_min_word_len = 8
character-set-server = utf8
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
innodb_file_per_table=1
innodb_thread_concurrency=16
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 2
innodb_flush_method = O_DIRECT
innodb_sync_spin_loops=11
innodb_spin_wait_delay=12
innodb_page_size=8192
query_cache_type = 0
query_cache_size=0
innodb_data_home_dir = /app/mysql/data
innodb_data_file_path = ibdata1:4096M:autoextend
innodb_write_io_threads = 12
innodb_read_io_threads = 12
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16M
innodb_log_file_size = 2048M
innodb_log_files_in_group = 3
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /app/mysql/data/
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 100
innodb_flush_neighbors = 0
innodb_io_capacity=400
innodb_io_capacity_max=1600
tmpdir=/app/mysql/data
测试用例表结构
create table user_info (
id int,
password int,
create_date char(8),
create_time char(9),
mail varchar(30),
phone varchar(15),
address varchar(30),
last_login_ip varchar(15),
user_name varchar(30),
photo varchar(30),
status varchar(2),
user_level varchar(2),
site varchar(19),
user_type varchar(19),
card_no varchar(16),
card_bank varchar(12),
account_balance int,
primary key (id)
);
通过oneproxy基于id键采用hash分区分成8张表,每个数据库服务器节点2张
oneproxy启动参数如下
${ONEPROXY_HOME}/oneproxy --keepalive --proxy-address=:3306 \
--proxy-master-addresses=172.168.10.164:3306@server1 \
--proxy-master-addresses=172.168.10.165:3306@server2 \
--proxy-master-addresses=172.168.11.166:3306@server3 \
--proxy-master-addresses=172.168.11.167:3306@server4 \
--proxy-user-list=syslog/3970E585A4128E104533EDACC19E6952AEB79412@oltp \
--proxy-part-tables=/app/oneproxy/part.txt \
--proxy-group-security=server1:0 \
--proxy-group-security=server2:0 \
--proxy-group-security=server3:0 \
--proxy-group-security=server4:0 \
--log-file=${ONEPROXY_HOME}/oneproxy.log \
--pid-file=${ONEPROXY_HOME}/oneproxy.pid \
--event-threads=56
part.txt
[{
"table" : "user_info",
"pkey" : "id",
"type" : "int",
"method" : "hash",
"partitions":
[
{ "suffix" : "_0", "group": "server1"},
{ "suffix" : "_1", "group": "server2"},
{ "suffix" : "_2", "group": "server3"},
{ "suffix" : "_3", "group": "server4"},
{ "suffix" : "_4", "group": "server1"},
{ "suffix" : "_5", "group": "server2"},
{ "suffix" : "_6", "group": "server3"},
{ "suffix" : "_7", "group": "server4"}
]
}
]
压力语句,基于主键ID随机查询,总并发数440
select last_login_ip from user_info where id=%s；
每个压力服务器基于多进程220个并发
负载情况
数据库端系统负载情况,其中一个节点,其他节点类似
数据库端性能指标
平均每个节点13W左右
一共有4个节点13*4
高峰的时候总QPS达到过55W
oneproxy节点负载情况
压力服务器负载情况
测试总结：
本次测试oneproxy的QPS转发能力以及突破了55W+，并且稳定在50W以上，从系统负载和数据库负载看，理论上还有可优化的空间，oneproxy的四块网卡，每个网卡的流量基本都是在30～50MB左右，ONEPROXY总CPU利用率高峰的时候上过%4800左右，50W的QPS时稳定在%4000左右。oneproxy节点的服务器还有大约8CORE的CPU处理能力，数据库端也大概还有6CORE左右的CPU余量。
我已经将我的环境和相关参数全部发出来了，理论上还有可调优的空间，本人技术有限，期待各位大神给出相关的优化建议，我将根据建议进行尝试，谢谢。
One Response