show engine innodb status;
  * can show average over period of time, make sure you look at large sample
  * order things appear could change in xtraDB
  * can be up to 64mb in size on v5.5
show engine innodb mutex;
  * not terribly useful, unless you're a developer
INFORMATION_SCHEMA
  * can look at locks

rare, but it is possible to get deadlock on insert

100 iops is average for 7200rpm disk
  * some variation, depending on inside/outside of platter
  * sequential much faster than random

should mount DB partition with noatime option

important to use battery-backed write cache on HW raid controller
  * this makes fsync's much faster

before commit succeeds -- data is in buffer pool (page marked dirty), in the innodb log, and in binary log. background process flushes dirty buffer pool pages to stable storage.
  * innodb will flush neighbor pages, to get more sequential writes

to figure out which pages need to be evicted there are two lists: the flush list and the LRU
log activities are assigned log sequence number (LSN)

=+===+=+==++===+==++++++===+++===========+++++

two rows must fit in page
  * single row can't be larger than 8kb
  * rows can't span pages?
  * unless you have blob/varchar/text, then it can overflow, which is into it's own 16kb page
  * an 800kb blob can take up to 1.5mb on disk, due to how it allocates extents

ORM that does varchar255's all over the place -- bad because will lead to overflow.
  * each overflowed varchar255 is in it's own page

mysql has compress/uncompress functions

buffer pool sizing
  * innodb metadata needs 5 - 10% on top of that
  * binary, relay logs need some free OS cache
  * maybe 80% of memory is too much for buffer pool

can set % of dirty pages
  * adjust if you are evicting clean pages too frequently
  * can sort of rate limit thread that flushes dirty pages
  * can see % clean and % dirty in show innodb status

lock wait timeout, last txn rolled back, might still be holding a lock
  * lock by innodb to ensure consistency with row-level replication (more like index-level replication)
  * foreign keys also do extra locks

primary key looks are fastest in innodb
  * secondary index lookups are more expensive
  * physical offset means nothing in ibd file
  * index is to primary key, then you have to go and find that?

don't create composite index using primary key, because you get that for free when you make secondary index
  * mk-duplicate-key-checker will find this situation
  * wastes tremendous amount of space in innodb

insert buffer can be up to 84% less i/o
  * will grow to half of buffer pool, then be free
  * percona server has knob to increase flush rate, so this never fills