1#@!#!123s

��JFIF�� 1#@!#!123s
: / opt / maint / bin /
Filename : large_mysql_table_str.py
back
#!/opt/imh-python/bin/python3
"""This script will scan a directory for .ibd files
over a certain size"""
import platform
from pathlib import Path
import rads


FLAG_SIZE = 10 * 2 ** 30  # 10 GiB

# Our scan root
MYSQL_DIR = '/var/lib/mysql'

HOST = platform.node().split('.', maxsplit=1)[0]

BODY = """
 1) Optimize/Shrink Table with mysql> optimize table TABLE;
 2) If size does not change, request CX to clean it up.
 3) Offer to convert table to MyISAM.
 4) If application depends on InnoDB - upgrade path to VPS
"""


def human_readable(bytes_size: int) -> str:
    """Converts bytes to sensible data"""
    symbol = ['B', 'KB', 'MB', 'GB']
    if bytes_size == 0:
        return '0 B'
    i = 0
    while bytes_size >= 1024 and i < len(symbol) - 1:
        bytes_size /= 1024
        i += 1
    f_val = f"{bytes_size:.2f}".rstrip('0').rstrip('.')
    return f'{f_val}{symbol[i]}'


def find_large_ibd_files():
    """Scans for ibd files over a certain size"""
    flagged_files = []
    for path in Path('/var/lib/mysql').glob('*/*.ibd'):
        if not path.is_file():
            continue
        try:
            size = path.stat().st_size
        except OSError:
            continue
        if size > FLAG_SIZE:
            flagged_files.append(f"{human_readable(size)} - {path}")
    return flagged_files


def main():
    """Sends STR for large InnoDB tables"""
    if large_ibd_tables := find_large_ibd_files():
        joined = '\n'.join(large_ibd_tables)
        rads.send_email(
            to_addr="str@imhadmin.net",
            subject=f"Large InnoDB Tables on {HOST}",
            body=f"{joined}\n{BODY}",
        )


if __name__ == '__main__':
    main()