1 / 11

The Random Query Generator

The Random Query Generator is a powerful tool that generates pseudorandom data and executes targeted pseudorandom queries. It validates query results, monitors server status, and reports crashes and other deviations.

johntjackson
Download Presentation

The Random Query Generator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TheRandomQueryGenerator • “The Colonoscopy ofDatabase Software” • Jim Starkey • Philip StoevMySQL SystemQA Team • http://forge.mysql.com/wiki/RQG

  2. Testing Coverage [Slutz, 1998] All Possible SQL Statements and States Customer Usage Scenarios BUGS Tests

  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 > _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: WHERE _field > _digit SELECT _field FROM _table WHERE `f2` > 9 SELECT `f1` FROM `t1`

  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` > INNR.`time_nokey`) WHERE INNR . `int_nokey` > INNR . `int_nokey` AND OUTR . `time_key` < '2001-04-01‘)AND OUTR . `int_nokey` > 1HAVING X <> '18:18:19‘ORDER BY OUTR . `varchar_nokey` , OUTR . `pk`;

  8. Testing MySQL @ 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@sun.com • Code: http://launchpad.net/randgen • Documentation: http://forge.mysql.com/wiki/RQG

More Related