Sysbench 基準壓測 my.cnf ============================================ [TOC] line： V1.1 mail: [email protected] date: 2017 11 10 一、Sysench測試前準備 1.1、壓測環境 配置 | ...
Sysbench 基準壓測 my.cnf
[TOC]
############################################
- line： V1.1
- mail: [email protected]
- date: 2017-11-10
###########################################
一、Sysench測試前準備
1.1、壓測環境
| 配置 | 信息 |
|---|---|
| 主機 | Dell PowerEdge R730xd |
| CPU | 24 * Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz |
| 記憶體 | 64G (16G * 4) |
| RAID | RAID1 |
| 硬碟 | 7.2K RPM, 6000G SAS, 12G/s |
| 文件系統 | ext4 |
| 系統 | Red Hat Enterprise Linux Server release 7.3 (Maipo) |
| 內核 | 3.10.0-514.el7.x86_64 |
| MySQL | MySQL5.7.18 |
| Sysbench | 1.1.0-0167e45 |
1.2、壓測基準值
| 測試工具 | sysbench |
|---|---|
| 每張表初始化的數據數量 | 10,000,000 |
| 測試表數 | 16 |
| 測試腳本 | oltp_update_index.lua、oltp_update_non_index.lua |
| run time(秒) | 3600 |
| 最大請求數 | 100,000,000 |
| 併發線程數 | 8 - 256 |
| 生成數據量 | 127.56G |
二、進行OLTP_update測試
2.1、安裝壓測工具sysbench
- 標準安裝
[root@localhost-m(252) /r2/soft/dbtest] curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash &&
sudo yum -y install sysbench
- my.cnf文件
# line V1.2
# mail:
# data: 2017-10-26
# file_name: my.cnf
# function: 無數據一致性要求，只要求update速度快
## optimized mysql configure file my.cnf
## 註意：建議參數可需要根據實際情況作調整
## 本配置文件主要適用於MySQL 5.7.18版本
# ********* 以下重要參數必須修改核對 *********
# 1.innodb_flush_log_at_trx_commit=2
# 2.sync_binlog = 0
# 3.innodb_strict_mode = OFF
# 4.innodb_flush_method = O_DIRECT
# 5.lower_case_table_names = 0
# 6.character-set-server = utf8
# 7.sql_mode 配置為空值
# 8.server-id =1 修改成對應數值
# 9.innodb_buffer_pool_size = 32G
# ********************************************
[client]
port = 3306
socket = /r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock
#=======================================================================
# # MySQL客戶端配置
#=======================================================================
[mysql]
prompt="\u@ipanel \R:\m:\s [\d]> "
#no-auto-rehash
#=======================================================================
# MySQL伺服器全局配置
#=======================================================================
[mysqld]
user = mysql
port = 3306
server-id = 168
tmpdir = /r2/soft/dbtest/mysql-5.7.18/mysqldata
datadir = /r2/soft/dbtest/mysql-5.7.18/mysqldata
socket = /r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock
wait_timeout = 31536000
#interactive_timeout = 600
sql_mode =
#sql_mode 配置為空值
skip_name_resolve = 1
lower_case_table_names = 0
character-set-server = utf8
#auto_increment_increment = 1
#auto_increment_offset = 1
# init_connect =
######################### 性能參數 ####################
open_files_limit = 1024000
max_connections = 10000
max_user_connections=9990
max_connect_errors = 100000
table_open_cache = 1024
max_allowed_packet = 128M
thread_cache_size = 64
max_heap_table_size = 32M
query_cache_type = 0
###global cache ###
key_buffer_size = 16M
query_cache_size = 0
###session cache ###
sort_buffer_size = 8M #排序緩衝
join_buffer_size = 4M #表連接緩衝
read_buffer_size = 8M #順序讀緩衝
read_rnd_buffer_size = 8M #隨機讀緩衝
tmp_table_size = 32M #記憶體臨時表
binlog_cache_size = 4M #二進位日誌緩衝
thread_stack = 256KB #線程的堆棧的大小
######################### binlog設置 #####################
binlog_format = MIXED
log_bin = /r2/soft/dbtest/mysql-5.7.18/mysqldata/binlog
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 30
sync_binlog = 0 #重要參數必須修改為0
######################### 複製設置 ########################
log_slave_updates=1
#replicate-do-db=User
#binlog-ignore-db = test
#slave-skip-errors=1146,1032,1062
### GTID 配置 ###
gtid_mode=ON
enforce-gtid-consistency=true
######################### innodb ##########################
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 32G #系統記憶體50%
innodb_open_files = 1000000
innodb_flush_log_at_trx_commit = 2 #線上伺服器必須配置為2
innodb_file_per_table = 1
innodb_lock_wait_timeout = 5
# 根據您的伺服器IOPS能力適當調整innodb_io_capacity
# 一般配普通SSD盤的話，可以調整到 10000 - 20000
#innodb_io_capacity = 200
innodb_io_capacity = 600
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_large_prefix = 0
innodb_thread_concurrency = 64
innodb_strict_mode = OFF
innodb_sort_buffer_size = 4194304
######################### log 設置 #####################
log_error = /r2/soft/dbtest/mysql-5.7.18/mysqldata/error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /r2/soft/dbtest/mysql-5.7.18/mysqldata/slow.log
#=======================================================================
# MySQL mysqldump配置
#=======================================================================
[mysqldump]
quick
max_allowed_packet = 32M
#=======================================================================
# MySQL mysqld_safe配置
#=======================================================================
[mysqld_safe]
log_error = /r2/soft/dbtest/mysql-5.7.18/mysqldata/error.log
pid_file = /r2/soft/dbtest/mysql-5.7.18/mysqldata/mysqldb.pid
- Test 腳本
# line V1.2
# mail: [email protected]
# date: 2017-10-26
# file_name: mysql_oltp_sysbench.sh
# 修改符合公司環境
#通過sysbench測試mysql相關性能，並將關鍵數據存儲於‘dbtest.sysbenc_test’表中
#----------自定義部分----------
#定義記錄測試結果的mysql連接相關參數，本例我在測試機上記錄測試結果
m_user='gcdb'
m_passwd='iforgot'
m_port='3306'
m_host='localhost'
#測試結果存儲於哪個庫
m_db='dbtest'
#測試結果存儲於哪個表
m_table='sysbench_test'
m_sock='/r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock'
#sysbench lua腳本目錄
lua_dir=/usr/local/sysbench-1.1.0/share/sysbench
#sysbench 參數
SYSBENCH_PARAMETER="--table-size=10000000 --tables=32 --max-requests=100000000 --rand-type=uniform --report-interval=10 --max-time=1200 "
#SYSBENCH_PARAMETER="--mysql-table-engine=innodb --oltp-table-size=5000000 --oltp-tables-count=16 --oltp-test-mode=complex --rand-type=uniform --rand-init=on --report-interval=10 --max-time=1500 "
#畫圖維度（關註的六個指標，也就是會畫六張圖）
target="server_load request_read request_write request_per_second transactions_per_second 95_pct_time"
#定義錯誤日誌文件
log=/tmp/mysql_oltp.log
#定義分析結果文件
data=/tmp/mysql_oltp.dat
#定義測試線程
threds_num='24 32 64 96 128 160 192 256'
#每種場景的測試次數，分析時取平均值
times=1
#----------自定義部分結束----------
# sysbenc cleanup and perpare
#sysbench $lua_dir/oltp_read_only.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 $SYSBENCH_PARAMETER --threads=24 cleanup
#sysbench $lua_dir/oltp_read_only.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 $SYSBENCH_PARAMETER --threads=24 prepare
#測試函數
sb_test() {
if [ "$3" == "read-only" ];then read_only='on';else read_only='off';fi #根據腳本參數確定是否read-only
#創建記錄測試信息的表
echo -e "\n---------------\n創建測測試結果表$m_db.$m_table\n---------------"
return=$(mysql -u$m_user -p$m_passwd -P$m_port -h$m_host -S $m_sock <<EOF 2>&1
CREATE TABLE IF NOT EXISTS $m_db.$m_table (
scenario varchar(30) NOT NULL DEFAULT '' COMMENT '測試場景',
server_name varchar(15) NOT NULL COMMENT '被測DB name',
test_type varchar(15) NOT NULL COMMENT 'read-only,read-write,insert等',
sb_threads int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 測試線程',
server_load decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以當前線程測試完後立刻記錄一分鐘負載值',
request_read int(11) NOT NULL DEFAULT '0',
request_write int(11) NOT NULL DEFAULT '0',
transactions_per_second decimal(12,2) NOT NULL DEFAULT '0.00',
request_per_second decimal(12,2) NOT NULL DEFAULT '0.00',
95_pct_time decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '單位毫秒'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
EOF
)
if [ $? -ne 0 ];then
echo $return|sed 's/[Ww]arning:.*password on the command.*insecure\.//'
#echo "create table $m_db.$m_table failed"
exit -1
fi
#開始測試,每種條件測$times次,分析時取平均值
echo -e "\n---------------\n場景:$2 模式:$3\n---------------"
for i in `seq $times`;do
for sb_threds in $threds_num;do #按照指定的sysbench線程測試
printf " %-10s %s\n" $sb_threds線程 第$i次運行...
#result 作為每次最小測試單元的結果，根據sysbench測試結果各參數的出現順序
#以request_read、request_write、request_total、request_per_second、total_time、95_pct_time為順序插入表中
#下條命令中，egerp之後的操作是為了對sysbench的輸出做篩選和格式化，以便插入資料庫
#sysbench 參數
#--oltp_table_count=1：指定測試過程中表的個數，0.5新增，0.4整個測試過程只有一個表。
#--oltp-table-size=：指定表的大小，如果指定1000，那麼它會往表裡初始化1000條數據
#--rand-init=on：是否隨機初始化數據，如果不隨機化那麼初始好的數據每行內容除了主鍵不同外其他完全相同。
#--num-threads=：測試過程中併發線程數，看測試要求來定併發壓力。
#--otlp-read-only=off：知否只讀測試
#--report-interval=10：每隔多久列印一次統計信息，單位秒，0.5新增
#--rand-type=special：數據分佈模式，special表示存在熱點數據，uniform表示非熱點數據模式，還有其他幾個選項。
#--rand-spec-pct=5：這個與上面那個選項相關，熱點數據的百分比，我們公司的一個應用測試出來是4.9%的熱點數據。
#--mysql-table-engine=$type：表的存儲引擎類型，innodb/myisam/tokudb/這些都可以。
#--max-time=8000：這個命令跑多長時間，單位秒，與之相反的是指定請求數--max-requests
# sysbench $lua_dir/oltp_update_non_index.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 \
sysbench $lua_dir/oltp_update_index.lua --mysql-user=$6 --mysql-password=$7 --mysql-port=$5 --mysql-host=$4 \
--num-threads=$sb_threds $SYSBENCH_PARAMETER run &> $log
if [ $? -ne 0 ];then
echo -e "\nSysbench error! For more information see $log"
exit -1
fi
result=$(cat $log | egrep "read:|write:|transactions:|queries:|95th\ percentile:" | sed -r -e "s/[0-9]+ \(//g" -e "s/\ per sec\.\)//g" -e "s/m?s$//g"| awk '{printf("%s ",$NF)}' | sed "s/\ /,/g" | sed "s/,$//g" | sed "s/(//g")
#測試完成後立刻記錄系統一分鐘負載值，可近似認為測試過程中proxy的負載抽樣
load=`uptime|awk -F: '{print $NF}'| awk -F , '{print $1}'`
if [ -s $load ];then
load=0.00
fi
return=$(mysql -u$m_user -p$m_passwd -P$m_port -h$m_host -S $m_sock $m_db <<EOF 2>&1
INSERT INTO $m_table (scenario,server_name,test_type,sb_threads,server_load,request_read,
request_write,transactions_per_second,request_per_second,95_pct_time)
VALUES ('$2','$4','$3','$sb_threds','$load',$result);
EOF
)
# echo "INSERT INTO $m_table (scenario,server_name,test_type,sb_threads,server_load,request_read,request_write,transactions_per_second,request_per_second,95_pct_time) VALUES ('$2','$4','$3','$sb_threds','$load',$result)"
# exit 1
if [ $? -ne 0 ];then
echo -e "\n----------$sb_threds線程測試，第$i次插入資料庫時失敗----------"
#錯誤輸出中排除mysql安全提示
echo $return|sed 's/[Ww]arning:.*password on the command.*insecure\.//'
#echo "INSERT VALUES ('$2','$4','$3',$sb_threds,$load,$result)"
exit -2
fi
sleep 60 #讓庫歇一會，也讓一分鐘負載能夠恢復到測試前的值
done
done
}
#結果分析函數
sb_analyse() {
#2>&1|grep部分為避免安全提示,使用該技巧就無法獲取SQL執行的返回碼了
mysql -u$m_user -p$m_passwd -h$m_host -S $m_sock <<EOF 2>&1|grep -v 'password on the command line'
SELECT
scenario,
server_name,
test_type,
sb_threads,
convert(avg(server_load),decimal(12,2)) as server_load,
convert(avg(request_read),decimal(12,0)) as request_read,
convert(avg(request_write),decimal(12,0)) as request_write,
convert(avg(transactions_per_second),decimal(12,2)) as transactions_per_second,
convert(avg(request_per_second),decimal(12,2)) as request_per_second,
convert(avg(95_pct_time),decimal(12,2)) as 95_pct_time
FROM $m_db.$m_table group by scenario,server_name,test_type,sb_threads
EOF
}
#畫圖函數
sb_chart() {
sb_analyse >$data
for chart_type in $target;do
col_num=0 #該行及下麵這個for迴圈用於取得三個指標在數據中的列號
for col_name in `cat $data |awk 'NR<2 {print}'`;do
let col_num++
if [ $col_name == $chart_type ];then break;fi
done
if [ $chart_type == "transactions_per_second" ];then #根據圖表特點為不同的chart_type設置gunplot不同的key position
key_pos="top left"
unit="TPS"
elif [ $chart_type == "request_per_second" ];then #根據圖表特點為不同的chart_type設置gunplot不同的key position
key_pos="top left"
#key_pos="bottom right"
unit="QPS"
elif [ $chart_type == "95_pct_time" ];then
key_pos="top left"
unit="(95_pstms)"
elif [ $chart_type == "request_write" ];then
key_pos="top left"
unit="request_write"
elif [ $chart_type == "request_read" ];then
key_pos="top left"
unit="(only_read)"
elif [ $chart_type == "server_load" ];then
key_pos="top left"
unit="(CPU_load_1_min)"
fi
plot_cmd="set term png size 800,600; set output '/tmp/$chart_type.png';set title '$unit'; set ylabel '$chart_type'; set grid;set key $key_pos;set ylabel '$chart_type';set xlabel 'threads';plot "
# plot_cmd="set term png size 800,600;set output '/tmp/$chart_type.png';set title '$chart_type $unit';set grid;set key $key_pos;plot "
if [ $# -eq 0 ];then
#對分析結果中所有場景進行畫圖
for scenario in `mysql -u$m_user -p$m_passwd -h$m_host -P$m_port -s -e "select distinct(scenario) from $m_db.$m_table" 2>/dev/null`;do
sb_analyse | awk -v scenario=$scenario '$1 == scenario {print}' > /tmp/"$scenario.dat"
plot_cmd=${plot_cmd}"'/tmp/"$scenario.dat"' using $col_num:xtic(4) title '$scenario' with linespoints lw 2,"
done
plot_cmd=$(echo $plot_cmd | sed 's/,$//g')
echo $plot_cmd | gnuplot
else
#只繪製指定的場景
for scenario in $*;do
sb_analyse | awk -v scenario=$scenario '$1 == scenario {print}' > /tmp/"$scenario.dat"
plot_cmd=${plot_cmd}"'/tmp/"$scenario.dat"' using $col_num:xtic(4) title '$scenario' with linespoints lw 2,"
done
plot_cmd=$(echo $plot_cmd | sed 's/,$//g')
echo "$plot_cmd" | gnuplot
fi
done
}
#腳本使用說明/參數判斷
if [ $# -eq 1 ] && [ $1 == "-h" -o $1 == "--help" ];then
echo -e "\nUsage: $0 test (test_scenario) (test_type) (mysql_host) (mysql_port) (mysql_user) (mysql_password)\n $0 analyse\n $0 chart [scenario]...\n"
echo ----------
echo -e "測試: 子命令test"
echo -e " test_scenario: 自定義的測試場景名"
echo -e " test_type: read-only 或 read-write, 表示測試模式"
echo -e " 其餘4參數表示待測試MySQL連接相關信息,密碼若包含特殊字元,將其置於單引號內"
echo -e "----------"
echo -e "分析: 子命令analyse"
echo -e "----------"
echo -e "畫圖: 子命令chart"
echo -e " 會在/tmp/下生成request_per_second.png transactions_per_second.png 95_pct_time.png server_load.png request_read.png request_write.png 六張圖"
echo -e " chart (對分析結果中的所有測試場景畫圖)"
echo -e " chart scenario ... (對指定的測試場景畫圖，場景名依據先前自定義的名稱)\n"
exit -1
elif [ "$1" == "test" -a $# -eq 7 ];then
sb_test $1 $2 $3 $4 $5 $6 $7
elif [ "$1" == "analyse" -a $# -eq 1 ];then
sb_analyse
elif [ "$1" == "chart" ];then
#chart函數可不接參數,也可接任意個'測試場景'作為參數
arg=($*)
arg_len=${#arg[@]}
sb_chart ${arg[@]:1:$arg_len-1}
else
echo -e "\nUsage: $0 test (test_scenario) (test_type) (mysql_host) (mysql_port) (mysql_user) (mysql_password)\n $0 analyse\n $0 chart [scenario]...\n"
fi
2.2、執行壓測
- 依據測試環境修改腳本的mysql參數和sysbench參數，然後執行
- 註意：腳本會把執行結果寫入到dbtest庫下麵的sysbench_test表裡
Usage: ./sysbench_t1.sh test (test_scenario) (test_type) (mysql_host) (mysql_port) (mysql_user) (mysql_password)
./sysbench_t1.sh analyse
./sysbench_t1.sh chart [scenario]...
----------
測試: 子命令test
test_scenario: 自定義的測試場景名
test_type: read-only 或 read-write, 表示測試模式
其餘4參數表示待測試MySQL連接相關信息,密碼若包含特殊字元,將其置於單引號內
----------
分析: 子命令analyse
----------
畫圖: 子命令chart
會在/tmp/下生成request_per_second.png transactions_per_second.png 95_pct_time.png 三張圖
chart (對分析結果中的所有測試場景畫圖)
chart scenario ... (對指定的測試場景畫圖，場景名依據先前自定義的名稱)
[root@localhost-m(252) /r2/soft]# ll /usr/local/sysbench/share/sysbench/
total 64
-rwxr-xr-x 1 root root 1452 11月 2 17:14 bulk_insert.lua
-rw-r--r-- 1 root root 13918 11月 2 17:14 oltp_common.lua
-rwxr-xr-x 1 root root 1290 11月 2 17:14 oltp_delete.lua
-rwxr-xr-x 1 root root 2415 11月 2 17:14 oltp_insert.lua
-rwxr-xr-x 1 root root 1265 11月 2 17:14 oltp_point_select.lua
-rwxr-xr-x 1 root root 1649 11月 2 17:14 oltp_read_only.lua
-rwxr-xr-x 1 root root 1824 11月 2 17:14 oltp_read_write.lua
-rwxr-xr-x 1 root root 1118 11月 2 17:14 oltp_update_index.lua
-rwxr-xr-x 1 root root 1127 11月 2 17:14 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1440 11月 2 17:14 oltp_write_only.lua
-rwxr-xr-x 1 root root 1919 11月 2 17:14 select_random_points.lua
-rwxr-xr-x 1 root root 2118 11月 2 17:14 select_random_ranges.lua
drwxr-xr-x 4 root root 4096 11月 2 17:14 tests
[root@localhost-m(252) /r2/soft]# nohup ./mysql_oltp_sysbench.sh test scenario_oltp_update_non_index read-write localhost 3306 gcdb 'iforgot' &
[root@localhost-m(252) /r2/soft]# tail -f /tmp/mysql_oltp.log
[ 10s ] thds: 192 tps: 255.30 qps: 255.30 (r/w/o: 0.00/255.30/0.00) lat (ms,95%): 2728.81 err/s: 0.00 reconn/s: 0.00
三、執行結果
3.1 sysbench 執行結果
SQL statistics:
queries performed:
read: 0
write: 367252 --寫總數
other: 0
total: 367252
transactions: 367252 (203.97 per sec.) -- 總事務數(每秒事務數)
queries: 367252 (203.97 per sec.) -- 讀寫務數(每秒事務數)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 203.9661 --每秒事務數
time elapsed: 1800.5544s --總耗時
total number of events: 367252 --總事務數
Latency (ms):
min: 0.12 --最小耗時
avg: 470.60 --平均耗時
max: 48410.54 --最大耗時
95th percentile: 846.57 --超過95%平均耗時
sum: 172829974.82 --總耗時
Threads fairness:
events (avg/stddev): 3825.5417/76.81 --事件(平均值/偏差)
execution time (avg/stddev): 1800.3122/0.10 --執行時間(平均值/偏差)
3.2 查看腳本執行記錄
- 查詢dbtest.sysbench_test表
mysql> SELECT scenario,sb_threads,server_load,request_read,request_write,transactions_per_second,request_per_second,95_pct_time FROM dbtest.sysbench_test;
+--------------------------------+------------+-------------+--------------+---------------+-------------------------+--------------------+-------------+
| scenario | sb_threads | server_load | request_read | request_write | transactions_per_second | request_per_second | 95_pct_time |
+--------------------------------+------------+-------------+--------------+---------------+-------------------------+--------------------+-------------+
| scenario_oltp_update_index | 24 | 13.35 | 0 | 745072 | 413.84 | 413.84 | 277.21 |
| scenario_oltp_update_index | 32 | 7.81 | 0 | 249685 | 138.69 | 138.69 | 493.24 |
| scenario_oltp_update_index | 64 | 8.20 | 0 | 333937 | 185.50 | 185.50 | 733.00 |
| scenario_oltp_update_index | 96 | 20.03 | 0 | 343386 | 190.69 | 190.69 | 1032.01 |
| scenario_oltp_update_index | 128 | 6.72 | 0 | 326057 | 177.64 | 177.64 | 1479.41 |
| scenario_oltp_update_index | 160 | 7.23 | 0 | 342488 | 187.00 | 187.00 | 1869.60 |
| scenario_oltp_update_index | 192 | 9.93 | 0 | 331669 | 184.15 | 184.15 | 2493.86 |
| scenario_oltp_update_index | 256 | 6.63 | 0 | 322515 | 177.02 | 177.02 | 2680.11 |
| scenario_oltp_update_non_index | 24 | 24.78 | 0 | 1003021 | 557.19 | 557.19 | 189.93 |
| scenario_oltp_update_non_index | 32 | 17.21 | 0 | 547258 | 303.78 | 303.78 | 601.29 |
| scenario_oltp_update_non_index | 64 | 27.47 | 0 | 454084 | 251.93 | 251.93 | 1618.78 |
| scenario_oltp_update_non_index | 96 | 18.86 | 0 | 452345 | 250.91 | 250.91 | 2120.76 |
| scenario_oltp_update_non_index | 128 | 16.62 | 0 | 437129 | 242.45 | 242.45 | 2778.39 |
| scenario_oltp_update_non_index | 160 | 22.01 | 0 | 421289 | 233.70 | 233.70 | 3511.19 |
| scenario_oltp_update_non_index | 192 | 16.81 | 0 | 398962 | 221.19 | 221.19 | 4358.09 |
| scenario_oltp_update_non_index | 256 | 18.24 | 0 | 407225 | 225.80 | 225.80 | 5813.24 |
| scenario_oltp_update_non_index | 24 | 16.45 | 0 | 406238 | 225.59 | 225.59 | 746.32 |
| scenario_oltp_update_non_index | 32 | 16.82 | 0 | 419489 | 232.93 | 232.93 | 1032.01 |
| scenario_oltp_update_non_index | 64 | 23.02 | 0 | 426404 | 236.83 | 236.83 | 1836.24 |
| scenario_oltp_update_non_index | 96 | 20.04 | 0 | 457630 | 253.94 | 253.94 | 2045.74 |
| scenario_oltp_update_non_index | 128 | 24.04 | 0 | 460669 | 255.80 | 255.80 | 2632.28 |
| scenario_oltp_update_non_index | 160 | 21.59 | 0 | 454371 | 252.04 | 252.04 | 3267.19 |
| scenario_oltp_update_non_index | 192 | 18.05 | 0 | 450096 | 249.65 | 249.65 | 3911.79 |
| scenario_oltp_update_non_index | 256 | 19.35 | 0 | 437986 | 243.02 | 243.02 | 5507.54 |
| scenario_oltp_update_non_index | 24 | 15.51 | 0 | 427093 | 237.23 | 237.23 | 733.00 |
| scenario_oltp_update_non_index | 32 | 17.80 | 0 | 433449 | 240.72 | 240.72 | 977.74 |
| scenario_oltp_update_non_index | 64 | 17.65 | 0 | 432506 | 240.08 | 240.08 | 1803.47 |
| scenario_oltp_update_non_index | 96 | 18.55 | 0 | 426209 | 236.44 | 236.44 | 2279.14 |
| scenario_oltp_update_non_index | 128 | 17.06 | 0 | 415146 | 230.27 | 230.27 | 2880.27 |
| scenario_oltp_update_non_index | 160 | 19.04 | 0 | 416016 | 230.63 | 230.63 | 3511.19 |
| scenario_oltp_update_non_index | 192 | 19.73 | 0 | 411943 | 228.56 | 228.56 | 4280.32 |
| scenario_oltp_update_non_index | 256 | 17.48 | 0 | 418163 | 231.88 | 231.88 | 5709.50 |
| scenario_oltp_update_index | 24 | 11.59 | 0 | 490759 | 272.63 | 272.63 | 411.96 |
| scenario_oltp_update_index | 32 | 21.27 | 0 | 560223 | 311.19 | 311.19 | 458.96 |
| scenario_oltp_update_index | 64 | 29.03 | 0 | 357102 | 198.36 | 198.36 | 601.29 |
| scenario_oltp_update_index | 96 | 19.62 | 0 | 403699 | 222.30 | 222.30 | 846.57 |
| scenario_oltp_update_index | 128 | 15.11 | 0 | 380223 | 211.14 | 211.14 | 1170.65 |
| scenario_oltp_update_index | 160 | 16.88 | 0 | 391103 | 216.96 | 216.96 | 1506.29 |
| scenario_oltp_update_index | 192 | 8.91 | 0 | 378316 | 209.84 | 209.84 | 2045.74 |
| scenario_oltp_update_index | 256 | 8.18 | 0 | 377455 | 206.79 | 206.79 | 2932.60 |
| scenario_oltp_update_index | 24 | 8.76 | 0 | 375782 | 208.75 | 208.75 | 257.95 |
| scenario_oltp_update_index | 32 | 5.58 | 0 | 356393 | 197.99 | 197.99 | 356.70 |
| scenario_oltp_update_index | 64 | 5.52 | 0 | 377180 | 205.27 | 205.27 | 559.50 |
| scenario_oltp_update_index | 96 | 8.48 | 0 | 367252 | 203.97 | 203.97 | 846.57 |
| scenario_oltp_update_index | 128 | 11.43 | 0 | 374881 | 205.08 | 205.08 | 1149.76 |
| scenario_oltp_update_index | 160 | 9.46 | 0 | 366998 | 200.27 | 200.27 | 1618.78 |
| scenario_oltp_update_index | 192 | 5.50 | 0 | 351154 | 194.18 | 194.18 | 2159.29 |
| scenario_oltp_update_index | 256 | 11.49 | 0 | 367447 | 202.42 | 202.42 | 2449.36 |
+--------------------------------+------------+-------------+--------------+---------------+-------------------------+--------------------+-------------+
3.3 最後一次壓測MySQL各種狀態
- CPU MEM
- IO
- 線程數和update
- Innodb buffer
3.4 統計三次迴圈壓測結果並畫圖
3.5 測試結果
- 當線程併發到64個時TPS在203左右，事務95%的平均耗時也在不斷攀升，線上程數達併發到96個時候，耗時在1000ms即1s,系統不可以用
四、關於測試後的分析和建議
4.1、從上面io和cpu圖可能執行併發壓測是CPU負載上升並未達到瓶頸，但是IO使用率100%並出現io延遲，觀察看到iops一直在200之下，是否受配置參數innodb_io_capacity=200限制？
4.2、進行sysbench fileio測試
[root@localhost-m(252) /r2/soft/sysbench]# sysbench fileio \
> --time=1800 \ --30分鐘
> --events=100000000 \ --最大隨機數請求是100000000次
> --threads=24 \ --線程數24
> --file-num=16 \ --文件數16個
> --file-total-size=2G \ --文件總大小2G
> --file-test-mode=rndrw \ --文件測試模式，包含seqwr（順序寫）、seqrewr（順序讀寫）、seqrd（順序讀）、rndrd（隨機讀）、rndwr（隨機寫）和rndrw（隨機讀寫）
> --file-extra-flags=direct \ --以哪種模式打開O_SYNC，O_DSYNC，O_DIRECT
> --file-fsync-freq=0 \ --執行fsync函數的頻率
> --file-block-size=16384 \ --塊的大小，mysql預設16K
> run
sysbench 1.1.0-0167e45 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 24
Initializing random number generator from current time
Extra file open flags: 3
16 files, 128MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 100000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...
Threads started!
Throughput:
read: IOPS=0.00 0.00 MiB/s (0.00 MB/s)
write: IOPS=529.69 8.28 MiB/s (8.68 MB/s)
fsync: IOPS=0.00
Latency (ms):
min: 0.12
avg: 45.30
max: 1212.98
95th percentile: 287.38
sum: 43198787.07
- 分別進行rndwr（隨機寫）和rndrw（隨機讀寫）各30分鐘，如上圖R2目錄（RAID1）IOPS:
- rndwr（隨機寫）平均600IOPS
- rndrw（隨機讀寫）平均寫306iops，平均讀460iops 4.3、建議用/r2目錄所在磁碟(RAID1 SAS 7.2K 3.5 12G/s 6T)， 進行磁碟升級換成(RAID10)，目前瓶頸在io上面，記憶體和cpu都滿是需求，把日誌分開保存有效利用io
