热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

深入解析MySQL并发参数优化与调整方法

本文详细探讨了MySQL并发参数的优化与调整方法,旨在帮助读者深入了解如何通过合理配置这些参数来提升数据库性能。文章不仅介绍了常见的并发参数及其作用,还提供了实际操作中的调整策略和最佳实践,适合希望提高数据库管理技能的技术人员阅读。

查询缓存优化

不建议使用,一般是在应用服务做缓存层,毕竟数据并发能力是有限的

概述


当开始Mysql的查询缓存,执行相同的sql语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存

查询流程


1、客户端发送一条查询给服务器
2、服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果,否则进入下一阶段
3、服务器端进行sql解析、预处理、再由优化器生成对应的执行计划
4、根据生成的执行计划,调用存储引擎的Api来执行查询
5、将结果返回给客户端--并将查询到的结果放到查询缓存中

查询缓存配置


查询数据库是否支持查询缓存(YES or ON)
show variables like 'have_query_cache';
查看数据库是否开启了查询缓存
show variables like 'query_cache_type';
OFF 或 0 查询缓存功能关闭
ON 或 1查询缓存功能打开,select的结果符合缓存条件即会缓存,否则不予缓存,显示指定SQL_NO_CACHE,不予缓存
DEMAND 或 2 查询缓存功能按需进行,显示指定SQL_CACHE的SELECT语句才会缓存;其他不予缓存
查看查询缓存的大小
show variables like 'query_cache_size';
查看缓存的状态
show status like 'Qcache%';
Qcache_free_blocks 缓存中可用的内存块数量
Qcache_free_memory 缓存中可用的内存量
Qcache_hits 查询缓存命中数
Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached 非缓存查询的数量(query_cache_type设置而无法缓存)
Qcache_queries_in_cache 查询缓存中注册的查询数
Qcache_total_blocks 查询缓存中的块总数
开启查询缓存
找到mysql的配置文件添加 my.cnf 添加query_cache_type=1
开启查询缓存结果是
query_cache_type=DEMAND
select SQL_CACHE id from table
开启查询缓存结果是
query_cache_type=ON
select SQL_NO_CACHE id from table

查询缓存失效的情况


sql语句不一致情况
查询sql中有不确定信息:
如 now(),current_date(),curdate(),curtime(),rand(),uuid(),database()
不使用任何表查询语句
查询 mysql ,infomation_schema或 performance_schema数据库中的表时,不会走查询缓存
在存储函数,触发器或事件的主体内执行的查询
表修改会删除查询缓存,如 insert,update,delete,drop...

内存管理优化

内存优化原则


将尽量多的内存分配给Mysql做缓存,要给操作系统和其他程序预留足够内存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存
排序区,连接区等缓存是分配给每个数据库会话(session)专用的,其默认的设置要根据最大的连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致无力内存耗尽

MyISAM内存优化


myisam存储引擎使用key_buffer缓存索引块,加速myisam索引的读写速度。对于myisam表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的io缓存
key_buffer_size=512M
key_buffer_size的值越大效率越高,建议至少将1/4可用内存分配给key_buffer_size
show variables like 'key_buffer_size'; 查询key_buffer_size的大小
red_buffer_size
如果经常顺序扫描myisam表,可以通过增大read_buffer_size的大小值来改善性能,但不能调的过大,因为key_buffer_size是每个session独占的,如果设置多个会造成资源浪费
red_rnd_buffer_size
经常需要排序的myisam表的查询,如果带有order by 子句的sql 适当增加red_rnd_buffer_size的值可以改善此类sql性能---也是session独享的,也不能设置过大

InnoDB内存优化


