Personal blog of Yzmir Ramirez

Welcome to the final – but most important – episode in the MySQL QA Series.

In it, I present my vision for all MySQL Quality Assurance – for all distributions – worldwide.

Episode 13: A Better Approach to all MySQL Regression, Stress & Feature Testing: Random Coverage Testing & SQL Interleaving

1. pquery Review
2. Random Coverage Testing
3. SQL Interleaving
4. The past & the future

Presented by Roel Van de Paar. Full-screen viewing @ 720p resolution recommended

Interested in the full MySQL QA Series?

The post MySQL Quality Assurance: A Vision for the Future by Roel Van de Paar (Final Episode 13) appeared first on MySQL Performance Blog.

MySQL QA Episode #1: Bash/GNU Tools & Linux Upskill & Scripting Fun

This episode consists of 13 parts, and an introduction. See videos below

In HD quality (set your player to 720p!)

Introduction

Part 1: echo, ls, cp, rm, vi, cat, df, du, tee, cd, clear, uname, date, time, cat, mkdir

Part 2: find, wc, sort, shuf, tr, mkdir, man, more

Part 3: Redirection, tee, stdout, stderr, /dev/null, cat

Part 4: Vars, ‘ vs “, $0, $$, $!, screen, chmod, chown, export, set, whoami, sleep, kill, sh, grep, sudo, su, pwd

Part 5: grep, regex (regular expressions), tr

Part 6: sed, regex (regular expressions)

Part 7: awk

Part 8: xargs

Part 9: subshells, shells, sh

Part 10: if, for, while, seq, head, grep & grep -q, sleep, tee, read & more

Part 11: Arrays, lynx, grep, egrep, awk, redirection, variable, printf, while, wget, read

Part 12: Production scripting examples

Part 13: Gnuwin32, Gnuwin32 escaping & path name/binary selection gotcha’s, untar, unzip, gzip for Windows

If you enjoyed these video’s leave us a comment below!

The post MySQL QA Episode 1: Bash/GNU Tools & Linux Upskill & Scripting Fun appeared first on MySQL Performance Blog.

Welcome to the MySQL QA Training Series!

If you have not read our introductory blog post on pquery yet, I’d recommend reading that one first to get a bit of background. The community is enthuastic about pquery, and today I am happy to announce a full training series on pquery and more. Whether you are a Linux or MySQL newbie or a seasoned QA engineer, there is something here for you. From Bash scripting (see episode 1 below), to every aspect of the new pquery framework, it is my hope that you enjoy this series. If you do, please leave us a comment

Database quality assurance is not as straightforward as it may seem. It’s not a matter of point-and-click, but rather of many intertwined tools and scripts. Beyond that, due to the complexity of the underlying product, it’s about having an overall plan or vision on how to adequately test the product in every aspect.

Take for example the SELECT statement; it allows specifying about 30 different clauses or modifiers (GROUP BY, WHERE, ORDER, LIMIT, HAVING, …). Then, think further about what one could do inside these clauses, or inside subselects etc. The number of possible combinations (exhaustive testing) of all commands (and all formats and variations thereof) plus all mysqld options (nearly 500 of them) is for all intents and purposes infinite, and thus seemingly impossible to test.

In Episode 13, an approach is proposed which, in our view, adequately solves this test infinite-possibility coverage problem through the use of random spread coverage testing and sql interleaving.Knowing your Bash/Linux/Gnu scripting well is also an almost definite prerequisite to getting started with mysqld QA. Episode 1 in this series is over 3.5 hours of in-depth (from easy to advanced) training on many often-used Bash commands and topics. From ls to sed, from cp to xargs and from variables to arrays. Enjoy!

Without further ado, here are the planned upcoming episodes:

MySQL QA Episode 1: Bash/GNU Tools & Linux Upskill & Scripting Fun
MySQL QA Episode 2: Build a MySQL Server: Git, Compiling, Build Tools
MySQL QA Episode 3: Debugging: GDB, Backtraces, Frames, Library Dependencies
MySQL QA Episode 4: QA Framework Setup Time! percona-qa, pquery, reducer & more
MySQL QA Episode 5: Preparing Your QA Run: mtr_to_sql.sh and pquery-run.sh
MySQL QA Episode 6: Analyzing & Filtering: pquery-prep-red.sh, -clean-known.sh & pquery-results.sh
MySQL QA Episode 7: Reducing Testcases for Beginners: single-threaded reducer.sh
MySQL QA Episode 8: Reducing Testcases for Engineers: tuning reducer.sh
MySQL QA Episode 9: Reducing Testcases for Experts: multi-threaded reducer.sh
MySQL QA Episode 10: Reproducing and Simplifying: How to get it Right
MySQL QA Episode 11: Valgrind Testing: Pro’s, Con’s, Why and How
MySQL QA Episode 12: Multi-node Cluster Testing Using Docker
MySQL QA Episode 13: A Better Approach to all MySQL Regression, Stress & Feature Testing: Random Coverage Testing & SQL Interleaving

