Thursday, April 26, 2007

Innodb performance talk

This is a talk by Peter Z. from 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.


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

1 comment:

sexy said...



A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,av dvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX