標籤：mongodb 慢查詢 分析指令碼
1 簡介
這個是用在生產環境中的一個MongoDB慢查詢日誌自動收集指令碼，當初想寫這個指令碼的思路就是為了方便收集慢查詢日誌並且利於分析。由於公司的mognodb不多，就4台，所以這個小指令碼也只是適用普通的生產環境。
頁面主要使用了 bootstrap 為最上層顯示，datatable 為分析的時候使用。
2 流程
主要的流程是：
1 通過 pymongo 串連到 mongodb
2 使用find()命令查詢 system.profile下的所有慢查詢日誌
3 使用jinjia2模組，把查詢到的日誌資訊整合到一個HTML模版中去。
4 把整合後的HTML檔案，以附件的形式發送郵件給本人（使用sendmail模組）
5 將指令碼寫到 crontab 中去，每天定時發送郵件。
注意： 前提是要開啟慢查詢日誌，開啟方法見：http://yucanghai.blog.51cto.com/5260262/1705195
3 代碼
3.1 檔案結構
config.py # 設定檔mongodb_slowlog.html #產生的html檔案mongodb_slowlog.py # 主檔案myhtml.py # html模組
3.2 代碼資訊
1 config.py
#!/usr/bin/env python# -*- coding: utf-8 -*-mail_to_list = [‘[email protected]‘] #收件者清單mail_host = ‘smtp.163.com‘mail_user = ‘xxx‘ #寄件者使用者mail_pass = ‘xxx‘mail_postfix = ‘163.com‘#mongodb 的使用者和密碼usename = ‘root‘password = ‘xxx‘# db 為要分析的 資料庫ip_db_list = [ {‘ip‘:‘10.18.96.86‘,‘port‘:27017,‘db‘:‘test‘,‘hostname‘:‘test1‘,‘property‘:‘primary‘,‘slow_log‘:[]},]
2 mongodb_slowlog.py
#!/usr/bin/env python# -*- coding: utf-8 -*-import pymongoimport bsonfrom jinja2 import Templatefrom myhtml import html_strimport configimport smtplibfrom email.mime.text import MIMETextfrom email.mime.multipart import MIMEMultipartimport timeimport datetimedef _conn(ip,port): try: conn = pymongo.MongoClient(‘%s‘%ip,port) except Exception,e: conn = None print e return conn#將系統時間轉化為 mongodb能識別的時間def convert_time(t): tm = bson.Timestamp(t,1) return tm.as_datetime()#發送郵件 -- 有附件def send_mail(): msg = MIMEMultipart() att = MIMEText(open(‘mongodb_slowlog.html‘,‘rb‘).read(),‘base64‘,‘gb2312‘) att[‘Content-Type‘] = ‘application/octet-stream‘ att["Content-Disposition"] = ‘attachment; filename="MongoDB-Slow-Log%s.html"‘%time.strftime(‘%Y-%m-%d %H:%M:%S‘) msg.attach(att) me = ‘MongoDB‘+‘<‘+config.mail_user+"@"+config.mail_postfix+">" msg[‘Subject‘] = ‘MongoDB Slow Log -- %s‘%time.strftime(‘%Y-%m-%d %H:%M:%S‘) msg[‘From‘] = me msg[‘To‘] = ‘;‘.join(config.mail_to_list) try: server = smtplib.SMTP() server.connect(config.mail_host) server.login(config.mail_user,config.mail_pass) server.sendmail(me,config.mail_to_list,msg.as_string()) server.quit() return True except Exception,e: print e return False#擷取慢日誌資訊def profile_info(today,yesterday): for ip_db in config.ip_db_list: conn = _conn(ip_db[‘ip‘],ip_db[‘port‘]) #使用者驗證 #auth_db = conn.admin #auth_db.authenticate(config.usename,config.password) db_conn = getattr(conn,ip_db[‘db‘]) if db_conn.profiling_level(): #擷取 一天內的 排除 getmore 和 comadn 後的 慢日誌 ip_db[‘slow_log‘].append(list(db_conn.system.profile.find({‘ts‘:{"$lt":today,"$gt":yesterday}, "op":{"$nin":[‘getmore‘,‘command‘]}}))) #print ip_db[‘slow_log‘] #解析擷取到的日誌到 html中 template = Template(html_str) rest_html = template.render(db_info = ip_db) #print rest_html #將擷取到的html 寫如檔案，作為附件發送給 使用者 with open(‘mongodb_slowlog.html‘,‘w‘) as f: f.write(rest_html) print ‘發送成功！‘ if send_mail() else ‘發送失敗！‘if __name__ == ‘__main__‘: today_time = convert_time(datetime.datetime.today()) yesterday_time = convert_time(today_time + datetime.timedelta(days=-1)) profile_info(today_time,yesterday_time)
備忘：
可以根據自己的需求更改，思路不變
3 myhtml.py
#!/usr/bin/env python# -*- coding: utf-8 -*-html_str =‘‘‘<!DOCTYPE html><html lang="en"><head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags --> <meta name="description" content=""> <meta name="author" content=""> <title>MongoDB Slow Log</title> <!-- Bootstrap core CSS --> <link rel="stylesheet" href="http://cdn.bootcss.com/bootstrap/3.3.5/css/bootstrap.min.css"> <link rel="stylesheet" href="http://cdn.bootcss.com/bootstrap/3.3.5/css/bootstrap-theme.min.css"> <link rel="stylesheet" href="http://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"> <!--[endif]--></head><body><nav class="navbar navbar-inverse navbar-fixed-top"> <div class="container-fluid"> <div class="navbar-header"> <a class="navbar-brand" href="#">MongoDB Slow Log</a> </div> </div></nav><div class="container-fluid"> <div class="row"> <div class="col-sm-9 col-sm-offset-1 col-md-10 col-md-offset-1 main"> <br><br><br><br> <h4 class="page-header" style="display: inline">IP:{{ db_info.ip }}</h4> <h4 class="page-header" style="display: inline">HostName:{{ db_info.hostname }}</h4> <h4 class="page-header" style="display: inline">Property:{{ db_info.property }}</h4> <br><br> <div class="table-responsive"> <table id="myTable" class="table table-striped"> <thead> <tr> <th>ID</th> <th>Namespace</th> <th>Option</th> <th>Query</th> <th>Millis</th> <th>Nscanned</th> <th>Nreturned</th> <th>ResponseLength</th> <th>Ts</th> </tr> </thead> <tbody> {% for item in db_info.slow_log %} {% for i in item %} <tr> <td>{{ loop.index }}</td> <td>{{ i.ns }}</td> <td>{{ i.op }}</td> <td>{{ i.query }}</td> <td>{{ i.millis }}</td> <td>{{ i.nscanned }}</td> <td>{{ i.nreturned }}</td> <td>{{ i.responseLength }}</td> <td>{{ i.ts }}</td> </tr> {% endfor %} {% endfor %} </tbody> </table> </div> </div> </div></div><!-- Bootstrap core JavaScript================================================== --><!-- Placed at the end of the document so the pages load faster --><script src="http://cdn.bootcss.com/jquery/1.11.3/jquery.min.js"></script><script src="http://cdn.bootcss.com/bootstrap/3.3.5/js/bootstrap.min.js"></script><script src="http://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script><script type="text/javascript"> $(‘#myTable‘).DataTable( { select: true } );</script></body></html>‘‘‘
4 mongodb_slowlog.html
主要為通過 myhtml.py 產生的模版
這個指令碼寫的比較匆忙，還有不完善的地方，有什麼疑問大家都可以來問我呀
本文出自 “一個奮鬥的小營運” 部落格，請務必保留此出處http://yucanghai.blog.51cto.com/5260262/1705559
mongodb+python 實現自動收集mongodb的慢查詢日誌（附代碼）
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
