1#@!#!123s

: / opt / dedrads / mysql /
Filename : slowqueryparser.py
back
#!/usr/lib/rads/venv/bin/python3
import argparse
import configparser
from pathlib import Path
import sys
import re
from pymysql.optionfile import Parser as PyMySQLParser

USER_RE = re.compile(r'^# User@Host:\s+([a-z0-9]+)')
STATS_RE = re.compile(
    r'^# Query_time:\s+([0-9\.]+)\s+Lock_time:\s+([0-9\.]+)\s+'
    r'Rows_sent:\s+(\d+)\s+Rows_examined:\s+(\d+)'
)


def parse_args():
    parser = argparse.ArgumentParser()
    # fmt: off
    parser.add_argument(
        "-q", "--quiet", dest="quiet", action='store_true',
        help="Suppress stderr output",
    )
    parser.add_argument(
        '-H', '--no-header', dest='no_header', action='store_true',
        help='Suppress column headers',
    )
    parser.add_argument(
        "-o", "--output", metavar="FILE",
        help="Write output to FILE (default: stdout)",
    )
    parser.add_argument(
        "-u", "--user", metavar="USER", default=None,
        help="Output USER's queries instead of tallys",
    )
    parser.add_argument(
        "-a", "--average", action="store_true",
        help="Print averages per query instead of totals",
    )
    parser.add_argument('logpath', nargs='?', help='Path to slow query log')
    # fmt: on
    return parser.parse_args()


class MySQLUser:
    """Holds a user name and tracks numbers of queries"""

    def __init__(self, username: str):
        self.username = username
        self.num_queries = 0
        self.query_time = 0.0
        self.lock_time = 0.0
        self.rows_sent = 0
        self.rows_examined = 0

    def add_query(self, stats_match: re.Match):
        query_time, lock_time, rows_sent, rows_examined = stats_match.groups()
        self.num_queries += 1
        self.query_time += float(query_time)
        self.lock_time += float(lock_time)
        self.rows_sent += int(rows_sent)
        self.rows_examined += int(rows_examined)

    @classmethod
    def row_header(cls, file=sys.stdout):
        cls.header(
            ['QUERIES', 'TIME', 'LOCKTIME', 'ROWSSENT', 'ROWSRECVD'], file=file
        )

    @classmethod
    def avg_header(cls, file=sys.stdout):
        cls.header(
            ['QUERIES', 'TIME/Q', 'LOCKTIME/Q', 'ROWSSENT/Q', 'ROWSRECV/Q'],
            file=file,
        )

    @staticmethod
    def header(cols: list[str], file=sys.stdout):
        print('USER'.rjust(16), end=' ', file=file)
        print(*map(lambda x: x.rjust(10), cols), file=file)

    def row_print(self, file=sys.stdout):
        print(
            self.username.rjust(16),
            str(self.num_queries).rjust(10),
            str(int(self.query_time)).rjust(10),
            str(int(self.lock_time)).rjust(10),
            str(self.rows_sent).rjust(10),
            str(self.rows_examined).rjust(10),
            file=file,
        )

    def avg_print(self, file=sys.stdout):
        print(
            self.username.rjust(16),
            str(self.num_queries).rjust(10),
            str(int(self.query_time / self.num_queries)).rjust(10),
            str(int(self.lock_time / self.num_queries)).rjust(10),
            str(int(self.rows_sent / self.num_queries)).rjust(10),
            str(int(self.rows_examined / self.num_queries)).rjust(10),
            file=file,
        )


def default_log_path():
    try:
        parser = PyMySQLParser(strict=False)
        if not parser.read('/etc/my.cnf'):
            return None
        path = Path(parser.get('mysqld', 'slow_query_log_file')).resolve()
        if path == Path('/dev/null'):
            print("MySQL log points to /dev/null currently", file=sys.stderr)
            return None
        return str(path)
    except configparser.Error:
        return None


def open_log(args):
    # if nothing piped to stdin and no path supplied
    if not args.logpath and sys.stdin.isatty():
        query_log = default_log_path()
        if not query_log:
            print(
                "Failed to get slow query log path from /etc/my.cnf",
                file=sys.stderr,
            )
            sys.exit(1)
        if not args.quiet:
            print(
                f"Reading from the default log file, `{query_log}'",
                file=sys.stderr,
            )
            return open(query_log, encoding='utf-8', errors='replace')
    # if something piped to stdin with no path supplied, or explicitly sent -
    if not args.logpath or args.logpath == '-':
        query_log = sys.stdin
        if not args.quiet:
            print(
                "MySQL slow query log parser reading from stdin/pipe...",
                file=sys.stderr,
            )
            return sys.stdin
    return open(args.logpath, encoding='utf-8', errors='replace')


def iter_log(args):
    try:
        with open_log(args) as query_log:
            while line := query_log.readline():
                yield line
    except OSError as exc:
        sys.exit(exc)


def main():
    args = parse_args()
    if args.output:  # if we've specified an output file
        out_file = open(args.output, "w", encoding='utf-8')
    else:
        out_file = sys.stdout
    with out_file:
        # init user and id dictionaries
        user_table: dict[str, MySQLUser] = {}
        this_user = "NO_SUCH_USER"
        for line in iter_log(args):
            if user_match := USER_RE.match(line):
                this_user = user_match.group(1)
                if args.user and this_user == args.user:
                    print(line, end=' ', file=out_file)
            elif stats_match := STATS_RE.match(line):
                if this_user not in user_table:
                    user_table[this_user] = MySQLUser(this_user)
                user_table[this_user].add_query(stats_match)
                if args.user and this_user == args.user:
                    print(line, end=' ', file=out_file)
            elif args.user and this_user == args.user:
                try:
                    print(line, end='', file=out_file)
                except Exception:
                    sys.exit(0)
        if args.user:
            return
        if not args.no_header:
            if args.average:
                MySQLUser.avg_header(out_file)
            else:
                MySQLUser.row_header(out_file)
        for data in sorted(user_table.values(), key=lambda x: x.num_queries):
            if args.average:
                data.avg_print(out_file)
            else:
                data.row_print(out_file)


if __name__ == '__main__':
    main()