Load Testing Tools

通常,我们在开发应用程序时使用基准测试工具。部署应用程序时,基准测试通常为时已晚,无法提供帮助。
但是,在进行故障排除时,我确实经常使用基准测试工具。这个网络研讨会不包括实际的基准测试,但它关注的是基准测试工具。当您需要重复并发客户机执行导致的情况时,它们是最佳选择。这些类型的问题包括各种锁定和性能问题,以及暂停和崩溃。
在这个网络研讨会中,我将介绍一些我使用的主要工具,例如(但不限于)SysBench和Mysqlsap。我将演示如何在处理特定自定义问题时使用它们的标准选项,以及如何编写脚本来开发尽可能接近真实场景的测试用例。

展开查看详情

1.Load Testing Tools for Troubleshooting MySQL Concurrency Issues May, 23, 2018 Sveta Smirnova

2.Introduction • This is very personal webinar • No intended use • No best practices • No QA-specific tools Real life don’t generate queries Each customer has its own corner case Typical issues usually already found by QA team before first GA release date • Creative by nature 2

3.Introduction • This is very personal webinar • I usually don’t do benchmarks • Unless have a reason to Performance Schema Benchmarks: OLTP RW 2

4.Introduction • This is very personal webinar • I usually don’t do benchmarks • I use load tools for reproducible scenarios • Locking issues • Crashes • Custom slowdowns • Replication issues • Almost everything! 2

5.Introduction • This is very personal webinar • I usually don’t do benchmarks • I use load tools for reproducible scenarios • It helps to fix them! 2

6.Few Examples Create binary log events 3. Connect to master, create database foo. 4. In parallel client start some load: mysqlslap --user=root --host=127.0.0.1 --port=13000 --create-schema=foo \ --query="create table if not exists t1(f1 int); insert into t1 values(1); \ drop table if exists t1;" -c 10 -i 10000 5. kill -9 slave process 6. Restart MTR in dirty mode: ... 3

7.Few Examples Generate parallel load 6. Run concurrent environment: sysbench --test=/home/sveta/src/sysbench/sysbench/tests/db/oltp.lua --mysql-engine-trx=yes \ --mysql-table-engine=innodb --oltp_table_size=1000 --oltp_tables_count=1 --mysql-user=root \ --mysql-host=127.0.0.1 --mysql-port=13000 --num-threads=8 --max-requests=1000 run 7. Turn OFF and ON slow log: mysql> set global slow_query_log=0; Query OK, 0 rows affected (0,09 sec) mysql> set global slow_query_log=1; Query OK, 0 rows affected (0,00 sec) 8. Now you have 4 descriptors: ... 3

8.Few Examples Prepare data ... --disable_query_log --let $i=10 while($i) { insert into cartest values (null, 1); --dec $i } --enable_query_log ... 3

9.Table of Contents •What Do I Do? •Case Study: Slave Crash •Why Do I Do Load Tests? •Machines Which I Use •Binaries Which I Use •How Do I Scale •Summary 4

10.What Do I Do?

11.Single Thread Issues: MySQL CLI • Benchmarking tool by nature mysql> SELECT id, k FROM sbtest2 WHERE id=5014; +------+------+ | id | k | +------+------+ | 5014 | 3972 | +------+------+ 1 row in set (0.16 sec) 6

12.Single Thread Issues: MySQL CLI • Benchmarking tool by nature mysql> SELECT id, k FROM sbtest2 WHERE id=5014; +------+------+ | id | k | +------+------+ | 5014 | 3972 | +------+------+ 1 row in set (0.16 sec) • Access to • Status variables • Engine status • Performance Schema 6 • All internal troubleshooting tools

13.More than One Query: SHELL • Shell is our friend $ while (true); do mysql -e "flush slow logs"; done ... $ for i in ‘seq 1 1 100000‘; \ do mysql -e "insert into t1(id, myfield) values \ ($i, ’‘cat /dev/urandom | tr -dc A-Za-z0-9 | head -c $(( RANDOM % (250 - 5 + 1 ) + 5 ))‘’)"; \ done 7

14.More than One Query: SHELL • Shell is our friend $ while (true); do mysql -e "flush slow logs"; done ... $ for i in ‘seq 1 1 100000‘; \ do mysql -e "insert into t1(id, myfield) values \ ($i, ’‘cat /dev/urandom | tr -dc A-Za-z0-9 | head -c $(( RANDOM % (250 - 5 + 1 ) + 5 ))‘’)"; \ done • Helps to • Generate data • Craft queries with slightly various parameters 7

