What is pquery?

pquery is an open-source (GPLv2 licensed) multi-threaded test program created to stress test the MySQL server (in any flavor), either randomly or sequentially, for QA purposes. Given it's modern C++ core, it is able to maximise the physical server's queries per second (qps) rate. pquery is an acronym for 'parallel query'. Prebuilt pquery binaries (with statically linked client libraries) for Percona Server, MySQL Server, MariaDB, and WebScaleSQL are available as part of the pquery framework.

• pquery v1.0 was designed for single-node MySQL setup, and accepts command line options only. Ref pquery -help (v1.0 only)
• pquery v2.0 was designed for multi-node MySQL setups, and accepts command line options, as well as options from a configuration file in INI format. Ref pquery --config-help (v2.0 only)

Please note that v2.0 accepts the same CLI options as v1.0 does, for backwards compatibility. And, alike to v1.0, it can handle a single node setup in that mode. The recommended way to pass all options and params to pquery v2.0 is using a configuration file.

pquery v2.0 is under active development. v1.0 is no longer in use. All capabilities of pquery v1.0 are included in v2.0.

What is new in pquery v2.0?

pquery v2.0 can be used for single and multi-node (cluster, replication etc.) testing. It can send different SQL to each tested node. It is also possible to enable the SQL randomizer only for particular nodes. It also supports the same features, and is largely backwards compatible with v1.0 (some output file names and locations have changed).

One can now also specify if a pquery worker should be started for a given node by setting run = YES | NO option for such a node in the configuration file.

What is the pquery framework?

When the pquery binary is used in combination with the Bash scripted pquery framework and a medium spec QA server (Intel i7/16GB/SSD), a QA engineer can achieve 80+ mysqld crashes per hour. The pquery framework further offers automatic testcase creation, bug filtering, sporadic issue handling, true multi-threaded testcase reduction, near-100% bug reproducibility and much more. The pquery framework furthermore contains high quality SQL input files, and "already known bug" filter lists for Percona Server and MySQL Server. The pquery framework is also GPLv2 licensed, and available from GitHub here: https://github.com/Percona-QA/percona-qa

What is reducer.sh?

