Storage Engine Evaluation

1
Storage Engine Evaluation

• MySQL 2009

2
Objectives

• Evaluation of storage engines in 6.0
• Build a set of test suites for future evaluations
• Additional benefit
• observe common issues across engines

3
Participants

• in alphabetical order
• Alexey Stroganov
• Amit K. Saha
• John Embretsen
• Nidhi Shrotriya
• Nirbhay Choubey
• Rune Humborstad (technical lead)
• Sanjay Manwani (project manager)

4
Tools

• Hand-crafted tests via mysqltest
• functional tests via MTR
• stress tests via mysql-stress-test.pl
• Random Query Generator
• stress tests created from a SQL grammar
• comparisons against MyISAM/InnoDB
• transactional consistency and recovery

5
Functional Testing

• Check what capabilities are advertised by the
  engine
• Test all field types and borderline values
• very important for SE that were stand-alone
• compare resultsets against MyISAM/InnoDB
• Exercise all table access methods
• full table scan, index scan, ranges, etc.

6
Transactional Testing

• Consistency zero sum transactions
• manually-crafted mysqltest stress test
• Random Query Generator test
• Repeatable Read
• in a RQG test, re-run each SELECT using various
  table access methods within the same transaction
• Durability
• crash the server and then compare the database to
  a reference source

7
Recovery Testing

• Crash server with kill -9 and then check
• recovery completes successfully
• no crashes, deadlocks, 100 CPU loops, suspicious
  entries in the logs
• database is physically consistent
• read the tables using various access methods
• database is logically consistent
• zero-sum property is preserved
• no transactions were lost

8
Stress Testing

• A general test via a hand-crafted, concurrent
  mysqltest suite
• A RQG workload
• Looking for
• crashes
• unexpected error messages
• thread-level deadlocks

9
Lessons Learned

• Storage Engines that were previously independent
  projects may need extra care
• mysql-specific data types, extreme values
• Transactional properties and recovery must not be
  taken for granted
• concurrent tests are required for testing
  Isolation
• thousands of crashes are required for recovery
• Thread-level deadlocks are widespread
• build yourself a deadlock detector/reporter