当前位置:首页 >> 计算机软件及应用 >>

MySQL配置优化


安装 MySQL 后,配置文件 my.cnf 在 /MySQL 安装目录/share/mysql 目录中,该目录中还包含 多个配置文件可供参考,有 my-large.cnf ,my-huge.cnf, my-medium.cnf,my-small.cnf,分 别对应大中小型数据库应用的配置。win 环境下即存在于 MySQL 安装目录中的.ini 文件。

下面列出了对性能优化影响较大的主要变量,主要分为连接请求的变量和缓冲区变量。 1. 连接请求的变量:

1)

max_connections

MySQL 的最大连接数,增加该值增加 mysqld 要求的文件描述符的数量。如果服务器的并发连 接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因 为如果连接数越多,介于 MySQL 会为每个连接提供连接缓冲区,就会开销越多的内存,所以要 适当调整该值,不能盲目提高设值。 数值过小会经常出现 ERROR 1040: Too many connections 错误,可以过’conn%’通配符查看当 前状态的连接数量,以定夺该值的大小。 show variables like ‘max_connections’ 最大连接数 show status like ‘max_used_connections’响应的连接数 如下: mysql> show variables like ‘max_connections‘; +———————–+——-+ | Variable_name | Value |

+———————–+——-+ | max_connections | 256 +———————–+——-+ mysql> show status like ‘max%connections‘; +———————–+——-+ | Variable_name | Value | |

+—————————-+——-+ | max_used_connections | 256| +—————————-+——-+ max_used_connections / max_connections * 100% (理想值≈ 85%) 如果 max_used_connections 跟 max_connections 相同 那么就是 max_connections 设置过低 或者超过服务器负载上限了,低于 10%则设置过大。

2)

back_log

MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求,这 就起作用。如果 MySQL 的连接数据达到 max_connections 时,新来的请求将会被存在堆栈中, 以等待某一连接释放资源,该堆栈的数量即 back_log,如果等待连接的数量超过 back_log,将 不被授予连接资源。 back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈 中。 只有如果期望在一个短时间内有很多连接, 你需要增加它, 换句话说, 这值对到来的 TCP/IP 连接的侦听队列的大小。 当观察你主机进程列表 (mysql> show full processlist) , 发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大 back_log 的值了。 默认数值是 50,可调优为 128,对于 Linux 系统设置范围为小于 512 的整数。

3)

interactive_timeout

一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是 28800,可调优为 7200。 2. 缓冲区变量 全局缓冲:

4)

key_buffer_size

key_buffer_size 指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过 检查状态值 Key_read_requests 和 Key_reads,可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests 应该尽可能的低,至少是 1:100,1:1000 更好(上述状态值可 以使用 SHOW STATUS LIKE ‘key_read%’获得)。 key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表,但是内部的临时磁盘表是 MyISAM 表,也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。 举例如下: mysql> show variables like ‘key_buffer_size‘; +——————-+————+ | Variable_name | Value |

+———————+————+ | key_buffer_size | 536870912 | +———— ———-+————+

key_buffer_size 为 512MB,我们再看一下 key_buffer_size 的使用情况: mysql> show global status like ‘key_read%‘; +————————+————-+ | Variable_name | Value |

+————————+————-+ | Key_read_requests| 27813678764 | | Key_reads | 6798830 |

+————————+————-+ 一共有 27813678764 个索引读取请求, 有 6798830 个请求在内存中没有找到直接从硬盘读取索 引,计算索引未命中缓存的概率: key_cache_miss_rate =Key_reads / Key_read_requests * 100%,设置在 1/1000 左右较好 默认配置数值是 8388600(8M),主机有 4GB 内存,可以调优值为 268435456(256MB)。

5)

query_cache_size

使用查询缓冲,MySQL 将查询结果存放在缓冲区中,今后对于同样的 SELECT 语句(区分大小 写),将直接从缓冲区中读取结果。 通过检查状态值 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_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。 举例如下: mysql> show global status like ‘qcache%‘;

+——————————-+—————–+ | Variable_name | Value |

+——————————-+—————–+ | Qcache_free_blocks | Qcache_free_memory | Qcache_hits | Qcache_inserts | Qcache_lowmem_prunes | Qcache_not_cached | 22756 | 76764704 | 213028692 | | 208894227 | 4010916 | | | | | | |

| 13385031

| Qcache_queries_in_cache | 43560 | Qcache_total_blocks | 111212

+——————————-+—————–+ mysql> show variables like ‘query_cache%‘; +————————————–+————–+ | Variable_name | Value |

+————————————–+———–+ | query_cache_limit | query_cache_min_res_unit | query_cache_size | query_cache_type | 2097152 | 4096 | |

| 203423744 | | ON | |

| query_cache_wlock_invalidate | OFF