InnoDB用一块内存区做io缓存池,该缓存池不仅用来缓存innoDB的索引块,而且也用来缓存innoDB的数据块
inodb_buffer_pool_size
该变量决定了innoDB存储引擎和索引数据的最大缓存区大小,在保证操作系统及其他程序有足够内存可用的情况下,值越大缓存命中率越高,访问innoDb表需要的磁盘io就越少,性能也就越高
innodb_buffer_pool_size=521M
innodb_log_buffer_size
决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size大小,可以避免innodb在事务提交前执行不必要的日志写入磁盘io
innodb_log_buffer_size=10M
show variables like 'innodb_buffer_pool_size';
innodb_flush_log_at_trx_commit
控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,值分别为,0,1,2
innodb_thread_concurrency
设置innodb线程的并发数,默认为0表示不受限制,如果要设置建议跟服务器的CPU核心数一致或者CPU核心数的两倍
innodb_log_buffer_size
执行日志文件所用的内存大小,以M为单位
innodb_log_files_in_group
以循环的方式将日志文件写到多个文件中
read_buffer_size
mysql读入缓冲区大小,对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
mysql随机读的缓冲区大小

连接优化

max_connection


最大连接数,默认是151,linux一般可以支持500--1000具体根据服务器性能评估
show variables like 'max_connections';
查看最大连接数

back_log


超过最大连接数后最大的等待数量,存放在堆栈中(一般是50+(max_connections/5)),最大不超过900
show variables like 'back_log'

table_open_cache


控制所有sql语句执行线程可打开表缓存数量,而在执行sql语句时,每一个sql执行线程至少要打开1个表缓存,该参数的值应该根据设置的最大连接数max_connectons以及每个连接执行关联查询中涉及的表的最大数量来设定

thread_cache_size


为了加快连接数据库的速度,mysql会缓存一定数量的客户服务线程以备重用,通过参数thread_cache_size可控制Mysql缓存客户服务线程的数量

innodb_lock_wait_timeout


设置innoDB事务等待行锁的时间,默认是50ms,可以根据需要动态设置,对于需要快速反馈的业务系统,可以将行锁的等待时间调小,避免事务长时间挂起,对于后台运行的批量处理程序,可以将行锁的等待时间调大,以避免发生大的回滚操作
show variables like 'innodb_lock_wait_timeout';

日志

错误日志文件名称,用户记录当mysql启动和停止时,以及服务器在运行中发生发生任何验证错误时相关信息

log_bin


指定二进制日志文件名称,用于记录对数据库造成更改的所有语句

binlog_do_db binlog_ignore_db


指定将更新记录到二进制日志的数据库,其他所有没有显示指定的数据库更新将忽略,不记录在日志中

sync_binlog


指定多少次写日志后同步磁盘

general_log=1


是否开启查询日志记录

general_log_filefile_name(默认host_name.log)


指定查询日志文件名,用户记录所有的查询语句
查询日志文件名:slow_query_log_file=slow_query.log
mysqldumpslow slow_query.log;查看慢查询日志文件

slow_query_log=1


是否开启慢查询日志,1代表开启,0代表关闭

long_query_time=3


设置慢查询的时间,超过这个时间的查询语句才会记录日志

log_slow_admin_statements


是否将管理语句写入慢查询日志

查询缓存优化

不建议使用,一般是在应用服务做缓存层,毕竟数据并发能力是有限的

概述


当开始Mysql的查询缓存,执行相同的sql语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存

查询流程


1、客户端发送一条查询给服务器
2、服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果,否则进入下一阶段
3、服务器端进行sql解析、预处理、再由优化器生成对应的执行计划
4、根据生成的执行计划,调用存储引擎的Api来执行查询
5、将结果返回给客户端--并将查询到的结果放到查询缓存中

查询缓存配置


查询数据库是否支持查询缓存(YES or ON)
show variables like 'have_query_cache';
查看数据库是否开启了查询缓存
show variables like 'query_cache_type';
OFF 或 0 查询缓存功能关闭
ON 或 1查询缓存功能打开,select的结果符合缓存条件即会缓存,否则不予缓存,显示指定SQL_NO_CACHE,不予缓存
DEMAND 或 2 查询缓存功能按需进行,显示指定SQL_CACHE的SELECT语句才会缓存;其他不予缓存
查看查询缓存的大小
show variables like 'query_cache_size';
查看缓存的状态
show status like 'Qcache%';
Qcache_free_blocks 缓存中可用的内存块数量
Qcache_free_memory 缓存中可用的内存量
Qcache_hits 查询缓存命中数
Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached 非缓存查询的数量(query_cache_type设置而无法缓存)
Qcache_queries_in_cache 查询缓存中注册的查询数
Qcache_total_blocks 查询缓存中的块总数
开启查询缓存
找到mysql的配置文件添加 my.cnf 添加query_cache_type=1
开启查询缓存结果是
query_cache_type=DEMAND
select SQL_CACHE id from table
开启查询缓存结果是
query_cache_type=ON
select SQL_NO_CACHE id from table

查询缓存失效的情况


sql语句不一致情况
查询sql中有不确定信息:
如 now(),current_date(),curdate(),curtime(),rand(),uuid(),database()
不使用任何表查询语句
查询 mysql ,infomation_schema或 performance_schema数据库中的表时,不会走查询缓存
在存储函数,触发器或事件的主体内执行的查询
表修改会删除查询缓存,如 insert,update,delete,drop...

内存管理优化

内存优化原则


将尽量多的内存分配给Mysql做缓存,要给操作系统和其他程序预留足够内存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存
排序区,连接区等缓存是分配给每个数据库会话(session)专用的,其默认的设置要根据最大的连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致无力内存耗尽

MyISAM内存优化


myisam存储引擎使用key_buffer缓存索引块,加速myisam索引的读写速度。对于myisam表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的io缓存
key_buffer_size=512M
key_buffer_size的值越大效率越高,建议至少将1/4可用内存分配给key_buffer_size
show variables like 'key_buffer_size'; 查询key_buffer_size的大小
red_buffer_size
如果经常顺序扫描myisam表,可以通过增大read_buffer_size的大小值来改善性能,但不能调的过大,因为key_buffer_size是每个session独占的,如果设置多个会造成资源浪费
red_rnd_buffer_size
经常需要排序的myisam表的查询,如果带有order by 子句的sql 适当增加red_rnd_buffer_size的值可以改善此类sql性能---也是session独享的,也不能设置过大

InnoDB内存优化


InnoDB用一块内存区做io缓存池,该缓存池不仅用来缓存innoDB的索引块,而且也用来缓存innoDB的数据块
inodb_buffer_pool_size
该变量决定了innoDB存储引擎和索引数据的最大缓存区大小,在保证操作系统及其他程序有足够内存可用的情况下,值越大缓存命中率越高,访问innoDb表需要的磁盘io就越少,性能也就越高
innodb_buffer_pool_size=521M
innodb_log_buffer_size
决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size大小,可以避免innodb在事务提交前执行不必要的日志写入磁盘io
innodb_log_buffer_size=10M
show variables like 'innodb_buffer_pool_size';
innodb_flush_log_at_trx_commit
控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,值分别为,0,1,2
innodb_thread_concurrency
设置innodb线程的并发数,默认为0表示不受限制,如果要设置建议跟服务器的CPU核心数一致或者CPU核心数的两倍
innodb_log_buffer_size
执行日志文件所用的内存大小,以M为单位
innodb_log_files_in_group
以循环的方式将日志文件写到多个文件中
read_buffer_size
mysql读入缓冲区大小,对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
mysql随机读的缓冲区大小

连接优化

max_connection


最大连接数,默认是151,linux一般可以支持500--1000具体根据服务器性能评估
show variables like 'max_connections';
查看最大连接数

back_log


超过最大连接数后最大的等待数量,存放在堆栈中(一般是50+(max_connections/5)),最大不超过900
show variables like 'back_log'

table_open_cache


控制所有sql语句执行线程可打开表缓存数量,而在执行sql语句时,每一个sql执行线程至少要打开1个表缓存,该参数的值应该根据设置的最大连接数max_connectons以及每个连接执行关联查询中涉及的表的最大数量来设定

thread_cache_size


为了加快连接数据库的速度,mysql会缓存一定数量的客户服务线程以备重用,通过参数thread_cache_size可控制Mysql缓存客户服务线程的数量

innodb_lock_wait_timeout


设置innoDB事务等待行锁的时间,默认是50ms,可以根据需要动态设置,对于需要快速反馈的业务系统,可以将行锁的等待时间调小,避免事务长时间挂起,对于后台运行的批量处理程序,可以将行锁的等待时间调大,以避免发生大的回滚操作
show variables like 'innodb_lock_wait_timeout';

日志

错误日志文件名称,用户记录当mysql启动和停止时,以及服务器在运行中发生发生任何验证错误时相关信息

log_bin


指定二进制日志文件名称,用于记录对数据库造成更改的所有语句

binlog_do_db binlog_ignore_db


指定将更新记录到二进制日志的数据库,其他所有没有显示指定的数据库更新将忽略,不记录在日志中

sync_binlog


指定多少次写日志后同步磁盘

general_log=1


是否开启查询日志记录

general_log_filefile_name(默认host_name.log)


指定查询日志文件名,用户记录所有的查询语句
查询日志文件名:slow_query_log_file=slow_query.log
mysqldumpslow slow_query.log;查看慢查询日志文件

slow_query_log=1


是否开启慢查询日志,1代表开启,0代表关闭

long_query_time=3


设置慢查询的时间,超过这个时间的查询语句才会记录日志

log_slow_admin_statements


是否将管理语句写入慢查询日志


