mysql配置文件my.ini详解

最新推荐文章于 2024-10-12 08:00:00 发布
woshibianhe
最新推荐文章于 2024-10-12 08:00:00 发布
阅读量1.2k
点赞数
CC 4.0 BY-SA版权
分类专栏： Mysql 文章标签： mysql cache buffer 数据库服务器 table
本文链接：https://blog.youkuaiyun.com/woshibianhe/article/details/7317544
Mysql 专栏收录该内容
3 篇文章
订阅专栏
本文详细阐述了针对MySQL数据库的优化配置策略，包括关键参数的设置与解释，旨在提升数据库性能与稳定性。重点关注了my.cnf文件中[mysqld]段落中的配置项，如back_log、key_buffer_size、query_cache_size等，并提供了内存大小与配置值的对应建议，以适应不同规模的数据库应用。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这里写的不错：http://robbin.javaeye.com/blog/461382

结合硬件配置的一份已经优化好的my.cnf进行说明：

# vi /etc/my.cnf

以下只列出my.cnf文件中[mysqld]段落中的内容，其他段落内容对MySQL运行性能影响甚微，因而姑且忽略
[mysqld]
port      = 3306
serverid  = 1
socket    = /tmp/mysql.sock

skip-locking      # 避免MySQL的外部锁定，减少出错几率增强稳定性。

skip-networking   # 开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！ 

skip-name-resolve # 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

back_log = 384 ########### 我的1G内存没有设置此项使用的默认值 50，目前观察还不错。
# 1. 指定MySQL可能的连接数量，当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。
# 2. back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。
# 3. 注意：试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

key_buffer_size  = 256M ########### 我设置的128M，目前观察还不错。
# 只对MyISAM表起作用，指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。尤其是索引读的速度。
# 1. 对于内存在4GB左右的服务器该参数可设置为256M或384M。
# 2. 另外一个估计key_buffer_size的办法　把你网站数据库的每个表的索引所占空间大小加起来看看, 以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大.
# 3. 一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的，通过检查状态值 Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。
以下的状态值都是本人(hexu.org)通过 Sqlyog 获得的实例分析:
    #> SHOW STATUS LIKE 'key_read%'
    ##> key_read_requests – 650759289
    ##> key_reads - 79112
    ##> 比例接近1:8000 健康状况非常好
# 4. 注意：该参数值设置的过大反而会是服务器整体效率降低！ 

query_cache_type  = 1 # 指定是否使用查询缓冲

query_cache_limit = 2M

query_cache_size  = 64M  ########### 我的1G内存设置为64M，目前观察还不错。
# 使用查询缓冲，MySQL将Select语句和查询结果存放在缓冲区中，今后对于同样的Select语句（区分大小写），将直接从缓冲区中读取结果。
# 1. 根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。 指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：
    #> SHOW VARIABLES LIKE '%query_cache%';
    #> SHOW STATUS LIKE 'Qcache%';
    # 1. 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小
    # 2. 如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在Select语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
    # 3. 如果Qcache_free_blocks的值非常大，则表明缓冲区中碎片很多

max_allowed_packet = 4M
thread_stack       = 256K

table_cache        = 256 ########### 我的1G内存设置为256，目前观察还不错。
# 指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
# 1. 对于有1G内存的机器，推荐值是128－256。
# 2. 通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。
# 3. 如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了。
    #> SHOW STATUS LIKE ’Open%tables‘;
    # 1. Open tables 256
    # 2. Opened tables 9046
    # 虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说,已经运行了20 天，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache
# 4. 注意：不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

sort_buffer_size   = 6M # 查询排序时所能使用的缓冲区大小。
read_buffer_size   = 4M # 读查询操作所能使用的缓冲区大小。
join_buffer_size   = 8M # 联合查询操作所能使用的缓冲区大小
# 注意：sort/read/join_buffer_size 三个参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。

myisam_sort_buffer_size = 64M
table_cache             = 512

tmp_table_size     = 256M

max_connections    = 768
# 指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。

max_connect_errors = 10000000

wait_timeout       = 10
# 指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。

thread_cache         32
# 没找到具体说明，不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的 

thread_concurrency = 8
# 该参数取值为服务器逻辑CPU数量×2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8

thread_cache_size  = 8
# 默认是 8 ，这个值表示可以重新利用保存在缓存中线程的数量, 当断开连接时:
# 1. 如果缓存中还有空间,那么客户端的线程将被放到缓存中,
# 2. 如果线程重新被请求，那么请求将从缓存中读取,
# 3. 如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,
# 4. 如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
# 5. 根据物理内存设置规则如下：
    # Mem: 1G  可以设置为 8
    # Mem: 2G  可以设置为 16
    # Mem: 3G  可以设置为 32
    # Mem: >3G 可以设置为 64 

ft_min_word_len = 4 ########### 我的设置为2
#被全文检索索引的最小的字长. 你也许希望减少它,如果你需要搜索更短字的时候. 注意在你修改此值之后,你需要重建你的 FULLTEXT 索引 

log-bin  = /home/mysql/logs/binlog/mysql-bin
# 如果你不需要记录2进制log 就把这个功能关掉,注意关掉以后就不能恢复出问题前的数据了，需要您手动备份，
# 1. 二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。
# 2. 另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。
# 3. 如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。
# 4. 此外说明:
    # 1. 使用log-bin-index可以指定索引文件；
    # 2. 使用binlog-do-db可以指定记录的数据库；
    # 3. 使用binlog-ignore- db可以指定不记录的数据库。
    # 注意第一：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。
    # 注意第二：MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。

log_slow_queries = /home/mysql/logs/slowlog/slow.query
# 指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。

long_query_time  = 2 ########### 我设置的如果查询时间大于 2s，就记录慢查询到log_slow_querys指的文件中

log_long_format
# 打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里

log-queries-not-using-indexes
# 打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里

skip-innodb  #去掉innodb支持 
更多设置可以查看：
1. http://flynndang.javaeye.com/blog/399374
2. http://robbin.javaeye.com/blog/461382
3. http://www.suibing.com/post/158/
4. http://www.falcon-monitor.com/blog/243.html