ib_logfile的最小大小

 由于my.cnf中没有给bi_logfile 的设置合理的size，导致mysql在启动的时候报错:
140107 14:45:44  InnoDB: Setting file ./ibdata1 size to 50 MB
InnoDB: Database physically writes the file full: wait...
140107 14:45:44  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 1 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: ib_logfiles are too small for innodb_thread_concurrency 8.
InnoDB: The combined size of ib_logfiles should be bigger than
InnoDB: 200 kB * innodb_thread_concurrency.
InnoDB: To get mysqld to start up, set innodb_thread_concurrency in my.cnf
InnoDB: to a lower value, for example, to 8. After an ERROR-FREE shutdown
InnoDB: of mysqld you can adjust the size of ib_logfiles, as explained in
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
InnoDB: Cannot continue operation. Calling exit(1).

从上面的报错中，我们可以很明显的看到ib_logfiles设置的size太小，而所有ib_logfile的总大小应该大于:200kb * innodb_thread_concurrency
在mysql中，innodb_thread_concurrency 默认值为8，所以ib_logfile的大小至少应该设置为200kb*8/2.

如何合理的设置ib_logfile的大小呢？如下是文档的答案: InnoDB Log File Size May Not Be Optimal
Discussion

The InnoDB storage engine uses log files that improve the overall performance and fault tolerance of MySQL databases. In order to achieve good results, 
innodb_log_file_size parameter has to be assigned with the proper value that corresponds to the available memory and other InnoDB parameters   such
as innodb_buffer_pool_size will need to be set. Non-optimal settings of innodb_log_file_size may cause unnecessary I/O disk activity or make the recovery
time longer. InnoDB writes to its log files on a rotating basis. All committed modifications that make the database pages in the buffer pool different from
the images on disk must be available in the log files in case InnoDB has to do a recovery. This means that when InnoDB starts to reuse a log file, it has to
make sure that the database page images on disk contain the modifications logged in the log file that InnoDB is going to reuse. In other words, InnoDB
must create a checkpoint and this often involves flushing of modified database pages to disk.

Solution

Set the innodb_log_file_size parameter in the configuration file (i.e., my.cnf or my.ini, depending on the server's system) to the size of buffer pool
(i.e., innodb_buffer_pool_size). Increase the log buffer as well (e.g., innodb_log_buffer_size = 8M). When this is done, restart the MySQL Server.

Do not set innodb_log_file_size bigger than 2Gb. The drawback of large log files is that the recovery time is longer.

在mysql中有两种记录数据库改变的日志，一个是在server layer的binlog，一个是在innodb engine的iblog。对于binlog而言，它记录的是mysql数据库逻辑的改变，是整个数据库，
而不仅仅是innodb engine。并且根据sync_binlog的设置来确定binlog的刷新策略:0 由master thread来控制刷新，每1s刷新一次。1, 每次commit都会刷新到磁盘(通过fysnc调用)。
2 每次commit都会刷新到操作系统，操作系统根据它的调度策略来刷新到disk。所以，在ms环境中，为1是最安全的。为0最少也会有1s的延迟。为2的话，在mysql crash掉，而操作
系统还OK的情况下，数据不会丢失。而对于 ib_logfile,同样由innodb_flush_log_at_trx_commit来控制它的刷新策略：0 表示每1s刷新一次，由master thread来完成。1 表示每次
commit都会调用fysnc刷新到disk。2 表示每次commit都会刷新到OS buffer,由OS来完成后续刷新到磁盘的工作。所以为1最安全，不丢数据。为0可能会丢失1s的数据。 为2在mysql
crash掉，而操作系统OK的情况下可能丢失部分数据。
了解日志所处的layer和相关的刷新机制，我们在来看看上面关于innodb log的具体作用：
对于innodb而言，由于是基于transaction的存储存储引擎，所以采用了相关的机制来保证transaction成功，在数据没有写到数据文件时，数据库crash掉的恢复机制，也就是常说的
ACID中D的要求，所以提供了日志保护机制，当然该日志是基于page的保护，是物理层面上的保护而不是逻辑层面(binlog是逻辑层面)。同时，由于有日志的保护，每次事物commit都
不必须把数据刷新到数据文件，只需要把相关事物的日志刷新日志文件即可。相比数据文件的写入，日志文件要快很多(数据文件采用random write,日志文件采用sequential write)。
所以，每次commit只要保重日志成功，就返回"commit ok"，提高了交互性，也提高了数据库的性能。也就是上述第一句话所说：
The InnoDB storage engine uses log files that improve the overall performance and fault tolerance of MySQL databases

当然，innodb的logfile还有其他更深入的细节，在这里就先不陈述了，先写到这里吧!