logstash 解析mysql slow log

原创 2016年08月30日 20:24:35
# User@Host: zjzc_app[zjzc_app] @  [10.252.148.16xx]  Id: 6043127
# Query_time: 2.581184  Lock_time: 0.000336 Rows_sent: 6  Rows_examined: 6331310
SET timestamp=1472536079;
select temp.* from ( SELECT 
		    *
		FROM
		    (SELECT 
		        c.sn,
	            c.userNick,
	            c.mobilePhone,
	            date_format(c.registerTime,'%Y/%m/%d') registerTime,
	            cp.personName,
	            cp.personCardNo,
	            cp.gender,
	            cp.personAuthStatus,
	            asm.clientManagerSn,
	            (select personName from ClientPersonalInfo where clientSn = asm.clientManagerSn) as clientManagerName,
	            IF(1600=asm.clientManagerSn or 1600 IS null, 1, 0) as isMyself,
	            IFNULL(cei.policyAreaSn,IF( LENGTH(SUBSTRING_INDEX(cm.`governAreas`,';',2))=LENGTH(cm.`governAreas`),SUBSTRING_INDEX(cm.`governAreas`,';',1),NULL )) policyAreaSn,
	            area.areaName as policyAreaName,
			    (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
				FROM view_client_invest_summary 
				WHERE status =2 AND clientSn=c.`sn` AND DATEDIFF(settlementDate,CURDATE())   <=7   
				AND DATEDIFF(settlementDate,CURDATE())   >=0   ) investBalance,
			    (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
				FROM view_client_invest_summary pr
				WHERE pr.clientSn = c.sn AND pr.status = 2 ) unclearedBalance,
			    (SELECT IFNULL(SUM(capitalBalance), 0) + cai.availableBalance + cai.frozenWithDraw +tra.availableBalance
				FROM view_client_invest_summary
				WHERE clientSn = c.sn AND status in(1,2)) totalBalance,
			    (cai.frozenWithDraw + cai.frozenPay) frozenBalance,(cai.availableBalance+tra.availableBalance) availableBalance
		    FROM
		        Client c 
		        LEFT JOIN ClientExtraInfo cei on c.sn=cei.clientSn 
		        LEFT JOIN Area area on cei.policyAreaSn=area.sn
		        LEFT JOIN AssignClientManager asm ON asm.clientSn=c.sn
		        LEFT JOIN ClientManager cm on cm.sn = asm.clientManagerSn
		        LEFT JOIN TransferClientAssetInfo tra on c.sn=tra.clientSn, 
		        ClientPersonalInfo cp, ClientAssetInfo cai
		    WHERE
		        c.sn = cp.clientSn AND asm.status='1'
		            AND c.sn = cai.clientSn
		             
		       		 
		       			AND exists (select * from ClientInvestOrder where clientSn = c.sn and status =3)
		       		  
		       		  
		            AND c.sn IN (SELECT 
		                asm.clientSn
		            FROM
		                AssignClientManager asm
		            WHERE
		                asm.clientManagerSn IN 
		                 (  
                       		 1600
                		 ) 
                		 
                		AND asm.status='1')		
		       ) t
		     
		ORDER BY
		 
		t.clientManagerSn desc,t.sn desc ) temp   limit 10,10;
# Time: 160830 14:22:27


(?m)\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s*
SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\S+\s+).*)\s*#\s+Time:\s+.*

