MyISAM转innodb后的参数设置优化

转了MYSQL数据库引擎之后，相关的参数也要重新调整和优化

innodb_flush_logs_at_trx_commit=0 （为 2 好像更合理吧。）

 

该参数设定了事务提交时内存中 log 信息的处理。
1) =1 时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。 Truly ACID 。速度慢。 2) =2 时，在每个事务提交时，日志缓冲被写到文件， 但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。 3) =0 时， 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何 mysqld 进程的崩溃会删除崩溃前最后一秒的事务

抱怨 Innodb 比 MyISAM 慢 100 倍？那么你大概是忘了调整 innodb_flush_log_at_trx_commit  。默认值 1 的意思是每一次事务提交或事务外的指令都需要把日志写入（ flush ）硬盘，这是很费时的。特别是使用电 池供电缓存（ Battery backed up cache ）时。设成 2 对于很多运用，特别是从 MyISAM 表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒 flush 到硬 盘，所以你一般不会丢失超过 1-2 秒的更新。设成 0 会更快一点，但安全方面比较差，即使 MySQL 挂了也可能会丢失事务的数据。而值 2 只会在整个操作系统 挂了时才可能丢数据。

innodb_buffer_pool_size=2048M （这个数值，是否可以调整？当然，在内存不多的情况下， 2G 也是可以的）

如 果用 Innodb ，那么这是一个重要变量。相对于 MyISAM 来说， Innodb 对于 buffer size 更敏感。 MySIAM 可能对于大数据量使用默认的 key_buffer_size 也还好，但 Innodb 在大数据量时用默认值就感觉在爬了。 Innodb 的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用 Innodb ，可以把这个值设为内存的 70%-80% 。和 key_buffer 相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

这是 InnoDB 最重要的设置，对 InnoDB 性能有决定性的影响。默认的设置只有 8M ，所以默认的数据库设置下面 InnoDB 性能很差。在只有 InnoDB 存储引擎的数据库服务器上面，可以设置 60-80% 的内存。更精确一点，在内存容量允许的情况下面设置比 InnoDB tablespaces 大 10% 的内存大小。

innodb_data_file_path=innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend （格式似乎错误， innodb_data_file_path 出现了两次，而每个数据文件超过 10G 之后，再建立文件，有没有可能 10G 太大了？）

参数的名字和实际的用途有点出入，它不仅指定了所有 InnoDB 数据文件的路径，还指定了初始大小分配，最大分配以及超出起始分配界线时是否应当增加文件的大小。此参数的一般格式如下 :

path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]

例如，假设希望创建一个数据文件 sales ，初始大小为 100MB ，并希望在每次达到当前大小限制时，自动增加 8MB （ 8MB 是指定 autoextend 时的默认扩展大小 ). 但是，不希望此文件超过 1GB ，可以使用如下配置 :

innodb_data_home_dir =

innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB

如果此文件增加到预定的 1G 的限制，可以再增加另外一个数据文件 , 如下 :

innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/sales2:100M:autoextend:8M: max:2GB

要注意的是，在这些示例中， inndb_data_home_dir 参数开始设置为空，因为最终数据文件位于单独的位置 (/data/ 和 /data2/ ） . 如果希望所有 InnoDB 数据文件都位于相同的位置，就可以使用 innodb_data_home_dir 来指定共同位置，然后在通过 inndo_data_file_path 来指定文件名即可。如果没有定义这些值，将在 datadir 中创建一个 sales 。

innodb_log_file_size=256M （大多数推荐设置）

对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用 64M-512M ，具体取决于服务器的空间。

该参数决定了 recovery speed 。太大的话 recovery 就会比较慢，太小了影响查询性能，一般取 256M 可以兼顾性能和 recovery 的速度

注意：在重新设置该值时，好像要把原来的文件删除掉。

innodb_log_buffer_size=4M （也是推荐设置）

此参数确定些日志文件所用的内存大小，以 M 为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据 .MySQL 开发人员建议设置为 1 － 8M 之间

默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多 blob 数据，应该增大这个值。但太大了也是浪费内存，因为 1 秒钟总会 flush （这个词的中文怎么说呢？）一次，所以不需要设到超过 1 秒的需求。 8M-16M 一般应该够了。小的运用可以设更小一点。

innodb_flush_logs_at_trx_commit=2 （此处配置重复，前面的为 0 ，这里又配置为 2 ，是否设置为 2 对于我们提高速度更需要？） transaction-isolation=READ-COMITTED （内涵没有了解清楚，但如果不影响网站功能，这样也 OK ）

如果应用程序可以运行在 READ-COMMITED 隔离级别，做此设定会有一定的性能提升。 innodb_flush_method=O_DIRECT （推荐设置）

设置 InnoDB 同步 IO 的方式：
1) Default – 使用 fsync （）。 2) O_SYNC 以 sync 模式打开文件，通常比较慢。 3) O_DIRECT ，在 Linux 上使用 Direct IO 。可以显著提高速度，特别是在 RAID 系统上。避免额外的数据复制和 double buffering （ mysql buffering 和 OS buffering ）。 innodb_thread_concurrency=16 （如果满足这个值大约为 cpu 数 + 磁盘数） *2 ，那暂时 OK 的，如果我们不清楚物理 CPU 和磁盘够成，这个参数不设置，用默认的也 OK ）

用于限制能够进入 innodb 层的线程数

当进入 innodb 层调用 read_row/write_row/update_row/delete_row 时，会检查已经进入 innodb 的线程数： innodb_srv_conc_enter_innodb

如果已经满了，就会等待 innodb_thread_sleep_delay 毫秒尝试一次

如果再次失败，则进入到一个 FIFO 队列 sleep 。

当在 innodb 层完成操作后，会调用 innodb_srv_conc_exit_innodb 退出 innodb 层

当线程进入时，获得一段时间片 innodb_concurrency_tickets ，在时间片范围内，该线程就无需检测，直接进入 innodb 。

理论上讲，我们可以把 innodb_thread_concurrency 设置为（ cpu 数 + 磁盘数） *2 ，但这需要取决于具体的应用场景。

innodb_commit_concurrency ，用于限制在 innodb 层 commit 阶段的线程数，大多数情况下，默认值已经足够。