15.More than One Query: SHELL • Shell is our friend $ while (true); do mysql -e "flush slow logs"; done ... $ for i in ‘seq 1 1 100000‘; \ do mysql -e "insert into t1(id, myfield) values \ ($i, ’‘cat /dev/urandom | tr -dc A-Za-z0-9 | head -c $(( RANDOM % (250 - 5 + 1 ) + 5 ))‘’)"; \ done • Helps to • Generate data • Craft queries with slightly various parameters • Execute in multiple terminals 7 • To imitate concurrent load

16.Simple Tests: mysqlslap • Queries without variations $ mysqlslap --concurrency=8 --number-of-queries=1000 \ --query="REPLACE INTO mytest (ts, id, f1, f2, f3, f4) \ VALUES (now(), 1000*rand(), ’field text’, 1000000*rand(), ’more text’, 1)" \ --create-schema=test \ --pre-query="CREATE TABLE test.mytest(ts TIMESTAMP, id INT NOT NULL PRIMARY KEY, \ f1 VARCHAR(255), f2 INT, f3 CHAR(20), f4 INT) ENGINE=INNODB" \ --post-query="DROP TABLE test.mytest" Benchmark Average number of seconds to run all queries: 113.591 seconds Minimum number of seconds to run all queries: 113.591 seconds Maximum number of seconds to run all queries: 113.591 seconds Number of clients running queries: 8 Average number of queries per client: 125 8

17.Simple Tests: mysqlslap • Queries without variations • Supports auto-generated SQL $ time mysqlslap --concurrency=8 --auto-generate-sql --number-of-queries=100 Benchmark Average number of seconds to run all queries: 8.126 seconds Minimum number of seconds to run all queries: 8.126 seconds Maximum number of seconds to run all queries: 8.126 seconds Number of clients running queries: 8 Average number of queries per client: 12 real 1m37.932s user 0m0.032s sys 0m0.008s 8

18.Simple Tests: mysqlslap • Queries without variations • Supports auto-generated SQL • Helpful for • Simple query patterns • When you need to imitate some activity • Generate binary log events 8

19.Simple Tests: mysqlslap • Queries without variations • Supports auto-generated SQL • Helpful for • Should be used with care • In auto-generate mode drops schema No matter if it has other tables of not! • Always use option --no-drop • Do not use on production databases 8

20.Standard and Custom Tests: SysBench • Comes with standard OLTP tests $ sysbench --test=/home/sveta/src/sysbench/sysbench/tests/db/oltp.lua \ --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=10000 \ --oltp_tables_count=2 --num-threads=8 \ --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=13002 prepare sysbench 0.5: multi-threaded system evaluation benchmark Creating table ’sbtest1’... Inserting 10000 records into ’sbtest1’ Creating secondary indexes on ’sbtest1’... Creating table ’sbtest2’... Inserting 10000 records into ’sbtest2’ Creating secondary indexes on ’sbtest2’... 9

21.Standard and Custom Tests: SysBench • Comes with standard OLTP tests $ sysbench --test=/home/sveta/src/sysbench/sysbench/tests/db/oltp.lua \ --mysql-engine-trx=yes --mysql-table-engine=innodb --oltp_table_size=10000 \ --oltp_tables_count=2 --num-threads=8 \ --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=13002 run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Random number generator seed is 0 and will be ignored Threads started! ... Threads fairness: events (avg/stddev): 1281.0000/12.29 execution time (avg/stddev): 1564.7510/1.16 9

22.Standard and Custom Tests: SysBench • Comes with standard OLTP tests • Standard tests help to • Imitate some activity • Generate binary log events • Test effect of options/upgrades 9

23.Standard and Custom Tests: SysBench • Comes with standard OLTP tests • Standard tests help to • Standard OLTP tests include • Read only • INSERTs only • DELETEs only • Mixed • More 9

24.Standard and Custom Tests: SysBench • Comes with standard OLTP tests • Standard tests help to • Standard OLTP tests include • SysBench has standard hardware tests 9

25.Standard and Custom Tests: SysBench • Comes with standard OLTP tests • Standard tests help to • Standard OLTP tests include • SysBench has standard hardware tests • I use mostly OLTP for troubleshooting 9

26.Custom SysBench Tests • True power for troubleshooting • You can imitate any load • Supports Lua scripting 10

27.SysBench: Test Structure • thread init • Initialize thread 11

28.SysBench: Test Structure • thread init • set vars • Set user variables 11

29.SysBench: Test Structure • thread init • set vars • prepare • Prepare test 11