+————————————–+—————+ 查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100% 如果查询缓存碎片率超过 20%,可以用 FLUSH QUERY CACHE 整理缓存碎片,或者试试减小 query_cache_min_res_unit,如果你的查询都是小数据量的话。 查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大,可适当减小;查询缓存利 用率在 80%以上而且 Qcache_lowmem_prunes > 50 的话说明 query_cache_size 可能有点小, 要不就是碎片太多。 查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服务器查询缓存碎片率=20.46%,查询缓存利用率=62.26%,查询缓存命中率=1.94%, 命中率很差,可能写操作比较频繁吧,而且可能有些碎片。 每个连接的缓冲

6)

record_buffer_size

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。 如果你做很多顺序 扫描,你可能想要增加该值。 默认数值是 131072(128K),可改为 16773120 (16M)

7)

read_rnd_buffer_size

随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。 进行排序查询时,MySQL 会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要 排序大量数据,可适当调高该值。但 MySQL 会为每个客户连接发放该缓冲空间,所以应尽量适 当设置该值,以避免内存开销过大。 一般可设置为 16M

8)
操作。

sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速 ORDER BY 或 GROUP BY

默认数值是 2097144(2M),可改为 16777208 (16M)。

9)

join_buffer_size

联合查询操作所能使用的缓冲区大小 record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size 为每个线 程独占,也就是说,如果有 100 个线程连接,则占用为 16M*100

10) table_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。内存在 4GB 左右的服务器该参数可设置为 256M 或 384M。

11) max_heap_table_size
用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量 支持动态改变,即 set @max_heap_table_size=# 这个变量和 tmp_table_size 一起限制了内部内存表的大小。如果某个内部 heap(堆积)表大小 超过 tmp_table_size,MySQL 可以根据需要自动将内存中的 heap 表改为基于硬盘的 MyISAM 表。

12) tmp_table_size
通过设置 tmp_table_size 选项来增加一张临时表的大小, 例如做高级 GROUP BY 操作生成的临 时表。如果调高该值,MySQL 同时将增加 heap 表的大小,可达到提高联接查询速度的效果, 建议尽量优化查询,要确保查询过程中生成的临时表在内存中,避免临时表过大导致生成基于 硬盘的 MyISAM 表。 mysql> show global status like ‘created_tmp%‘; +——————————–+———+ | Variable_name | Value |

+———————————-+———+ | Created_tmp_disk_tables | 21197 | | Created_tmp_files | Created_tmp_tables | 58 |

| 1771587 |

+——————————–+———–+ 每次创建临时表,Created_tmp_tables 增加,如果临时表大小超过 tmp_table_size,则是在磁 盘上创建临时表,Created_tmp_disk_tables 也增加,Created_tmp_files 表示 MySQL 服务创建 的临时文件文件数,比较理想的配置是: Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器 Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%,应该相当好了 默认为 16M,可调到 64-256 最佳,线程独占,太大可能内存不够 I/O 堵塞

13) thread_cache_size
可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有 空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。 通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。 默认值为 110,可调优为 80。

14) thread_concurrency

推荐设置为服务器 CPU 核数的 2 倍,例如双核的 CPU, 那么 thread_concurrency 的应该为 4; 2 个双核的 cpu, thread_concurrency 的值应为 8。默认为 8

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

3.

配置 InnoDB 的几个变量

innodb_buffer_pool_size 对于 InnoDB 表来说,innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM 表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL 数 据库服务器,最大可以把该值设置成物理内存的 80%。 根据 MySQL 手册,对于 2G 内存的机器,推荐值是 1G(50%)。

innodb_flush_log_at_trx_commit 主要控制了 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点,取值分别为 0、 1、2 三个。0,表示当事务提交时,不做日志写入操作,而是每秒钟将 log buffer 中的数据写入 日志文件并 flush 磁盘一次;1,则在每秒钟或是每次事物的提交都会引起日志文件写入、flush 磁盘的操作,确保了事务的 ACID;设置为 2,每次事务提交引起写入日志文件的动作,但每秒 钟完成一次 flush 磁盘操作。 实际测试发现,该值对插入数据的速度影响非常大,设置为 2 时插入 10000 条记录只需要 2 秒, 设置为 0 时只需要 1 秒,而设置为 1 时则需要 229 秒。因此,MySQL 手册也建议尽量将插入操 作合并成一个事务,这样可以大幅提高速度。 根据 MySQL 手册,在允许丢失最近部分事务的危险的前提下,可以把该值设为 0 或 2。

innodb_log_buffer_size log 缓存大小,一般为 1-8M,默认为 1M,对于较大的事务,可以增大缓存大小。 可设置为 4M 或 8M。

