MySQL Key的含义，sysbench测试工具

mysql的key和index多少有点令人迷惑，这实际上考察对数据库体系结构的了解的。

  • PRI — 主键约束；
  • UNI — 唯一约束；
  • MUL — 可以重复;

1 、key 是数据库的物理结构，它包含两层意义，

一是约束（偏重于约束和规范数据库的结构完整性），

二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。


    primary key 有两个作用：

  一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；


   unique key 也有两个作用：

一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；

foreign key也有两个作用，

一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

 

  可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在oracle上建立外键，不会自动建立index），因此创建key也有如下几种方式：
  （1）在字段级以key方式建立， 如 create table t (id int not null primary key);
  （2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
  （3）在表级以key方式建立，如create table t(id int, primary key (id));

  其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。

2、 index是数据库的物理结构，它只是辅助查询的。

它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；
  因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。
  如，create table t(id int, index inx_tx_id  (id));

3 最后的释疑：

（1）我们说索引分类，分为主键索引、唯一索引、普通索引(这才是纯粹的index)等，也是基于是不是把index看作了key。
  比如 create table t(id int, unique index inx_tx_id  (id));  --index当作了key使用
（2）最重要的也就是，不管如何描述，理解index是纯粹的index，还是被当作key，当作key时则会有两种意义或起两种作用。

MySql性能测试工具-sysbench

转自：MySql性能测试工具-sysbench — 没那么简单的博客

虽然mysql默认的有mysqlslap这个性能测试工具，但和sysbench比较来说，还逊色不少。

  1. 下载安装包 https://github.com/akopytov/sysbench
shell> wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
shell> unzip sysbench-1.0.zip
shell> cd sysbench-1.0
  1. 安装依赖库
shell> yum install automake libtool -y
  1. 开始安装
shell> ./autogen.sh
shell> ./configure
#ERROR: cannot find MySQL libraries. If you want to compile with MySQL support 没找到mysql库 需要用参数指定下 --with-mysql-includes和--with-mysql-libs
shell> ./configure --with-mysql-includes=/alidata/server/mysql5.7/include/ --with-mysql-libs=/alidata/server/mysql5.7/lib/
shell> make
  1. 执行下命令:
shell> sysbench --help
#sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
#问题原因：sysbench无法找到mysql的库文件，可能是环境变量LD_LIBRARY_PATH没有设置，设置后即可解决该问题：
shell> export LD_LIBRARY_PATH=/alidata/server/mysql5.7/lib/lib
shell> sysbench --version
sysbench 1.0
  1. 创建测试数据库sbtest
shell> mysqladmin create sbtest -uroot -p
Enter password:
  1. 测试准备: 20个并发连接，20张表 每个表填充10W条数据 最大请求时间120s
#-test=tests/db/oltp.lua 表示调用 tests/db/oltp.lua 脚本进行 oltp 模式测试
#--oltp_tables_count=10 表示会生成 10 个测试表
#--oltp-table-size=100000 表示每个测试表填充数据量为 100000 
#--rand-init=on 表示每个测试表都是用随机数据来填充的
#-num-threads=8 表示发起 8个并发连接
#--oltp-read-only=off 表示不要进行只读测试，也就是会采用读写混合模式测试
#--report-interval=10 表示每10秒输出一次测试进度报告
#--rand-type=uniform 表示随机类型为固定模式，其他几个可选随机模式：uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
#--max-time=120 表示最大执行时长为 120秒
#--max-requests=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长
#--percentile=99 表示设定采样比例，默认是 95%，即丢弃1%的长请求，在剩余的99%里取最大值

shell> sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex prepare
sysbench 1.0:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'...

7.开始测试

shell> sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex run >> /tmp/log/sysbench_oltpx_20161121.log

#执行结束后查看测试报告
shell> less /tmp/log/sysbench_oltpx_20161121.log
sysbench 1.0:  multi-threaded system evaluation benchmark

#报告内容如下:
Running the test with following options:
Number of threads: 20
Initializing random number generator from current time


Initializing worker threads...

Threads started!

OLTP test statistics:
    queries performed:
        read:                            935592 --读总数
        write:                           267295 --写总数
        other:                           133650 --其他操作(CURD之外的操作，例如COMMIT)
        total:                           1336537 --全部总数
    transactions:                        66822  (556.77 per sec.) --总事务数(每秒事务数)
    read/write requests:                 1202887 (10022.55 per sec.) --读写总数(每秒读写次数)
    other operations:                    133650 (1113.58 per sec.)  --其他操作总数(每秒其他操作次数)
    ignored errors:                      6      (0.05 per sec.)  --总忽略错误总数(每秒忽略错误次数)
    reconnects:                          0      (0.00 per sec.) --重连总数(每秒重连次数)

