MySQL Forge

Resources for the MySQL Community

EngineIndependentTestSuite

Contents 1 Storage Engine Independent Testing 1.1 Functional Tests 1.1.1 Testing a single feature 1.1.1.1 Basic Tests 1.1.1.2 Availability 1.1.2 Enhanced Tests 1.1.2.1 Scope of testing for Insert, Select, Delete, Update 1.1.2.1.1 INSERT detail 1.1.2.2 Test setup 1.1.2.2.1 Availability 1.1.2.2.2 Test outcome verification 1.1.2.3 Table access methods to be tested 1.1.2.3.1 Notes 1.1.2.3.2 Access Patterns 1.1.2.3.2.1 Single-table access patterns 1.1.2.3.2.2 Join access patterns 1.1.3 Managing legitimate differences between storage engines using 'mysql-test-run.pl' 1.1.3.1 Option #1. Maintain a single .test and .result files for all engines. 1.1.3.2 Option #2. Maintain separate .test and .result files for each storage engine in a separate test suite. 1.2 Transactional Tests 1.3 Stress Tests 1.4 Using the existing test suite 1.5 Benchmarks 1.5.1 MySQL Bench 1.5.2 Sysbench 1.5.3 DBT*/TPC-* benchmarks

[edit] Storage Engine Independent Testing

[edit] Functional Tests

Creating the functional tests that verify the correct operation of the storage engine occupies the bulk of this project due to the sheer number of individual features that need to be tested. Maximum automation is key and for the purpose, a setup will be created that tests a single feature and this setup will be perfected before it is being used to test all features.

[edit] Testing a single feature

[edit] Basic Tests

• Database
  • CREATE
  • ALTER
  • DROP
  • RENAME
  • USE
  • SHOW
• Table
  • CREATE COLUMN
  • CREATE STRUCTURE
  • ALTER
  • DROP
  • RENAME
  • SHOW
  • DESCRIBE
  • TEMPORARY
  • PARTITION
• Index
  • CREATE
  • DROP
• Select
  • _rowid
  • DISTINCT
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT
  • UNION
• Join
  • DEFAULT
  • INNER
  • CROSS
  • STRAIGHT
  • LEFT
  • LEFT OUTER
  • RIGHT
  • RIGHT OUTER
  • NATURAL LEFT
  • NATURAL LEFT OUTER
  • NATURAL RIGHT
  • NATURAL RIGHT OUTER
• Sub Select
  • Scalar
  • ANY
  • IN
  • SOME
  • ALL
  • EXISTS
  • NOT EXISTS
  • Correlated
  • FROM
  • Error
• Insert
  • VALUES
  • UNIQUE CONSTRAINT
  • SET
  • SELECT
  • LOW_PRIORITY
  • HIGH_PRIORITY
  • DELAYED
  • IGNORE
  • ON DUPLICATE KEY
• Update
  • SET
  • Multi-table
  • LOW_PRIORITY
  • IGNORE
  • LIMIT
• Delete/TRUNCATE
  • Single Table
  • TRUNCATE
  • Multi-table
  • USING
  • LOW_PRIORITY
  • IGNORE
  • QUICK
  • LIMIT
• Replace
  • VALUES
  • SET
  • SELECT
  • LOW_PRIORITY
  • DELAYED
• LOAD DATA
  • LOAD DATA
• AUTO_INCREMENT
  • Initialization
  • Overflow
  • Reset by Truncate
  • LAST_INSERT_ID()
  • CREATE TABLE,
  • ALTER TABLE,
  • direct insert,
  • insert_id,
  • SQL_AUTO_IS_NULL

• PreparedStatement
  • PREPARE/EXECUTE
  • SELECT/UPDATE/INSERT/DELETE
  • DDL
• Stored Procedure
  • CREATE PROCEDURE
  • ALTER PROCEDURE
  • DROP PROCEDURE
  • CALL
• Stored Function
  • CREATE FUNCTION
  • ALTER FUNCTION
  • DROP FUNCTION
  • SELECT
  • INSERT
  • UPDATE
  • DELETE

• Trigger
  • CREATE/DROP/Using Trigger
  • BEFORE/AFTER, INSERT/UPDATE/DELETE

• Replication
• Crash Tests
  • Creating 1000 tables
  • Creating 1000 columns
  • Creating 64 indexes
• Table Maintenance
  • ANALYZE TABLE
  • BACKUP TABLE
  • CHECK TABLE
  • CHECKSUM TABLE
  • OPTIMIZE TABLE
  • REPAIR TABLE
  • RESTORE TABLE
• Foreign Keys
• SET
  • INSERT_ID
• Transaction
  • BEGIN,
  • START TRANSACTION,
  • START TRANSACTION WITH CONSISTENT SNAPSHOT,
  • SET AUTOCOMMIT=0/1
  • SAVEPOINT,
  • COMMIT
  • ROLLBACK
  • ROLLBACK TO SAVEPOINT
  • SET TRANSACTION ISOLATION LEVEL, XA
  • UNLOCK TABLES
