前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql性能优化

Mysql性能优化

作者头像
飞天小子
发布2021-10-13 15:56:04
1.8K0
发布2021-10-13 15:56:04
举报
文章被收录于专栏:公众号-测试驿栈

Mysql性能优化 Mysql的性能参数可以分为以下几个大类,这里仅整理一些常用的参数配置

连接参数

max_connections mysql服务器的最大连接数 show VARIABLES like '%connections%' 查看mysql服务器的最大连接数 Max_user_connections 单个用户最大连接数限制 set GLOBAL max_connections 设置mysql服务器的最大连接数 max_connect_errors 每个IP的连接请求异常中断的最大次数

Connections 试图连接到mysql服务器的连接总数 show STATUS like '%Connections%' 查看试图连接的总数 Max_used_connections 已经建立连接的总数

Aborted_connects 尝试重连的次数,结合 host_cache分析,可能的原因有 1.连接到MySQL服务器的失败总数 2.客户端没有权限但是尝试访问MySQL 3.客户端输入的密码不对 4.超过connect_timeout限制(mysql默认是10s,除非网络环境极端不好,一般不会超时)

Aborted_clients 客户端没有正常关闭连接而强行中止的连接数 有客户端成功建立连接,但是由于某些原因断开连接,一般是由于网络环境不稳定。主要的可能性有

1.客户端退出之前未调用mysql_close()正确关闭MySQL连接 2.sleep时间超过了变量wait_timeout和interactive_timeout的值,导致连接被MySQL进程终止 3.客户端在数据传输过程中突然结束

select USER,COUNT(USER) AS CNT from information_schema.PROCESSLIST GROUP BY USER ORDER BY CNT DESC; 查询执行sql的连接总数

注:VARIABLES是参数变量,可以优化;status是状态位,只能查看不能修改

缓冲空间

sort_buffer_size 查询排序缓存,为每个connection一次性分配设置的内存。500个连接将会消耗 500*sort_buffer_size(8M)=4G内存

join buffer 表关联缓存空间。增加join buffer的值来优化join查询,因为增加的join buffer可以容纳下更多的外表join字段记录

query_cache_limit MySQL将SELECT语句和查询结果存放在缓冲区,后期对于同样的 SELECT语句,将直接从缓冲区读取结果 使用show status like "%Qcache%"查看缓冲区。如果Qcache_lowmem_prunes的值非常大,表明缓冲区不够,如果Qcache_hits也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲

query_cache_size MySQL查询缓存保存查询返回的完整结果。查询命中该缓存,则立刻返回结果,跳过解析,优化和执行阶段。 如果表发生变更,要把Query_cache和该表相关的语句全部置为失效,然后再写入更新。如果Query_cache非常大,该表的查询结构又比较多,那么查询语句失效也会很慢,更新或是Insert就会更慢 数据库写入量或是更新量也比较大的系统,该参数不宜分配过大

query_cache_min_res_unit 指定分配缓冲区空间的最小单位,缺省为4K。检查Qcache_free_blocks。如果该值非常大,则表明缓冲区中碎片很多,查询结果都比较小,此时需要减小尺寸。小数据查询,此值过大容易造成内存碎片和浪费

query_cache_type 设置为0,OFF,缓存禁用 设置为1,ON,缓存开启

innodb_buffer_pool_size 内存中开辟的缓存空间用于缓存索引和数据,。数据读写在内存中非常快, 它减少对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中

innodb_buffer_pool_instances 缓冲池总数 注意:mysql的缓冲区空间尺寸只能在my.cnf里面修正

back_log 如果MySql的连接数达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源 这个值不能超过TCP的 net.ipv4.tcp_max_syn_backlog

socket参数

Bytes_received 从客户端接收到的字节数,作为数据库网卡吞吐量的评测指标

Bytes_sent 发送给客户端的字节数,作为数据库网卡吞吐量的评测指标

max_allowed_packet 网络传输中单次接收数据包尺寸的上限值

线程参数

innodb_thread_concurrency thread_stack Threads_cached 缓存线程总数。空闲的连接线程放在连接池中,而不是立即销毁.当有一个新的请求的时候,mysql不会立即去创建连接线程,而是先去Thread_Cache中去查找空闲的连接线程 Threads_connected【当前打开的连接数】 Threads_created【已创建的线程数】 Threads_running【已经激活的线程数】 thread_cache_size 当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程

文件参数

Created_tmp_files【创建的临时文件总数】 Open_files Open_tables

日志参数

general_log = OFF 关闭通用查询日志 max_binlog_size = 50M log_bin=ON expire_logs_days = 10 清除过期日志

慢sql

show variables like '%slow%';查询慢sql状态 show variables like "long%";慢查询时间 show global status like '%slow%'; 慢sql条数 set long_query_time=0.0001;【定义慢查询时间】 set global slow_query_log=ON;【开启慢查询日志】

Durid连接池

spring.datasource.druid.initial-size=1 初始化连接 spring.datasource.druid.min-idle=1 最小空闲连接 spring.datasource.druid.max-active=30 最大活动连接 spring.datasource.druid.max-wait=60000 从连接池获取连接超时

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2021-10-09 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 连接参数
  • 缓冲空间
  • socket参数
  • 线程参数
  • 文件参数
    • 日志参数
    • 慢sql
    • Durid连接池
    相关产品与服务
    云数据库 SQL Server
    腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
    领券
    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档