mysql慢查詢日志進行按庫切割重寫文件然后分析

需求：

1. 把每天的慢查詢日志進行按庫切割

2. 對每個庫的慢查詢日志進行分析

思路：

通過google出來的這幾款工具，mysqlsla的幫助文檔提示是可以根據-db來進行分庫篩選的，但是卻無法得出想要的結果，不知道是不是我打開姿勢不對的原因還是怎么著~ 后來一想就擼一把吧，雖然很糙~

約束條件：

切割工具：python腳本

慢查詢日志分析工具：mysqlsla

日志格式：

# Time: 150331 22:23:48
# User@Host: test[test] @  [192.168.1.200]
# Thread_id: 251049087  Schema: zhuima Last_errno: 0  Killed: 0
# Query_time: 4.581437  Lock_time: 0.000072  Rows_sent: 0  Rows_examined: 741  Rows_affected: 0  Rows_read: 740
# Bytes_sent: 89  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
use zhuima;
SET timestamp=1427811828;
SELECT cid FROM user_point_list where uid = '473781' and (cid ='ni' or cid ='ac' or cid ='qq' or cid ='de' or cid ='ec') limit 0,10;

python腳本：

用法：python script_name slow.log_name

[root@mysql01 test]# cat cut_slog.py
#!/usr/bin/evn python
#coding:utf8
#Author:zhuima
#QQ:993182876

'''
實現功能，對每日的慢查詢日志進行按庫名稱切割
注意事項:
1、由于是把慢查詢日志直接讀進內存的，所以slow.log如果過大，會產生假死情況.
'''

import time
import re
import sys

def get_file_content(fname):
    '''
    獲取文件內容，這里是把所有文件直接讀進內存的
    '''
    fp = file(fname)
    content = fp.read()
    fp.close()
    return content

def get_dbname():
    '''
    1、獲取所有包含Schema的字符串
    2、再次進行篩選，獲取最終的db名稱
    3、對list進行排序去重
    4、最終獲得當前日志中所有數據庫的名稱
    '''
    db_key = re.findall(r'Schema: \w+',content)
    dbname_list = []
    for db in db_key:
        match_db_key = re.search(r'\s{1}\w+',db)
        if match_db_key:
            dbname_list.append(match_db_key.group().strip())
    dbnamelist = list(set(dbname_list))
    return dbnamelist


def cut_slog_bydbname():
    '''
    根據獲取的db名稱來進行分庫重寫操作
    '''
    dblist = get_dbname()
    content_list = content.split(';\n#')
    for i in range(len(dblist)):
        db_name = dblist[i]
        dblist[i] = [ x for x in content_list if dblist[i] in x]
        for n in range(len(dblist[i])):
            if n == 0:
                dblist[i][n] = '#' + dblist[i][n]+';'
            elif n == len(dblist):
                dblist[i][n] = '#' + dblist[i][n]
            else:
                dblist[i][n] = '#' + dblist[i][n] + ';'
        new_fp = file(db_name,'w')
        new_fp.write('\n'.join(dblist[i]))
        new_fp.close()


def main():
    '''
    統計腳本執行所消耗的時間
    '''
    start_time = time.time()
    cut_slog_bydbname()
    end_time = time.time()
    take_time = end_time - start_time
    print 'Running This Script Take %s Time' % take_time

if __name__ == '__main__':
    fname = sys.argv[1]
    content = get_file_content(fname)
    main()

對170M大小的文件進行切割過程及耗費時長:

使用mysqlsla分析查看：

致勝王牌：

其實說了那么多，實際上新貴pt-query-digest是可以勝任這個任務的部分工作的，比如按照庫進行分析。

光說不練假把式，來點干貨上來，先安裝好工具再說

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

用起來：

[root@mysql01 test]# pt-query-digest --filter '$event->{db} =~ m/app_zhuima/  and $event->{arg} 
=~ m/^select/i' --order-by max --limit=10  s.log 

s.log:  36% 00:52 remain
s.log:  75% 00:19 remain
--order-by attribute max doesn't exist, using Query_time:sum