{
  "user": [
    [
      "zjzc_app"
    ]
  ],
  "clientip": [
    [
      "10.252.148.164"
    ]
  ],
  "id": [
    [
      "Id: 6043127"
    ]
  ],
  "Query_time": [
    [
      "2.581184"
    ]
  ],
  "lock_time": [
    [
      "0.000336"
    ]
  ],
  "rows_sent": [
    [
      "6"
    ]
  ],
  "rows_examined": [
    [
      "6331310"
    ]
  ],
  "timestamp": [
    [
      "1472536079"
    ]
  ],
  "query": [
    [
      "select temp.* from ( SELECT \n\t\t    *\n\t\tFROM\n\t\t    (SELECT \n\t\t        c.sn,\n\t            c.userNick,\n\t            c.mobilePhone,\n\t            date_format(c.registerTime,'%Y/%m/%d') registerTime,\n\t            cp.personName,\n\t            cp.personCardNo,\n\t            cp.gender,\n\t            cp.personAuthStatus,\n\t            asm.clientManagerSn,\n\t            (select personName from ClientPersonalInfo where clientSn = asm.clientManagerSn) as clientManagerName,\n\t            IF(1600=asm.clientManagerSn or 1600 IS null, 1, 0) as isMyself,\n\t            IFNULL(cei.policyAreaSn,IF( LENGTH(SUBSTRING_INDEX(cm.`governAreas`,';',2))=LENGTH(cm.`governAreas`),SUBSTRING_INDEX(cm.`governAreas`,';',1),NULL )) policyAreaSn,\n\t            area.areaName as policyAreaName,\n\t\t\t    (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n\t\t\t\tFROM view_client_invest_summary \n\t\t\t\tWHERE status =2 AND clientSn=c.`sn` AND DATEDIFF(settlementDate,CURDATE())   <=7   \n\t\t\t\tAND DATEDIFF(settlementDate,CURDATE())   >=0   ) investBalance,\n\t\t\t    (SELECT IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n\t\t\t\tFROM view_client_invest_summary pr\n\t\t\t\tWHERE pr.clientSn = c.sn AND pr.status = 2 ) unclearedBalance,\n\t\t\t    (SELECT IFNULL(SUM(capitalBalance), 0) + cai.availableBalance + cai.frozenWithDraw +tra.availableBalance\n\t\t\t\tFROM view_client_invest_summary\n\t\t\t\tWHERE clientSn = c.sn AND status in(1,2)) totalBalance,\n\t\t\t    (cai.frozenWithDraw + cai.frozenPay) frozenBalance,(cai.availableBalance+tra.availableBalance) availableBalance\n\t\t    FROM\n\t\t        Client c \n\t\t        LEFT JOIN ClientExtraInfo cei on c.sn=cei.clientSn \n\t\t        LEFT JOIN Area area on cei.policyAreaSn=area.sn\n\t\t        LEFT JOIN AssignClientManager asm ON asm.clientSn=c.sn\n\t\t        LEFT JOIN ClientManager cm on cm.sn = asm.clientManagerSn\n\t\t        LEFT JOIN TransferClientAssetInfo tra on c.sn=tra.clientSn, \n\t\t        ClientPersonalInfo cp, ClientAssetInfo cai\n\t\t    WHERE\n\t\t        c.sn = cp.clientSn AND asm.status='1'\n\t\t            AND c.sn = cai.clientSn\n\t\t             \n\t\t       \t\t \n\t\t       \t\t\tAND exists (select * from ClientInvestOrder where clientSn = c.sn and status =3)\n\t\t       \t\t  \n\t\t       \t\t  \n\t\t            AND c.sn IN (SELECT \n\t\t                asm.clientSn\n\t\t            FROM\n\t\t                AssignClientManager asm\n\t\t            WHERE\n\t\t                asm.clientManagerSn IN \n\t\t                 (  \n                       \t\t 1600\n                \t\t ) \n                \t\t \n                \t\tAND asm.status='1')\t\t\n\t\t       ) t\n\t\t     \n\t\tORDER BY\n\t\t \n\t\tt.clientManagerSn desc,t.sn desc ) temp   limit 10,10;\n"
    ]
  ]
}

logstash 通过mysql 慢日志了解(?m)

# User@Host: zjzc_app[zjzc_app] @ [10.171.243.55] Id: 1621705 # Query_time: 10.666552 Lock_time: ...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年10月13日 14:07
  • 832

MySQL5.6版本性能调优my.cnf详解

[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock basedi...
  • qq_22929803
  • qq_22929803
  • 2016年04月25日 00:19
  • 8246

Logstash实践之MySQL Slowlog解析

Logstash实践之MySQL Slowlog解析
  • wzhg0508
  • wzhg0508
  • 2016年01月31日 23:36
  • 1741

logstash 解析mysql slow log

# User@Host: zjzc_app[zjzc_app] @ [10.252.148.16xx] Id: 6043127 # Query_time: 2.581184 Lock_time:...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年08月30日 20:24
  • 540

logstash (?m) 经典例子

在和 codec/multiline 搭配使用的时候,需要注意一个问题,grok 正则和普通正则一样,默认是不支持匹配回车换行的。就像你需要 =~ //m 一样也需要单独指定,具体写法是在表达式开始位...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年10月12日 23:13
  • 504

mysql慢日志 :slow query log 分析数据

目录: 1 慢查询日志 配置 2  查看慢查询日志工具:mysqldumpslow mysqlsla 3. 五种mysql日志分析工具比拼       ...
  • zkaipmoo
  • zkaipmoo
  • 2013年09月26日 19:00
  • 6198

MySQL 之 slow log

慢查询有什么用?          它能记录下所有执行超过long_query_time时间的SQL语句,帮你找到执行慢的SQL,方便我们对这些SQL进行优化。    测试用 MySQL 版本...
  • jkh753
  • jkh753
  • 2013年09月12日 08:38
  • 4666

ELK 之 Logstash

ELK 之 Logstash简介:ELK 之 LogstashLogstash 是一个接收,处理,转发日志的工具。支持系统日志,webserver 日志,错误日志,应用日志,总之包括所有可以抛出来的日...
  • iguyue
  • iguyue
  • 2017年08月09日 17:27
  • 2405

MySQL 慢日志解析推送

背景 方案确定 搭建 配置文件 部署 问题 背景之前有用 pt-query-digest 做的慢日志分析的方案,定期将所有机器的慢日志的收集起来,传到中心机上用 pt-query-digest 分析一...
  • shuxiang1990
  • shuxiang1990
  • 2016年11月30日 23:30
  • 330

mysql 5.5 开启慢日志slow log

1.mysql 5.5命令行里面 set global log_slow_queries = on;                               # 开启慢日志 set [sess...
  • m582445672
  • m582445672
  • 2012年07月30日 21:31
  • 6538
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:logstash 解析mysql slow log
举报原因:
原因补充:

(最多只允许输入30个字)