A short introduction on each episode:

As episodes are finished, the series titles above will be linked so it’s easy to check this page for updates.

Enjoy!

The post Free MySQL QA & Bash/Linux Training Series appeared first on MySQL Performance Blog.

Being a QA Engineer, how would you feel if you had access to a framework which can generate 80+ crashes – a mix of hitting developer introduced assertions (situations that should not happen), and serious unforeseen binary crashes – for the world’s most popular open source database software – each and ever hour? What if you could do this running on a medium spec machine – even a laptop?

The seniors amongst you may object “But… generating a crash or assertion is one thing – creating a repeatable testcase for the same is quite another.”

Introducing pquery, mtr_to_sql, reducer.sh (the pquery-enabled version), and more:

80+ coredumps per hour. Fully automatic testcase creation. Near-100% testcase reproducibility. C++ core. 15 Seconds run time per trial. Up to 20-25k lines of SQL executed per trial. CLI testcases. Compatible with sporadic issues. High-end automation of many aspects.

It all started when we realized how slow RQG really is. The Random Query Generator by Philip Stoev is a fantastic tool, and it has been greatly expanded over the years, but though it is a Swiss army knife in what you can do with it, when it comes to speed it is not the fastest. The Perl backend – combined with much random-ness – has made the system slow. At least when compared with the ultra-fast execution of C++ code.

I discussed things with Alexey Bychko, and before long pquery was born. The C++ core code of pquery is Alexey’s creation. It easily executes 20k random lines of SQL in 15-20 seconds, with full logging (including errors) turned on. Though the tool looks fairly simple when reviewing the code, it is incredibly powerful.

Now, one thing which people being introduced to QA for MySQL (or any other large software with many features/functions/options etc.) have to grasp is “random spread testing”. If your random spread (and the amount of samples) is large enough (read: ‘sufficient’), it is relatively easy to get a good “overall quality” estimate of your software by doing a few runs (taking usually around 1-3 days – or longer if various options are being tested).

So,we now had speed (pquery) and near-perfect testcase simplification/reduction & reproducibility (the new pquery adaption of reducer.sh) – but we needed one thing more: SQL which would cover… well… every area of mysqld. A fast framework without matching grammars is not worth much…

Converting the grammars from RQG seemed like a mammoth task – and we would really just be re-writing RQG in C. And creating a new SQL generator was an almost hopeless venture (I gave it a short try) – given the huge variety and complexity when constructing SQL statements.

I took the cheeky road. And it paid off. mtr_to_sql.sh was born.

The MTR testcases included (and delivered) with the MySQL server download contain individual tests for nearly every possible SQL syntax possible, including ones that – ought to – error out (but not crash). Not only that, there are specific MTR testcases for each feature, not to mention the many MTR testcases that were added to cover bug regression testing. Where there is smoke…

[roel@localhost 5.6]$ pwd
/bzr/5.6
[roel@localhost 5.6]$ find . | grep ".test$" | wc -l
3867
[roel@localhost 5.6]$ cat ~/percona-qa/mtr_to_sql.sh | grep BZR_PATH | head -n1
BZR_PATH="/bzr/5.6/mysql-test"
[roel@localhost 5.6]$ time ~/percona-qa/mtr_to_sql.sh
Done! Generated /tmp/mtr_to_sql.yy for RQG, and /tmp/mtr_to_sql.sql for pquery (SQL is indentical in both, but first is formatted for use with RQG)
real    0m20.150s
user    1m2.951s
sys     0m1.214s
[roel@localhost 5.6]$ cat /tmp/mtr_to_sql.sql | wc -l
107541

107.5K of high-quality SQL. Covering every possible functionality and buggy area out there. Free.

Let the testing begin!

I was quite dumbfounded when (after further tuning and scripting) we started seeing 50+, then 80+ cores per hour. Sig11’s (crashes), Sig6’s (asserts), server hangs, character set issues, error log errors and so on. Many crashes and issues in optimized code. Fun.