# 78s user time, 540ms system time, 41.20M rss, 223.39M vsz
# Current date: Fri Apr  3 12:11:02 2015
# Hostname: mysql01.opsdev.cn
# Files: s.log
# Overall: 101.75k total, 99 unique, 0.16 QPS, 0.02x concurrency _________
# Time range: 2015-03-16 09:10:59 to 2015-03-23 18:03:22
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         12085s   230us      5s   119ms   253ms   123ms    53ms
# Lock time            26s    52us      2s   255us   224us    11ms   119us
# Rows sent        477.36k       0   2.92k    4.80   17.65   71.55       0
# Rows examine       1.77G       0  36.01k  18.23k  33.17k  12.57k  19.40k
# Rows affecte           0       0       0       0       0       0       0
# Rows read        122.33M       0  18.75k   1.23k   7.31k   2.85k    1.96
# Bytes sent       404.76M      60  70.40k   4.07k  28.66k   8.92k  246.02
# Tmp tables        22.58k       0       1    0.23    0.99    0.42       0
# Tmp disk tbl       1.12k       0       1    0.01       0    0.10       0
# Tmp tbl size      25.78G       0  25.25M 265.70k       0   2.11M       0
# Query size        19.09M      46  20.21k  196.76  271.23  233.92  174.84

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Item
# ==== ================== =============== ===== ====== ===== =============
#    1 0xDF65C6303461AC09  551.9743  4.6% 10798 0.0511  0.01 SELECT apk_index apk_content?
#    2 0xB12492DDCD79AC3C    8.1966  0.1% 10612 0.0008  0.01 SELECT apk_index apk_content?
#    3 0x132FC5C1DD41AB40    8.5364  0.1% 10612 0.0008  0.10 SELECT apk_index apk_content?
#    4 0xE5745B6DB0863C99 2156.1381 17.8%  8986 0.2399  0.02 SELECT apk_index apk_content?
#    5 0xF4F4AC9E9B2C9707 1186.5799  9.8%  6982 0.1699  0.04 SELECT apk_index apk_content?
#    6 0xFA2AFA52C427310A 1572.7090 13.0%  6554 0.2400  0.01 SELECT apk_index apk_content?
#    7 0x46E8908EFD5250D1 1017.2819  8.4%  5370 0.1894  0.03 SELECT apk_index apk_content?
#    8 0x1B0924852232D745  196.3949  1.6%  4129 0.0476  0.01 SELECT apk_index apk_content?
#    9 0xF7A7B1118F17CC05  716.8696  5.9%  3014 0.2378  0.01 SELECT apk_index apk_content?
#   10 0xC4422E67F9F74B81  140.8687  1.2%  2958 0.0476  0.00 SELECT apk_index apk_content?
#   80 0xF1F228E747F8E739   62.4422  0.5%    19 3.2864  0.31 SELECT apk_tid
# MISC 0xMISC             4466.9640 37.0% 31721 0.1408   0.0 <88 ITEMS>

# Query 1: 0.02 QPS, 0.00x concurrency, ID 0xDF65C6303461AC09 at byte 4659
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2015-03-16 09:11:01 to 2015-03-23 17:57:15
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10   10798
# Exec time      4    552s     2ms   935ms    51ms    65ms    20ms    51ms
# Lock time      5      1s    75us     5ms   137us   194us   110us   113us
# Rows sent      2  11.72k       0      18    1.11   17.65    4.25       0
# Rows examine  11 202.50M      36  20.49k  19.20k  20.37k   4.85k  20.37k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      0  17.73k       0   7.62k    1.68    0.99   72.02    0.99
# Bytes sent     9  37.17M   1.64k  54.05k   3.53k  30.09k   7.17k   1.61k
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     9   1.82M     172     178  176.50  174.84    2.02  174.84
# String:
# Databases    app_zhuima
# Hosts
# Last errno   0
# Users        app_zhuima
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `app_zhuima` LIKE 'apk_xindex'\G
#    SHOW CREATE TABLE `app_zhuima`.`apk_xindex`\G
#    SHOW TABLE STATUS FROM `app_zhuima` LIKE 'apk_xx'\G
#    SHOW CREATE TABLE `app_zhuima`.`apk_xx`\G
# EXPLAIN /*!50100 PARTITIONS*/
select a.title,a.postdate,a.url,b.* from apk_index a,apk_xx b where a.tid=b.tid and 
a.ifpub=1 and a.publisher != 'adminzhuima'  order by postdate desc  limit 1294510,18\G

pt-query-digest的更多實用功能請關注官網：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

作者：追馬

博客鏈接：http://lovelace.blog.51cto.com/