推荐阅读
  • MySQL索引详解及其优化策略
    本文详细解析了MySQL索引的概念、数据结构及管理方法,并探讨了如何正确使用索引以提升查询性能。文章还深入讲解了联合索引与覆盖索引的应用场景,以及它们在优化数据库性能中的重要作用。此外,通过实例分析,进一步阐述了索引在高读写比系统中的必要性和优势。 ... [详细]
  • 分布式开源任务调度框架 TBSchedule 深度解析与应用实践
    本文深入解析了分布式开源任务调度框架 TBSchedule 的核心原理与应用场景,并通过实际案例详细介绍了其部署与使用方法。首先,从源码下载开始,详细阐述了 TBSchedule 的安装步骤和配置要点。接着,探讨了该框架在大规模分布式环境中的性能优化策略,以及如何通过灵活的任务调度机制提升系统效率。最后,结合具体实例,展示了 TBSchedule 在实际项目中的应用效果,为开发者提供了宝贵的实践经验。 ... [详细]
  • 在过去,我曾使用过自建MySQL服务器中的MyISAM和InnoDB存储引擎(也曾尝试过Memory引擎)。今年初,我开始转向阿里云的关系型数据库服务,并深入研究了其高效的压缩存储引擎TokuDB。TokuDB在数据压缩和处理大规模数据集方面表现出色,显著提升了存储效率和查询性能。通过实际应用,我发现TokuDB不仅能够有效减少存储成本,还能显著提高数据处理速度,特别适用于高并发和大数据量的场景。 ... [详细]
  • 掌握PHP框架开发与应用的核心知识点:构建高效PHP框架所需的技术与能力综述
    掌握PHP框架开发与应用的核心知识点对于构建高效PHP框架至关重要。本文综述了开发PHP框架所需的关键技术和能力,包括但不限于对PHP语言的深入理解、设计模式的应用、数据库操作、安全性措施以及性能优化等方面。对于初学者而言,熟悉主流框架如Laravel、Symfony等的实际应用场景,有助于更好地理解和掌握自定义框架开发的精髓。 ... [详细]
  • Node.js 配置文件管理方法详解与最佳实践
    本文详细介绍了 Node.js 中配置文件管理的方法与最佳实践,涵盖常见的配置文件格式及其优缺点,并提供了多种实用技巧和示例代码,帮助开发者高效地管理和维护项目配置,具有较高的参考价值。 ... [详细]
  • 在数据库设计中,谨慎使用外键至关重要。本文探讨了九个关键原因,包括数据完整性的维护、性能优化、系统复杂性的管理、数据迁移的灵活性以及对外部系统的依赖性控制。通过深入分析这些因素,可以帮助开发人员和架构师做出更明智的设计决策,确保数据库系统的高效与稳定。 ... [详细]
  • MySQL 数据备份与恢复的常见方法及其实践经验总结。物理备份涉及直接复制数据库文件,适用于大规模数据库环境,但无法在异构系统(如 Windows)中恢复。逻辑备份则侧重于导出建表语句和数据插入语句,便于跨平台迁移和部分数据恢复。此外,本文还探讨了增量备份、全量备份以及使用工具如 mysqldump 和 Percona XtraBackup 的具体应用场景和优缺点。 ... [详细]
  • 揭秘腾讯云CynosDB计算层设计优化背后的不为人知的故事与技术细节
    揭秘腾讯云CynosDB计算层设计优化背后的不为人知的故事与技术细节 ... [详细]
  • Nginx不仅是一款轻量级的高性能Web服务器,还具备出色的负载均衡和反向代理功能。它支持复杂的正则匹配规则、动静内容分离以及灵活的URL重写功能,使得配置和管理更加便捷高效。此外,Nginx提供了多种负载均衡算法,如轮询、加权轮询、最少连接数等,以满足不同应用场景的需求。 ... [详细]
  • 在基于.NET框架的分层架构实践中,为了实现各层之间的松散耦合,本文详细探讨了依赖注入(DI)和控制反转(IoC)容器的设计与实现。通过合理的依赖管理和对象创建,确保了各层之间的单向调用关系,从而提高了系统的可维护性和扩展性。此外,文章还介绍了几种常见的IoC容器实现方式及其应用场景,为开发者提供了实用的参考。 ... [详细]
  • 本文深入探讨了ASP.NET中ViewState、Cookie和Session三种状态管理技术的区别与应用场景。ViewState主要用于保存页面控件的状态信息,确保在多次往返服务器过程中数据的一致性;Cookie则存储在客户端,适用于保存少量用户偏好设置等非敏感信息;而Session则在服务器端存储数据,适合处理需要跨页面保持的数据。文章详细分析了这三种技术的工作原理及其优缺点,并提供了实际应用中的最佳实践建议。 ... [详细]
  • 在《PHP应用性能优化实战指南:从理论到实践的全面解析》一文中,作者分享了一次实际的PHP应用优化经验。文章回顾了先前进行的一次优化项目,指出即使系统运行时间较长后出现的各种问题和性能瓶颈,通过采用一些通用的优化策略仍然能够有效解决。文中不仅详细阐述了优化的具体步骤和方法,还结合实例分析了优化前后的性能对比,为读者提供了宝贵的参考和借鉴。 ... [详细]
  • 作为140字符的开创者,Twitter看似简单却异常复杂。其简洁之处在于仅用140个字符就能实现信息的高效传播,甚至在多次全球性事件中超越传统媒体的速度。然而,为了支持2亿用户的高效使用,其背后的技术架构和系统设计则极为复杂,涉及高并发处理、数据存储和实时传输等多个技术挑战。 ... [详细]
  • 本题库精选了Java核心知识点的练习题,旨在帮助学习者巩固和检验对Java理论基础的掌握。其中,选择题部分涵盖了访问控制权限等关键概念,例如,Java语言中仅允许子类或同一包内的类访问的访问权限为protected。此外,题库还包括其他重要知识点,如异常处理、多线程、集合框架等,全面覆盖Java编程的核心内容。 ... [详细]
  • 进程(Process)是指计算机中程序对特定数据集的一次运行活动,是系统资源分配与调度的核心单元,构成了操作系统架构的基础。在早期以进程为中心的计算机体系结构中,进程被视为程序的执行实例,其状态和控制信息通过任务描述符(task_struct)进行管理和维护。本文将深入探讨进程的概念及其关键数据结构task_struct,解析其在操作系统中的作用和实现机制。 ... [详细]
author-avatar
集束群
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有