General statistics: --常规统计
    total time:                          120.0180s --总耗时
    total number of events:              66822 --共发生多少事务数
    total time taken by event execution: 2399.7900s  --所有事务耗时相加(不考虑并行因素)
    response time:
         min:                                  2.76ms --最小耗时
         avg:                                 35.91ms --平均耗时
         max:                               1435.19ms --最长耗时
         approx.  95 percentile:              84.22ms --超过95%平均耗时

Threads fairness: --并发统计
    events (avg/stddev):           3341.1000/37.54 --总处理事件数/标准偏差
    execution time (avg/stddev):   119.9895/0.02
--总执行时间/标准偏差

sysbench测试工具

一、安装

Github地址：https://github.com/akopytov/sysbench

上边有各种OS的安装教程，以下仅列出CentOS7的安装方法。

  1. curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
  2. sudo yum -y install sysbench

二、参数

是用sysbench的版本是1.0.20

2.1 通用参数

参数解释
--threads=N                     使用的线程数量，默认值为1。
--events=N                      总请求数，与--time选择一个设置即可，默认值为0。
--time=N                        总执行时间，与--events选择一个设置即可，单位为s，默认值为10。
--forced-shutdown=STRING 超过--time后强制中断，默认为off。
--thread-stack-size=SIZE 每个线程的stack大小，默认为64K。
--rate=N                        平均事务率(tps)，0为不限速，默认值0
--report-interval=N             表示N秒输出一次测试进度报告，0表示关闭测试进度报告输出，仅输出最终的报告结果，默认值为0。
--report-checkpoints=[LIST,...] 转储完整的统计数据，并在指定的时间点重置所有计数器。

参数是一个用逗号分隔的值列表，表示从测试开始到必须执行报告检查点所经过的秒数，默认关闭
--validate[=on|off]             在可能的情况下执行验证检查，默认为off。
--config-file=FILENAME          配置文件路径。

2.2 随机数生成参数

参数解释
--rand-type=STRING 表示随机类型的模式，共有4种模式：uniform(固定)，gaussian(高斯)，special(特定)，pareto(帕雷特)，默认值为：special。
--rand-spec-iter=N 用于数字生成的迭代次数，默认值12
--rand-spec-pct=N  对于’special’随机模式中待处理值的百分比，默认值1
--rand-spec-res=N  对于’special’随机模式中指定值的比例，默认值为75。
--rand-seed=N      随机数生成依据，0表示按当前时间为基础生成随机数，默认值0
--rand-pareto-h=N  pareto 分布，默认值0.2

2.3 日志参数

参数解释
--verbosity=N 日志级别，5为debug信息，0位仅仅输出严重信息，默认值为3。
--percentile=N 查询相应时间采样的百分比，0禁用，默认值为95%。
--histogram[=on|off] 在报告中打印延迟直方图，默认值关闭

2.4 数据库参数

参数解释
--db-driver=STRING  连接数据库的驱动，默认mysql
--db-ps-mode=STRING SQL是否需要预编译，模式有：auto/disable，默认为disable。
--db-debug[=on|off] 输出数据库层面的debug信息，默认为off。

2.5 MySQL参数

略

2.6 pgSQL参数

略

2.7 fileio参数

参数解释
--file-num=N 创建文件的数量，默认值：128。
--file-block-size=N 每次IO操作的block大小，默认值：16K。
--file-total-size=SIZE 所有文件大小总和，默认值：2G。
--file-test-mode=STRING 测试模式：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。
--file-io-mode=STRING 文件操作模式：sync(同步),async(异步),mmap(快速map映射)，默认值：sync。
--file-async-backlog=N 每个线程排队的异步操作数，默认值[128]。
--file-extra-flags=[LIST,...] 使用额外的标志符来打开文件{sync,dsync,direct}。默认值空
--file-fsync-freq=N 在完成N次请求之后，执行fsync()，0表示不使用fsync，默认值：100。
--file-fsync-all[=on|off] 每次写操作后执行fsync()，默认值：off。
--file-fsync-end[=on|off]     测试结束后执行fsync()，默认值：on。
--file-fsync-mode=STRING 使用fsync或fdatasync方法进行同步，默认值：fsync。
--file-merged-requests=N 尽可能的合并N个IO请求数，0表示不合并，默认值：0。
--file-rw-ratio=N             测试时候的读写比例，默认值：1.5(即3:2)。

2.8 cpu参数

参数解释
--cpu-max-prime=N 最大质数生成器的上限，默认值：10000。

