Load Testing Tools

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