MySQL性能优化：语句、连接池与网络抓包，cpu打满的优化策略

2023-12-10 本文已影响0人后来丶_a24d

mysql语句性能优化

背景

业务场景有四张表需要一起查，并且要查出所有数据，没法用应用侧关联，这张表索引都相对比较合理。这里只能用join或者in来处理关联关系，这是个定时任务查询，允许sql查询基于业务结果慢一点

最后优化后sql

最开始先只join两张表，IN查询另外一张表，IN所需要的数据量很大导致查询结果很慢，第一步优化就是将IN改成JOIN，因为JOIN能用JOIN的Buffer
后面用三次join，使用客户端查结果很快，但是这边有个点要注意，业务是要返回全部数据，用客户端一般有默认限制返回数量比如1k，所以要加大limit测试，这个sql最后返回了3万左右的数据。所以还需要继续优化

  from A a
               left join C c on a.xxx = c.xxx
               left join D d on a.xxxx = d.id
               left join B b on x.xxx = b.name

通过不断调整第一层select的join表最终发现下面sql性能最好，数据尽可能的小, JOIN太多笛卡尔积不好，最后一个join的是比较小的表，弄出去变成新的一层select性能提升很多从200s -> 15s,15s看着也不少，但这个业务场景下能接受的，跟我们普遍的高并发后端查询场景是不一样

select x.xxxx
from (select xxxx
      from A a
               left join C c on a.xxx = c.xxx
               left join D d on a.xxxx = d.id
      where a.XXX = '0'
        and a.AREA IN ('东北', '华东', '....')
        and a.XXXX NOT IN (XXX十多个)
        and d.xxx = '0'
        and (FIND_IN_SET(1, a.xx三个))
        and a.xxx NOT LIKE '%xxx not like也有3个%') as x
         left join B b on x.xxx = b.name
where FIND_IN_SET(0, b.xxxx)  OR (FIND_IN_SET(3, b.xxx) and x.xxx IN (子查询));

c3P0架构简介

C3P0连接池的架构主要包含以下几个部分：

数据源（Data Source）：C3P0的数据源是应用程序直接使用的对象，用于获取数据库连接。为了适应不同的应用需求，C3P0提供了多种数据源实现
连接池管理器（Pool Manager）：负责管理和控制连接池的生命周期，包括创建、销毁连接，以及连接的获取和归还
连接池（Connection Pool）：这是C3P0的核心，包含一组预先创建的数据库连接。应用程序从连接池中获取连接，使用完后归还给连接池
连接测试器（Connection Tester）：用于定期检测连接池中的连接是否有效，无效的连接会被移除并建立新的连接
配置管理器（Configuration Manager）：提供了一系列参数用于配置数据源和连接池，如最大/最小连接数、连接超时时间、连接测试频率等

c3P0连接池优化

排查问题的业务特征是有时有任务来，有时没有，mysql用的是5.5.59版本，c3P0版本也很老时16年的。这段时间一直都有这个问题，获取不到连接，看几个数据库连接telnet看网络都正常，服务端看着没啥问题，因为不是大面积出现，只是间断的出现，其他功能也正常，时不时有几个小功能爆出来，频率不高。这里并不是所有最大最小连接都要设置成一样有高峰低谷期，除非都非常平稳都要那么多

数据库连接没有设置minPoolSize，只设置了initialPoolSize 10 maxPoolSize设置80，业务特性一段时间后导致连接变成minPoolSize 默认值3, 业务来时很多地方就要创建连接，数据库连接池如果在没超过最大连接时会去创建连接，当在规定时间内能获取连接则放入连接池并返回连接池中数据，默认值是dump堆然后观察快照得到的结果，这边通过线上dump堆来查看c3P0配置是为了排除配置不一致导致的问题，dump下来有6个G，直接从机器sc下下来经常断，所以这边有个小技巧先tar压缩下变成不到1个G，然后再win10解压缩，本地mat解析hprof
2.1 查看默认值最小连接3的步骤，Histogram搜JDBCTemplate代码里面是这个类作为数据库连接配置类(本质上是C3P0)

Histogram搜JDBCTemplate

2.2 右键list objects -> withoutgoings

查看默认最小连接池值.png

目前解决方案设置最小连接池大小解决，就不要去获取连接了，这个获取不到数据库连接问题得到比较大的解决
还有个参数preferredTestQuery 旧版本C3P0有bug, 不同数据库版本不同，使用的检测也不一样，再加上mysql版本较老，有时候发现select 1在mysql服务的processlist里面没执行，导致等待结果时间很长，如果放到客户端去就会导致数据库连接获取超时而报连接池获取不到数据，之前代码还有个参数preferredTestQuery设置为Null, 这边设置 select 1 from dual, 做个优化观察效果良好。对于之前的版本c3p0默认在连接关联的DatabaseMetaData上使用getTables()方法,这个方法非常稳定并且适用于不同的数据库,但是比正常的query慢很多,使用它做连接测试可能会严重影响连接池性能。c3p0在0.9.5版本以上,建议放手让driver进行连接测试,c3p0会首先尝试使用JDBC4引入的isValid()方法进行测试,这个方法速度快并且可靠。这边是检测连接是否有效，无效则销毁连接重新创建连接
强调下dump下载过程这边是在有问题的java客户端dump hprof有七个G左右，然后gzip压缩变不到1个G，使用xshell下到本地，本地mat分析dump快照调度参数，注意xshell限制每次传输4个G, 之前使用tabby terminal传输一直有bug 提示hprof文件损坏，或者是因为没gzip先压缩再传输把，毕竟tabby termianl没限制传输大小

网络抓包优化

发现了个新问题报无法获取连接C3P0 BasicResourcePool.prelimCheckoutResource在保活检测时报错了，大概率是网络问题，通过tcpdump抓包发现tcp握手时没响应，几台客户端都是这样，于是换了台机器，之前机器年纪也大了有些硬件问题，观察了一段时间问题解决
同时也发现域名解析也有点问题，换了一台namseserver

域名解析.png
抓包用eth4是网卡，然后本地wireshark查看

tcpdump -i eth4 -s 0 -w xxx.pcap host 域名

cpu打满问题

现象，mysql所在机器cpu全部打满了，32C的都打满了，mysql机器有跟其他脚本等混部署
mysql占比不大，但发现超级多的sh在跑，积少成多

mysql占比不大.png
有个脚本有个奇怪的逻辑，经过确定这个脚本可以停掉，也不知道咋触发的

无限循环.png
批量停止命令,xxx脚本名称

ps aux|grep xxx|grep -v "grep"|grep -v "xxx"|awk '{print $2}'|xargs kil

参考文章

https://blog.csdn.net/weixin_29614223/article/details/113457138
http://www.mchange.com/projects/c3p0/#configuring_connection_testing

MySQL性能优化：语句、连接池与网络抓包，cpu打满的优化策略

目录

mysql语句性能优化

背景

最后优化后sql

c3P0架构简介

c3P0连接池优化

网络抓包优化

cpu打满问题

参考文章

猜你喜欢

热点阅读