mysql Innodb性能参数设置

1. innodb_buffer_pool_size

#134217728 = 128M(默认值)

show GLOBAL VARIABLES like 'innodb_buffer_pool_size'

Innodb已使用的缓存"页Page"数量

show global status like 'Innodb_buffer_pool_pages_data';

Innodb全部缓存页数量

show global status like 'Innodb_buffer_pool_pages_total';

Innodb每页的长度

show global status like 'Innodb_page_size';

页面使用率

result = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

        val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%

        val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

set global innodb_buffer_pool_size = 10244444;

2，innodb_flush_log_at_trx_commit 

        在事务控制中,存在"事务区"来保证事务完整性,在事务提交以后,这些事务区的数据会写入到硬盘上,同时事务操作日志(log)也需要向硬盘中写入.这个参数就是用来控制何时写日志数据的.

        0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

        1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

        2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

三者比较

        当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

        当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。

        当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

        实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。

3，innodb_doublewrite 双写操作

同一份数据写入两次,保证数据存在一个副本,预防数据因为介质问题产生丢失

show global VARIABLES like  'innodb_doublewrite';

        innodb_file_per_table=1

        设置独立表空间文件 xxx.ibd

4，innodb_thread_concurrency 

        设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。

show global VARIABLES like  'innodb_thread_concurrency';