基准测试绘图

mingjie

发布于 2022-05-12 09:50:38

44700

代码可运行

文章被收录于专栏：Postgresql源码分析Postgresql源码分析

运行总次数：0

代码可运行

OLTP基准测试

mysql参数（HDD硬盘64核CPU）

[mysqld]
innodb_buffer_pool_size = 128M
basedir = xxx
datadir = xxx
port = 5400
server_id = 06700000
socket = xxx
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
log_bin = mysql-bin

准备数据（默认使用数据库sbtest）

sysbench oltp_common --threads=64 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --tables=10 --table_size=1000000 prepare

64线程10分钟

sysbench oltp_read_write --threads=64 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=10 --table_size=1000000 --time=600 --report-interval=1 run > ./64.log

SQL statistics:
    queries performed:
        read:                            2004898
        write:                           572828
        other:                           286414
        total:                           2864140
    transactions:                        143207 (238.55 per sec.)
    queries:                             2864140 (4770.94 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.3291s
    total number of events:              143207

Latency (ms):
         min:                                    4.49
         avg:                                  268.20
         max:                                 3120.51
         95th percentile:                      846.57
         sum:                             38407832.31

Threads fairness:
    events (avg/stddev):           2237.6094/25.40
    execution time (avg/stddev):   600.1224/0.03

128线程10分钟

sysbench oltp_read_write --threads=128 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=10 --table_size=1000000 --time=600 --report-interval=1 run > ./128.log
SQL statistics:
    queries performed:
        read:                            2998604
        write:                           856744
        other:                           428372
        total:                           4283720
    transactions:                        214186 (356.69 per sec.)
    queries:                             4283720 (7133.88 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.4736s
    total number of events:              214186

Latency (ms):
         min:                                    6.18
         avg:                                  358.83
         max:                                 3474.23
         95th percentile:                     1149.76
         sum:                             76856235.74

Threads fairness:
    events (avg/stddev):           1673.3281/20.67
    execution time (avg/stddev):   600.4393/0.05

256线程10分钟

sysbench oltp_read_write --threads=256 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=10 --table_size=1000000 --time=600 --report-interval=1 run > ./256.log
SQL statistics:
    queries performed:
        read:                            3312344
        write:                           946384
        other:                           473192
        total:                           4731920
    transactions:                        236596 (393.76 per sec.)
    queries:                             4731920 (7875.11 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.8682s

...skipping one line

Latency (ms):
         min:                                    5.96
         avg:                                  649.49
         max:                                 6085.77
         95th percentile:                     1903.57
         sum:                            153665561.59

Threads fairness:
    events (avg/stddev):           924.2031/13.66
    execution time (avg/stddev):   600.2561/0.22

512线程10分钟

sysbench oltp_read_write --threads=512 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=10 --table_size=1000000 --time=600 --report-interval=1 run > ./512.log
SQL statistics:
    queries performed:
        read:                            2910740
        write:                           831640
        other:                           415820
        total:                           4158200
    transactions:                        207910 (345.89 per sec.)
    queries:                             4158200 (6917.72 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          601.0922s
    total number of events:              207910

Latency (ms):
         min:                                    8.11
         avg:                                 1478.86
         max:                                20588.49
         95th percentile:                     3326.55
         sum:                            307470559.14

Threads fairness:
    events (avg/stddev):           406.0742/27.49
    execution time (avg/stddev):   600.5284/0.28

cleanup

sysbench oltp_common --threads=64 --events=0 --mysql-socket=xxx --mysql-user=xxx --mysql-password=xxx --tables=10 --table_size=1000000 cleanup

4 Gunplot输出结果图

资料很多而杂这个算是比较完整的实例资料

安装

https://cfhcable.dl.sourceforge.net/project/gnuplot/gnuplot/5.2.6/gnuplot-5.2.6.tar.gz
./configure --prefix=/home/mingjie.gmj/bin/gunplot/
make -sj64
make install

技巧

set datafile separator <string> 指定自己的分隔符
set datafile commentschar 指定除了 # 之外的注释字符

实例1

set grid
set xlabel "collection time"
set xrange[0:600]
set ylabel "qps"
set yrange[0:35000]
plot "64.log" using 2:9 with line linecolor 1 linewidth 1 title "64 threads",\
"128.log" using 2:9 with line linecolor 2 linewidth 1 title "128 threads",\
"256.log" using 2:9 with line linecolor 5 linewidth 1 title "256 threads",\
"512.log" using 2:9 with linespoints linecolor 4 linewidth 1 pointtype 7 pointsize 1 title "512 threads"

set grid
set xlabel "collection time"
set xrange[0:600]
set ylabel "qps"
set yrange[0:35000]
plot "64.log" using 2:9 w l lc 1 lw 1 title "64 threads",\
"128.log" using 2:9 w l lc 2 lw 1 title "128 threads",\
"256.log" using 2:9 w l lc 5 lw 1 title "256 threads",\
"512.log" using 2:9 w lp lc 7 lw 1 pt 1 ps 1 title "512 threads"

实例2曲线平滑

拟合

https://blog.csdn.net/liyuanbhu/article/details/7574193?utm_source=blogkpcl12

set grid
set xlabel "collection time"
set xrange[0:600]
set ylabel "qps"
set yrange[0:35000]
plot "64.log" using 2:9 w l lc 1 lw 1 title "64 threads" smooth acsplines,\
"128.log" using 2:9 w l lc 2 lw 1 title "128 threads" smooth acsplines,\
"256.log" using 2:9 w l lc 5 lw 1 title "256 threads" smooth acsplines,\
"512.log" using 2:9 w lp lc 7 lw 1 pt 1 ps 1 title "512 threads" smooth acsplines

set grid
set xlabel "collection time"
set xrange[0:600]
set ylabel "qps"
set yrange[0:35000]
plot "64.log" using 2:9 w l lc 1 lw 1 title "64 threads" smooth sbezier,\
"128.log" using 2:9 w l lc 2 lw 1 title "128 threads" smooth sbezier,\
"256.log" using 2:9 w l lc 5 lw 1 title "256 threads" smooth sbezier,\
"512.log" using 2:9 w lp lc 7 lw 1 pt 1 ps 1 title "512 threads" smooth sbezier

set grid
set xlabel "collection time"
set xrange[0:600]
set ylabel "qps"
plot "pgsql_p128_0.log" using 2:9 w l lc 1 lw 1 title "[0] threads" smooth sbezier,\
"pgsql_p128_1.log" using 2:9 w l lc 2 lw 1 title "[1] threads" smooth sbezier

实例3

读写qps

Cc	Select	Update	Delete	Insert
0	16.6w	3500	1700	1700
500	16.5w	4000	2000	2000
100	14.8w	6800	3400	3400
50	11.7w	7100	3600	3600
10	8.7w	5400	2700	2700

500 1650 4000 2000 2000
100 148000 6800 3400 3400
50 117000 7100 3600 3600
10 87000 5400 2700 2700

set xrange[-10:700]
set xtics(10, 50, 100, 500)
plot "a" using 1:2 title "SELECT" with linespoints linewidth 2, "a" using 1:3 title "UPDATE" with linespoints linewidth 2,"a" using 1:4 title "DELETE" with linespoints linewidth 2,"a" using 1:5 title "INSERT" with linespoints linewidth 2

ps. MYSQL测试数据收集脚本

mytest.sh

#!/bin/sh

INTERNAL=5
PREFIX=$INTERNAL-sec-status
RUNFILE=/tmp/bm_running
MYPREFIX='mysql -S pathto/mysql5400.sock -uroot -e'
$MYPREFIX 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
  file=$(date +%F_%I)
  sleep $INTERNAL
  ts="$(date +"TS %s.%N %F %T")"
  loadavg="$(uptime)"
  echo "$ts $loadavg"            >> $PREFIX-${file}-status
  $MYPREFIX 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
  echo "$ts $loadavg"                 >> $PREFIX-${file}-processlist
  $MYPREFIX 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
  echo "$ts $loadavg"                     >> $PREFIX-${file}-innodbstatus
  $MYPREFIX 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
  echo $ts
done
echo Exiting because $RUNFILE does not exist.

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-04-03，如有侵权请联系 cloudcommunity@tencent.com 删除

sql