sql执行超时排查-dbeaver工具

问题：

        在mysql工具上，执行 update语句时需要很长时间，直到链接出现超时？

        执行查询是，一个几十条数据的sql 查询时间也很久，使用的dbeaver工具。

思考：

        1.是否由于筛选条件过于复杂，导致筛选失败？

        2.数据量过大导致数据操作失败  ？

        3.是否被锁表？

        4.数据库响应慢，还是可视化工具获取到数据展示慢;

处理：

        思路1：

                检查筛选条件，发现条件并不复杂，切为了排除该原因，修改where 条件为 主键ID，结果发现执行依然超时；

        思路2：

                检查数据量，发现数据的数据并不多，且表内容并不复杂，该想法排除。

        思路3：                

        show processlist ;  执行脚本

show processlist显示正在运行的线程。如果有process权限，则可以查看所有正在运行的线程。否则，只能看到自己的线程。如果不使用full关键字，则只在info字段显示每个语句的前100个字符。

        发现问题；

在列表中，有大量的 我的Host 主机IP的进程处于 Sleep状态，且Time时间巨长，我将我主机上所有连接数据库的程序全部关闭，执行该脚本发现 进程数据并没有减少，故考虑kill掉对应的进程，然后尝试。

kill   36590983

将我host主机对应的线程kill，只保修当前的query连接，然后在执行update语句，发现秒成功。该问题处理完成。

• ID：连接标识。这个值和INFORMATION_SCHEMA.PROCESSLIST表的ID列，以及PERFORMANCE_SCHEMA中的threads中的process_id值是相同的。
• user： 发出该语句的MySQL用户。system user是指由服务器生成的用于内部处理任务的非客户端线程。这可以是用于复制的I/O线程，或SQL线程，也可以是延迟的行处理程序。未经身份验证的用户是指已经与客户端连接但尚未对客户端身份进行验证的线程。
• Host：客户端使用的主机。系统用户没有host值。
• db：连接的数据库。
• command：线程执行的命令

  显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

• time： 线程已经在当前状态的时间。
• state：一个动作，一个事件，标识线程正在做什么。
• info：当前的详细信息。

show proceslist时发现大量的sleep，有什么风险吗，该如何处理？

（一）可能的风险有：
    1、大量sleep线程会占用连接数，当超过max_connections后，新连接无法再建立，业务不可用；
    2、这些sleep线程中，有些可能有未提交事务，可能还伴随着行锁未释放，有可能会造成严重锁等待；
    3、这些sleep线程中，可能仍有一些内存未释放，数量太多的话，是会消耗大量无谓的内存的，影响性能。

（二）建议应对措施：
    1、升级到5.7及以上版本，连接性能有所提升；
    2、采用MariaDB/Percona版本，根据情况决定是否启用thread pool功能；
    3、适当调低wait_timeout/interactive_timeout值，例如只比java连接池的timeout时间略高些即可；
    4、利用pt-kill或辅助脚本/工具巡查并杀掉无用sleep进程；
    5、利用5.7的新特性，适当设置max_execution_time阈值，消除长时间执行的SQL；
    6、定期检查show processlist的结果，找到长时间sleep的线程，根据host&port反推找到相关应用负责人，协商优化方案。

在没有办法解决的情况下  临时使用 shell脚本 配合  crontab 定期kill sleep 进程

1  #vi kill_sleep.sh
代码如下

#!/bin/sh
n=`/usr/bin/mysqladmin  -uuser  -ppasswdprocesslist | grep -i sleep | wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $n
if [ "$n" -gt 10 ]
then
   for i in `/usr/bin/mysqladmin  -uuser  -ppasswdprocesslist | grep -i sleep | awk '{print $2}'`
   do
     /usr/bin/mysqladmin kill $i
   done
echo "sleep is too many i killed it" >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi

把上述代码 另存为  kill_sleep.sh
2 修改文件可执行权限
＃ chmod +x  kill_sleep.sh
3 修改crontab
# vi  /etc/crontab
在打开文件中添加以下命令
*/10 *  *  *  * root sh /home/program/kill_sleep.sh 表示每10分钟执行一次脚本

dbeaver工具连接问题

1.1 查看sql执行的结果，主要是sql的响应时间，和页面获取的时间差距；

 1.2 或者通过开启数据库自己的

查看执行时间步骤

                1.show profiles;

MySQL 5.1以后有了show profile默认禁用，该命令启动后：会测量服务器上执行的所有语句的耗费时间和其他一些查询执行状态变量相关的数据。

                2.show variables; 查看profiling 是否是on状态；

                3.如果是off，则执行命令  set profiling=1;            

                开启功能

                set profiling=1;
                set profiling=on;

                关闭功能

                set profiling=0;
                set profiling=off;

                4.执行sql

                5. 再次执行 show profiles;查看执行时间 或者  show profile all 

结果显示数据库响应速度在正常范围内，问题来自于软件或者网络；

切换到navicat工具尝试连接速度很快， 问题在软间；