value == high price/performance

transaction processing == OLTP (sharding, availability, legacy applications, used by many applications)

sharding
  * it's easy to do (application programmers might disagree)
  * joins w/in shard are frequent and useful
  * aren't going to allow big queries in a shard, FB uses Hive for that
  * some fault-isolation benefits

schema change used to be a big source of downtime, FB wrote online schema change (OSC)

in innodb when you delete a row, it just sets a bit
  * when all txns finish, row is eligible to be removed in background by purge thread
  * prior to 5.5, this was on main thread, ran every 10s
  * in 5.5 it's on separate thread.
  * if you have long running txn, purge is blocked, single threaded requires disk i/o, can get behind
  * have servers that can get stuck, waiting on purge thread

insert buffer is not drained as fast as it fills
  * drain rate is 5% of innodb_io_capacity
  * fixed in xtraDB, patch pending for mysql 5.5

in file per table
  * when innodb table needs to grow, has global mutex
  * all reads are blocked until write finishes

stalls from opening innodb tables, fixed in v5.5

stalls from purge and undo block each other due to exclusive lock, fixed in v5.5
  * replace statement can generate a lot of undo

truncate table, lock open is held, has to remove file in file-per-table
  * freezes server
  * fixed in v5.5
  * also locks buffer pool, to remove pages from there
    * pretty much nothing else can get done
    * has to do LRU scan, can be slow for large buffer tool

drop table and lock open
  * new queries cannot be started
  * fixed in v5.5

can get stalls due to innodb_thread_concurrency
  * waking a specific thread, when you have 1000 sleeping threads, can be slow
  * did a hack to fix in facebook patch

high priority is to support very large databases, reduce iops

improvements
  * run innosim to confirm storage performance
    * good way to simulate innodb performance
    * good way to test storage
  * want to go to xtrabackup
    * mysqldump respnsoble for 50% of disk reads in prod
    * scans in key order, leads to more seeks
    * added single table restore to xtrabackup

search for "launchpad innosim", source code on launchpad

smaller pages (8k or 4k) better for some tables, but not all tables

big benefit from 4gb innodb log, percona supports unlimited size

can track stats per mysql account, can help you determine which parts of app are causing perf issue (if they have different user)

open problems
  * parallel replication
  * automate slave failover when a master fails
  * use innodb compression for lotp
  * multi-master replication with conflict resolution
  * max concurrent queries -- want 10k or more connections

when you run with compression in innodb, doubles rate of writes to innodb log
  * uses zlib, QuickLZ is better

Competition:
  * hbase, cassandra, mongodb are taking transactions
  * postgresql is improving

why nosql? does less, but does it better