mysql配置性能优化
Database 2024/12/5 9:16:32 点击:不统计
%77w%77%2Ef%6F%72p%73%70%2Ec%6E
本文主要说的是myql 配置优化不包括sql语句优化。
sql语句优化参考 https://www.forasp.cn/html/2934.html
这样配置可以优化mysql性能。优化可以在配置文件,也可以在运行命令。区别在于在my.conf 中是永久配置;命令行是临时配置,重启后失效,可以做为临时调整。现在大部分的表是innodb ,对于myisam 暂时不做讨论。
1. 慢查询配置,在my.conf 中配置慢查询定义或者在命令行定义
参考 https://www.forasp.cn/html/2669.html
2. 链接配置 max_connections
(1)查询历史请求最大链接数
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
(2)当前设置最大连接数:
SHOW VARIABLES LIKE 'max_connections';
如果历史请求超过最大链接数 50% ,建议调大一点,根据实际情况
3. 优化长连接
(1)查看现在的 等待时间
SHOW VARIABLES LIKE 'wait_timeout';
(2)降低 wait_timeout ,可以是120秒
4.优化打开表数量
(1)查看代开表数量
SHOW GLOBAL STATUS LIKE 'Open_tables';
(2) 查看允许打开表数量
SHOW VARIABLES LIKE 'table_open_cache';
(3)查看缓存打开表命中率,越高越好。不能超过1.如果超过则需要调整
SHOW GLOBAL STATUS LIKE 'Opened_tables';
调整:
table_open_cache = 2000 数量根据实际情况 调整表缓存大小
table_open_cache_instances = 4 数量根据实际情况 可将表缓存分成多个实例,减少争用,提高并发性能。
5. 缓存线程的参数配置
(1) 查看当前配置
SHOW VARIABLES LIKE 'thread_cache_size';
(2)查看命中率
SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Threads_cached';
如果 Threads_created 很高,且 Threads_cached 频繁保持较低的值,说明 MySQL 正在频繁创建新线程,可能是 thread_cache_size 设置过低。
如果 Threads_created 很低,且 Threads_cached 很高,说明线程缓存设置合理,线程的重用效果良好。
根据实际调整。
6. 查询缓存配置 query cache
(1)查看当前配置
SHOW VARIABLES LIKE 'query_cache%';
query_cache_size:缓存大小。
query_cache_type:缓存类型,可能的值:
0(或 OFF):禁用查询缓存。
1(或 ON):默认启用查询缓存。
2(或 DEMAND):仅对带 SQL_CACHE 的查询启用缓存。
query_cache_limit:单个查询缓存结果的最大大小。
query_cache_min_res_unit:每次分配缓存的最小单位
(2)查看缓存使用情况
SHOW GLOBAL STATUS LIKE 'Qcache%';
Qcache_hits:命中缓存的查询次数。
Qcache_inserts:插入缓存的查询次数。
Qcache_lowmem_prunes:因内存不足而删除缓存的次数(高说明缓存不足)。
Qcache_free_memory:剩余的缓存内存。
启用qcache的场景:
大量相同的 SELECT 查询。
读多写少的环境。
数据变化不频繁的场景(如报告生成系统)。
禁用qcache的场景:
高并发写操作的环境。
动态查询较多的场景。
query_cache_size 是总的查询缓存大小。调整建议:
如果 Qcache_free_memory 过高,说明缓存未充分利用,可以适当减小 query_cache_size。
如果 Qcache_lowmem_prunes 过高,说明内存不足,需要增加 query_cache_size。
(3)限制缓存的查询结果大小
query_cache_limit 限制单个查询缓存的最大结果大小。默认值为 1MB,可根据查询结果的实际大小进行调整。
(4)优化内存分配单位
query_cache_min_res_unit 控制每次分配的最小缓存块大小。较大的值适合大查询,但可能导致内存浪费;较小的值适合小查询,但可能增加内存分配的开销。
MySQL 8.0 中,query_cache 已被移除。推荐的优化方案:
使用 InnoDB 缓存:通过优化 innodb_buffer_pool_size 来缓存数据和索引。
innodb_buffer_pool_size=16G 为每个缓存池大小 如果内存16G 可以设置8G 这个自行决定
innodb_buffer_pool_instances=4 有几个缓冲池实例,上面配置8G 4个实例,则每个2G 建议2G起
使用缓存层:如 Memcached 或 Redis,减少对数据库的直接查询。
使用查询优化:通过索引优化和减少冗余查询提高性能。
7 open_files_limit 参数配置
open_files_limit 设置是 MySQL 可同时打开的最大文件数。这是一个系统级的参数,它不仅限于数据库的表文件,还包括日志文件、索引文件等。如果设置太低,MySQL 可能会遇到文件句柄不足的问题;如果设置得太高,可能会浪费系统资源。
open_files 不应接近 open_files_limit
比例建议:通常,open_files 应该保持在 open_files_limit 的 80%-90% 以内,这样 MySQL 可以在不达到文件描述符限制的情况下自由操作
mysql> show global status like 'open_files';
mysql> show variables like 'open_files_limit';
8. 多核心服务器 多进程配置:
按照核心数 或者核心数*2 配置,默认不配置表示不限制
innodb_thread_concurrency = 32
9.
转www.载for网站制作学习asp必.cn究
转www.载for网站制作学习asp必.cn究
http://www.forasp.cn/
·上一篇:mysql同步更改从库relay_log位置 >> ·下一篇:数据库同步工具fink-cdc >>