1 / 29

Falcon is not InnoDB

Falcon is not InnoDB. Kevin Lewis, Falcon Team Lead Ann Harrison, Falcon Team klewis@mysql.com , ann@mysql.com. Falcon is not InnoDB. Different design Different concurrency methods Different sweet spots Different quirks Different performance. Design differences.

giulio
Download Presentation

Falcon is not InnoDB

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. 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 ?

More Related