sysbench简单测试主从复制

最新推荐文章于 2023-02-22 21:16:14 发布

硅藻泥先森

最新推荐文章于 2023-02-22 21:16:14 发布

阅读量653

点赞数

分类专栏： mysql 数据库高可用

本文链接：https://blog.csdn.net/jilindaxue/article/details/81714003

版权

数据库同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

高可用

1 篇文章 0 订阅

订阅专栏

一、主机环境

两台机配置相同：

[root@192 ~]# pt-summary
# Percona Toolkit System Summary Report ######################
        Date | 2018-08-15 16:47:37 UTC (local TZ: EDT -0400)
    Hostname | 192.168.237.128
      Uptime |  4:41,  1 user,  load average: 0.00, 0.01, 0.05
      System | VMware, Inc.; VMware Virtual Platform; vNone (Other)
 Service Tag | VMware-56 4d 17 8c b3 93 0a 4b-22 86 08 db fd 63 8d 28
    Platform | Linux
     Release | Red Hat Enterprise Linux Server release 7.5 (Maipo)
      Kernel | 3.10.0-862.el7.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.17
     SELinux | Disabled
 Virtualized | VMWare
# Processor ##################################################
  Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no
      Speeds | 1x3408.002
      Models | 1xIntel(R) Core(TM) i7-6700 CPU @ 3.40GHz
      Caches | 1x8192 KB
# Memory #####################################################
       Total | 974.6M
        Free | 508.8M
        Used | physical = 206.3M, swap allocated = 2.0G, swap used = 0.0, virtual = 206.3M
     Buffers | 259.4M
      Caches | 598.7M
       Dirty | 416 kB
     UsedRSS | 213.2M
  Swappiness | 30
 DirtyPolicy | 30, 10
 DirtyStatus | 0, 0

二、主从库配置

数据库环境：

[root@192 ~]# pt-mysql-summary 
# Percona Toolkit MySQL Summary Report #######################
              System time | 2018-08-15 16:50:03 UTC (local TZ: EDT -0400)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /var/lib/mysql             0    0   /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/libexec/mysqld
              Has symbols | No
# Report On Port 3306 ########################################
                     User | @
                     Time | 2018-08-15 12:50:03 (EDT)
                 Hostname | 192.168.237.128
                  Version | 5.5.56-MariaDB MariaDB Server
                 Built On | Linux x86_64
                  Started | 2018-08-15 08:06 (up 0+04:43:15)
                Databases | 5
                  Datadir | /var/lib/mysql/

主库：

[root@192 sysbench]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake
#skip-grant-tables
log_bin=mysql-bin
server_id=129

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

从库：

