The Random Query Generator

1
TheRandomQueryGenerator

• The Colonoscopy ofDatabase Software
• Jim Starkey
• Philip StoevMySQL SystemQA Team
• http//forge.mysql.com/wiki/RQG

2
Testing Coverage Slutz, 1998
3
What is it?

• The Random Query Generator
• Generates (pseudo-)random data
• Executes targeted (pseudo-)random queries
• Validates query results and monitors server
  status
• Reports crashes and other deviations
• http//forge.mysql.com/wiki/RQG

4
Features

• Multi-platform- supports Linux, Solaris, Windows
  (native)?
• Fully automatic- integrated monitoring for
  unattended runs- provides pass/fail indication
  via exit() code and XML
• Highly customizable- tests all types of queries,
  tables and fields- plug-in components for
  validating the result
• Repeatable runs- for non-concurrent tests

5
The SQL Grammar

• Describes the queries to generate,YACC-style
• query
• SELECT _field FROM _table where LIMIT _digit
• UPDATE _table SET _field _digit where
• where
• WHERE _field gt _digit
• WHERE _field IS NULL
• Provides convenience functions
• _field , _table , _digit, etc.
• Supports Embedded Perl

6
Query Generation

• Walk the grammar tree and selectively take one of
  the branches at each fork in the road
• Once a sequence of tokens has been obtained,
  replace each with an appropriate value

query
select
where
SELECT
_field
FROM
_table
WHERE
_field
gt _digit
SELECT
f1
FROM
t1
WHERE
f2
gt 9
7
Sample Generated Query

• SELECT SUM(DISTINCT OUTR . varchar_nokey ) AS
  XFROM C AS OUTRWHERE OUTR . pk IN ( SELECT
  INNR . pk AS Y FROM BB AS INNR2 LEFT JOIN
  BB AS INNR ON (INNR2.datetime_nokey gt
  INNR.time_nokey) WHERE INNR . int_nokey gt
  INNR . int_nokey AND OUTR . time_key lt
  '2001-04-01)AND OUTR . int_nokey gt 1HAVING
  X ltgt '181819ORDER BY OUTR . varchar_nokey ,
  OUTR . pk

8
Testing MySQL _at_ home

• Describe the data you wish to operate on
• Describe the SQL queries that you will be running
  in a grammar
• Run a synthetic workload and monitor server
  behavior and performance

9
Testing storage engines

• Compare query results with a reference storage
  engine (MyISAM or InnoDB)
• Ready tests for transactional integrity-
  Atomicity, Isolation and Consistency- REPEATABLE
  READ- Durability and recovery
• Stress testing across a wide range of- engine
  configurations- row counts- field sizes-
  composition of the workload

10
Testing Recovery

• Recovery testing is performed at the end of
  every test- recovery is tested for free every
  time
• Kill the server (with kill -9)- or kill at
  important code points via instrumentation- more
  sadistic methods forthcoming, e.g. a power cut
• Restart the server or restore from
  snapshot/backup
• Validate the data- verify that the last
  transactions were durable- check that the
  database remains consistent- walk the tables
  back and forth using different methods- issue
  CHECK/ANALYZE/OPTIMIZE/REPAIR

11
Questions?

• Philip Stoev
• Contact philip.stoev_at_sun.com
• Code http//launchpad.net/randgen
• Documentation http//forge.mysql.com/wiki/RQG