Our best weeks yet?

class="twitter-tweet" width="500">

Pquery update: 220+ bugs logged, of which 140+ in MySQL, 60+ in Percona, 10+ in TokuTek (with limited TokuTek testing) #mysql #percona #qa

— Roel Van de Paar (@RoelVandePaar) November 21, 2014

class="twitter-tweet" width="500">

Last week @rameshvs02 & @RoelVandePaar logged 70+ bugs. Today reducer.sh was updated w/ pquery functionality. Testcases on their way! #mysql

— Roel Van de Paar (@RoelVandePaar) October 27, 2014

All of the above can be done on commodity hardware, running a single server, running single-threaded SQL (single client) and with no special mysqld options activated.

Compare this to RQG. Even with combinations.pl running hundreds if not thousands of mysqld — option combinations, and with nearly-everything-is-random-sql, it still comes nowhere near even one tenth of that number/speed. And this is even when using high-end hardware, 8 simultaneous mysqld servers, up to 20-25 client threads and at times special grammar-mix tools like MaxiGen etc.

In preparation for the Twitter week mentioned above we started running 4-5 simultaneous pquery run’s (5x mysqld, still all single threaded; a single client per mysqld) in different shell screen sessions, controlled by cron jobs.

A whole set of automation scripts were quickly added to handle the huge influx in bugs (you can get all for free (GPLv2) at $bzr branch lp:percona-qa – see pquery*.sh files), and now you can quickly review a list of issues pquery discovered. For writing this article, I started a run and in it’s first hour it found exactly 85 crashes. Here is a report from around ~2h;

[roel@localhost 830147]$ ~/percona-qa/pquery-results.sh
================ Sorted unique issue strings (Approx 439 trials executed, 167 remaining reducer scripts)
btr0btr.ic line 143                               (Seen  31 times: reducers 123,124,135,150,159,164,16,173,175,178,179,18,196,199,224,22,238,245,286,310,319,324,366,388,38,401,67,73,78,88,9)
btr0btr.ic line 169                               (Seen   1 times: reducers 158)
btr0cur.cc line 769                               (Seen   1 times: reducers 304)
buf0buf.cc line 2738                              (Seen   2 times: reducers 113,257)
fsp0fsp.cc line 1899                              (Seen   5 times: reducers 145,174,409,69,85)
. is_set                                          (Seen  32 times: reducers 112,165,170,192,203,218,231,249,24,253,259,273,278,280,289,329,331,333,336,338,363,371,373,379,384,398,404,44,47,6,72,82)
.length % 4                                       (Seen   4 times: reducers 169,220,307,80)
m_lock .= __null .... thd->mdl_context.is_lock_owner.m_namespace, ...., ...., MDL_SHARED(Seen   3 times: reducers 297,403,86)
row0quiesce.cc line 683                           (Seen   1 times: reducers 97)
row0umod.cc line 338                              (Seen   1 times: reducers 357)
.slen % 2                                         (Seen  21 times: reducers 106,122,131,144,221,250,251,252,275,282,296,316,318,32,359,375,39,405,407,43,46)
.slen % 4                                         (Seen   5 times: reducers 103,382,76,7,81)
sort_field->length >= length                      (Seen   1 times: reducers 138)
timer == __null                                   (Seen  36 times: reducers 133,139,149,160,161,181,183,184,185,20,212,227,229,234,244,260,266,274,292,294,295,298,301,308,326,327,330,343,346,364,367,400,48,50,59,99)
.tlen % 2                                         (Seen   8 times: reducers 117,119,200,205,213,217,285,35)
.tlen % 4                                         (Seen   3 times: reducers 25,355,365)
trx0roll.cc line 264                              (Seen   1 times: reducers 40)
Z21mysql_execute_commandP3THD                     (Seen   4 times: reducers 182,237,291,393)
ZN13Bounded_queueIhhE4pushEPh                     (Seen   3 times: reducers 101,118,214)
ZN8Protocol13end_statementEv                      (Seen   4 times: reducers 211,410,42,61)
================

For these (standard by now) pquery runs, we use pquery-run.sh. It starts a server, executes and monitors the pquery binary, and then checks on the outcome:

