一. GTID半同步复制+ansible配置
半同步复制要先在mysql里安装这个插件
root@(none) 14:37 mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
# MASTER配置
[root@mysql ~]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
log-error = tanxue.err
general_log
slow_query_log = 0.001
log_bin
server_id = 1
expire_logs_days = 3
rpl_semi_sync_master_enabled=1 # 半同步复制
rpl_semi_sync_master_timeout=1000 # 半同步复制
gtid-mode=ON
enforce-gtid-consistency=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
# slave配置
[root@my-slv1 ~]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
server-id=2
log-bin
rpl_semi_sync_slave_enabled=1 # 半同步复制
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
root@(none) 21:38 mysql>grant replication slave on . to 'zhangwz'@'192.168.138.%' identified by 'sanchuang123#';
root@(none) 21:42 mysql>CHANGE MASTER TO MASTER_HOST='192.168.138.133' ,
-> MASTER_USER='zhangwz',
-> MASTER_PASSWORD='sanchuang123#',
-> MASTER_PORT=3306,
-> master_auto_position=1;
root@(none) 21:43 mysql>start slave;
root@(none) 21:45 mysql>show slave status\G;
************************* 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.138.133
Master_User: zhangwz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: my-slvdelay-relay-bin.000002
Relay_Log_Pos: 665
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 452
Relay_Log_Space: 878
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7cc8fa5e-0f19-11ee-935c-000c29576596
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7cc8fa5e-0f19-11ee-935c-000c29576596:1
Executed_Gtid_Set: 7cc8fa5e-0f19-11ee-935c-000c29576596:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
验证GTID配置是否成功
root@(none) 15:16 mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
root@(none) 15:17 mysql>show variables like "%semi_sync%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
root@(none) 14:22 mysql>show variables like 'gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------+
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 7cc8fa5e-0f19-11ee-935c-000c29576596:1-12 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)
on说明master和slave的gtid半同步已开启
1.从slave1上拿数据做延迟备份
延迟备份配置
root@(none) 20:34 mysql>stop slave;
root@(none) 20:35 mysql>reset master;
root@(none) 20:35 mysql>reset slave all;
root@(none) 20:35 mysql>CHANGE MASTER TO MASTER_HOST='192.168.138.132' , **//slave1 ip地址**
-> MASTER_USER='zhangwz',
-> MASTER_PASSWORD='sanchuang123#',
-> MASTER_PORT=3306,
-> master_auto_position=1;
root@(none) 20:36 mysql>CHANGE MASTER TO MASTER_DELAY = 10;
delay-slave
root@(none) 21:35 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.138.132
Master_User: zhangwz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: my-slv1-bin.000001
Read_Master_Log_Pos: 2130
Relay_Log_File: my-slvdelay-relay-bin.000007
Relay_Log_Pos: 662
Relay_Master_Log_File: my-slv1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2130
Relay_Log_Space: 1221
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 935799ae-2fa9-11ee-add1-000c290ec7d1
Master_Info_File: /data/mysql/master.info
SQL_Delay: 10
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7cc8fa5e-0f19-11ee-935c-000c29576596:2-8,
935799ae-2fa9-11ee-add1-000c290ec7d1:1-5
Executed_Gtid_Set: 0e4011eb-2f9a-11ee-8c5c-000c29c7fe14:1-2,
7cc8fa5e-0f19-11ee-935c-000c29576596:2-8,
935799ae-2fa9-11ee-add1-000c290ec7d1:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
gtid+relay-log半同步复制配置完成
2.在ansible和master之间建立免密双向通道
[root@mysql ~]# ssh-keygen -t rsa
[root@mysql ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.138.137
3.创建备份脚本和计划任务
crontab -e
[root@sc-master backup]# crontab -l
30 2 * * * bash /backup/backup_alldb.sh
[root@sc-master backup]# cat backup_alldb.sh
#!/bin/bash
mkdir -p /backup
mysqldump -uroot -p'sanchuang124#' --all-databases --triggers --routines --events >/backup/$(date +%Y%m%d%H%M)_all_db.SQL
scp /backup/$(date +%Y%m%d%H%M)_all_db.SQL 192.168.2.103:/backup
二：读写分离 --> mysqlrouter配置
mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@mysqlrouter ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
[root@mysqlrouter ~]# cd /etc/mysqlrouter
[root@mysqlrouter mysqlrouter]# vim mysqlrouter.conf
[routing:slaves]
bind_address = 0.0.0.0:7001
destinations = 192.168.138.132:3306,192.168.138.131:3306
mode = read-only
connect_timeout = 1
[routing:masters]
bind_address = 0.0.0.0:7002
destinations = 192.168.138.133:3306
mode = read-write
connect_timeout = 1
2.刷新服务
[root@mysqlrouter mysqlrouter]# service mysqlrouter restart
3.在master上授权（二进制同步到slave）
root@(none) 20:03 mysql>grant select on *.* to 'scread'@'%' identified by 'sanchuang123#';
root@(none) 20:04 mysql>grant all on *.* to 'scwrite'@'%' identified by 'sanchuang123#';
4.查看端口
[root@mysqlrouter mysqlrouter]# netstat -anplut|grep mysql
tcp 0 0 192.168.138.134:7001 0.0.0.0:* LISTEN 3184/mysqlrouter
tcp 0 0 192.168.138.134:7002 0.0.0.0:* LISTEN 3184/mysqlrouter
5.测试
scwrite写操作连接成功
scread只读连接成功
思考：读写分离的关键点是授权用户，真正检查权限的是后端的mysql服务器
读写分离的关键点：其实是用户的权限，让不同的用户连接不同的端口，最后任然要到后端的mysql服务器里去验证是否有读写的权限
mysqlrouter只是做了读写的分流，让应用程序去连接不同的端口--》mysqlrouter只是一个分流的工具
主要是用户权限的控制，有写权限的用户走读的通道也可以写，读的用户走写的通道只能读
三.中间件mysqlroute的HA配置
1.在另一台mysqlroute也装好mysqlrouter
[root@keepalived mysqlrouter]# ps aux|grep mysql
mysqlro+ 3165 0.1 0.3 366804 6072 ? Ssl 21:32 0:00 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
root 3173 0.0 0.0 112824 980 pts/0 S+ 21:32 0:00 grep --color=auto mysql
2.安装keepalived
[root@keepalived mysqlrouter]# yum install keepalived
3.修改配置文件
backup
[root@mysqlrouter keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state backup
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.138.16
}
}
master
[root@keepalived ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.138.16
}
}
4.刷新服务查看ip地址
[root@keepalived ~]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.138.16/32 scope global ens33
valid_lft forever preferred_lft forever
5.模拟vip漂移
5.1 停止有vip的那台服务器的keepalived服务，在另一台keepalived服务器查看是否有vip
[root@keepalived ~]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service
[root@mysqlrouter ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:f0:9d:ae brd ff:ff:ff:ff:ff:ff
inet 192.168.138.134/24 brd 192.168.138.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.138.16/32 scope global ens33
valid_lft forever preferred_lft forever
[root@keepalived ~]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service
[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.138.16/32 scope global ens33
valid_lft forever preferred_lft forever
6.发送告警
[root@keepalived mail]# cat sendmail.sh
#!/bin/bash
mkdir -p /mail/$RANDOM-sc
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
# vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
#keepalived进程去封装vrrp报文
#当keepalived进程发送vrrp报文，执行的脚本，每隔3秒钟执行一次，配置名字叫send_mail
#在vrrp实例里的配置，只要启动keepalived进程就会每隔3秒执行一次，不管你是master还是backup
#/mail/sendmail.sh 需要自己去创建，编写
vrrp_script send_mail {
script "/mail/sendmail.sh"
interval 3 #每隔3秒钟就执行一次这个脚本
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
#追踪执行脚本，只要成为master，发送vrrp宣告消息就执行脚本
track_script {
send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.138.16
}
}
[root@mysqlrouter mail]# ls
15107-master 19951-stop 21961-backup backup.sh master.sh sendmail.sh stop.sh
keepalived正常启动的时候，共启动3个进程：
一个是父进程，负责监控其子进程；一个是VRRP子进程，另外一个是checkers子进程；
两个子进程都被系统watchdog看管，两个子进程各自负责自己的事。
Healthcheck子进程检查各自服务器的健康状况，，例如http,lvs。如果healthchecks进程检查到master上服务不可用了，就会通知本机上的VRRP子进程，让他删除通告，并且去掉虚拟IP，转换为BACKUP状态。
[root@keepalived mail]# ps aux|grep keepalived
root 3524 0.0 0.0 123064 1408 ? Ss 21:55 0:00 /usr/sbin/keepalived -D
root 3525 0.0 0.1 134036 3396 ? S 21:55 0:00 /usr/sbin/keepalived -D
root 3526 0.0 0.1 133972 2912 ? S 21:55 0:00 /usr/sbin/keepalived -D
root 3749 0.0 0.0 112824 980 pts/0 S+ 21:59 0:00 grep --color=auto keepalived
四.keepalived实现双vip功能
思路：2个vrrp实例，2个vip，2个实例互为主备
1.第一台服务器的配置
[root@keepalived ~]# cat /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.138.16
}
}
vrrp_instance VI_2 {
state backup
interface ens33
virtual_router_id 52
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.138.17
}
}
# 第二台服务器的配置
vrrp_script send_mail {
script "/mail/sendmail.sh"
interval 3 #每隔3秒钟就执行一次这个脚本
}
vrrp_instance VI_1 {
state backup
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
#追踪执行脚本，只要成为master，发送vrrp宣告消息就执行脚本
track_script {
send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.138.16
}
}
vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 52
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
virtual_ipaddress {
192.168.138.17
}
}
2.停掉其中一台服务器的keepalived
[root@mysqlrouter mail]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:f0:9d:ae brd ff:ff:ff:ff:ff:ff
inet 192.168.138.134/24 brd 192.168.138.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.138.17/32 scope global ens33
valid_lft forever preferred_lft forever
[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.138.16/32 scope global ens33
valid_lft forever preferred_lft forever
五.mysql监控
六.压力测试
1.安装sysbench
[root@localhost ~]# rpm -ivh mysql80-community-release-el7-9.noarch.rpm
[root@localhost ~]# yum install mysql-community-devel
[root@localhost sysbench-1.0.15]# yum -y install automake
[root@localhost sysbench-1.0.15]# yum -y install libtool
[root@localhost ~]# tar xf sysbench-1.0.15.tar.gz
[root@localhost ~]# cd sysbench-1.0.15
[root@localhost sysbench-1.0.15]# ./autogen.sh
[root@localhost sysbench-1.0.15]# ./configure
[root@localhost sysbench-1.0.15]# make -j
[root@localhost sysbench-1.0.15]# make install
[root@localhost sysbench-1.0.15]# which sysbench
/usr/local/bin/sysbench
2.测试
在mysql里创建sbtest（默认）这个数据库用于测试
[root@my-slv1 mysql]# mysql -uscwrite -p'sanchuang123#' -h 192.168.138.134 -P 7001
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
scwrite@(none) 15:04 mysql>create database sbtest;
Query OK, 1 row affected (0.01 sec)
[root@localhost lianxi]# sysbench --mysql-host=192.168.138.134 --mysql-port=7001 --mysql-user=scwrite --mysql-password='sanchuang123#' /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=10000 prepare
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 10000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 10000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 10000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'..
[root@localhost lianxi]# sysbench --threads=4 --time=20 --report-interval=5 --mysql-host=192.168.138.134 --mysql-port=7001 --mysql-user=scwrite --mysql-password='sanchuang123#' /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 4 tps: 174.75 qps: 3511.07 (r/w/o: 2459.35/183.75/867.97) lat (ms,95%): 27.66 err/s: 0.60 reconn/s: 0.00
[ 10s ] thds: 4 tps: 177.61 qps: 3552.83 (r/w/o: 2487.16/203.61/862.06) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 173.38 qps: 3469.66 (r/w/o: 2428.96/214.98/825.72) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 172.41 qps: 3449.02 (r/w/o: 2414.35/231.61/803.05) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 48972
write: 4175
other: 16807
total: 69954
transactions: 3495 (174.58 per sec.)
queries: 69954 (3494.25 per sec.)
ignored errors: 3 (0.15 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0191s
total number of events: 3495
Latency (ms):
min: 15.84
avg: 22.90
max: 49.10
95th percentile: 28.16
sum: 80028.98
Threads fairness:
events (avg/stddev): 873.7500/3.34
execution time (avg/stddev): 20.0072/0.01