2.9 memory参数

参数解释
--memory-block-size=SIZE    测试时内存块大小，默认值：1K。
--memory-total-size=SIZE    传输数据可使用的最大内存大小，默认值：100G。
--memory-scope=STRING       内存访问范围：global/local，默认值：global。
--memory-hugetlb[=on|off]   从HugeTLB池分配内存，默认值：off。
--memory-oper=STRING        内存操作类型：read/ write/none，默认值：write。
--memory-access-mode=STRING 内存访问方式：seq(顺序)/rnd(随机)，默认值：seq。

2.10 threads参数

参数解释
--thread-yields=N 每个请求产生多少线程，默认值：1000。
--thread-locks=N  每个线程的锁的数量，默认值：8。

2.11 mutex参数

参数解释
--mutex-num=N 数组互斥的总大小，默认值：4096。
--mutex-locks=N 每个线程互斥锁的数量，默认值：50000。
--mutex-loops=N 内部互斥锁的空循环数量，默认值：1000

三、示例

3.1 fileio

sysbench的性能测试都需要做prepare,run,cleanup这三步，准备数据，跑测试，删除数据。

  1. # 要进入到要测试的硬盘（硬盘挂载点），否则测试的是系统盘
  2. cd /home
  3. sysbench fileio --file-num=1 --file-total-size=15G --file-test-mode=rndrw --time=300 prepare
  4. sysbench fileio --file-num=1 --file-total-size=15G --file-test-mode=rndrw --time=300 run
  5. sysbench fileio --file-num=1 --file-total-size=15G --file-test-mode=rndrw --time=300 cleanup

结果分析：

  1. Threads started!
  2.  
  3.  
  4. File operations:
  5. reads/s: 405.66
  6. writes/s: 270.44
  7. fsyncs/s: 6.76
  8.  
  9. Throughput:
  10. read, MiB/s: 6.34
  11. written, MiB/s: 4.23
  12.  
  13. General statistics:
  14. total time: 300.0919s
  15. total number of events: 204929
  16.  
  17. Latency (ms):
  18. min: 0.01
  19. avg: 1.46
  20. max: 219.09
  21. 95th percentile: 2.97
  22. sum: 299537.03
  23.  
  24. Threads fairness:
  25. events (avg/stddev): 204929.0000/0.00
  26. execution time (avg/stddev): 299.5370/0.00

换算IOPS为(6.34+4.23)*1024/16.384=660.625，另外IO性能和CPU核数和测试时候的文件数量有关。

 

3.2 cpu

如果设置为3，则表示2、3、5（要计算1-5共5次）；如果设置为10，则表示2、3、5、7、11、13、17、19、23、29（要计算1-29共29次）

sysbench cpu --cpu-max-prime=3 run

 

3.4 memory

sysbench memory --memory-total-size=2G run

 

3.5 threads

sysbench threads --thread-yields=2 --thread-locks=4 run

 

3.6 mutex

略

3.7 mysql

模版位于/usr/share/sysbench，准备数据。可以使用其他模版测试

  1. sysbench /usr/share/sysbench/oltp_insert.lua \
  2. --threads=4 \
  3. --table-size=500000 \
  4. --tables=10 \
  5. --mysql-db=lw_test \
  6. --mysql-user=admin \
  7. --mysql-password=admin \
  8. --mysql-socket=/home/mysql/mysql-8.0.20/run/mysqld.sock prepare

结果分析，以下结果使用oltp_insert.lua测试。

  1. Threads started!
  2.  
  3. SQL statistics:
  4. queries performed:
  5. read: 0 # select数量
  6. write: 99 # insert、update、delete数量
  7. other: 0 # commit、unlock tables以及其他mutex的数量
  8. total: 99
  9. transactions: 99 (9.68 per sec.) # 通常需要关注的数字(TPS)
  10. queries: 99 (9.68 per sec.)
  11. ignored errors: 0 (0.00 per sec.)
  12. reconnects: 0 (0.00 per sec.)
  13.  
  14. General statistics:
  15. total time: 10.2170s # 运行时间
  16. total number of events: 99 # 总的事件数，一般与transactions相同
  17.  
  18. Latency (ms):
  19. min: 157.73
  20. avg: 410.56
  21. max: 939.20
  22. 95th percentile: 682.06 # 95%的语句的平均响应时间
  23. sum: 40645.31
  24.  
  25. Threads fairness:
  26. events (avg/stddev): 24.7500/0.43
  27. execution time (avg/stddev): 10.1613/0.07

 

posted @ 2021-12-21 16:56  CharyGao  阅读(153)  评论(0)    收藏  举报