Using INNODB in MySQL

While configuring MySQL for using innodb table handler, the following settings (in the my.ini file) have impact on performance:

INNODB Flush Log at Transaction Commit

innodb_flush_log_at_trx_commit

By default this option is turned on i.e set to 1. When enabled, the log is flushed to disk, and the modifications made by the transaction become permanent after a transaction commit. By flushing the log to the disk, recently transaction history will survive a database crash (the number of records effected depends on he size of the log buffer).

If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 to reduce disk I/O to the logs. Frequent flushing of transaction log will slow down the performance of insert, update and delete operations by as much as 50%.

INNODB Buffer Pool Size

innodb_buffer_pool_size

The size of the memory buffer InnoDB uses to cache data and indexes of its tables. this the less disk I/O is needed to access data in tables. On a dedicated database server parameter up to 80% of the machine physical memory size. Do not set it too large, though, competition of the physical memory may cause paging in the operating system.

The user should read more about it in the MySQL doc for newer options and settings.