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

mysql数据库调优参考手册


安装完新版的 mysql5.6 之后,在很少的连接数及数据的情况下,发现内存占用居然达到了 500M 左右,这 对小型服务器来说还是相对较高的。原因在于 mysql5.6 相比于前代的 GA 版本性能提升显著,但默认的缓存设 置对于小型站点并不合理。以下提供一些通过修改 mysql 配置文件中的参数以降低内存占用的思路: 首先找到 mysql5.6 的配置文件位置: ? ? Windows 系统在 C:\ProgramData\MySQL\MySQL Server 5.6 \my.ini Linux 系统在\etc\my.cnf。 performance_schema_max_table_instances 12500 table_definition_cache 1400 table_open_cache 2000 performance_schema_max_table_instances 600 table_definition_cache 400 table_open_cache 256

修改以下三个主要的关于缓存的参数可以起到比较明显的降低内存的效果,以下是 mysql5.6 默认的设置: ? ? ?

可以调成(根据实际情况自行调节): ? ? ?

修改完毕后重启 mysql5.6 服务,在同等情况下内存占用由原来的 400-500M 可以降到 100M 以内。重启 mysql 服务的命令是: Windows 系统: ? ? net stop mysqld(mysql 服务名) net start mysqld(mysql 服务名)

Linux 系统: ? ? service mysqld stop service mysqld start

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

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,对系统设置范围为小于 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。

Mysql 数据库有几个配置选项可以帮助我们及时捕获低效 SQL 语句: 1,slow_query_log 这个参数设置为 ON,可以捕获执行时间超过一定数值的 SQL 语句。 2,long_query_time 当 SQL 语句执行时间超过此数值时,就会被记录到日志中,建议设置为 1 或者更短。 3,slow_query_log_file 记录日志的文件路径及文件名。 4,log_queries_not_using_indexes 这个参数设置为 ON,可以捕获到所有未使用索引的 SQL 语句,尽管这个 SQL 语句有可能执行得挺快。


相关文章:
mysql数据库调优参考手册.doc
mysql数据库调优参考手册_计算机软件及应用_IT/计算机_专业资料。安装完新
mysql性能优化_图文.ppt
数据库切片(分布式),也考虑使 用相应缓存服务帮助 MySQL 缓解访问压力 服务优化...MySQL优化专区 ?MySQL参考手册:优化 MySQL函数 MySQL 函数 MySQL函数分为几种...
MySQL5.1性能优化方案.doc
MySQL5.1 性能优化方案 1. 平台数据库 1.1. 操作系统 Red Hat Enterprise ...具体实施参考 MySQL 手册。 需要注意的是查询在日志中只出现一次并不意味着它是...
mysql 官方用户手册中文版.pdf
MySQL 5.7 参考手册这是一个MySQL 5.7 的中文参考手册,翻译自官方文档,需要...“连接和断开服务器”,“查询”部分 2016/7/30 添加“创建和使用数据库”,...
比较全面的MySQL优化参考_教育指南_百度教育攻略.pdf
本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职...这三种设置值导致数据库的性能受到影响程度分别是:高、中、低,也就是第一个会...
云平台开发技术人员MySQL数据库知识参考手册实用.doc
云平台开发人员 MySQL 数据库知识 参考手册 云平台开发技术人员 MySQL 数据库...上介绍了 MySQL 字符集、存储引擎、schema 设计、SQL 优化、应用优化、安全防范...
云平台开发技术人员MySQL数据库知识参考手册.doc
云平台开发技术人员MySQL数据库知识参考手册 - 云平台开发人员 MySQL 数据库知识 参考手册 云平台开发技术人员 MySQL 数据库知识参考手册 内容简介 本文档组织结构: ...
01-MySQL 5.6:有史以来最佳版本全面解析 中文_图文.pdf
优化器: MRR、ICP、文件排序 优化器: BKA、新的 ...“MySQL 数据库 5.5 版可能是有史以来最好的 ...MySQL中文参考手册 56页 1下载券 MySQL 语言结构 ...
MySQL_5.5中文参考手册_图文.pdf
MySQL_5.5中文参考手册 - MySQL 5.1 Reference Manual MySQL 5.5参考手册 这是MySQL参考手册的翻译版本,关于MySQL参考手册,请访问dev.m...
MySQL 5.1参考手册.txt
手册采用的惯例 1.3. MySQL AB概述 1.4. MySQL数据库管理系统概述 1.4.1....优化 7.1. 优化概述 7.1.1. MySQL设计局限与折衷 7.1.2. 为可移植性...
InnoSQL参考手册.pdf
InnoSQL参考手册_计算机软件及应用_IT/计算机_专业...此外,由于 MySQL 数据库在经历 Sun、Oracle 公司...故对于固态硬盘的优化, 各数据库厂商还有很多问题...
叶金荣:Linux下的MySQL调优_图文.ppt
垂直/水平切分服务器/数据库、表 ? 开启MySQL复制,实现读、写分离 ? 在复制...参考&推荐: MySQL官方手册(英文在线) 高性能MySQL(第2版) 文档贡献者 yang...
MySQL 5.1参考手册.doc
演示如何使用 mysql 客户程序创造和使用一个简单的数据库,提供一个 MySQL 的...WHERE field1_index = '1' OR field2_index = '1' 该情形是已经优化过的...
MySQL优化.doc
Mysql 应用层面的优化本书若不讲解一章关于连接到 ...如果你能从缓存里读取数据,就 不要去连数据库了。...你可以按照 Apache 使 用手册里的说明,把你不需要...
MySQL中文参考手册--8.MySQL教程--8.4 创造并使用一个数.doc
MySQL中文参考手册--8.MySQL教程--8.4 创造并使用一个数_IT/计算机_专业资料。...8.4.1 创建并选用一个数据库 如果在设置你的权限时,管理员为你创建了数据库,...
Mysql 手册.doc
(大数据工程师学习路径) 第五步 MySQL 参考 手册中文版---MySQL 基本操作一、创建并使用数据库 1.创建并选择数据库使用 SHOW 语句找出服务器上当前存在什么数据...
MySQL服务器端参数详解和优化建议_图文.pdf
官方手册上建议专用的数据库服 务器,可考虑设置为物理内存总量的80%,但是个人...MySQL my.ini参数详解 2页 1下载券 MySQL优化参考方案建议 6页 免费 喜欢...
MySQL学习之数据库基本操作篇_图文.doc
MySQL学习之数据库基本操作篇 - 实验前准备: 实验前准备: 1、 安装 mysql 数据库,使用“添加/删除程序”组件,安装好 mysql 相关的组件,建议安 装 mysql 下面...
mysql c api manual 中文版_图文.pdf
Guides Expert Guides Other Docs Archives About MySQL 5.1参考手册 :: 25. ...数据库管理 6. 中的复制 7. 优化 8. 客户端和实用工具程序 9.语言结构 10...
MySQL MySQL 5_1参考手册 4_ MySQL程序概述.doc
MySQL MySQL 5_1参考手册 4_ MySQL程序概述_IT/计算机_专业资料。MySQL 数据...该图形工具由 MySQL AB 提供,用于创建、执行以及优化MySQL 数据库的查询...
更多相关标签: