对MySQL配置参数my.ini/my.cnf的详细解析
2023-06-13 09:14:24 时间
以下的文章主要描述的是对MySQL配置参数my.ini/my.cnf的详细解析,我们主要是以实例演示的方式来对MySQL配置参数my.ini/my.cnf的实际操作步骤进行说明,以下就是相关内容的具体描述。
1.获取当前配置参数
要优化MySQL配置参数,首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数:
复制代码代码如下:
1.获取当前配置参数
要优化MySQL配置参数,首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数:
mysqld?verbose?help
mysqladminvariablesextended-status?uroot?p
在MySQL控制台里面,运行下列命令可以获取状态变量的值:
mysql>SHOWSTATUS;如果只要检查某几个状态变量,可以使用下列命令:
mysql>SHOWSTATUSLIKE‘[匹配模式]";(可以使用%、?等)
2.优化参数
参数优化基于一个前提,就是在我们的数据库中通常都使用InnoDB表,而不使用MyISAM表。在优化MySQL时,有两个MySQL配置参数是最重要的,即table_cache和key_buffer_size。
table_cache
table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOWSTATUSLIKE‘Open%tables"获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。
对于有1G内存的机器,推荐值是128-256。
案例1:该案例来自一个不是特别繁忙的服务器
table_cache?512
open_tables?103
opened_tables?1273
uptime?4021421(measuredinseconds)
该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。
案例2:该案例来自一台开发服务器。
table_cache?64
open_tables?64
opened-tables?431
uptime?1662790(measuredinseconds)
虽然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:100,1:1000更好(上述状态值可以使用SHOWSTATUSLIKE‘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设置是否合理(上述状态值可以使用SHOWSTATUSLIKE‘Qcache%"获得)。如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;
如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_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。
开启二进制日志(BinaryLog)
二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外,如果做同步复制(Replication)的话,也需要使用二进制日志传送修改情况。
开启二进制日志,需要设置参数log-bin。log_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引,每次启动服务时,都会重新生成一个新的二进制文件。
此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore-db可以指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。
在MySQL中使用SHOWMASTERSTATUS命令可以查看目前的二进制日志状态。
开启慢查询日志(slowquerylog)
慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:
开启慢查询日志,需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。log_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中。一旦达到50MB,ibdata2将以8MB为单位自动增长。
如果磁盘满了,你需要在另外的磁盘上面增加一个数据文件。为此,你需要查看最后一个文件的尺寸,然后计算最接近的整数(MB)。然后手工修改该文件的大小,并添加新的数据文件。例如:假设ibdata2已经有109MB数据,那么可以修改如下:
innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend
flush_time
如果系统有问题并且经常锁死或重新引导,应将该变量设置为非零值,这将导致服务器按flush_time秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能,但可减少表讹误或数据丢失的机会。
相关文章
- 方案MySQL主从备份恢复方案:实现高可用方便稳定(mysql主从备份恢复)
- 重装MySQL:让LNMP升格新技能(lnmp重装mysql)
- 使用 .net 操作MySQL数据库(.net使用mysql)
- MySQL中的毫秒时间戳解析(mysql毫秒时间戳)
- 开启安卓之旅:连接MySQL编程(安卓编程连接mysql)
- 性能解析MySQL中变长字符串的性能优化(mysql变长字符串)
- 实现MySQL主主双机配置的简单步骤(mysql主主配置)
- MySQL安装后的快捷配置指南(mysql安装后配置)
- 在centos上安装MySQL服务器(centos装mysql)
- MySQL的主从库配置,实现数据的同步与备份(mysql主从库)
- MySQL数据库加密:实现安全保护(mysql数据库加密方式)
- MySQL查看数据库:掌握必备的SQL语句(mysql查看数据库语句)
- 优雅式PHP配置MySQL数据库(php 配置 mysql)
- MySQL连接指定端口号的方法(mysql 连接 端口号)
- 深入解析MySQL中的key索引使用方法(mysql中的key)
- 重装Linux系统,MySQL数据库重新开始(linux重装mysql)
- MySQL 时分秒格式:解析精妙之处(mysql时分秒格式)
- 解析MySQL中未包含的函数(mysql中不包含的函数)
- 二代MySQL尽善尽美的实现并发运行(2g mysql 并发)
- MySQL参数中的小括号解析(mysql中参数有括号)
- MySQL中的动态查询语句详解(mysql中动态查询语句)
- MySQL服务类型解析单机模式主从模式集群模式(mysql三种服务类型)
- MySQL中的三木运算,解析及其实现方式(mysql 三木运算)
- MySQL的三个日志类型解析和使用方法(mysql三大日志区别)
- 技术问题MySQL服务器频繁重启,无法启动的解决方法(mysql一直重启不了)
- MySQL下载详细教程轻松掌握安装及配置方法(mysql下载详细教程)
- MySQL如何使用两根竖线完成连接运算(mysql两根竖线)
- MySQL中如何给一列命名(mysql一列叫什么)