[root@192 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake
#skip-grant-tables
log_bin=mysql-bin
server_id=130
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

三、测试脚本

主机执行：

mysqladmin create sbtest
sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password= \
 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 --oltp-table-size=10000000 --rand-init=on prepare
 
sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password= \
--test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 \
--oltp-table-size=10000000 --num-threads=100 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=120 \
 --max-requests=0 --percentile=99 run >> sysbench_oltpX_8_20180816.log

四、输出

主机监控(mysqladmin ext -i1 | awk '/Queries/{q=$4-qp;qp=$4}/Threads_connected/{tc=$4}/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}')输出：

 2569    76    19
11826   102    11
11203   102    88
11817   102    91
11639   102    84
12545   102    11
12581   102    91
12848   102    46
12281   102    82
12769   102     9
11993   102    52
12807   102    15
12449   102    12
11901   102    13
12680   102    24
11498   102    27
12793   102    25
12612   102    18
12141   102   102
12471   102    24
10620   102     5
13148   102    27
12679   102    10
12609   102    71
12028   102     4
12912   102    14
12561   102    15
12803   102    13
11966   102    12
12493   102    74
11592   102    26
12194   102    25
12836   102    26
12345   102     8
12597   102    29
10947   102    14
12886   102    20
12934   102    17
10743   102    20
11795   102    17
12655   102    29
12825   102     4
12572   102    27
12587   102    25
12984   102    60
12945   102    17
11768   102    96
10871   102    50
12032   102    14
 9072   102    60
11684   102    17
12717   102    30
10613   102     8
11733   102    30
12063   102    90
12941   102    32
12245   102    42
12221   102    66
12964   102     5
12556   102    25
11134   102    49
12215   102    13
11688   102     3
12414   102    14
12278   102    21
12800   102    12
11524   102    14
13264   102   102
12592   102     3
13047   102    55
11420   102    63
11926   102     7
12644   102    23
12954   102    44
12278   102    97
13449   102    12
12873   102    43
13150   102    86
13381   102    23
12662   102    22
12607   102    22
12732   102    15
12570   102    11
12568   102    20
13009   102    26
13234   102    28
12832   102     5
12640   102    39
11975   102    44
11693   102    22
12417   102     3
13128   102    22
13027   102    28
13157   102    18
12959   102    22
12825   102    79
13117   102    85
13012   102     3
13304   102     4
13121   102    60
12978   102    15
12992   102    90
11598   102    24
12915   102    12
11535   102    22
12652   102    36
12051   102    57
12661   102    10
12588   102   102
12562   102   102
12803   102    24
12404   102     2
13089   102    38
13101   102    59
11132   102    49
12903   102    10
13068   102    10
13077   102    25
12916   102    29
12722   102   102
 5254     2     2

备机相同脚本输出：

    6     1     1
  216     1     1
 1331     1     1
 1651     1     1
 2291     1     1
 2356     1     1
 2701     1     1
 2611     1     1
 2521     1     1
 2946     1     1
 2881     1     1
 3401     1     1
 3101     1     1
 3016     1     1
 3001     1     1
 2851     1     1
 3316     1     1
 3031     1     1
 3086     1     1
 2526     1     1
 3166     1     1
 3391     1     1
 3211     1     1
 3146     1     1
 3001     1     1
 3241     1     1
 3146     1     1
 3181     1     1
 2951     1     1
 3261     1     1
 2801     1     1
 3046     1     1
 3131     1     1
 3091     1     1
 3296     1     1
 2726     1     1
 3186     1     1
 3266     1     1
 2576     1     1
 3021     1     1
 3176     1     1
 3096     1     1
 3206     1     1
 3196     1     1
 3231     1     1
 3156     1     1
 3141     1     1
 2651     1     1
 2971     1     1
 2096     1     1
 3046     1     1
 3211     1     1
 2591     1     1
 3021     1     1
 2926     1     1
 3216     1     1
 3266     1     1
 2941     1     1
 3131     1     1
 3201     1     1
 2466     1     1
 3311     1     1
 3036     1     1
 3001     1     1
 3171     1     1
 3176     1     1
 2891     1     1
 3246     1     1
 2936     1     1
 3316     1     1
 2771     1     1
 3101     1     1
 3131     1     1
 3321     1     1
 2906     1     1
 3486     1     1
 3191     1     1
 3181     1     1
 3431     1     1
 2811     1     1
 3581     1     1
 3171     1     1
 3171     1     1
 3111     1     1
 3186     1     1
 3426     1     1
 3316     1     1
 2976     1     1
 3031     1     1
 3021     1     1
 2928     1     1
 3379     1     1
 3201     1     1
 3336     1     1
 2941     1     1
 3521     1     1
 3116     1     1
 3401     1     1
 3306     1     1
 3361     1     1
 2841     1     1
 3501     1     1
 3096     1     1
 3141     1     1
 2936     1     1
 3086     1     1
 3091     1     1
 3086     1     1
 3191     1     1
 3101     1     1
 3201     1     1
 2996     1     1
 3141     1     1
 3426     1     1
 2856     1     1
 3186     1     1
 3206     1     1
 2536     1     1
 3971     1     1
 3221     1     1
 1726     1     1

五、测试报告

主机日志：cat sysbench_oltpX_8_20180816.log

Threads started!

[ 10s ] thds: 100 tps: 406.50 qps: 11696.01 (r/w/o: 8472.68/428.79/2794.54) lat (ms,99%): 1771.29 err/s: 190.81 reconn/s: 0.00
[ 20s ] thds: 100 tps: 617.09 qps: 12358.18 (r/w/o: 8652.02/633.50/3072.67) lat (ms,99%): 390.30 err/s: 1.10 reconn/s: 0.00
[ 30s ] thds: 100 tps: 615.67 qps: 12311.33 (r/w/o: 8617.70/635.97/3057.66) lat (ms,99%): 344.08 err/s: 0.30 reconn/s: 0.00
[ 40s ] thds: 100 tps: 605.53 qps: 12116.38 (r/w/o: 8482.41/621.83/3012.14) lat (ms,99%): 325.98 err/s: 0.40 reconn/s: 0.00
[ 50s ] thds: 100 tps: 601.30 qps: 12016.50 (r/w/o: 8409.77/622.71/2984.02) lat (ms,99%): 404.61 err/s: 0.10 reconn/s: 0.00
[ 60s ] thds: 100 tps: 595.27 qps: 11976.49 (r/w/o: 8387.27/616.17/2973.05) lat (ms,99%): 404.61 err/s: 0.40 reconn/s: 0.00
[ 70s ] thds: 100 tps: 617.52 qps: 12349.92 (r/w/o: 8638.39/642.42/3069.10) lat (ms,99%): 325.98 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 100 tps: 640.29 qps: 12707.61 (r/w/o: 8888.67/656.49/3162.45) lat (ms,99%): 404.61 err/s: 0.40 reconn/s: 0.00
[ 90s ] thds: 100 tps: 626.38 qps: 12557.64 (r/w/o: 8792.68/652.28/3112.69) lat (ms,99%): 383.33 err/s: 0.20 reconn/s: 0.00
[ 100s ] thds: 100 tps: 647.50 qps: 12976.51 (r/w/o: 9093.24/670.20/3213.08) lat (ms,99%): 530.08 err/s: 0.10 reconn/s: 0.00
[ 110s ] thds: 100 tps: 616.24 qps: 12397.55 (r/w/o: 8669.49/648.04/3080.01) lat (ms,99%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 639.19 qps: 12721.81 (r/w/o: 8911.17/657.99/3152.65) lat (ms,99%): 397.39 err/s: 0.10 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1040550
        write:                           74939
        other:                           367129
        total:                           1482618
    transactions:                        72384  (602.49 per sec.)
    queries:                             1482618 (12340.60 per sec.)
    ignored errors:                      1941   (16.16 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1403s
    total number of events:              72384

Latency (ms):
         min:                                    1.29
         avg:                                  165.87
         max:                                 6444.46
         99th percentile:                      475.79
         sum:                             12006265.24

Threads fairness:
    events (avg/stddev):           723.8400/13.82
    execution time (avg/stddev):   120.0627/0.04

硅藻泥先森

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sysbench简单测试主从复制

一、主机环境两台机配置相同：[root@192 ~]# pt-summary# Percona Toolkit System Summary Report ###################### Date | 2018-08-15 16:47:37 UTC (local TZ: EDT -0400) Hostname | 192.168.237.128 ...
复制链接

扫一扫