#!/opt/imh-python/bin/python3

[0] Fix | Delete

import argparse

[1] Fix | Delete

import configparser

[2] Fix | Delete

from pathlib import Path

[3] Fix | Delete

import sys

[4] Fix | Delete

import re

[5] Fix | Delete

from pymysql.optionfile import Parser as PyMySQLParser

[6] Fix | Delete

[7] Fix | Delete

USER_RE = re.compile(r'^# User@Host:\s+([a-z0-9]+)')

[8] Fix | Delete

STATS_RE = re.compile(

[9] Fix | Delete

r'^# Query_time:\s+([0-9\.]+)\s+Lock_time:\s+([0-9\.]+)\s+'

[10] Fix | Delete

r'Rows_sent:\s+(\d+)\s+Rows_examined:\s+(\d+)'

[11] Fix | Delete

)

[12] Fix | Delete

[13] Fix | Delete

[14] Fix | Delete

def parse_args():

[15] Fix | Delete

parser = argparse.ArgumentParser()

[16] Fix | Delete

# fmt: off

[17] Fix | Delete

parser.add_argument(

[18] Fix | Delete

"-q", "--quiet", dest="quiet", action='store_true',

[19] Fix | Delete

help="Suppress stderr output",

[20] Fix | Delete

)

[21] Fix | Delete

parser.add_argument(

[22] Fix | Delete

'-H', '--no-header', dest='no_header', action='store_true',

[23] Fix | Delete

help='Suppress column headers',

[24] Fix | Delete

)

[25] Fix | Delete

parser.add_argument(

[26] Fix | Delete

"-o", "--output", metavar="FILE",

[27] Fix | Delete

help="Write output to FILE (default: stdout)",

[28] Fix | Delete

)

[29] Fix | Delete

parser.add_argument(

[30] Fix | Delete

"-u", "--user", metavar="USER", default=None,

[31] Fix | Delete

help="Output USER's queries instead of tallys",

[32] Fix | Delete

)

[33] Fix | Delete

parser.add_argument(

[34] Fix | Delete

"-a", "--average", action="store_true",

[35] Fix | Delete

help="Print averages per query instead of totals",

[36] Fix | Delete

)

[37] Fix | Delete

parser.add_argument('logpath', nargs='?', help='Path to slow query log')

[38] Fix | Delete

# fmt: on

[39] Fix | Delete

return parser.parse_args()

[40] Fix | Delete

[41] Fix | Delete

[42] Fix | Delete

class MySQLUser:

[43] Fix | Delete

"""Holds a user name and tracks numbers of queries"""

[44] Fix | Delete

[45] Fix | Delete

def __init__(self, username: str):

[46] Fix | Delete

self.username = username

[47] Fix | Delete

self.num_queries = 0

[48] Fix | Delete

self.query_time = 0.0

[49] Fix | Delete

self.lock_time = 0.0

[50] Fix | Delete

self.rows_sent = 0

[51] Fix | Delete

self.rows_examined = 0

[52] Fix | Delete

[53] Fix | Delete

def add_query(self, stats_match: re.Match):

[54] Fix | Delete

query_time, lock_time, rows_sent, rows_examined = stats_match.groups()

[55] Fix | Delete

self.num_queries += 1

[56] Fix | Delete

self.query_time += float(query_time)

[57] Fix | Delete

self.lock_time += float(lock_time)

[58] Fix | Delete

self.rows_sent += int(rows_sent)

[59] Fix | Delete

self.rows_examined += int(rows_examined)

[60] Fix | Delete

[61] Fix | Delete

@classmethod

[62] Fix | Delete

def row_header(cls, file=sys.stdout):

[63] Fix | Delete

cls.header(

[64] Fix | Delete

['QUERIES', 'TIME', 'LOCKTIME', 'ROWSSENT', 'ROWSRECVD'], file=file

[65] Fix | Delete

)

[66] Fix | Delete

[67] Fix | Delete

@classmethod

[68] Fix | Delete

def avg_header(cls, file=sys.stdout):

[69] Fix | Delete

cls.header(

[70] Fix | Delete

['QUERIES', 'TIME/Q', 'LOCKTIME/Q', 'ROWSSENT/Q', 'ROWSRECV/Q'],

[71] Fix | Delete

file=file,

[72] Fix | Delete

)

[73] Fix | Delete

[74] Fix | Delete

@staticmethod

[75] Fix | Delete

def header(cols: list[str], file=sys.stdout):

[76] Fix | Delete

print('USER'.rjust(16), end=' ', file=file)

[77] Fix | Delete

print(*map(lambda x: x.rjust(10), cols), file=file)

[78] Fix | Delete

[79] Fix | Delete

def row_print(self, file=sys.stdout):

[80] Fix | Delete

print(

[81] Fix | Delete

self.username.rjust(16),

[82] Fix | Delete

str(self.num_queries).rjust(10),

[83] Fix | Delete

str(int(self.query_time)).rjust(10),

[84] Fix | Delete

str(int(self.lock_time)).rjust(10),

[85] Fix | Delete

str(self.rows_sent).rjust(10),

[86] Fix | Delete

str(self.rows_examined).rjust(10),

[87] Fix | Delete

file=file,

[88] Fix | Delete

)

[89] Fix | Delete

[90] Fix | Delete

def avg_print(self, file=sys.stdout):

[91] Fix | Delete

