methane · December 5, 2011 08:54 · Dec 15, 2011 · Dec 15, 2011 · Dec 15, 2011 · Dec 15, 2011
diff --git a/myprofiler.py b/myprofiler.py
diff --git a/myprofiler.py b/myprofiler.py
@@ -17,10 +17,11 @@
 try:
     import MySQLdb  # MySQL-python
 except ImportError:
-    import pymysql as MySQLdb  # PyMySQL
-except ImportError:
-    print "Please install MySQLdb or PyMySQL"
-    sys.exit(1)
+    try:
+        import pymysql as MySQLdb  # PyMySQL
+    except ImportError:
+        print "Please install MySQLdb or PyMySQL"
+        sys.exit(1)
 
 
 CMD_PROCESSLIST = "show full processlist"

diff --git a/myprofiler.py b/myprofiler.py
@@ -6,12 +6,9 @@
 https://github.com/methane/myprofiler
 """
 
-from __future__ import print_function
-
 import os
 import sys
 import re
-import functools
 from time import sleep
 from collections import defaultdict
 from ConfigParser import SafeConfigParser
@@ -101,12 +98,11 @@ def build_option_parser():
 
 
 def show_summary(counter, limit, file=sys.stdout):
-    p = functools.partial(print, file=file)
-    p('---')
+    print >>file, '---'
     items = counter.items()
     items.sort(key=lambda x: x[1], reverse=True)
     for query, count in items[:limit]:
-        p("{0:4d} {1}".format(count, query))
+        print >>file, "%4d %s" % (count, query)
 
 
 def main():
@@ -129,14 +125,14 @@ def main():
                     continue
                 counter[normalize_query(row)] += 1
                 if outfile:
-                    print(row, file=outfile)
+                    print >>outfile, row
 
             show_summary(counter, opts.num_summary)
-            print()
+            print
             sleep(opts.interval)
     finally:
         if outfile:
-            print("\nSummary", file=outfile)
+            print >>outfile, "\nSummary"
             show_summary(counter, opts.num_summary, outfile)
 
 

diff --git a/myprofiler.py b/myprofiler.py
@@ -1,6 +1,11 @@
 #!/usr/bin/env python
 # coding: utf-8
 
+"""myprofiler - Casual MySQL Profiler
+
+https://github.com/methane/myprofiler
+"""
+
 from __future__ import print_function
 
 import os
@@ -25,10 +30,9 @@
 
 
 def connect(conf='~/.my.cnf', section='DEFAULT'):
-    u"""
-    ~/.my.cnf から接続に必要な情報を読み込む.
     """
-    #parser = SafeConfigParser(allow_no_value=True)
+    connect to MySQL from conf file.
+    """
     parser = SafeConfigParser()
     parser.read([os.path.expanduser(conf)])
 
@@ -41,16 +45,16 @@ def connect(conf='~/.my.cnf', section='DEFAULT'):
         return MySQLdb.connect(host=host, user=user, passwd=password)
 
 
-def gather_infos(con):
+def processlist(con):
     con.query(CMD_PROCESSLIST)
     for row in con.store_result().fetch_row(maxrows=200, how=1):
         if row['Info']:
             yield row['Info']
 
 
 def normalize_query(row):
