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命中率.