对MySQL配置参数 my.ini/my.cnf的详细解析

以下文章主要描述是对MySQL配置参数 my.ini/my.cnf详细解析我们主要是以例子演示方式来对MySQL配置参数 my.ini/my.cnf实际操作步骤进行介绍说明以下就是相关内容具体描述

1．获取当前配置参数

要优化MySQL配置参数首先要了解当前配置参数以及运行情况使用下列命令可以获得目前服务器使用配置参数:
复制代码 代码如下:

mysqld –verbose –help
mysqladmin variables extended-status –u root –p


在MySQL控制台里面运行下列命令可以获取状态变量值:
复制代码 代码如下:

mysql> SHOW STATUS; 如果只要检查某几个状态变量可以使用下列命令:
mysql> SHOW STATUS LIKE ‘[匹配模式]'; ( 可以使用%、?等 )


2．优化参数 参数优化基于个前提就是在我们数据库中通常都使用InnoDB表而不使用MyISAM表在优化MySQL时有两个MySQL配置参数是最重要即table_cache和key_buffer_size

table_cache

table_cache指定表高速缓存Cache大小每当MySQL访问个表时如果在表缓冲区 中还有空间该表就被打开并放入其中这样可以更快地访问表内容通过检查峰值时间状态值Open_tables和Opened_tables可以决 定是否需要增加table_cache值如果你发现 open_tables等于table_cache并且opened_tables在不断增长那么你就需要增加table_cache值了(上述状 态值可以使用SHOW STATUS LIKE ‘Open%tables'获得)注意不能盲目地把table_cache设置成很大值如果设置得太高可能会造成文件描述符不足从而造成性能 不稳定或者连接失败

对于有1G内存机器推荐值是128－256

案例1:该案例来自个不是特别繁忙服务器
复制代码 代码如下:

table_cache – 512
open_tables – 103
opened_tables – 1273
uptime – 4021421 (measured in seconds)


该案例中table_cache似乎设置得太高了在峰值时间打开表数目比table_cache要少得多

案例2:该案例来自台开发服务器

复制代码 代码如下:

table_cache – 64
open_tables – 64
opened-tables – 431
uptime – 1662790 (measured in seconds)


虽然open_tables已经等于table_cache但是相对于服务器运行时间来说opened_tables值也非常低因此增加table_cache值应该用处不大

案例3:该案例来自个upderperforming服务器

复制代码 代码如下:

table_cache – 64
open_tables – 64
opened_tables – 22423
uptime – 19538


该案例中table_cache设置得太低了虽然运行时间不到6小时open_tables达到了最大值opened_tables值也非常高这样就需要增加table_cache值

key_buffer_size

key_buffer_size指定索引缓冲区大小它决定索引处理速度尤其是索引读 速度通过检查状态值 Key_read_requests和Key_reads可以知道key_buffer_size设置是否合理比例key_reads / key_read_requests应该尽可能低至少是1:1001:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%'获得)

key_buffer_size只对MyISAM表起作用即使你不使用MyISAM表但是内部临时磁盘表是MyISAM表也要使用该值可以使用检查状态值created_tmp_disk_tables得知详情

对于1G内存机器如果不使用MyISAM表推荐值是16M(8-64M)

案例1:健康状况
复制代码 代码如下:

key_buffer_size – 402649088 (384M)
key_read_requests – 597579931
key_reads – 56188


案例2:警报状态

复制代码 代码如下:

key_buffer_size – 16777216 (16M)
key_read_requests – 597579931
key_reads – 53832731


案例1中比例低于1:10000是健康情况；案例2中比例达到1:11警报已经拉响

优化query_cache_size

从4.0.1开始MySQL配置参数提供了查询缓冲机制使用查询缓冲MySQL将SELECT语句和查询结果存放在缓冲区中今后对于同样SELECT语句(区分大小写)将直接从缓冲区中读取结果根据MySQL用户手册使用查询缓冲最多可以达到238％效率

通过检查状态值Qcache_*可以知道query_cache_size设置是否合理 (上述状态值可以使用SHOW STATUS LIKE ‘Qcache%'获得)如果Qcache_lowmem_prunes值非常大则表明经常出现缓冲不够情况如果Qcache_hits值也 非常大则表明查询缓冲使用非常频繁此时需要增加缓冲大小；

如果Qcache_hits值不大则表明你查询重复率很低这种情况下使用查询缓冲反 而会影响效率那么可以考虑不用查询缓冲此外在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲

和查询缓冲有关参数还有query_cache_type、 query_cache_limit、 query_cache_min_res_unitquery_cache_type指定是否使用查询缓冲可以设置为0、1、2该变量是 SESSION级变量

query_cache_limit指定单个查询能够使用缓冲区大小缺省为1M query_cache_min_res_unit是在4.1版本以后引入它指定分配缓冲区空间最小单位缺省为4K检查状态值 Qcache_free_blocks如果该值非常大则表明缓冲区中碎片很多这就表明查询结果都比较小此时需要减小 query_cache_min_res_unit

开启 2进制日志( Binary Log )

2进制日志包含所有更新数据语句其目是在恢复数据库时用它来把数据尽可能恢复到最后状态另外如果做同步复制( Replication )话也需要使用 2进制日志传送修改情况

开启 2进制日志需要设置参数log-binlog_bin指定日志文件如果不提供文件名MySQL将自己产生缺省文件名MySQL会在文件名后面自动添加数字索引每次启动服务时都会重新生成个新 2进制文件

此外使用log-bin-index可以指定索引文件；使用binlog-do-db可以 指定记录数据库；使用binlog-ignore- db可以指定不记录数据库注意是:binlog-do-db和binlog-ignore-db次只指定个数据库指定多个数据库需要多个语 句而且MySQL会将所有数据库名称改成小写在指定数据库时必须全部使用小写名字否则不会起作用

在MySQL中使用SHOW MASTER STATUS命令可以查看目前 2进制日志状态

开启慢查询日志( slow query log )

慢查询日志对于跟踪有问题查询非常有用它记录所有查过long_query_time查询如果需要还可以记录不使用索引记录下面是个慢查询日志例子:

开启慢查询日志需要设置参数log_slow_queries、 long_query_times、log-queries-not-using -indexeslog_slow_queries指定日志文件如果不提供文件名MySQL将自己产生缺省文件名 long_query_times指定慢查询阈值缺省是10秒log-queries-not-using-indexes是4.1.0以后引入 参数它指示记录不使用索引查询

配置InnoDB

相对于MyISAM表来说正确MySQL配置参数对于InnoDB表更加关键其中最重要参数是 innodb_data_file_path它指定表数据和索引存储空间可以是个或者多个文件最后个数据文件必须是自动扩充也只有最后 个文件允许自动扩充这样当空间用完后自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外数据例如:
复制代码 代码如下:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend


两个数据文件放在区别磁盘上数据首先放在ibdata1中当达到900M以后数据就放在ibdata2中旦达到50MBibdata2将以8MB为单位自动增长

如果磁盘满了你需要在另外磁盘上面增加个数据文件为此你需要查看最后个文件尺寸然后计算最接近整数(MB)然后手工修改该文件大小并添加新数据文件例如:假设ibdata2已经有109MB数据那么可以修改如下:

复制代码 代码如下:

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend
flush_time


如果系统有问题并且经常锁死或重新引导应将该变量设置为非零值这将导致服务器按flush_time 秒来刷新表高速缓存Cache用这种思路方法来写出对表修改将降低性能但可减少表讹误或数据丢失机会

Tags: 

延伸阅读