innodb_additional_mem_pool_size 该参数指定 InnoDB 用来存储数据字典和其他内部数据结构的内存池大小。缺省值是 1M。通常 不用太大,只要够用就行,应该与表结构的复杂度有关系。如果不够用,MySQL 会在错误日志 中写入一条警告信息。 根据 MySQL 手册,对于 2G 内存的机器,推荐值是 20M,可适当增加。

innodb_thread_concurrency=8

推荐设置为 2*(NumCPUs+NumDisks),默认一般为 8


相关文章:
高并发环境下MySQL软硬件配置优化_图文.pdf
通常只能使用mysqldump导出数据,然后再导入来解决这个问题.可以修改InnoDB为独立表 万方数据 第6期 范新民:高并发环境下MysQL软硬件配置优化 空间模式,即innodbfile...
MySQL配置优化.doc
MySQL配置优化 - 安装 MySQL 后,配置文件 my.cnf 在 /My
MYSQL数据库优化设定.doc
MYSQL数据库优化设定_计算机软件及应用_IT/计算机_专业资料。MYSQL数据库优化及...mysql 打开文件限制方法:把 set-variable=open_files_limit=10240 加到配置文件...
MySql配置和调优.doc
MySql配置调优_计算机软件及应用_IT/计算机_专业资料。入门级Mysql调优文档,欢迎下载 第一部分:安装 MySql 压缩包-查看默认存储引擎 1.复制 mysql-5.6.10-win32...
MySQL5.1性能优化方案.doc
MySQL5.1性能优化方案 - MySQL5.1 性能优化方案 1. 平台数据
MySql数据库配置优化.doc
MySql数据库配置优化 - 在做软件开发时,如果选用MySQL数据库的系统,当需要存储数据量大或数据操作很频繁时,MySQL对于性能的影响很大,也是关键的核心部分
MySQL数据库性能优化_图文.ppt
MySQL数据库性能优化_计算机软件及应用_IT/计算机_专业资料。给公司培训用的PPT,关于MySQL数据库性能优化方面,涉及:索引、参数配置等等 ...
MySQL 调优/优化的 100 个建议_教育指南_百度教育攻略.pdf
62、质疑使用通用的MySQL配置文件。 查询优化: 63、使用慢查询日志,找出
MySQL2介绍及性能优化_图文.ppt
MySQL2介绍及性能优化_计算机软件及应用_IT/计算机_专业资料。mysql 性能优化 ...? 如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时 MySQL都会把log...
MySQL优化笔记-整理版.doc
MySQL优化笔记-整理版 - High Performance Memcach
mysql配置和优化.doc
mysql配置优化 - mysql 配置优化 在安装 MySQL 之前,先来了解一下相关的配置文件 MySQL 配置文件 MySQL 发布的最新产品实际上超出了 Linux 所携带的版本。...
MySql常规优化配置.doc
MySql常规优化配置_电脑基础知识_IT/计算机_专业资料。MySql常规优化配置CPU: 2颗 Intel Xeon 2.4GHz 内存: 4GB DDR 硬盘: SCSI 73GB 下面,我们根据以上硬件配置...
mysql数据库调优参考手册.doc
mysql数据库调优参考手册_计算机软件及应用_IT/计算机_专业资料。安装完新版的 ...通过检查状态值 Qcache_*,可以知道 query_cache_size 设置是否合理(上述状态值...
mysql存储引擎介绍及优化_图文.ppt
mysql存储引擎介绍及优化_计算机软件及应用_IT/计算机_专业资料。MySQL介绍和优化...看到明显提升,当然,同样你可以SQL中提交 “SET AUTOCOMMIT = 0”来设置达到好...
mysql 优化.txt
mysql 优化 - MySQL优化 本文来自: IT运维专家网(LinuxTo
MySQL数据库优化总结.doc
MySQL数据库优化总结 - 对于一个以数据为中心的应用,数据库的好坏直接影响到
大型项目MySQL性能优化实例_图文.pdf
大型项目MySQL性能优化实例 - 大型移动项目 MySQL 数据库性能优化张翔 上海爱可生信息技术有限公司 MySQL高级技术顾问 项目背景 应用环境: 注册用...
Mysql状态变量性能调优.doc
Mysql 状态变量性能调优 Mysql 状态变量通过”show global status”(自 Mysql 上次启动以来的统计)获取,重要 的参数包括各种 SQL 执行频率,索引使用情况、锁资源使用...
通过show status 来优化MySQL数据库.doc
通过show status 来优化MySQL数据库 - 1, 查看 MySQL 服务器配置信息 Java 代码 1. mysql> show variables; 2, 查看 MySQL 服务...
MySQL性能优化缓存参数优化.pdf
MySQL性能优化缓存参数优化 - query_cache_size/query_cache_type (global) Query cache 作用于整个 MySQL Instance,主...
更多相关标签: