MySQL基准测试

2019-07-15  本文已影响0人  一剑光寒十九洲

MySQL基准测试

原因

我们只能进行大概的测试，来确定系统大致的余量。

策略

集成式

  1. 测试整体性能而不只是MySQL;
  2. 可以发现各组件之间缓存带来的影响;
  3. 更能揭示应用的真是表现;
  4. 很难建立，也很难正确设置。

组件式

  1. 需要比较不同的表结构或查询的性能;
  2. 需要针对应用中的某个具体问题进行测试；
  3. 为了避免漫长的基准测试，可以通过一个短期基准测试，快速循环，检测出调整后的效果。

指标

  1. 吞吐量：单位时间内的事务处理数，TPS和TPM。
  2. 响应时间：任务所需的整体时间，百分比响应时间和平均响应时间。
  3. 并发性：任意时间内同时发生的并发数，同时工作的线程数Threads_running或者连接数。并发性测试不是为了测试应用能达到的并发度，而是为了测试应用在不同并发下的性能。
  4. 可扩展性：系统性能/资源数的函数关系，衡量系统性能是否可以随着资源的扩展而线性扩展。

方法

错误行为

步骤

  1. 提出问题并明确目标；
  2. 决定采用标准基准测试还是设计专用测试；
  3. 如果要设计专用测试，获取生产数据集的快照，该快照要易于还原，以便后续的测试；选择一个时间段，记录生产系统上的查询（querylog），要记录查询所在的线程以进行回放；
  4. 需要为每一轮测试创建一个目录，保存测试结果、配置文件、测试指标、脚本和相关说明；
  5. 需要准备好系统状态收集的相关的脚本，可以自己编写也可以使用pt工具；
  6. 确定基准测试的时长，基准测试运行的时间至少要等到系统达到稳定状态之后，这时候的稳定状态才是可以相信的；
  7. 决定是否使用自动化测试，从而准备自动化基准测试脚本，或者使用相关工具；
  8. 进行基准测试，通过逐步修改基准测试参数来进行迭代找出正确的参数值；
  9. 使用工具将测试过程中的数据绘制成图形，可以使用gnuplot或者R或者python；
  10. 通过图形将数字转化为结论。

在整个测试过程中，要详细地写下测试规划，应该记录测试数据、系统配置步骤、如何测量和分析结果、预热方案等，将参数和结果文档化。

工具

sysbench
help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --rate=N                        average transactions rate. 0 for unlimited rate [0]
  --report-interval=N             periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
  --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --tx-rate=N                     deprecated alias for --rate [0]
  --max-requests=N                deprecated alias for --events [0]
  --max-time=N                    deprecated alias for --time [0]
  --num-threads=N                 deprecated alias for --threads [1]

Pseudo-Random Numbers Generator options:
  --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
  --rand-spec-iter=N number of iterations used for numbers generation [12]
  --rand-spec-pct=N  percentage of values to be treated as 'special' (for special distribution) [1]
  --rand-spec-res=N  percentage of 'special' values to use (for special distribution) [75]
  --rand-seed=N      seed for random number generator. When 0, the current time is used as a RNG seed. [0]
  --rand-pareto-h=N  parameter h for pareto distribution [0.2]

Log options:
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]


Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

See 'sysbench <testname> help' for a list of options for each test.

常用通用参数：

fileio测试
fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

在这个测试中，你需要创建一个大于最大内存的文件，然后选择一种读写方式(seqwr\seqrewr\seqrd\rndrd\rndwr\rndrw)测试。

CPU测试
cpu options:
  --cpu-max-prime=N upper limit for primes generator [10000]

这个测试将会执行cpu_max_prime的标准整除验证算法（从2到cpu_max_prime平方根，除去10的整数倍）

内存测试
memory options:
  --memory-block-size=SIZE    size of memory block for test [1K]
  --memory-total-size=SIZE    total size of data to transfer [100G]
  --memory-scope=STRING       memory access scope {global,local} [global]
  --memory-hugetlb[=on|off]   allocate memory from HugeTLB pool [off]
  --memory-oper=STRING        type of memory operations {read, write, none} [write]
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]

这个测试将会分配一个memory-block-size大小的内存缓冲区，对其进行读写，直到达到memory-total-size数据量。

线程测试
threads options:
  --thread-yields=N number of yields to do per request [1000]
  --thread-locks=N  number of locks per thread [8]

这个测试将会执行thread-yields次"加thread-locks个锁-让出调度器-解锁"的循环。

互斥锁测试
mutex options:
  --mutex-num=N   total size of mutex array [4096]
  --mutex-locks=N number of mutex locks to do per thread [50000]
  --mutex-loops=N number of empty loops to do outside mutex lock [10000]

这个测试将会从mutex-num大小的互斥锁池中取出互斥锁，每次取出后，执行mutex-loops全局变量递增操作，mutex-locks次。

OLTP测试

内置支持的测试类型：bulk_insert,empty_test,oltp_common,oltp_delete,oltp_insert,oltp_point_select,oltp_read_only,oltp_read_write,oltp_update_index,oltp_update_non_index,oltp_write_only,prime-test,select_random_points,select_random_ranges

mysql options:
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

可以通过(sysbench oltp_read_write help)命令获取对应test的详细信息。

参考资料
案例
上一篇 下一篇

猜你喜欢

热点阅读