Innodb performance talk

This is a talk by Peter Z. from www.mysqlperformanceblog.com and Heikki from INNOBASE i.e. Oracle.

Here is a list of things commonly known. The talk is well organized and concise.

Keep Primary keys small in innodb, like less then 16 bytes. Don't update primary keys since it will require all the indexes to change it's pointer.


Auto increments may limit scalability since generating it requires a full table lock per increment. There is a patch from INNODB but it will not make it into the tree anytime soon.


Multi Versioning:

Complements row level locking to get even better concurrency. READ COMMITTED is a good transaction isolation level, if you have very long transactions that are unpurged.

Try to avoid SERIALIZABLE - it's just not good.


Foreign Keys Performance:

May cause locks it certainly increases row level locking done, I just avoid them all together personally.

Transactions:

Restrict the number of Open Transactions, it performs best when with a limited number of simultaneous transactions. The reason multiple transactions can cause thrashing.


BEWARE of a very high number of tables:
Once openeded INNODB never removes the table from it's own table cache, we are talking about millions of tables.

innodb_locks_unsafe_for_binlog helps in 5.0

SELECT INTO OUTFILE + LOAD DATA INFILE is your own sort of row based replication :)


Next Key Locks (GAP Locks)

INNODB locks the rows it operates on and the "gap" between rows as well.
Prevents Phantom rows, or rows that are old in the MVCC.


Group Commit:

XA is broken in 5.0 and doesn't allow group commit to work. So watch out if using this and upgrading from 4.1