Innodb performance talk part 2

innodb_buffer_pool_size - most important tunable, set to 70% of system memory, this is much more efficient then system cache.

innodb_additional_mem_pool - This is used for the dictionary and automatically grows so don't set to high. If you really don't use transactions, just use the default.


Putting Log Files on a different disk really doesn't make sense for INNODB. I too concur, none of my tests show any real improvement.

innodb_flush_logs_at_trx_commit: use 2 and 0 if using innodb like myISAM,
0 - flush the log per second to disk
1 - flush the log at commit expensive
2 - flush the log every second to the file system cache, it good if mysql crashes not good if the OS crashes.

innodb_flush_method *IMPORTANT* use O_DIRECT but can make writes a bit slower, and have a battery backed cache.
- O_OSYNC == O_DSYNC so don't worry - DSYNC would be cool because you could use innodb_flush_logs_at_trx_commit set to 2 and O_DSYNC to flush data to disk in parallel. This is the only flush method that can effect innodb log files. But in LINUX it can corrupt data so, INNODB commented it out in the code.

innodb_autoextend_increment - debate if increasing is good or not, I think so if it's done in the background.

innodb_support_xa - set to 0 reduces the work INNODB has to do, but binlogs can get out of sync.

innodb_max_purge_lag - it's mesured in the length of history of transactions, set it to 100000.