-    u"""
-    クエリを種類ごとに集計するために変形する.
+    """
+    Modify query to summarize.
     """
     row = ' '.join(row.split())
     subs = [
@@ -104,6 +108,7 @@ def show_summary(counter, limit, file=sys.stdout):
     for query, count in items[:limit]:
         p("{0:4d} {1}".format(count, query))
 
+
 def main():
     parser = build_option_parser()
     opts, args = parser.parse_args()
@@ -112,15 +117,14 @@ def main():
         outfile = None
         if opts.out:
             outfile = open(opts.out, "w")
-
         con = connect(opts.config, opts.section)
-    except Exception as e:
+    except Exception, e:
         parser.error(e)
 
     counter = defaultdict(int)
     try:
         while True:
-            for row in gather_infos(con):
+            for row in processlist(con):
                 if row == CMD_PROCESSLIST:
                     continue
                 counter[normalize_query(row)] += 1

diff --git a/myprofiler.py b/myprofiler.py
@@ -12,8 +12,13 @@
 from ConfigParser import SafeConfigParser
 from optparse import OptionParser
 
-import MySQLdb  # MySQL-python
-#import pymysql as MySQLdb  # PyMySQL
+try:
+    import MySQLdb  # MySQL-python
+except ImportError:
+    import pymysql as MySQLdb  # PyMySQL
+except ImportError:
+    print "Please install MySQLdb or PyMySQL"
+    sys.exit(1)
 
 
 CMD_PROCESSLIST = "show full processlist"
@@ -132,4 +137,4 @@ def main():
 
 
 if __name__ == '__main__':
-    main()
+    main()
diff --git a/db_profiler.py → myprofiler.py b/db_profiler.py → myprofiler.py
diff --git a/db_profiler.py b/db_profiler.py
@@ -6,6 +6,7 @@
 import os
 import sys
 import re
+import functools
 from time import sleep
 from collections import defaultdict
 from ConfigParser import SafeConfigParser
@@ -90,35 +91,44 @@ def build_option_parser():
     return parser
 
 
+def show_summary(counter, limit, file=sys.stdout):
+    p = functools.partial(print, file=file)
+    p('---')
+    items = counter.items()
+    items.sort(key=lambda x: x[1], reverse=True)
+    for query, count in items[:limit]:
+        p("{0:4d} {1}".format(count, query))
+
 def main():
     parser = build_option_parser()
     opts, args = parser.parse_args()
 
-    outfile = None
-    if opts.out:
-        outfile = open(opts.out, "w")
-
     try:
+        outfile = None
+        if opts.out:
+            outfile = open(opts.out, "w")
+
         con = connect(opts.config, opts.section)
     except Exception as e:
         parser.error(e)
 
     counter = defaultdict(int)
-    while True:
-        for row in gather_infos(con):
-            if row == CMD_PROCESSLIST:
-                continue
-            counter[normalize_query(row)] += 1
-            if outfile:
-                print(row, file=outfile)
-
-        print('---')
-        items = counter.items()
-        items.sort(key=lambda x: x[1], reverse=True)
-        for query, count in items[:opts.num_summary]:
-            print("{0:4d} {1}".format(count, query))
-        print()
-        sleep(opts.interval)
+    try:
+        while True:
+            for row in gather_infos(con):
+                if row == CMD_PROCESSLIST:
+                    continue
+                counter[normalize_query(row)] += 1
+                if outfile:
+                    print(row, file=outfile)
+
+            show_summary(counter, opts.num_summary)
+            print()
+            sleep(opts.interval)
+    finally:
+        if outfile:
+            print("\nSummary", file=outfile)
+            show_summary(counter, opts.num_summary, outfile)
 
 
 if __name__ == '__main__':

diff --git a/db_profiler.py b/db_profiler.py
@@ -29,9 +29,10 @@ def connect(conf='~/.my.cnf', section='DEFAULT'):
     host = parser.get(section, 'host')
     user = parser.get(section, 'user')
     password = parser.get(section, 'password')
-    #port = parser.getint(section, 'port')
-    #return MySQLdb.connect(host=host, user=user, passwd=password, port=port)
-    return MySQLdb.connect(host=host, user=user, passwd=password)
+    if parser.has_option(section, 'port'):
+        return MySQLdb.connect(host=host, user=user, passwd=password, port=port)
+    else:
+        return MySQLdb.connect(host=host, user=user, passwd=password)
 
 
 def gather_infos(con):
@@ -64,39 +65,43 @@ def build_option_parser():
     parser = OptionParser()
     parser.add_option(
             '-o', '--out',
-            help="write raw queries to this file."
+            help="write raw queries to this file.",
             )
     parser.add_option(
             '-c', '--config',
-            help="read MySQL configuration instead of ~/.my.cnf",
+            help="read MySQL configuration from. (default: '~/.my.cnf'",
             default='~/.my.cnf'
             )
     parser.add_option(
             '-s', '--section',
-            help="read MySQL configuration from this section.",
+            help="read MySQL configuration from this section. (default: '[DEFAULT]')",
             default="DEFAULT"
             )
     parser.add_option(
             '-n', '--num-summary', metavar="K",
-            help="show most K common queries.",
+            help="show most K common queries. (default: 10)",
             type="int", default=10
             )
     parser.add_option(
             '-i', '--interval',
-            help="Interval of executing show processlist",
+            help="Interval of executing show processlist [sec] (default: 1.0)",
             type="float", default=1.0
             )
     return parser
 
 
 def main():
-    opts, args = build_option_parser().parse_args()
+    parser = build_option_parser()
+    opts, args = parser.parse_args()
 
     outfile = None
     if opts.out:
         outfile = open(opts.out, "w")
 
-    con = connect(opts.config, opts.section)
+    try:
+        con = connect(opts.config, opts.section)
+    except Exception as e:
+        parser.error(e)
 
     counter = defaultdict(int)
     while True:
@@ -111,7 +116,7 @@ def main():
         items = counter.items()
         items.sort(key=lambda x: x[1], reverse=True)
         for query, count in items[:opts.num_summary]:
-            print("{1:4d} {2}".format(count, query))
+            print("{0:4d} {1}".format(count, query))
         print()
         sleep(opts.interval)
 

diff --git a/db_profiler.py b/db_profiler.py
@@ -81,6 +81,11 @@ def build_option_parser():
             help="show most K common queries.",
             type="int", default=10
             )
+    parser.add_option(
+            '-i', '--interval',
+            help="Interval of executing show processlist",
+            type="float", default=1.0
+            )
     return parser
 
 
@@ -108,7 +113,7 @@ def main():
         for query, count in items[:opts.num_summary]:
             print("{1:4d} {2}".format(count, query))
         print()
-        sleep(1)
+        sleep(opts.interval)
 
 
 if __name__ == '__main__':

diff --git a/db_profiler.py b/db_profiler.py
@@ -1,46 +1,115 @@
 #!/usr/bin/env python
 # coding: utf-8
 
-import MySQLdb  # MySQL-python
-from collections import defaultdict
+from __future__ import print_function
+
+import os
+import sys
+import re
 from time import sleep
+from collections import defaultdict
+from ConfigParser import SafeConfigParser
+from optparse import OptionParser
+
+import MySQLdb  # MySQL-python
+#import pymysql as MySQLdb  # PyMySQL
+
+
+CMD_PROCESSLIST = "show full processlist"
+
+
+def connect(conf='~/.my.cnf', section='DEFAULT'):
+    u"""
+    ~/.my.cnf から接続に必要な情報を読み込む.
+    """
+    #parser = SafeConfigParser(allow_no_value=True)
+    parser = SafeConfigParser()
+    parser.read([os.path.expanduser(conf)])
+
+    host = parser.get(section, 'host')
+    user = parser.get(section, 'user')
+    password = parser.get(section, 'password')
+    #port = parser.getint(section, 'port')
+    #return MySQLdb.connect(host=host, user=user, passwd=password, port=port)
+    return MySQLdb.connect(host=host, user=user, passwd=password)
 
-def connect():
-    # 設定ファイル等から読み込む.
-    host = 'dbhost'
-    user = 'sampleuser'
-    passwd = 'samplesecret'
-    db = 'sampledb'
-    return MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
 
 def gather_infos(con):
-    con.query('show full processlist')
-    for row in con.store_result().fetch_row(maxrows=100, how=1):
+    con.query(CMD_PROCESSLIST)
+    for row in con.store_result().fetch_row(maxrows=200, how=1):
         if row['Info']:
             yield row['Info']
 
+
+def normalize_query(row):
+    u"""
+    クエリを種類ごとに集計するために変形する.
+    """
+    row = ' '.join(row.split())
+    subs = [
+            (r"\b\d+\b", "N"),
+            (r"\b0x[0-9A-Fa-f]+\b", "0xN"),
+            (r"(\\')", ''),
+            (r'(\\")', ''),
+            (r"'[^']+'", "'S'"),
+            (r'"[^"]+"', '"S"'),
+            (r'(([NS],){4,})', r'...'),
+            ]
+    for pat,sub in subs:
+        row = re.sub(pat, sub, row)
+    return row
+
+
+def build_option_parser():
+    parser = OptionParser()
+    parser.add_option(
+            '-o', '--out',
+            help="write raw queries to this file."
+            )
+    parser.add_option(
+            '-c', '--config',
+            help="read MySQL configuration instead of ~/.my.cnf",
+            default='~/.my.cnf'
+            )
+    parser.add_option(
+            '-s', '--section',
+            help="read MySQL configuration from this section.",
+            default="DEFAULT"
+            )
+    parser.add_option(
+            '-n', '--num-summary', metavar="K",
+            help="show most K common queries.",
+            type="int", default=10
+            )
+    return parser
+
+
 def main():
-    con = connect()
-    # Python 2.7 では Counter を使うともっとシンプルに書ける.
-    dic = defaultdict(int)
+    opts, args = build_option_parser().parse_args()
+
+    outfile = None
+    if opts.out:
+        outfile = open(opts.out, "w")
+
+    con = connect(opts.config, opts.section)
 
+    counter = defaultdict(int)
     while True:
         for row in gather_infos(con):
-            if row == 'show full processlist':
+            if row == CMD_PROCESSLIST:
                 continue
-            # ざっくりと、変数ぽい部分をカット
-            for k in ('=', ' IN ', ' BETWEEN '):
-                row = row.split(k)[0]
-                row = row.split(k.lower())[0]
-            dic[row] += 1
+            counter[normalize_query(row)] += 1
+            if outfile:
+                print(row, file=outfile)
 
-        items = dic.items()
+        print('---')
+        items = counter.items()
         items.sort(key=lambda x: x[1], reverse=True)
-        print '--'
-        for it in items[:20]:
-            print it[1], it[0]
-
+        for query, count in items[:opts.num_summary]:
+            print("{1:4d} {2}".format(count, query))
+        print()
         sleep(1)
 
+
 if __name__ == '__main__':
     main()
diff --git a/db_profiler.py b/db_profiler.py
@@ -0,0 +1,46 @@
+#!/usr/bin/env python
+# coding: utf-8
+
+import MySQLdb  # MySQL-python
+from collections import defaultdict
+from time import sleep
+
+def connect():
+    # 設定ファイル等から読み込む.
+    host = 'dbhost'
+    user = 'sampleuser'
+    passwd = 'samplesecret'
+    db = 'sampledb'
+    return MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
+
+def gather_infos(con):
+    con.query('show full processlist')
+    for row in con.store_result().fetch_row(maxrows=100, how=1):
+        if row['Info']:
+            yield row['Info']
+
+def main():
+    con = connect()
+    # Python 2.7 では Counter を使うともっとシンプルに書ける.
+    dic = defaultdict(int)
+
+    while True:
+        for row in gather_infos(con):
+            if row == 'show full processlist':
+                continue
+            # ざっくりと、変数ぽい部分をカット
+            for k in ('=', ' IN ', ' BETWEEN '):
+                row = row.split(k)[0]
+                row = row.split(k.lower())[0]
+            dic[row] += 1
+
+        items = dic.items()
+        items.sort(key=lambda x: x[1], reverse=True)
+        print '--'
+        for it in items[:20]:
+            print it[1], it[0]
+
+        sleep(1)
+
+if __name__ == '__main__':
+    main()
No results found