mysql-5.7.35 aarch64安装
- what: mysql 5.7.35 aarch64安装
- where: centos:7 aarch64
- when: 2021.11.08
- who: JRT
- why: 知识库初始化
- how:
环境
创建容器
[root@ecs-hk-arm jrt]$ docker run -itd --privileged=true --name jrt1 -p 8878:3306 centos:7
625c2ca790e5d2000c053b5ad3f6fcc4e7c7475112e24fbd1343efcfa15e6280
查环境信息
#查架构
[root@625c2ca790e5 sbin]$ uname -a
Linux 625c2ca790e5 4.18.0-193.28.1.el7.aarch64 $1 SMP Wed Oct 21 16:25:35 UTC 2020 aarch64 aarch64 aarch64 GNU/Linux
#查系统
[root@625c2ca790e5 sbin]$ cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (AltArch)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (AltArch)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7:server"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
[root@625c2ca790e5 sbin]$
安装mysql
将编译好的rpm包拷贝至容器(编译过程见mysql5.7.35编译文档)
[root@ecs-hk-arm jrt]$ docker cp ./mysql5.7.35/ jrt1:/opt
[root@ecs-hk-arm jrt]$ docker exec -it jrt1 bash
[root@625c2ca790e5 /]$ cd /opt
[root@625c2ca790e5 opt]$ ls
mysql5.7.35
[root@625c2ca790e5 opt]$ cd mysql5.7.35/
[root@625c2ca790e5 mysql5.7.35]$ ls
mysql-community-client-5.7.35-1.el7.aarch64.rpm
mysql-community-common-5.7.35-1.el7.aarch64.rpm
mysql-community-devel-5.7.35-1.el7.aarch64.rpm
mysql-community-embedded-5.7.35-1.el7.aarch64.rpm
mysql-community-embedded-compat-5.7.35-1.el7.aarch64.rpm
mysql-community-embedded-devel-5.7.35-1.el7.aarch64.rpm
mysql-community-libs-5.7.35-1.el7.aarch64.rpm
mysql-community-libs-compat-5.7.35-1.el7.aarch64.rpm
mysql-community-server-5.7.35-1.el7.aarch64.rpm
mysql-community-test-5.7.35-1.el7.aarch64.rpm
安装mysql
[root@625c2ca790e5 mysql5.7.35]$ yum install -y *.rpm
Installed:
mysql-community-client.aarch64 0:5.7.35-1.el7
mysql-community-common.aarch64 0:5.7.35-1.el7
mysql-community-devel.aarch64 0:5.7.35-1.el7
mysql-community-embedded.aarch64 0:5.7.35-1.el7
mysql-community-embedded-compat.aarch64 0:5.7.35-1.el7
mysql-community-embedded-devel.aarch64 0:5.7.35-1.el7
mysql-community-libs.aarch64 0:5.7.35-1.el7
mysql-community-libs-compat.aarch64 0:5.7.35-1.el7
mysql-community-server.aarch64 0:5.7.35-1.el7
mysql-community-test.aarch64 0:5.7.35-1.el7
Dependency Installed:
groff-base.aarch64 0:1.22.2-8.el7 keyutils-libs-devel.aarch64 0:1.5.8-3.el7
krb5-devel.aarch64 0:1.15.1-50.el7 libaio.aarch64 0:0.3.109-13.el7
libcom_err-devel.aarch64 0:1.42.9-19.el7 libkadm5.aarch64 0:1.15.1-50.el7
libselinux-devel.aarch64 0:2.5-15.el7 libsepol-devel.aarch64 0:2.5-10.el7
libverto-devel.aarch64 0:0.2.5-4.el7 net-tools.aarch64 0:2.0-0.25.20131004git.el7
numactl-libs.aarch64 0:2.0.12-5.el7 openssl-devel.aarch64 1:1.0.2k-22.el7_9
pcre-devel.aarch64 0:8.32-17.el7 perl.aarch64 4:5.16.3-299.el7_9
perl-Carp.noarch 0:1.26-244.el7 perl-Data-Dumper.aarch64 0:2.145-3.el7
perl-Encode.aarch64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7
perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7
perl-Filter.aarch64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-3.el7
perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-JSON.noarch 0:2.59-2.el7
perl-PathTools.aarch64 0:3.40-5.el7 perl-Pod-Escapes.noarch 1:1.04-299.el7_9
perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7
perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.aarch64 0:1.27-248.el7
perl-Socket.aarch64 0:2.010-5.el7 perl-Storable.aarch64 0:2.45-3.el7
perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.aarch64 4:1.9725-3.el7
perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7
perl-libs.aarch64 4:5.16.3-299.el7_9 perl-macros.aarch64 4:5.16.3-299.el7_9
perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7
perl-threads.aarch64 0:1.87-4.el7 perl-threads-shared.aarch64 0:1.43-6.el7
zlib-devel.aarch64 0:1.2.7-19.el7_9
Dependency Updated:
openssl-libs.aarch64 1:1.0.2k-22.el7_9 zlib.aarch64 0:1.2.7-19.el7_9
Complete!
启动mysql
[root@625c2ca790e5 mysql5.7.35]$ cd /usr/sbin/
[root@625c2ca790e5 sbin]$ ./mysqld --user=mysql --initialize-insecure
[root@625c2ca790e5 sbin]$ cat /var/log/mysqld.log
2021-11-08T10:18:31.146183Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-11-08T10:18:31.303971Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-11-08T10:18:31.354006Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-11-08T10:18:31.417947Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 39117b2a-407d-11ec-b8cd-0242ac110008.
2021-11-08T10:18:31.420995Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-11-08T10:18:31.747796Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-11-08T10:18:31.747814Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-11-08T10:18:31.748302Z 0 [Warning] CA certificate ca.pem is self signed.
2021-11-08T10:18:31.822097Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@625c2ca790e5 sbin]$ ./mysqld --user=mysql &
[1] 148
[root@625c2ca790e5 sbin]$ ps -ef |grep mysql
mysql 148 15 2 10:19 pts/1 00:00:00 ./mysqld --user=mysql
root 177 15 0 10:19 pts/1 00:00:00 grep --color=auto mysql
[root@625c2ca790e5 sbin]$ mysql -V
mysql Ver 14.14 Distrib 5.7.35, for Linux (aarch64) using EditLine wrapper
修改密码
#静默安装默认是没有密码
[root@625c2ca790e5 sbin]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server - (GPL)
Copyright (c) 2000, 2021, 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.
mysql> set password='123456';
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@625c2ca790e5 sbin]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.35 MySQL Community Server - (GPL)
Copyright (c) 2000, 2021, 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.
mysql> exit
mysql-5.7.35 aarch64 性能测试
- what: mysql 5.7.35 aarch64 性能测试
- where: centos:7 aarch64
- when: 2021.11.08
- who: JRT
- why: 知识库初始化
- how:
环境
创建容器
[root@ecs-hk-arm jrt]$ docker run -itd --privileged=true --name jrt1 -p 8878:3306 centos:7
625c2ca790e5d2000c053b5ad3f6fcc4e7c7475112e24fbd1343efcfa15e6280
查环境信息
#查架构
[root@625c2ca790e5 sbin]$ uname -a
Linux 625c2ca790e5 4.18.0-193.28.1.el7.aarch64 $1 SMP Wed Oct 21 16:25:35 UTC 2020 aarch64 aarch64 aarch64 GNU/Linux
#查系统
[root@625c2ca790e5 sbin]$ cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (AltArch)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (AltArch)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7:server"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
#查运行内存
[root@625c2ca790e5 sbin]$ cat /proc/meminfo |grep MemTotal
MemTotal: 32948864 kB
编译安装测试工具
进入容器
[root@ecs-hk-arm jrt]$ docker exec -it jrt1 bash
安装依赖
[root@625c2ca790e5 /]$ cd /opt
[root@625c2ca790e5 opt]$ yum install wget vim -y
Installed:
vim-enhanced.aarch64 2:7.4.629-8.el7_9 wget.aarch64 0:1.14-18.el7_6.1
Dependency Installed:
gpm-libs.aarch64 0:1.20.7-6.el7 vim-common.aarch64 2:7.4.629-8.el7_9
vim-filesystem.aarch64 2:7.4.629-8.el7_9 which.aarch64 0:2.20-7.el7
Complete!
[root@625c2ca790e5 opt]$ yum install gcc gcc-c++ automake make libtool mysql-devel -y
Dependency Installed:
autoconf.noarch 0:2.69-11.el7 cpp.aarch64 0:4.8.5-44.el7
glibc-devel.aarch64 0:2.17-325.el7_9 glibc-headers.aarch64 0:2.17-325.el7_9
kernel-headers.aarch64 0:4.18.0-305.10.2.el7 libgomp.aarch64 0:4.8.5-44.el7
libmpc.aarch64 0:1.0.1-3.el7 libstdc++-devel.aarch64 0:4.8.5-44.el7
m4.aarch64 0:1.4.16-10.el7 mpfr.aarch64 0:3.1.1-4.el7
perl-Test-Harness.noarch 0:3.28-3.el7 perl-Thread-Queue.noarch 0:3.02-2.el7
Dependency Updated:
glibc.aarch64 0:2.17-325.el7_9 glibc-common.aarch64 0:2.17-325.el7_9
Complete!
安装测试工具
#安装mysql 5.7.35见mysql安装文档，此处略过
#下载sysbench
[root@625c2ca790e5 opt]$ wget https://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
--2021-11-09 01:46:28-- https://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
Resolving github.com (github.com)... 20.205.243.166
Connecting to github.com (github.com)|20.205.243.166|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.16 [following]
--2021-11-09 01:46:29-- https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.16
Resolving codeload.github.com (codeload.github.com)... 20.205.243.165
Connecting to codeload.github.com (codeload.github.com)|20.205.243.165|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: '1.0.16.tar.gz'
[ <=> ] 1,508,595 6.32MB/s in 0.2s
2021-11-09 01:46:30 (6.32 MB/s) - '1.0.16.tar.gz' saved [1508595]
[root@625c2ca790e5 opt]$ ls
1.0.16.tar.gz
$解压
[root@625c2ca790e5 opt]$ tar -xf 1.0.16.tar.gz
[root@625c2ca790e5 opt]$ ls
1.0.16.tar.gz sysbench-1.0.16
查询mysql数据保存路径
#回显信息中的“pkgincludedir”和“pkglibdir”的值，供后续步骤使用：
[root@625c2ca790e5 opt]$ mysql_config -help
Usage: /usr/bin/mysql_config [OPTIONS]
Compiler: GNU 7.3.1
Options:
--cflags [-I/usr/include/mysql ]
--cxxflags [-I/usr/include/mysql ]
--include [-I/usr/include/mysql]
--libs [-L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl]
--libs_r [-L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldl]
--plugindir [/usr/lib64/mysql/plugin]
--socket [/var/lib/mysql/mysql.sock]
--port [0]
--version [5.7.35]
--libmysqld-libs [-L/usr/lib64/mysql -lmysqld -lpthread -lm -lrt -lssl -lcrypto -ldl -lcrypt -laio -lnuma]
--variable=VAR VAR is one of:
pkgincludedir [/usr/include/mysql]
pkglibdir [/usr/lib64/mysql]
plugindir [/usr/lib64/mysql/plugin]
[root@625c2ca790e5 opt]$
编译安装sysbench
[root@625c2ca790e5 opt]$ cd sysbench-1.0.16/
[root@625c2ca790e5 sysbench-1.0.16]$ ls
COPYING README-Oracle.md autogen.sh debian m4 rpm src
ChangeLog README-WIN.txt config doc missing scripts tests
Makefile.am README.md configure.ac install-sh mkinstalldirs snap third_party
[root@625c2ca790e5 sysbench-1.0.16]$ ./autogen.sh
./autogen.sh: running `autoconf'
Libtoolized with: libtoolize (GNU libtool) 2.4.2
Automade with: automake (GNU automake) 1.13.4
Configured with: autoconf (GNU Autoconf) 2.69
[root@625c2ca790e5 sysbench-1.0.16]$ ./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql --with-mysql
LuaJIT : bundled
LUAJIT_CFLAGS : -I$(abs_top_builddir)/third_party/luajit/inc
LUAJIT_LIBS : $(abs_top_builddir)/third_party/luajit/lib/libluajit-5.1.a -ldl
LUAJIT_LDFLAGS : -rdynamic
Concurrency Kit : bundled
CK_CFLAGS : -I$(abs_top_builddir)/third_party/concurrency_kit/include
CK_LIBS : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.a
configure flags :
===============================================================================
[root@625c2ca790e5 sysbench-1.0.16]$ make
make[2]: Leaving directory `/opt/sysbench-1.0.16/src'
make[1]: Leaving directory `/opt/sysbench-1.0.16/src'
Making all in tests
make[1]: Entering directory `/opt/sysbench-1.0.16/tests'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Entering directory `/opt/sysbench-1.0.16'
make[1]: Nothing to be done for `all-am'.
make[1]: Leaving directory `/opt/sysbench-1.0.16'
[root@625c2ca790e5 sysbench-1.0.16]$ make install
make[2]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Leaving directory `/opt/sysbench-1.0.16/tests'
make[1]: Entering directory `/opt/sysbench-1.0.16'
make[2]: Entering directory `/opt/sysbench-1.0.16'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/opt/sysbench-1.0.16'
make[1]: Leaving directory `/opt/sysbench-1.0.16'
[root@625c2ca790e5 sysbench-1.0.16]$ sysbench --version
sysbench 1.0.16
测试准备
进入数据库， 创建 “sysuser”账户及数据库供后续测试sysbench使用
[root@625c2ca790e5 sysbench-1.0.16]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.35 MySQL Community Server - (GPL)
Copyright (c) 2000, 2021, 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.
mysql> create user 'sysuser'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'sysuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)
mysql>exit
使用sysbench创建15个表，每个表40万条记录，存储引擎innodb：
[root@625c2ca790e5 sysbench-1.0.16]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --rand-init=on prepare
Inserting 400000 records into 'sbtest14'
Creating secondary indexes on 'sbtest14'...
Creating table 'sbtest15'...
Inserting 400000 records into 'sbtest15'
Creating secondary indexes on 'sbtest15'.
调整最大连接数
#mysql在后续测试时可能因连接数过多失败，需按自己需求调整最大连接数
mysql>
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set GLOBAL max_connections = 2000;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
测试
每次测试完成后防止因最大连接数而导致后续测试失败，故需要重启mysql
mysql执行参数
--oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。
--oltp-tables-count：测试的表数量，根据实际情况选择
--oltp-table-size：测试的表的大小，根据实际情况选择
--threads：客户端的并发连接数
--time：测试执行的时间，单位是秒，该值不要太短，可以选择120
--report-interval：生成报告的时间间隔，单位是秒，如10
只写测试
使用sysbench进行100并发只读测试，测试时间2分钟
第一次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 666.08 qps: 4009.35 (r/w/o: 0.00/2667.20/1342.15) lat (ms,99%): 893.56 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 713.80 qps: 4286.60 (r/w/o: 0.00/2859.00/1427.60) lat (ms,99%): 995.51 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 895.20 qps: 5374.21 (r/w/o: 0.00/3583.81/1790.40) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 854.00 qps: 5116.89 (r/w/o: 0.00/3408.90/1708.00) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 842.70 qps: 5059.60 (r/w/o: 0.00/3374.20/1685.40) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 851.70 qps: 5111.00 (r/w/o: 0.00/3407.60/1703.40) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 957.10 qps: 5741.90 (r/w/o: 0.00/3827.70/1914.20) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 877.00 qps: 5261.20 (r/w/o: 0.00/3507.20/1754.00) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 925.60 qps: 5552.60 (r/w/o: 0.00/3701.40/1851.20) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 875.30 qps: 5252.40 (r/w/o: 0.00/3501.80/1750.60) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 901.10 qps: 5410.20 (r/w/o: 0.00/3608.00/1802.20) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 910.30 qps: 5488.49 (r/w/o: 0.00/3668.30/1820.20) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 411184
other: 205592
total: 616776
transactions: 102796 (856.17 per sec.)
queries: 616776 (5137.00 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0644s
total number of events: 102796
Latency (ms):
min: 1.42
avg: 116.74
max: 1662.06
99th percentile: 746.32
sum: 12000707.77
Threads fairness:
events (avg/stddev): 1027.9600/37.10
execution time (avg/stddev): 120.0071/0.01
[root@625c2ca790e5 opt]$
第二次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 772.52 qps: 4652.81 (r/w/o: 0.00/3097.77/1555.04) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 857.80 qps: 5149.02 (r/w/o: 0.00/3433.41/1715.61) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 816.80 qps: 4895.48 (r/w/o: 0.00/3261.89/1633.59) lat (ms,99%): 877.61 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 841.39 qps: 5070.62 (r/w/o: 0.00/3387.94/1682.67) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 887.52 qps: 5305.11 (r/w/o: 0.00/3529.97/1775.14) lat (ms,99%): 861.95 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 843.90 qps: 5063.10 (r/w/o: 0.00/3375.30/1687.80) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 928.50 qps: 5568.77 (r/w/o: 0.00/3711.78/1856.99) lat (ms,99%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 875.30 qps: 5257.53 (r/w/o: 0.00/3506.92/1750.61) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 906.50 qps: 5434.68 (r/w/o: 0.00/3621.68/1812.99) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 885.00 qps: 5309.72 (r/w/o: 0.00/3539.71/1770.01) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 849.00 qps: 5095.20 (r/w/o: 0.00/3397.20/1698.00) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 884.30 qps: 5309.50 (r/w/o: 0.00/3540.90/1768.60) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 414344
other: 207172
total: 621516
transactions: 103586 (862.47 per sec.)
queries: 621516 (5174.79 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1035s
total number of events: 103586
Latency (ms):
min: 1.36
avg: 115.90
max: 2114.28
99th percentile: 733.00
sum: 12005898.19
Threads fairness:
events (avg/stddev): 1035.8600/43.87
execution time (avg/stddev): 120.0590/0.01
[root@625c2ca790e5 opt]$
第三次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-write-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 792.46 qps: 4774.93 (r/w/o: 0.00/3180.02/1594.91) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 870.00 qps: 5213.81 (r/w/o: 0.00/3473.81/1740.00) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 904.80 qps: 5428.30 (r/w/o: 0.00/3618.70/1809.60) lat (ms,99%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 896.40 qps: 5384.50 (r/w/o: 0.00/3591.70/1792.80) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 881.20 qps: 5283.70 (r/w/o: 0.00/3521.30/1762.40) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 898.90 qps: 5389.40 (r/w/o: 0.00/3591.60/1797.80) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 843.80 qps: 5070.80 (r/w/o: 0.00/3383.20/1687.60) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 911.90 qps: 5469.10 (r/w/o: 0.00/3645.30/1823.80) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 923.70 qps: 5539.38 (r/w/o: 0.00/3691.99/1847.39) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 885.10 qps: 5311.12 (r/w/o: 0.00/3540.91/1770.21) lat (ms,99%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 913.30 qps: 5479.40 (r/w/o: 0.00/3652.80/1826.60) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 918.70 qps: 5510.88 (r/w/o: 0.00/3673.49/1837.39) lat (ms,99%): 669.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 426016
other: 213008
total: 639024
transactions: 106504 (886.21 per sec.)
queries: 639024 (5317.26 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1781s
total number of events: 106504
Latency (ms):
min: 1.40
avg: 112.77
max: 2353.47
99th percentile: 669.89
sum: 12009956.52
Threads fairness:
events (avg/stddev): 1065.0400/34.62
execution time (avg/stddev): 120.0996/0.03
[root@625c2ca790e5 opt]$
只读测试
第一次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 2136.70 qps: 34266.45 (r/w/o: 29983.46/0.00/4282.99) lat (ms,99%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2255.51 qps: 36089.81 (r/w/o: 31578.60/0.00/4511.21) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2257.28 qps: 36118.93 (r/w/o: 31604.47/0.00/4514.47) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2260.70 qps: 36172.01 (r/w/o: 31650.51/0.00/4521.50) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2252.81 qps: 36033.50 (r/w/o: 31528.19/0.00/4505.31) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2256.51 qps: 36105.73 (r/w/o: 31592.21/0.00/4513.52) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 2253.30 qps: 36068.52 (r/w/o: 31562.11/0.00/4506.40) lat (ms,99%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 2253.97 qps: 36059.87 (r/w/o: 31551.83/0.00/4508.05) lat (ms,99%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 2254.83 qps: 36069.86 (r/w/o: 31560.10/0.00/4509.76) lat (ms,99%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 2250.19 qps: 36006.92 (r/w/o: 31506.74/0.00/4500.18) lat (ms,99%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 2251.81 qps: 36028.74 (r/w/o: 31525.02/0.00/4503.72) lat (ms,99%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2248.35 qps: 35969.22 (r/w/o: 31472.82/0.00/4496.40) lat (ms,99%): 158.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3771880
write: 0
other: 538840
total: 4310720
transactions: 269420 (2244.21 per sec.)
queries: 4310720 (35907.37 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0502s
total number of events: 269420
Latency (ms):
min: 2.87
avg: 44.55
max: 1254.89
99th percentile: 155.80
sum: 12001895.45
Threads fairness:
events (avg/stddev): 2694.2000/42.73
execution time (avg/stddev): 120.0190/0.01
[2]+ Exit 1 ./mysqld --user=mysql
[root@625c2ca790e5 opt]$
第二次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 1890.63 qps: 30325.60 (r/w/o: 26534.55/0.00/3791.05) lat (ms,99%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 1982.73 qps: 31722.64 (r/w/o: 27757.69/0.00/3964.96) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 1989.69 qps: 31838.27 (r/w/o: 27858.19/0.00/3980.07) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2072.13 qps: 33155.02 (r/w/o: 29010.97/0.00/4144.05) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2103.70 qps: 33657.75 (r/w/o: 29450.34/0.00/4207.41) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1985.21 qps: 31762.95 (r/w/o: 27792.53/0.00/3970.42) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1986.09 qps: 31779.32 (r/w/o: 27807.43/0.00/3971.89) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 1955.70 qps: 31292.66 (r/w/o: 27380.75/0.00/3911.91) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 1969.67 qps: 31518.04 (r/w/o: 27579.11/0.00/3938.93) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 1972.21 qps: 31552.03 (r/w/o: 27607.30/0.00/3944.73) lat (ms,99%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 1962.42 qps: 31396.32 (r/w/o: 27471.78/0.00/3924.54) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2005.12 qps: 32081.28 (r/w/o: 28071.73/0.00/4009.55) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3343942
write: 0
other: 477706
total: 3821648
transactions: 238853 (1989.53 per sec.)
queries: 3821648 (31832.51 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0539s
total number of events: 238853
Latency (ms):
min: 2.66
avg: 50.24
max: 975.82
99th percentile: 200.47
sum: 11999533.83
Threads fairness:
events (avg/stddev): 2388.5300/50.90
execution time (avg/stddev): 119.9953/0.02
[root@625c2ca790e5 opt]$
第三次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=on --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 2135.53 qps: 34246.09 (r/w/o: 29965.44/0.00/4280.65) lat (ms,99%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 2259.60 qps: 36152.74 (r/w/o: 31633.44/0.00/4519.29) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 2259.41 qps: 36148.69 (r/w/o: 31630.07/0.00/4518.62) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 2261.76 qps: 36194.41 (r/w/o: 31670.58/0.00/4523.83) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 2253.94 qps: 36067.38 (r/w/o: 31559.60/0.00/4507.77) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 2256.59 qps: 36092.67 (r/w/o: 31579.50/0.00/4513.17) lat (ms,99%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 2256.89 qps: 36114.50 (r/w/o: 31600.41/0.00/4514.09) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 2252.02 qps: 36037.77 (r/w/o: 31533.84/0.00/4503.93) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 2264.31 qps: 36223.50 (r/w/o: 31695.18/0.00/4528.31) lat (ms,99%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 2254.30 qps: 36068.72 (r/w/o: 31560.02/0.00/4508.70) lat (ms,99%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 2255.11 qps: 36090.90 (r/w/o: 31580.49/0.00/4510.41) lat (ms,99%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 2256.02 qps: 36088.00 (r/w/o: 31576.16/0.00/4511.84) lat (ms,99%): 173.58 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3776584
write: 0
other: 539512
total: 4316096
transactions: 269756 (2247.03 per sec.)
queries: 4316096 (35952.47 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0491s
total number of events: 269756
Latency (ms):
min: 2.79
avg: 44.49
max: 968.58
99th percentile: 183.21
sum: 12001746.76
Threads fairness:
events (avg/stddev): 2697.5600/44.48
execution time (avg/stddev): 120.0175/0.01
[root@625c2ca790e5 opt]$
读写混合测试
第一次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 511.64 qps: 10355.68 (r/w/o: 7261.84/2060.66/1033.18) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 548.50 qps: 10916.48 (r/w/o: 7636.29/2183.10/1097.10) lat (ms,99%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 550.90 qps: 11051.31 (r/w/o: 7745.04/2204.58/1101.69) lat (ms,99%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 546.61 qps: 10936.97 (r/w/o: 7658.19/2185.45/1093.33) lat (ms,99%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 520.80 qps: 10387.29 (r/w/o: 7262.39/2083.30/1041.60) lat (ms,99%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 543.70 qps: 10889.79 (r/w/o: 7627.99/2174.40/1087.40) lat (ms,99%): 802.05 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 576.60 qps: 11568.16 (r/w/o: 8107.07/2307.89/1153.20) lat (ms,99%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 557.30 qps: 11120.51 (r/w/o: 7776.91/2229.00/1114.60) lat (ms,99%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 553.20 qps: 11045.12 (r/w/o: 7726.71/2212.00/1106.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 554.70 qps: 11136.08 (r/w/o: 7807.89/2218.80/1109.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 526.69 qps: 10493.08 (r/w/o: 7333.32/2106.38/1053.39) lat (ms,99%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 544.91 qps: 10919.95 (r/w/o: 7651.20/2178.93/1089.81) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 916384
write: 261824
other: 130912
total: 1309120
transactions: 65456 (544.72 per sec.)
queries: 1309120 (10894.45 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1630s
total number of events: 65456
Latency (ms):
min: 4.31
avg: 183.54
max: 1896.96
99th percentile: 719.92
sum: 12013618.92
Threads fairness:
events (avg/stddev): 654.5600/21.36
execution time (avg/stddev): 120.1362/0.01
[1]- Exit 1 ./mysqld --user=mysql
[root@625c2ca790e5 opt]$
第二次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 516.00 qps: 10413.91 (r/w/o: 7302.50/2069.40/1042.00) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 516.91 qps: 10342.91 (r/w/o: 7235.07/2074.02/1033.81) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 539.20 qps: 10755.50 (r/w/o: 7530.50/2146.60/1078.40) lat (ms,99%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 559.70 qps: 11229.81 (r/w/o: 7870.41/2240.00/1119.40) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 581.59 qps: 11657.55 (r/w/o: 8154.39/2340.47/1162.68) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 560.01 qps: 11200.94 (r/w/o: 7852.10/2228.33/1120.51) lat (ms,99%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 567.70 qps: 11355.49 (r/w/o: 7950.39/2269.70/1135.40) lat (ms,99%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 522.80 qps: 10466.31 (r/w/o: 7329.81/2090.90/1045.60) lat (ms,99%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 526.00 qps: 10506.65 (r/w/o: 7340.57/2114.39/1051.70) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 543.60 qps: 10858.74 (r/w/o: 7605.93/2165.31/1087.50) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 533.50 qps: 10703.60 (r/w/o: 7504.20/2132.40/1067.00) lat (ms,99%): 612.21 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 507.79 qps: 10135.06 (r/w/o: 7085.30/2034.17/1015.59) lat (ms,99%): 831.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 907886
write: 259396
other: 129698
total: 1296980
transactions: 64849 (539.86 per sec.)
queries: 1296980 (10797.29 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1199s
total number of events: 64849
Latency (ms):
min: 4.16
avg: 185.09
max: 2051.53
99th percentile: 733.00
sum: 12002888.33
Threads fairness:
events (avg/stddev): 648.4900/21.35
execution time (avg/stddev): 120.0289/0.03
[root@625c2ca790e5 opt]$
第三次测试
[root@625c2ca790e5 opt]$ sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysuser --mysql-password='123456' --oltp_tables_count=15 --oltp-table-size=400000 --mysql-table-engine=innodb --num-threads=100 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run
WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 100 tps: 404.57 qps: 8176.32 (r/w/o: 5736.46/1621.01/818.85) lat (ms,99%): 1089.30 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 473.50 qps: 9479.06 (r/w/o: 6634.54/1897.41/947.11) lat (ms,99%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 565.10 qps: 11288.42 (r/w/o: 7902.01/2256.20/1130.20) lat (ms,99%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 528.40 qps: 10605.56 (r/w/o: 7433.48/2115.29/1056.80) lat (ms,99%): 1013.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 544.40 qps: 10877.53 (r/w/o: 7611.62/2177.11/1088.80) lat (ms,99%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 582.69 qps: 11638.80 (r/w/o: 8142.56/2330.86/1165.38) lat (ms,99%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 560.61 qps: 11227.71 (r/w/o: 7866.04/2240.44/1121.22) lat (ms,99%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 556.20 qps: 11080.39 (r/w/o: 7742.00/2226.00/1112.40) lat (ms,99%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 100 tps: 552.20 qps: 11097.40 (r/w/o: 7784.20/2208.80/1104.40) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 100 tps: 545.29 qps: 10875.95 (r/w/o: 7605.60/2179.77/1090.59) lat (ms,99%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 100 tps: 540.01 qps: 10787.25 (r/w/o: 7545.91/2161.33/1080.02) lat (ms,99%): 646.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 889700
write: 254200
other: 127100
total: 1271000
transactions: 63550 (529.20 per sec.)
queries: 1271000 (10583.99 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0861s
total number of events: 63550
Latency (ms):
min: 4.36
avg: 188.87
max: 2190.20
99th percentile: 802.05
sum: 12002618.59
Threads fairness:
events (avg/stddev): 635.5000/17.46
execution time (avg/stddev): 120.0262/0.06
[root@625c2ca790e5 opt]$
测试总结
如下表所示，在8核32g的aarch64环境中，利用sysbench工具测试mysql并发100时，只读、只写、读写的平均耗时分别为：115.14ms、46.43ms、 185.83ms
从性能看，mysql可以达到要求。
| 平均耗时（ms） | ||||
|---|---|---|---|---|
| 第一次 | 第二次 | 第三次 | 平均 | |
| 只写测试 | 116.74 | 115.90 | 112.77 | 115.14 |
| 只读测试 | 44.55 | 50.24 | 44.49 | 46.43 |
| 读写测试 | 183.54 | 185.09 | 188.87 | 185.83 |