pquery is an open-source (GPLv2 licensed) multi-threaded test program, written in C++, created to stress test the MySQL server (in any flavor), either randomly or sequentially, for QA purposes.
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 -help
(v1.0 only)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.
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.
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
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.
-march=native
and -mtune=generic
resulting in all of the registers and capabilities from the currently installed CPU being used.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.
$ 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) ...
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
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/
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;
-DSTRICT-CPU
cmake flag, which will then automatically have the best speed optimization for this CPU on this machine-DSTRICT-CPU
cmake flag (the default) and use the resulting binary on any CPU. As described this option may be somewhat slower (perhaps in the area of 2% - unconfirmed)First, take a quick look at pquery --help, pquery --config-help, pquery --cli-help
to see available modes and options.
Option | Function | Example |
---|---|---|
--database | The database to connect | --database=test |
--address | IP address to connect to | --address=127.0.0.1 |
--port | The port to connect to | --port=3306 |
--infile | The SQL input file | --infile=./main-ms-ps-md.sql |
--logdir | Log directory | --logdir=/tmp/123 |
--socket | Socket file to use | --socket=/tmp/socket.sock |
--user | The MySQL userID to be used | --user=root |
--password | The MySQL user's password | --password=pazsw0rd |
--threads | The number of client threads to use | --threads=1 |
--queries-per-thread | The number of queries to randomly execute per thread | --queries-per-thread=100000 |
--verbose | Duplicates log to console when threads=1 | --verbose |
--log-all-queries | Log all queries yes/no | --log-all-queries |
--log-succeeded-queries | Log successful queries yes/no | --log-succeeded-queries |
--log-failed-queries | Log failed queries yes/no | --log-failed-queries |
--no-shuffle | Replay SQL shuffled (randomly) or not (sequentially) | --no-shuffle |
--log-query-statistics | Extended output of query result | --log-query-statistics |
--log-query-duration | Log query duration in milliseconds | --log-query-duration |
--test-connection | Test connection to server and exit | --test-connection |
--log-query-numbers | Write query numbers to log | --log-query-numbers |
--log-client-output | Log query output to separate file | --log-client-output |
[node0.ci.percona.com]
address = 192.168.10.1
user = test
password = test
database = test
# relative or absolute path so sql file
infile = pquery.sql
verbose = True
threads = 10
queries-per-thread = 100
# (NEW*) packet size is available only for config-based runs. see https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
max-packet-size = 32M
run = Yes
# Log all queries
log-all-queries = Yes
# Log successful queries
log-succeeded-queries = No
# Log failed queries
log-failed-queries = Yes
# Execute SQL randomly
shuffle = Yes
# Extended output of query result
log-query-statistics = Yes
# Log query duration in milliseconds
log-query-duration = Yes
# Log query output to separate file
log-client-output = No
# Write also query # from SQL file (to compare query and output for example)
log-query-numbers = No
[node1.ci.percona.com]
address = 127.0.0.1
user = test
password = test
infile = pquery.sql
shuffle = Yes
queries-per-thread = 150
run = No
[node2.ci.percona.com]
address = 127.0.0.1
user = root
password = 1q2w3e
infile = pquery2.sql
run = No
Note that logfiles (including SQL log files) are now overwritten. If SQL logs are appended to in old v2.0 versions, it will reduce issue reproducibility. To avoid this, simply use a new log file for each pquery run. The pquery framework (ref pquery-run.sh) already takes care of this automatically.