falcon for innodb users
TRANSCRIPT
Presented by,
MySQL & O’Reilly Media, Inc.
Falcon is not InnoDB
Kevin Lewis, Falcon Team Lead
Ann Harrison, Falcon Team
Falcon is not InnoDB
Different design Different concurrency methods Different sweet spots Different quirks Different performance
Design differences
InnoDB is modeled after OracleClustered storage
Old versions stored in log
Mixed MVCC and locking
Influenced by MySQLStatement based logging
File per table / index
Table name rules
Design differences
Falcon derives loosely from Rdb and InterBaseStarkey design
Pure MVCC
Originally had no log – careful write for durability
Designed for large memory multi-processorsPage cache plus record cache
Finely grained multi-threading
Synchronization (read/write) on shared structures
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
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
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
Falcon quirk 1
On two tables Insert into t1 (f1) select count (*) from t1M
Falcon quirk 1 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 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 2
Exchange values between two tables using two transactions
Neither “sees” the other’s changes
Falcon quirk 2mysql> 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 2mysql> 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
InnoDB quirk 1 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 2 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 |
+------+
Falcon Architecture – short form
Record cache
Page Cache
Serial Log
Tablespace Files
Gophers
Front end
Back end
I/O Threads
Performance
Where we were last year Performance peaks were good Standard deviation excessive
Performance problem 1
ProblemQuick benchmarks had bad results
SymptomAuto-commit / select * was slow
SolutionReuse read-only transactions
Reduce the cost of transaction startup
Non-blocking scavenge
Falcon performance problem 2
ProblemDBT2 times degraded badly
SymptomRunning a monitoring task improved performance
SolutionFirst, 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
Falcon’s back end
From Log to Page Cache - Gopher threadsAdd a pool (‘herd’) of Gophers threads
From Page Cache to diskAdd a pool of I/O threads
Direct IO
Page Consolidation
Thread Prioritization
Falcon performance problem 3
ProblemIndex access (read and insert) was disappointing
SymptomSignificant (>10%) time spent in locating index entry points
SolutionAdd Supernode lookup to each index page
Compromise between density of prefix compression and speed of binary search
Falcon performance problem 4
ProblemDbt2 tests were disappointing and erratic
SymptomSome tests were OK, many weren’t
Standard deviation was large
SolutionHold the mutex in sync object to avoid missing a wake-up call between recognizing the need to wait and going to sleep.
Falcon performance April 2008
CPU bound performance is better. 10 warehouse DBT2 (900 Mb) 16-way, 32GB Intel Caneland, 4 disk RAID 10
Falcon vs InnoDB - Dec, 2007
After Transaction Fix
No Thread Stalls !
Today
Record Cache / Page Cache
100 Warehouses (9GB) with 2GB Falcon Cache
Falcon Feature Preview
http://forge.mysql.com/wiki/Falcon_Feature_Preview
Questions ?