• Concurrency
  • Independent inserts
  • CREATE TABLE SELECT
  • INSERT INTO SELECT
  • ALTER TABLE
  • UPDATE full table
• Character Set
  • CREATE DATABASE, CREATE TABLE
  • SET NAMES
• Function(GROUP BY Function)
  • AVG()
  • COUNT()
  • MIN()
  • MAX()
  • SUM()
• General
  • Date/Time

[edit] Availability

Test covering the above basic functionality are part of the tests included with the MySQL server.
Tests are located in the 'mysql-test' directory and can be executed using the 'mysql-test-run.pl' script.

perl ./mysql-test-run.pl --suite=engines/funcs --mysqld=--default-storage-engine=<engine>

For more details see the README file located in 'mysql-test/suite/engines'

[edit] Enhanced Tests

[edit] Scope of testing for Insert, Select, Delete, Update

The functional test suite should cover at least the following:

• All field types, nullability, index types;
• All borderline cases, e.g. empty strings, big integers, nulls, 000-00-00 dates, etc;
• All partitioning types;
• All basic SQL constructs (SELECT, INSERT, UPDATE, DELETE)
• All compound SQL constructs (INSERT ... SELECT , multiple-table update, etc.)
• All optimizer table access methods, including those new to 6.0 (MRR, LIMIT, etc.)(See Table Access Methods Below)
  • This includes cases where the query contains a condition that matches no rows, or that contains constants outside of the field type.

[edit] INSERT detail

• Insert numerous records across the entire range of acceptable values;
• Insert records at the minumum and maximum of values, and outside of the acceptable range;
• Insert nulls, empty strings, zeroes, zero dates, etc.;
• Insert records that would cause errors, oversize records, null records in a not-null field, duplicate records;
• Multi-row inserts;
• Insert with options - DELAYED , HIGH_PRIORITY, etc. (Rune says not required, so this will be saved for a later separate test)
• INSERT ... SELECT;
• INSERTS performed by triggers, stored procedures, events and prepared statements;

[edit] Test setup

• Create both partitioned and non-partitioned tables, with and without a PK;
• Create fields with all field types supported by the engine;
• Create indexed and un-indexed columns and unique and multi-part indexes;
• Create fields that are both null and not null, signed and unsigned, etc.
• Use each field type in separate rows as a PK, indexed column, non-indexed column, unique index, multi-part index, etc.
• Create triggers that write into a log table for every operation in the test.
• Populate the table with at least 10K records that would fill the table with enough data so that it spans multiple disk blocks. This will also create index trees with non-trivial depth and contents;

[edit] Availability

Test covering the above insert/update/delete/ functionality are part of the tests included with the MySQL server.
Tests are located in the 'mysql-test' directory and can be executed using the 'mysql-test-run.pl' script.

perl ./mysql-test-run.pl --suite=engines/iuds --mysqld=--default-storage-engine=<engine>

For more details see the README file located in 'mysql-test/suite/engines'

[edit] Test outcome verification

• Retrieve records back using various access methods, e.g. full table scan, range scan, PK lookup, etc.
• Retrieve the entire row via SELECT *, just the desired column or the desired column + some extra columns (this will exercise different table access methods)
• Retrieve the table data directly or use the value in a formula so that it is not passed directly from storage engine to the user but is moved around MySQL's internals. This is important in case the value retrieved was corrupted and doing operations on it would expose the corruption.
• Determine updatability of the records once they have been inserted
• Delete the records and check for successful deletion
• Run ANALYZE/CHECK/REPAIR TABLE if supported by storage engine

[edit] Table access methods to be tested

[edit] Notes

• In this context "index" means both the primary key and any other indexes that are defined on the table;
• The examples below use the "=" operator, however inequality operators should also be used;
• Multipart indexes should also be tested;
• More complex WHERE clauses involving both AND and OR should be tested.
• Indexes are triggered by "=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()"

[edit] Access Patterns

[edit] Single-table access patterns

• full table scan

• fulltext scan

• forward index scan

• backward index scan

• range access, ordered
• range access, backwards scan
• MRR/BKA scans
• index_merge/sort_union
• index_merge/intersection
• loose scan
• rnd_pos() scan

[edit] Join access patterns

• [eq]ref scan

 This is probably the most common pattern seen in join execution. ref access
 means chaotic index lookups all over the table. There are two variants -
 "Using index" and full scan. eq_ref access has slightly different pattern.

• "Range checked for each record"

 This is repeated alteration between range/index_merge access and full
 table/index scan. It is used when there's a join like this:

[edit] Managing legitimate differences between storage engines using 'mysql-test-run.pl'

Since each storage engine is different both in its capabilities and its behavior, the test framework must manage those differences.

[edit] Option #1. Maintain a single .test and .result files for all engines.

For each storage engine, we maintain a list of capabilities that this storage engine has. Any test that exercises capabilities a storage engine does not have will be skipped automatically.

