Personal blog of Yzmir Ramirez

Welcome to MySQL QA Episode #5! In this episode we’ll be setting up and running pquery for the first time… and I’ll also trigger some actual bugs (fun guaranteed)! I’ll also introduce you to mtr_to_sql.sh and pquery-run.sh.

pquery-run.sh (the main wrapper around pquery) is capable of generating 80-120 MySQL Server crashes – per hour! See how it all works in this episode…

Full-screen viewing @ 720p resolution recommended

The post MySQL QA Episode 5: Preparing Your QA Run with pquery appeared first on MySQL Performance Blog.

Welcome to MySQL QA Episode 4! In this episode we’ll look into setting up our QA Framework: percona-qa, pquery, reducer & more.

1. All about percona-qa
2. pquery

$ cd ~; bzr branch lp:percona-qa

3. reducer.sh

$ cd ~; bzr branch lp:randgen
$ vi ~/randgen/util/reducer/reducer.sh

4. Short introduction to pquery framework tools

The tools introduced in this episode will be covered further in next two episodes.

Full-screen viewing @ 720p resolution recommended

To view the other episodes, you can watch the full series on YouTube:

Or checkout the MySQL QA Series Introduction which has an index & links to each episode!

The post MySQL QA Episode 4: QA Framework Setup Time! appeared first on MySQL Performance Blog.

After we released pquery to the community, and as we started logging bug reports with pquery testcases, it quickly became clear that pquery binaries with statically compiled-in client libraries would be of great convenience, both for ourselves and for the community.

(If you haven’t heard about pquery yet, read the pquery introduction blog post, come and join the pquery introduction lightning talk at Percona Live (15 April just around 6PM in Hall A), or keep an eye out for some of the upcoming episodes in the MySQL QA Series.)

While we were in the process of creating these binaries (which turned out to be not as straighforward as we thought it would be), we also disovered a rather significant memory de-allocation bug in pquery, which would at times have caused pquery to crash (segfault). I want to especially thank Sergei for helping get the client libraries compiled into pquery, as well as fixing the segfault memory de-allocation bug and some other problems we found, and Ramesh who helped with binary testing and debugging.

I also want to thank Sveta (previously at Oracle, now at Percona), Umesh (Oracle), and Satya Bodapati (Oracle) for not giving up too easily when we logged some bug reports with dynamically linked (i.e. client libs not included) and at times failing pquery binaries!

Now, the pquery binaries with statically included client binaries are finally ready! We have pquery-ps (with static Percona Server 5.6 client libs), pquery-ms (with static MySQL 5.6 client libs), and pquery-md (with static MariaDB 5.5 client libs). The pquery binaries can also be used to test  any other community solution or product. For example, we use it to test our beloved Percona XtraDB Cluster (PXC) using specially developed pquery+Docker+PXC scripts.

Things have also continued to evolve quickly in the pquery framework, as well as in the accompanying reducer.sh ($ bzr branch lp:randgen – available as randgen/util/reducer/reducer.sh), so check out the many updates now! To get it, $ bzr branch lp:percona-qa and start by having a look at pquery-run.sh

You may also like to checkout our latest pquery-reach.sh and pquery-reach++.sh – which are a wrapper around most of the major pquery framework tools. Not as straightforward to use and setup as pquery-run.sh (as it requires setup within the sub-scripts it uses…), but reviewing pquery-reach.sh will give you a good idea on how to setup pquery-run.sh and get into things.

Stay tuned for the upcoming MySQL QA episodes (link above), and you’ll soon be hunting bugs like Mr. Nuclear! (To meet (the nice) Mr. Nuclear, come and see our lightning talk at Percona Live!)

Enjoy!

The post pquery binaries with statically included client libs now available! 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.