print(

[92] Fix | Delete

self.username.rjust(16),

[93] Fix | Delete

str(self.num_queries).rjust(10),

[94] Fix | Delete

str(int(self.query_time / self.num_queries)).rjust(10),

[95] Fix | Delete

str(int(self.lock_time / self.num_queries)).rjust(10),

[96] Fix | Delete

str(int(self.rows_sent / self.num_queries)).rjust(10),

[97] Fix | Delete

str(int(self.rows_examined / self.num_queries)).rjust(10),

[98] Fix | Delete

file=file,

[99] Fix | Delete

)

[100] Fix | Delete

[101] Fix | Delete

[102] Fix | Delete

def default_log_path():

[103] Fix | Delete

try:

[104] Fix | Delete

parser = PyMySQLParser(strict=False)

[105] Fix | Delete

if not parser.read('/etc/my.cnf'):

[106] Fix | Delete

return None

[107] Fix | Delete

path = Path(parser.get('mysqld', 'slow_query_log_file')).resolve()

[108] Fix | Delete

if path == Path('/dev/null'):

[109] Fix | Delete

print("MySQL log points to /dev/null currently", file=sys.stderr)

[110] Fix | Delete

return None

[111] Fix | Delete

return str(path)

[112] Fix | Delete

except configparser.Error:

[113] Fix | Delete

return None

[114] Fix | Delete

[115] Fix | Delete

[116] Fix | Delete

def open_log(args):

[117] Fix | Delete

# if nothing piped to stdin and no path supplied

[118] Fix | Delete

if not args.logpath and sys.stdin.isatty():

[119] Fix | Delete

query_log = default_log_path()

[120] Fix | Delete

if not query_log:

[121] Fix | Delete

print(

[122] Fix | Delete

"Failed to get slow query log path from /etc/my.cnf",

[123] Fix | Delete

file=sys.stderr,

[124] Fix | Delete

)

[125] Fix | Delete

sys.exit(1)

[126] Fix | Delete

if not args.quiet:

[127] Fix | Delete

print(

[128] Fix | Delete

f"Reading from the default log file, `{query_log}'",

[129] Fix | Delete

file=sys.stderr,

[130] Fix | Delete

)

[131] Fix | Delete

return open(query_log, encoding='utf-8', errors='replace')

[132] Fix | Delete

# if something piped to stdin with no path supplied, or explicitly sent -

[133] Fix | Delete

if not args.logpath or args.logpath == '-':

[134] Fix | Delete

query_log = sys.stdin

[135] Fix | Delete

if not args.quiet:

[136] Fix | Delete

print(

[137] Fix | Delete

"MySQL slow query log parser reading from stdin/pipe...",

[138] Fix | Delete

file=sys.stderr,

[139] Fix | Delete

)

[140] Fix | Delete

return sys.stdin

[141] Fix | Delete

return open(args.logpath, encoding='utf-8', errors='replace')

[142] Fix | Delete

[143] Fix | Delete

[144] Fix | Delete

def iter_log(args):

[145] Fix | Delete

try:

[146] Fix | Delete

with open_log(args) as query_log:

[147] Fix | Delete

while line := query_log.readline():

[148] Fix | Delete

yield line

[149] Fix | Delete

except OSError as exc:

[150] Fix | Delete

sys.exit(exc)

[151] Fix | Delete

[152] Fix | Delete

[153] Fix | Delete

def main():

[154] Fix | Delete

args = parse_args()

[155] Fix | Delete

if args.output: # if we've specified an output file

[156] Fix | Delete

out_file = open(args.output, "w", encoding='utf-8')

[157] Fix | Delete

else:

[158] Fix | Delete

out_file = sys.stdout

[159] Fix | Delete

with out_file:

[160] Fix | Delete

# init user and id dictionaries

[161] Fix | Delete

user_table: dict[str, MySQLUser] = {}

[162] Fix | Delete

this_user = "NO_SUCH_USER"

[163] Fix | Delete

for line in iter_log(args):

[164] Fix | Delete

if user_match := USER_RE.match(line):

[165] Fix | Delete

this_user = user_match.group(1)

[166] Fix | Delete

if args.user and this_user == args.user:

[167] Fix | Delete

print(line, end=' ', file=out_file)

[168] Fix | Delete

elif stats_match := STATS_RE.match(line):

[169] Fix | Delete

if this_user not in user_table:

[170] Fix | Delete

user_table[this_user] = MySQLUser(this_user)

[171] Fix | Delete

user_table[this_user].add_query(stats_match)

[172] Fix | Delete

if args.user and this_user == args.user:

[173] Fix | Delete

print(line, end=' ', file=out_file)

[174] Fix | Delete

elif args.user and this_user == args.user:

[175] Fix | Delete

try:

[176] Fix | Delete

print(line, end='', file=out_file)

[177] Fix | Delete

except Exception:

[178] Fix | Delete

sys.exit(0)

[179] Fix | Delete

if args.user:

[180] Fix | Delete

return

[181] Fix | Delete

if not args.no_header:

[182] Fix | Delete

if args.average:

[183] Fix | Delete

MySQLUser.avg_header(out_file)

[184] Fix | Delete

else:

[185] Fix | Delete

MySQLUser.row_header(out_file)

[186] Fix | Delete

for data in sorted(user_table.values(), key=lambda x: x.num_queries):

[187] Fix | Delete

if args.average:

[188] Fix | Delete

data.avg_print(out_file)

[189] Fix | Delete

else:

[190] Fix | Delete

data.row_print(out_file)

[191] Fix | Delete

[192] Fix | Delete

[193] Fix | Delete

if __name__ == '__main__':

[194] Fix | Delete

main()

[195] Fix | Delete

[196] Fix | Delete