This allows for a single .test and .result file for all storage engines. It however has the following drawbacks:

• Each test must exercise well-contained functionality. If a storage engine does not support VARCHARs, then it will still need to run all tests for all other data types. This means that it will not be possible to test all data types once by using a huge table and a huge monolithic test, or this test will be skipped altogether.
• Even with a well written test, legitimate differences between the storage engines will cause different .result files. This can be overcome either by using clever mysql-test-run.pl hacks or by forking those tests into engine-specific variants that are skipped for all other storage engines.

Each storage engine declares its capabilities when it loads into the server, so we can use the list of capabilities in handler.h as a starting point when creating our tests.

[edit] Option #2. Maintain separate .test and .result files for each storage engine in a separate test suite.

The .test files will be generated from a template, and the .result files will be generated to match the behavior of the engine.

Any issues with the engine will be worked out by editing the files in the engine-specific test suite, without affecting other storage engines. The downside of this approach will be the proliferation of .test and .result files that need to be reviewed manually for each engine.

[edit] Transactional Tests

Functional tests are needed to test the transactional behavior and ACID properties of the storage engines. They should implement the following:

• Be runnable as a functional test (via mysql-test-run.pl) and as a stress test (via mysql-stress-test.pl). The stress test, should provide sufficient concurrency, controlled number of deadlocks and sufficient number of actually committed transactions.
• Be capable of testing both locking (table and row level) and MVCC engines, including any possible hybrid situations.
• Test all ACID properties of each storage engine;
• Test all transaction isolation levels supported by the storage engine (including any non-standard ones provided via command-line options, eg --falcon-consistent-read); This would include tests that verify that relaxed transactional semantics (e.g. READ UNCOMMITED indeed are more concurrent than strict semantics (e.g. SERIALIZABLE). Note that transaction isolation is a per-transaction setting, not a server-wide setting, so a mix of isolation levels should be tested.
• Test transaction deadlocks and deadlock resolution, including XA, multi-engine transactions and multi-table transactions and deadlocks; The test must be compatible with both deadlock resolution via timeout and via a dependency graph;
• Test corner cases in transaction processing as described in the literature, including phantom updates, index gap locking and so forth.
• Test Crash recovery, i.e. durability.
• Test transactional behavior in the face of errors, rollbacks, conflicts, etc. Be able to handle different engines responding differently to those events (e.g. roll back the entire transaction or just the last statement).
• Test statement-level atomicity, e.g. multi-line inserts and updates in face of constraints (unique key, null values, etc.) with both Autocommit On and Off.
• Test SAVEPOINTS
• Test implicit COMMIT statements (e.g. CHANGE USER)
• Test statements with special transactional semantics, e.g. TRUNCATE TABLE, ALTER ONLINE, etc.

Note:

• Tests covering Repeatable Reads are part of the tests included with the MySQL server.
  
• Tests are located in the 'mysql-test/suite/engines/trx' directory and can be executed using the :'run_stress_rr.pl' script.
  
• The test is can be run against any transactional engine. However scripts need to be modified in order to support such engines (current scripts support only InnoDB).
• To modify the script for the transactional engine of your choice, follow the instructions listed in the README file located in 'mysql-test/suite/engines' under the rr_trx' section.

[edit] Stress Tests

Those will attempt to verify the operation of the storage engine under load

• Random Query Generator testing Random Query Generator

• crash-me script - Determines the ceiling of storage-engine capabilities (e.g. maximum number of indexes, etc.) in order to check whether a certain limit is unacceptably low

• Run a simple script to generate tables with large amounts of data, such as 1-billion row table

[edit] Using the existing test suite

It is possible to run the existing test suite as-is against a new engine. This will produce a massive ammount of false positives, whose investigation will take time. Usually crashes and suspicious error messages will show up first, while bad-data bugs will require more investigation.

Once the first pass has been complete, all tests that fail due to legitimate reasons can be complied for the new engine and any failures are likely regressions.

[edit] Benchmarks

The emphasis here will be on benchmarks that can be targeted against a new storage engine out of the box, without major modifications:

[edit] MySQL Bench

This benchmark tests the speed of primitive SQL operations. An interface is provided to do comparisons between storage engines and server versions

[edit] Sysbench

This is a simple transactional or non-transactional OLTP benchmark. A framework is available to store historical performance data. sysbench

[edit] DBT*/TPC-* benchmarks

Those benchmarks are generally difficult to run against a new storage engine. It takes time to set them up and a lot of effort to investigate all the issues that come up. So, running those benchmarks will be deferred until:

• The storage engine vendor confirms that a given benchmark is runnable against their storage engine;
• The storage engine vendor provides a tuned configuration under which the storage engine will perform best.

This arrangement achieves the following benefits:

• Pushes the bulk of the effort to run the benchmark and investigate the issues back to the storage engine vendor;
• If any speed comparisons are being made, each vendor participates with an optimal configuration.