reducer.sh is a powerful multi-threaded SQL testcase simplification tool. It is included in the pquery Framework (https://github.com/Percona-QA/percona-qa), as https://github.com/Percona-QA/percona-qa/blob/master/reducer.sh. It is developed and maintained by Roel Van de Paar.

Any pquery success stories?

• In the first ~2 months of it's life, over 200 bugs were logged with Oracle, Percona and TokuTek, most with high quality short testcases.
• Early MySQL Server 5.7 versions, including RC1 & RC2, were tested with pquery in preparation for Percona Server 5.7. Many bugs, especially in RC1, were discovered & logged. Chapeau to the MySQL server team who triaged all bugs & resolved major bugs as can be seen in the 5.7.7 and 5.7.8 MYSQL server team notes.
• Early MySQL Server 8.0 and Percona Server 8.0 versions were tested with pquery. Many bugs were discovered & logged.
• Extensive testing of Percona XtraDB Cluster was achieved through a multi-node implementation of pquery. It's features are included in the publically available v2.0. Many bugs were found, logged and fixed. Chapeau to Ramesh V Sivaraman and Krunal Bauskar.
• A significant number of query correctness bugs were discovered in RocksDB

How to build pquery?

1. Install cmake >= 2.6 and C++ compiler >= 4.7 (gcc-c++ for RedHat-based, g++ for Debian-based), the development files for your MySQL version/fork, and potentially OpenSSL and AIO development files and/or other deps if needed.
2. Change dir to pquery
3. Run cmake with the required options, which are:

• PERCONASERVER - OFF by default, build pquery with Percona Server and Percona XtraDB Cluster support
• WEBSCALESQL - OFF by default, build pquery with WebScaleSQL support
• MYSQL - OFF by default, build pquery with Oracle MySQL support
• MARIADB - OFF by default, build pquery with MariaDB support.
• STATIC_LIB - ON by default, compile pquery using the MySQL | Percona Server | WebScaleSQL static client library instead of the dynamic one. For most distributions, the static library is included in standard downloads and definitely if you build MySQL/Percona Server yourself. Note however that for MariaDB, no static client library is provided with the standard MariaDB optimized package, so pquery will automatically compile MariaDB with a shared library (which has to be installed on the OS first, i.e. yum/apt-get install mariadb-devel). In other words, when using -DMARIADB=ON, this option is turned off by default.
• STRICT_CPU - OFF by default, compile pquery without processor optimization. This allows running the binary on all types of processors. If this is enabled, the binary is strictly bound to the CPU used at the time of building, and may therefore work only on the machine it was built on. Enable it to favor performance over portability. When enabled, pquery will be built with -march=native and -mtune=generic resulting in all of the registers and capabilities from the currently installed CPU being used.
• STRICT_FLAGS - ON by default, compile pquery with many compiler warnings enabled
• CMAKE_BUILD_TYPE - Release by default, other options are Debug, RelWithDebInfo, MinSizeRel. For more informaton see https://cmake.org/cmake/help/v3.0/variable/CMAKE_BUILD_TYPE.html
• ASAN - OFF by default, enables address sanitizer (for debugging pquery itself), available in GCC >= 4.8

4. If you have MySQL | Percona Server | WebScaleSQL | MariaDB installed to some custom location you may consider setting the additional flags to cmake: MYSQL_INCLUDE_DIR and MYSQL_LIBRARY. OR, you can set BASEDIR variable if you have binary tarball extracted to some custom place for fully automatic library detection (recommended).
5. The resulting binary will automatically receive an appropriate flavor suffix:

• pquery2-ms for MySQL
• pquery2-ps for Percona Server
• pquery2-ws for WebScaleSQL
• pquery2-md for MariaDB

Please note that only the MySQL client library will be linked statically if STATIC_LIB is set, all other required libraries (AIO, SSL, etc) will be linked dynamically.

Can you give an easy build example using an extracted Percona Server tarball?

$ cd pquery
$ ./clean-tree.sh  # Important note: this removes any local updates you may have made
$ cmake . -DPERCONASERVER=ON -DBASEDIR=/tmp/percona-server-5.7.21-20-linux-x86_64
$ make
$ sudo make install # If you want pquery to be installed on the system, otherwise the binary can be found in ./src
$ ./clean-tree.sh  # Ref above
$ ... build your other MySQL flavors/forks here in the same way, modifying the basedir and the servertype (both -D options) ...

pquery packages

There are currently no official RPM/APT etc. packages.

Automatic package creation is currently in alpha phase. You can generate a simple package using CPack:

$ cpack -G RPM
CPack: Create package using RPM
CPack: Install projects
CPack: - Run preinstall target for: pquery
CPack: - Install project: pquery
CPack: Create package
CPackRPM: Will use GENERATED spec file: /home/percona/pquery/_CPack_Packages/Linux/RPM/SPECS/pquery-ms.spec
CPack: - package: /home/percona/pquery/pquery-ms-2.0.0-Linux.rpm generated.
$

You can use RPM, DEB, TGZ, STGZ and other suitable targets for Linux and Unix

Any known build issues?

There is one known build issue, currently seen only when building using WebScaleSQL. If you see the following;

[ 50%] Building CXX object src/CMakeFiles/pquery-ws.dir/pquery.cpp.o
In file included from /home/percona/pquery/src/node.hpp:12:0,
                 from /home/percona/pquery/src/pquery.hpp:6,
                 from /home/percona/pquery/src/pquery.cpp:15:
/<your_basedir>/include/my_global.h:1197:27: fatal error: my_stacktrace.h: No such file or directory
 #include <my_stacktrace.h>
                           ^
compilation terminated.
make[2]: *** [src/CMakeFiles/pquery-ws.dir/pquery.cpp.o] Error 1
make[1]: *** [src/CMakeFiles/pquery-ws.dir/all] Error 2
make: *** [all] Error 2

Then simply copy the my_stacktrace.h file from the include directory of your source code copy (i.e. WebScaleSQL's source code) to the basedirectory used, e.g.

cp /source_code_dir/include/my_stacktrace.h /base_dir/include/

Any (build-related) runtime issues?

If pquery exits with exit code 4 (use echo $? at your command line to see the exit code directly after pquery terminates), or you see any other odd occurences when using pquery, please check dmesg log. If you see things like;

[16354204.300555] traps: pquery2-ps[24837] trap invalid opcode ip:42439f sp:7f90197fbe80 error:0 in pquery2-ps[400000+366000]
[16354210.748753] traps: pquery2-ps[25207] trap invalid opcode ip:42439f sp:7fa7cd7fbe80 error:0 in pquery2-ps[400000+366000]

You are using a binary compiled binary with strict CPU binding/optimization (ref the STRICT-CPU build flag above) while using it on a (likely older) machine which has a CPU incompatbile with the original build CPU.

To fix this, you can chose from 3 options;

1. Compile pquery locally on this machine with the -DSTRICT-CPU cmake flag, which will then automatically have the best speed optimization for this CPU on this mac