RQG Performance Comparisons

Performance testing

The performance/perfrun.pl executes each query against a set of tw servers and reports the outcome.

• --input-directory contains the queries to be run, one query per file. Alternative sources for queries will be made available in the future;

• --dsn1 and --dsn2 specify the locations of the two servers being compared in the Perl DBI URL format. If the queries are not fully qualified, the DSNs must contain the database name. The username and the password are also provided via the DSN. For example:

• --output-file=... specifies a file where matching queries will be dumped in a form suitable for follow-up filtering with perfreport

• if no --filter is specified, all queries from the input will be reported;

Performance reporting

The performance/perfreport.pl script takes an ouput file created byperfrun and prints out its contents, possibly applying a filter in the process:

If no --filter is specified, all queries present in the input file are printed.

Filters

The queries reported by the system can be filtered out using perl-based filtering expressions. A filter can be applied during data collection, at which point it determines what information is stored in the output file and at

If you do not specify a filter, it is assumed that you want to output all queries.

The following variables can participate in filter expressions:

• Variables from SHOW SESSION STATUS, except:

  • variables that are not reset at the start of the query, which includes Com_*, Uptime, Opened_files and the like;

  • variables that relate to the operation of SSL encryption or the query cache;

For each MySQL status variable, 4 Perl variables are provided — the value of the MySQL variable from each server, their absolute difference and their ratio. For example, for theInnodb_rows_read MySQL variable, you can use Innodb_rows_read1,Innodb_rows_read2 , Innodb_rows_read_delta andInnodb_rows_read_ratio in your Perl filter expressions.

In addition to the MySQL status variables, the framework provides the following additional variables:

• $Execution_time{1|2|delta|ratio} reports the time the query took to run from start to end in seconds. The value is truncated at milliseconds in order to prevent exorbitant performance ratios from being reported on very fast queries;

• $Temperature can either be cold or warm depending on whether the first execution of the query is being processed, or the second.

• $Query

For example, the following command-line option:

Will only process or report queries that took more than 0.1 sec to execute on a warm server.

On-screen Reporting Format

The ASCII on-screen reporting format is as follows:

Only variables whose values are different between the two servers are reported. In this particular example, the query ran slower on 5.3.0-MariaDB with warm cache and caused twice as many Innodb_rows_read.

On-disk Data Storage Format

The on-disk storage format is Data::Dumper objects, wrapped in<![CDATA[ ... ]]> tags, without the file being a full-blown XML. The serialized representation is created byGenTest::QueryPerformanceDelta::serialize() and is read by using eval() in performance/perfreport.pl

See also:

_{This page is licensed: CC BY-SA / Gnu FDL}