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

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中文参考手册
MySQL 分发的,它被包含在 mysqlclient 库且允许 C 程序存取一 个数据库。...如果你计划拷贝字段值,这个长度信息对 优化也是有用的,因为你可以避免调用 ...
MySQL MySQL 5_1参考手册 4_ MySQL程序概述
MySQL 查询浏览器:该图形工具由 MySQL AB 提供,用于创建、执行以及优化对 · MySQL 数据库的查询。 · MySQL 移植工具包:该工具可以帮助你将计划和数据从其它...
MySQL中文参考手册--8.MySQL教程--8.4 创造并使用一个数
MySQL中文参考手册--8.MySQL教程--8.4 创造并使用一个数_IT/计算机_专业资料。...8.4.1 创建并选用一个数据库 如果在设置你的权限时,管理员为你创建了数据库,...
mysql 的常用命令
2、显示库中的数据表: use mysql; //打开库,学过 FOXBASE 的一定不会陌生...最好的 MYSQL 教程还是"晏子"译的"MYSQL 中文参考手册"不仅免费每个相 关网站...
更多相关标签: