设置慢查询时间,超过3s以上的都查出来,修改哪个参数

__42_mrB 2023-03-23 05:23

• 

  ____简单de爱

  2023-03-23 05:44

  并发还不小，这是dba肯定会收到告警。dba上线处理第一时间是定位并kill慢查询，避免慢查询其他正常的事务。本文主要围绕kill展开，并附带介绍几种相关的timeout参数和实现机制。

  kill指令

  kill的语法如下：kill [connection|query] thread_id，通过kill命令可以kill一个查询或kill一个连接。一般而言，每个用户只能查看和终止自己用户的连接和查询，若用户具有process权限，则可以查看所有线程，具有super权限，可以查看和终止所有用户的连接和查询。假设有两个会话A和B，A的thread_id为xxx，A会话执行查询，B会话分别执行kill query xxx和kill xxx，会话A会分别收到1317和2013错误。

  mysql> select count(*) from test_slow where 1=1;ERROR 1317 (70100): Query execution was interruptedmysql> select count(*) from test_slow where 1=1;ERROR 2013 (HY000): Lost connection to MySQL server during query

  通过mysql客户端进行操作，除了发送kill指令，还可以通过ctrl+c来终结自己。这里的原理很简单，mysql客户端里面有一个信号捕获线程，***到SIGINT后，创建一个连接，然后发送kill命令到服务端，将自己终结。通过gdb调试时，为了避免gdb对信号的影响，通过命令设置即可，命令如下：

  handle SIGINT nostop print pass

  kill实现原理

  mysqld收到kill命令后，会将对应的线程实例thd设置为kill状态，若为kill_connection，会主动断开socket。因此对于killconnection 操作，线程马上会kill，但是对于kill query，则查询可能不能立即结束，因为查询可能正在运行过程中。因此，在mysqld的代码中的关键节点都会调用trx_is_interrupted函数判断自身的状态是否为killed，若是，则报错返回，终止执行。到底mysqld在哪些地方会进行检查，这里列出官方的文档，说明如下：

  • In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

  • During ALTER TABLE,the kill flag is checked before each block of rows are read from the originaltable. If the kill flag was set, the statement is aborted and the temporarytable is deleted.

  • During UPDATEor DELETE operations, the kill flag is checked after each block read and aftereach updated or deleted row. If the kill flag is set, the statement is aborted.Note that if you are not using transactions, the changes are not rolledback.

  kill自动化

  如果每次查询都需要dba人工去触发，那么遇到问题时，可能处理没那么及时，如果能自动化的kill慢查询则能将影响降到最低。在jdbc中通过接口Statement.setQueryTimeout(int)即可实现。这个原理其实与mysql客户端通过ctrl+ckill查询类似，只不过这种方式是通过超时实现。设置一个定时器，有一个线程定时不停地判断定时器事件有没有触发，达到触发点后，再发送kill查询到服务端，实现kill查询的目的。这种方式虽然能自动化，但这个接口调用控制权还是在开发手中，而且一般情况下，开发一定都认为自己的查询时没有问题的，肯定不会超时，所以这个接口是否被调用无法控制。那么遇到问题后，DB还是一样会挂，还是需要dba人工处理。因此在alimysql内核也做了一套类似的功能，通过参数max_statement_time可以控制当前会话和所有会话的超时时间。只要超时，mysql内部会调用接口awake接口将对应的thd状态设置为killed，达到kill query或kill connection的目的。

  几种超时参数

  除了我们上面提到的statementtimeout，jdbc中常见的超时参数还有connectTimeout和socket timeout。

  connectTimeout：和数据库服务器建立socket连接时的超时，单位：毫秒。

  socket timeout: socket操作（读写）超时

  具体而言，jdbc通过设置socket的属性来实现超时目的，不同的JDBC驱动其配置方式会有所不同。socket连接时的timeout：通过Socket.connect(SocketAddressendpoint, int timeout)设置；socket读写时的timeout：通过Socket.setSoTimeout(int timeout)设置，采用阻塞IO模型，如果不设置sockettimeout或connect timeout，应用多数情况下是无法发现网络错误的。因此，当网络错误发生后，在连接重新连接成功或成功接收到数据之前，应用会无限制地等下去。当然，操作系统层面，也会有socket timeout设置。相关的配置参数如下：

  tcp_keepalive_time:TCP连接在idle指定时间后,内核才发起probe

  tcp_keepalive_probes:TCP发送keepalive探测以确定该连接已经断开的次数

  tcp_keepalive_intvl:探测消息发送的频率

  因此，探测(tcp_keepalive_time + tcp_keepalive_intvl * tcp_keepalive_probes)时间后，若还不能连上，则断开连接。

  这些参数可以修改：

  /proc/sys/net/ipv4/tcp_keepalive_time/proc/sys/net/ipv4/tcp_keepalive_intvl/proc/sys/net/ipv4/tcp_keepalive_probes

  参考文献

  http://astar.baidu.com/forum/forum.php?mod=viewthread&tid=363

  http://dev.mysql.com/doc/refman/5.1/en/kill.html

  http://m.blog.csdn.net/blog/xieyuooo/39898449

  http://blog.sina.com.cn/s/blog_a2d4803001013hrk.html

  自动kill慢查询

  标签：

  一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句　　1，slow_query_log　　这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。　　2，long_query_time　　当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。　　3，slow_query_log_file　　记录日志的文件名。　　4，log_queries_not_using_indexes　　这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。　　二、检测mysql中sql语句的效率的方法　　1、通过查询日志　　（1）、Windows下开启MySQL慢查询　　MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上　　代码如下　　log-slow-queries = F:/MySQL/log/mysqlslowquery。log　　long_query_time = 2　　（2）、Linux下启用MySQL慢查询　　MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上　　代码如下　　log-slow-queries=/data/mysqldata/slowquery。log　　long_query_time=2　　说明　　log-slow-queries = F:/MySQL/log/mysqlslowquery。　　为慢查询日志存放的位置，一般这个目录要有MySQL的运行帐号的可写权限，一般都将这个目录设置为MySQL的数据存放目录；　　long_query_time=2中的2表示查询超过两秒才记录；　　2.show processlist 命令　　SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。　　各列的含义和用途：　　ID列　　一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。　　user列　　显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。　　host列　　显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。　　db列　　显示这个进程目前连接的是哪个数据库。　　command列　　显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。　　time列　　此这个状态持续的时间，单位是秒。　　state列　　显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成　　info列　　显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。　　这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：　　Checking table　　正在检查数据表（这是自动的）。　　Closing tables　　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。　　Connect Out　　复制从服务器正在连接主服务器。
  0 讨论(0)
  发布评论:

  提交评论

  ---

  •  加载中...