[roel@localhost percona-qa]$ ./pquery-run.sh
[07:23:21] [0] Workdir: /sda/459689 | Rundir: /dev/shm/459689 | Basedir: /sda/Percona-Server-5.6.21-rel69.0-687.Linux.x86_64-debug
[07:23:21] [0] mysqld Start Timeout: 60 | Client Threads: 1 | Queries/Thread: 25000 | Trials: 100000 | Save coredump trials only: TRUE
[07:23:21] [0] Pquery timeout: 15 | SQL file used: /home/roel/percona-qa/pquery/main-new.sql
[07:23:21] [0] MYSAFE: --maximum-bulk_insert_buffer_size=1M --maximum-join_buffer_size=1M --maximum-max_heap_table_size=1M --maximum-max_join_size=1M --maximum-myisam_max_sort_file_size=1M --maximum-myisam_mmap_size=1M --maximum-myisam_sort_buffer_size=1M --maximum-optimizer_trace_max_mem_size=1M --maximum-preload_buffer_size=1M --maximum-query_alloc_block_size=1M --maximum-query_prealloc_size=1M --maximum-range_alloc_block_size=1M --maximum-read_buffer_size=1M --maximum-read_rnd_buffer_size=1M --maximum-sort_buffer_size=1M --maximum-tmp_table_size=1M --maximum-transaction_alloc_block_size=1M --maximum-transaction_prealloc_size=1M --log-output=none --sql_mode=ONLY_FULL_GROUP_BY
[07:23:21] [0] Archiving a copy of this script (/home/roel/percona-qa/pquery-run.sh) in the workdir (/sda/459689) for later reference...
[07:23:21] [0] Archiving a copy of the infile used for this run (/home/roel/percona-qa/pquery/main-new.sql) in the workdir (/sda/459689) for later reference...
[07:23:21] [0] Generating datadir template (using mysql_install_db)...
[07:23:29] [0] Making a copy of mysqld used to /sda/459689/mysqld (handy for core file analysis and manual bundle creation)...
[07:23:29] [0] Storing a copy of ldd files for mysqld in same directory also...
[07:23:29] [0] Starting pquery testing iterations...
[07:23:29] [0] ====== TRIAL #1 ======
[07:23:29] [0] Ensuring there are no relevant servers running...
[07:23:29] [0] Clearing rundir...
[07:23:29] [0] Generating new workdir /dev/shm/459689/1...
[07:23:29] [0] Copying datadir from template...
[07:23:29] [0] Starting mysqld. Error log is stored at /dev/shm/459689/1/log/master.err
[07:23:29] [0] Waiting for mysqld (pid: 10879) to fully start...
[07:23:31] [0] Server started ok. Client:  /sda/Percona-Server-5.6.21-rel69.0-687.Linux.x86_64-debug/bin/mysql -uroot -S/dev/shm/459689/1/socket.sock
[07:23:31] [0] Starting pquery (log stored in /dev/shm/459689/1/pquery.log)...
[07:23:31] [0] pquery running...
./pquery-run.sh: line 150: 10879 Aborted                 (core dumped) $CMD > ${RUNDIR}/${TRIAL}/log/master.err 2>&1          <---- success!
[07:23:32] [0] Cleaning up...
[07:23:36] [0] pquery summary: 578/769 queries failed (24.84% were successful)
[07:23:36] [0] mysqld core detected at /dev/shm/459689/1/data/core.10879.1000.1000.6.1414700611.mysqld
[07:23:36] [1] Copying rundir from /dev/shm/459689/1 to /sda/459689/1
[07:23:36] [1] ====== TRIAL #2 ======
[...]

(The MYSAFE settings are some generic settings specifically suited for QA testing – kindly provided by Shane Bester)

Within the scripts many QA-goodies are already built-in: automated gdb query extraction from the core and the error log (each added 3x to the end of the sql trace to ensure maximum reproducibility), unique issue classification using bug-relevant strings, /dev/shm execution for optimal speed, etc. – it is all based/build on many years of mysqld QA experience.

If you can’t wait to spin off some I-crashed-mysqld (it’s easy you know…) bugs of your own, download the tools at lp:percona-qa ($bzr branch lp:percona-qa) and checkout the various pquery* scripts. Some shown in use above.

And, when you’re ready for slightly more serious feature testing – add whatever statements your feature uses to the sql file (or you may even replace it), plus the relevant mysqld options modifications to pquery-run.sh (i.e. set the $MYEXTRA string). You can also use sql-interleave.sh/sql-interleave-switch.sh to interleave new sql into the main sql file(s). This script is available in the pquery/ directory of the percona-qa branch mentioned above. Soon you will see fireworks.

Enjoy the show!

The post The future of MySQL quality assurance: Introducing pquery appeared first on MySQL Performance Blog.