Falcon is not InnoDB

1. Falcon is not InnoDB Kevin Lewis, Falcon Team Lead Ann Harrison, Falcon Team klewis@mysql.com, ann@mysql.com

2. Falcon is not InnoDB • Different design • Different concurrency methods • Different sweet spots • Different quirks • Different performance

3. Design differences • InnoDB is modeled after Oracle Clustered storage Old versions stored in log Mixed MVCC and locking • Influenced by MySQL Statement based logging File per table / index Table name rules

4. Design differences • Falcon derives loosely from Rdb and InterBase Starkey design Pure MVCC Originally had no log – careful write for durability • Designed for large memory multi-processors Page cache plus record cache Finely grained multi-threading Synchronization (read/write) on shared structures

5. Concurrency • Both default to Repeatable Read • Neither is exactly Repeatable Read per ISO/SQL • Differ from each other in implementation InnoDB mixes MVCC and locking Falcon is pure MVCC • Differ from each other in quirks

6. Different sweet spots • True of all databases • If you design an application to make best use of Database A, moving to Database B will be hard • The more you know about A, the harder it will be to move to B • InnoDB is part of the MySQL family and will be into the future

7. Different quirks • Serializable is serializable • Everything else is quirky in different ways • Standard repeatable read Select returns the same results Plus any insert that gets committed Equivalent to pure locking read locks + write locks w/o predicate locks • Mix in MVCC and you get write anomalies

8. Falcon quirk 1 • On two tables • Insert into t1 (f1) select count (*) from t1M

9. Trans1: Insert into t1 (f1) select count (*) from t1 Trans2: Insert into t1 (f1) select count (*) from t1 Repeat mysql> select * from t1; +------+ | f1 | +------+ | 0 | | 0 | | 1 | | 1 | +------+ 4 rows in set (0.00 sec) Falcon quirk 1

10. Innodb makes transaction 2 wait for transaction 1’s commit, then stores the “right” values in the table Transaction 2 has an inconsistent view of data mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 0 | +----------+ mysql> insert into t1 (f1) select count(*) from t1; mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 1 | +----------+ mysql> select * from t1; +------+ | f1 | +------+ | 2 | +------+ Falcon quirk 1

11. Falcon quirk 2 • Exchange values between two tables using two transactions • Neither “sees” the other’s changes

12. mysql> select * from dinner_menu; +--------+-------+ | entree | price | +--------+-------+ | steak | 25.00 | +--------+-------+ mysql> select * from lunch_menu; +--------+-------+ | entree | price | +--------+-------+ | steak | 5.00 | +--------+-------+ Transaction1: mysql> update lunch_menu -> set price = -> (select price * 0.5 from -> from dinner_menu where -> dinner_menu.entree = -> lunch_menu.entree); Transaction 2: mysql> update dinner_menu -> set price = -> (select price * 0.5 -> from lunch_menu where -> lunch_menu.entree = -> dinner_menu.entree); Falcon quirk 2

13. mysql> select * from lunch_menu; +--------+-------+ | entree | price | +--------+-------+ | steak | 12.50 | +--------+-------+ mysql> select * from dinner_menu; +--------+-------+ | entree | price | +--------+-------+ | steak | 10.00 | +--------+-------+ InnoDB transaction 2 waits for transaction 1 to commit, then gets the “correct” result Falcon quirk 2

14. Select for update sees a different scope than normal select With consistent-read off, Falcon does the same mysql> select * from t1; +------+ | f1 | +------+ | 1 | +------+ mysql> select * from t1 for update; +------+ | f1 | +------+ | 5 | +------+ InnoDB quirk 1

15. InnoDB does implicit “select for update” in some subqueries Falcon does not mysql> select * from t1; +------+ | f1 | +------+ | 1 | +------+ mysql> create table t2 as select * from t1; mysql> select * from t2; +------+ | f1 | +------+ | 5 | +------+ InnoDB quirk 2

16. Falcon Architecture – short form Serial Log Record cache Front end Gophers Page Cache Back end Tablespace Files I/O Threads

17. Performance • Where we were last year • Performance peaks were good • Standard deviation excessive

18. Performance problem 1 • Problem Quick benchmarks had bad results • Symptom Auto-commit / select * was slow • Solution Reuse read-only transactions Reduce the cost of transaction startup Non-blocking scavenge

19. Falcon performance problem 2 • Problem DBT2 times degraded badly • Symptom Running a monitoring task improved performance • Solution First, slow down the front end Put a limit on the number of Active transactions that can be committed but not “write complete” Second, speed up the back end

20. Falcon’s back end • From Log to Page Cache - Gopher threads Add a pool (‘herd’) of Gophers threads • From Page Cache to disk Add a pool of I/O threads Direct IO Page Consolidation Thread Prioritization

21. Falcon performance problem 3 • Problem Index access (read and insert) was disappointing • Symptom Significant (>10%) time spent in locating index entry points • Solution Add Supernode lookup to each index page Compromise between density of prefix compression and speed of binary search

22. Falcon performance problem 4 • Problem Dbt2 tests were disappointing and erratic • Symptom Some tests were OK, many weren’t Standard deviation was large • Solution Hold the mutex in sync object to avoid missing a wake-up call between recognizing the need to wait and going to sleep.

23. Falcon performance April 2008 • CPU bound performance is better. • 10 warehouse DBT2 (900 Mb) • 16-way, 32GB Intel Caneland, 4 disk RAID 10

24. Falcon vs InnoDB - Dec, 2007

25. After Transaction Fix

26. No Thread Stalls !

27. Today

28. Record Cache / Page Cache • 100 Warehouses (9GB) with 2GB Falcon Cache

29. Falcon Feature Preview http://forge.mysql.com/wiki/Falcon_Feature_Preview • Questions ?