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

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数据库优化设定
MYSQL数据库优化设定_计算机软件及应用_IT/计算机_专业资料。MYSQL数据库优化及...mysql 打开文件限制方法:把 set-variable=open_files_limit=10240 加到配置文件...
MySQL5.1性能优化方案
MySQL5.1性能优化方案_IT/计算机_专业资料。MySQL5.1性能优化方案MySQL...innodb_flush_log_at_trx_commit 如果比起持久性而更在意性能,可以通过设置 ...
MySql5.6性能优化
优化服务器硬件服务器的硬件性能直接决定着 MySQL 数据库的性能,硬件的性能瓶颈,直接决定 MySQL 数 据库的运行速度和效率。 需要从以下几个方面考虑: 1、 配置较...
Mysql for windows之my.ini优化
Mysql for windows 之 my.ini 优化 2013-4-29 1:08:54 1, 查看 MySQL 服务器配置信息 1. mysql> show variables; 2, 查看 MySQL 服务器运行的各种状态值...
mysql优化,部署,配置高级视频教程(1)
mysql优化,部署,配置高级视频教程(1)_计算机软件及应用_IT/计算机_专业资料。目前比较全面的 mysql 在线视频教程,有 mysql 的高可用架构、性能优化、读写分离等。...
MySQL性能优化
MySQL性能优化_计算机软件及应用_IT/计算机_专业资料。MySQL 性能优化 之个人见解 1、目的: 最大合理 的利用 MySQL 资源。 2、服务器参数: 服务器配置情况: 32G...
MYSQL-innodb性能优化学习总结
MYSQL-innodb 性能优化学习总结 BSS 测试部:newhackerman 数据库参数 MYSQL 数据库的参数配置一般在 my.ini 配置文件中修改/添加(部分参数也 可以用 set global ...
高并发web站点MySQL参数优化
转载】 转载】高并发 web 站点 MySQL 参数优化 (2010-11-10 10:46:03) 转载标签: 杂谈在高访问量的网站下,MySQL 自然成为瓶颈。因此 MySQL 的优化成为我们...
缓存+mysql优化
conf,要开启缓存配置模块。 具体配置代码: #开启缓存的配置 ExpiresActive On #...(); 二、mysql 优化概述前面我们讲页面静态化,memcache 是通过减少对 mysql ...
51CTO学院-LAMP部署-CentOS、Apache、MySQL、PHP配置优...
51CTO学院-LAMP部署-CentOS、Apache、MySQL、PHP配置优化视频课程_计算机软件及应用_IT/计算机_专业资料。系统整理并掌握PHP程序所遇到的环境部署的相关内容,使得运行...
更多相关标签: