MySQL的INNODB引擎性能优化总结一

1.并发:数据迄今最高有多少个连接,登录mysql以后执行

show global status like ‘max_used_connections’;

show variables like ‘max_connections’;

一般情况,满足以下关系即可:

max_used_connections / max_connections * 100% <=85%

2.Innodb_buffer_pool:它对innodb引擎的mysql性能起着决定性的作用。

由于Innodb把数据和索引都缓存起来,官方推荐将其设置到 70-80% 的可用内存。

如果业务不多,并且不会暴增,无需把innodb_buffer_pool_size 设置的太大。

3.innodb_log_file_size:配置文件提示设置到innodb_buffer_pool_size的25%

它在高写入负载情况下很重要。这个值越大则性能相对越高,但可能会增加恢复时间。

4.innodb_flush_logs_at_trx_commit:

默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,

这相当耗费资源。如果设置为 2 ,也就是先把日志刷新到操作系统的缓存上,然后日志会

每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。

5.innodb_log_buffer_size:这个值指的是在高并发下,如果在1秒钟内日志缓存写满

了这个值,就立即从内存中刷新到磁盘的ib_logfilex上。这个值一般设置8M即可,根据

实际情况酌情处理。

6.临时表: 执行show global status like ‘created_tmp%’; 将出现

 

每次创建临时表,Created_tmp_tables增加,如果是在磁盘上创建临时表,

Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文

件文件数:当Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

时候,说明健康状态可以。如果大于25%,说明tmp_table_size和max_heap_table_size

的值小了,需要增加,将他们都增加到一定值,比如都设置为256M(其实已经非常大了),

如果Created_tmp_disk_tables / Created_tmp_tables * 100% 的值还降不下去,

说明sql语句可能有问题,强制使用了硬盘临时表。通过执行

show variables where Variable_name in (‘tmp_table_size’, ‘max_heap_table_size’);

可知道tmp_table_size和max_heap_table_size的大小。

7.open table(打开表):执行 show global status like ‘open%tables%’; 显示如下:

 

Open_tables 表示打开表的数量,Opened_tables表示打开过的表数量,如果Opened_tables数量过大,

说明配置中 table_cache值可能太小。将它改大些,比如512.

通过执行show global status like ‘open%tables%’;和show variables like ‘table_cache;

找到 Open_tables ,Opened_tables ,table_cache(5.1.3之后这个值叫做table_open_cache)的值,

它们满足以下关系即可。

open_tables / opened_tables * 100% >= 85%

open_tables / table_cache(5.1.3之后这个值叫做table_open_cache) * 100% <= 95%

8.进程使用情况: 执行show global status like ‘Thread%’;出现

threads_created表示创建过的线程数,如果发现Threads_created值过大的话,

表明 MySQL服务器一直在创建线程和销毁线程,这是比较耗资源的,可以适当增加

配置文件中thread_cache_size值(如果[mysqld]字段下没有这个项目,请自行添加)。

比如增加到16,32,或64根据情况而定。通过执行 show variables like ‘thread_cache_size’;

可确定当前配置文件中设置的thread_cache_size的值是多少。使用Threads_created/Connections

计算cache命中率.

 

 

Leave a Reply