#!/usr/lib64/linuxfabrik-monitoring-plugins/venv/bin/python
# -*- coding: utf-8; py-indent-offset: 4 -*-
#
# Author:  Linuxfabrik GmbH, Zurich, Switzerland
# Contact: info (at) linuxfabrik (dot) ch
#          https://www.linuxfabrik.ch/
# License: The Unlicense, see LICENSE file.

# https://github.com/Linuxfabrik/monitoring-plugins/blob/main/CONTRIBUTING.md

"""See the check's README for more details."""

import argparse
import os
import sys

import lib.args
import lib.base
import lib.db_mysql
import lib.human
from lib.globals import STATE_OK, STATE_UNKNOWN, STATE_WARN

__author__ = 'Linuxfabrik GmbH, Zurich/Switzerland'
__version__ = '2026051101'

DESCRIPTION = """Checks the InnoDB buffer pool size configuration in MySQL/MariaDB. Compares the
configured `innodb_buffer_pool_size` against the actual InnoDB data and index sizes, and on
MySQL 8.0.30+ derives a workload-based recommendation for `innodb_redo_log_capacity` from
the per-hour `Innodb_os_log_written` write rate and the host's RAM tier (matches
mysqltuner). Also flags `innodb_file_per_table = OFF` and architecture-related
buffer-pool size limits.
Alerts if the buffer pool is undersized relative to the data or if `innodb_redo_log_capacity`
is smaller than the workload-based target. On older MySQL and on MariaDB (no
`innodb_redo_log_capacity`), the redo-log size check is skipped; the redo-log file size is
still emitted as perfdata for trending."""

DEFAULT_DEFAULTS_FILE = '/var/spool/icinga2/.my.cnf'
DEFAULT_DEFAULTS_GROUP = 'client'
DEFAULT_TIMEOUT = 3

# 32-bit address space ceiling for innodb_buffer_pool_size (`2**32 - 1`).
LIMIT_32BIT = 4294967295
# 64-bit theoretical ceiling for innodb_buffer_pool_size (`2**64 - 1`).
LIMIT_64BIT = 18446744073709551615

# Workload-based redo log capacity check (matches mysqltuner):
# - need at least 1 h of uptime for the hourly rate to be meaningful
# - alert when the current capacity is more than 10% below the recommendation
REDO_LOG_MIN_UPTIME = 3600
REDO_LOG_TOLERANCE = 0.9


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(description=DESCRIPTION)

    parser.add_argument(
        '-V',
        '--version',
        action='version',
        version=f'%(prog)s: v{__version__} by {__author__}',
    )

    parser.add_argument(
        '--always-ok',
        help=lib.args.help('--always-ok'),
        dest='ALWAYS_OK',
        action='store_true',
        default=False,
    )

    parser.add_argument(
        '--defaults-file',
        help='MySQL/MariaDB cnf file to read user, host and password from. '
        'Example: `--defaults-file=/var/spool/icinga2/.my.cnf`. '
        'Default: %(default)s',
        dest='DEFAULTS_FILE',
        default=DEFAULT_DEFAULTS_FILE,
    )

    parser.add_argument(
        '--defaults-group',
        help=lib.args.help('--defaults-group') + ' Default: %(default)s',
        dest='DEFAULTS_GROUP',
        default=DEFAULT_DEFAULTS_GROUP,
    )

    parser.add_argument(
        '--timeout',
        help=lib.args.help('--timeout') + ' Default: %(default)s (seconds)',
        dest='TIMEOUT',
        type=int,
        default=DEFAULT_TIMEOUT,
    )

    args, _ = parser.parse_known_args()
    return args


def get_vars(conn):
    # Do not implement `get_all_vars()`, just fetch the ones we need for this check.
    # Without the GLOBAL modifier, SHOW VARIABLES displays the values that are used for
    # the current connection to MariaDB.
    sql = """
        show global variables
        where variable_name like 'innodb_buffer_pool_size'
            or variable_name like 'innodb_file_per_table'
            or variable_name like 'innodb_log_file_size'
            or variable_name like 'innodb_redo_log_capacity'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_status(conn):
    sql = """
        show global status
        where variable_name like 'Innodb_os_log_written'
            or variable_name like 'Uptime'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_physical_memory():
    """Return the host's physical RAM in bytes, or `None` when we cannot
    determine it (non-Linux POSIX implementations without `SC_PHYS_PAGES`).
    """
    try:
        return os.sysconf('SC_PAGE_SIZE') * os.sysconf('SC_PHYS_PAGES')
    except (ValueError, OSError):
        return None


def recommended_redo_log_capacity(hourly_rate, physical_memory):
    """Return the recommended `innodb_redo_log_capacity` for this server,
    matching mysqltuner's RAM-tier rounding:

    - < 2 GiB RAM: at least 100 MiB, rounded up to the next 100 MiB
    - < 8 GiB RAM: at least 100 MiB, rounded up to the next 100 MiB, capped at 1 GiB
    - >= 8 GiB RAM: at least 1 GiB, rounded up to the next 1 GiB, capped at 16 GiB
    """
    mib_100 = 100 * 1024 * 1024
    gib_1 = 1024 * 1024 * 1024

    recommended = hourly_rate
    if physical_memory < 2 * gib_1:
        recommended = max(recommended, mib_100)
        recommended = -(-recommended // mib_100) * mib_100
    elif physical_memory < 8 * gib_1:
        recommended = max(recommended, mib_100)
        recommended = -(-recommended // mib_100) * mib_100
        recommended = min(recommended, gib_1)
    else:
        recommended = max(recommended, gib_1)
        recommended = -(-recommended // gib_1) * gib_1
        recommended = min(recommended, 16 * gib_1)
    return int(recommended)


def main():
    """The main function. This is where the magic happens."""

    # logic taken from mysqltuner.pl:mysql_innodb(), section "InnoDB Buffer Pool
    # Size", verified in sync with MySQLTuner (architecture limits,
    # buffer-pool-vs-data-size check, and the workload-based
    # `innodb_redo_log_capacity` recommendation for MySQL 8.0.30+). The older
    # 25% log-size-ratio rule that mysqltuner used pre-8.0.30 is intentionally
    # not ported - it is an unsourced heuristic that mysqltuner itself
    # replaced with the workload-based path. On servers without
    # `innodb_redo_log_capacity` (MariaDB, MySQL < 8.0.30) the redo-log sizing
    # is skipped; admins still get the underlying `innodb_log_file_size` as
    # perfdata for trending.

    # parse the command line
    try:
        args = parse_args()
    except SystemExit:
        sys.exit(STATE_UNKNOWN)

    # fetch data
    mysql_connection = {
        'defaults_file': args.DEFAULTS_FILE,
        'defaults_group': args.DEFAULTS_GROUP,
        'timeout': args.TIMEOUT,
    }
    conn = lib.base.coe(lib.db_mysql.connect(mysql_connection))
    lib.base.coe(lib.db_mysql.check_privileges(conn, 'SELECT'))

    engines = lib.db_mysql.get_engines(conn)
    if not engines.get('have_innodb', '') or engines['have_innodb'] != 'YES':
        lib.db_mysql.close(conn)
        lib.base.oao(
            'InnoDB Storage Engine not available or disabled.',
            STATE_OK,
            always_ok=args.ALWAYS_OK,
        )

    myvar = lib.db_mysql.lod2dict(get_vars(conn))
    mystat = lib.db_mysql.lod2dict(get_status(conn))

    sql = """
        select sum(data_length+index_length) as InnoDB
        from information_schema.tables
        where
            table_schema not in ("information_schema", "performance_schema", "mysql")
            and engine = "innodb";
    """
    enginestats = lib.base.coe(lib.db_mysql.select(conn, sql))
    innodb_data_size = int(enginestats[0]['InnoDB'] or 0)

    lib.db_mysql.close(conn)

    # init some vars
    # cache int conversions
    buffer_size = int(myvar['innodb_buffer_pool_size'])
    log_file_size = int(myvar['innodb_log_file_size'])
    redo_log_capacity_raw = myvar.get('innodb_redo_log_capacity')
    redo_log_capacity = (
        int(redo_log_capacity_raw)
        if redo_log_capacity_raw is not None and int(redo_log_capacity_raw) > 0
        else None
    )
    file_per_table = myvar.get('innodb_file_per_table', '').upper()
    uptime = int(mystat.get('Uptime') or 0)
    os_log_written = int(mystat.get('Innodb_os_log_written') or 0)
    physical_memory = get_physical_memory()

    state = STATE_OK
    sections = []

    # All recommendations from all WARN paths land here and render once at the
    # end as a `Recommendations:\n* ...` bulleted block, regardless of which
    # combinations of WARN paths fire.
    recommendations = []
    data_h = lib.human.bytes2human(innodb_data_size)
    buffer_h = lib.human.bytes2human(buffer_size)

    # build the message
    # 1. Architecture cap (rarely violated; emit only on violation).
    if not lib.base.IS_64BIT and buffer_size > LIMIT_32BIT:
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(
            f'`innodb_buffer_pool_size` ({buffer_h}) exceeds the 32-bit address'
            f' space limit ({lib.human.bytes2human(LIMIT_32BIT)})'
            f'{lib.base.state2str(STATE_WARN, prefix=" ")}.'
        )
        recommendations.append(
            f'Lower `innodb_buffer_pool_size` below'
            f' {lib.human.bytes2human(LIMIT_32BIT)}'
        )
    elif lib.base.IS_64BIT and buffer_size > LIMIT_64BIT:
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(
            f'`innodb_buffer_pool_size` ({buffer_h}) exceeds the 64-bit address'
            f' space limit ({lib.human.bytes2human(LIMIT_64BIT)})'
            f'{lib.base.state2str(STATE_WARN, prefix=" ")}.'
        )
        recommendations.append(
            f'Lower `innodb_buffer_pool_size` below'
            f' {lib.human.bytes2human(LIMIT_64BIT)}'
        )

    # 2. innodb_file_per_table - off means everything lives in `ibdata1`, which
    #    makes per-table maintenance (drop, optimize, rebuild) painful.
    if file_per_table != 'ON':
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(
            f'`innodb_file_per_table` is `{file_per_table or "unset"}`'
            f'{lib.base.state2str(STATE_WARN, prefix=" ")}.'
        )
        recommendations.append(
            'Set `innodb_file_per_table` = `ON` so each InnoDB table gets its'
            ' own .ibd file (per-table maintenance is harder when everything'
            ' lives in `ibdata1`)'
        )

    # 3. Buffer pool vs data size. mysqltuner emits a goodprint here too when
    #    buffer >= data; we mirror that so the OK output shows the comparison.
    if buffer_size <= innodb_data_size and innodb_data_size > 0:
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(
            f'`innodb_buffer_pool_size` ({buffer_h}) is smaller than the InnoDB'
            f' data + index size ({data_h})'
            f'{lib.base.state2str(STATE_WARN, prefix=" ")}.'
        )
        recommendations.append(
            f'Set `innodb_buffer_pool_size` >= {data_h} so the working set fits'
            f' in memory'
        )
    else:
        sections.append(
            f'`innodb_buffer_pool_size` ({buffer_h}) >= InnoDB data + index size'
            f' ({data_h}).'
        )

    # 4. Workload-based redo log capacity check (MySQL 8.0.30+ only).
    #    Compare the configured `innodb_redo_log_capacity` against an hourly
    #    write rate target rounded into the appropriate RAM tier. Skipped on
    #    servers without `innodb_redo_log_capacity` (MariaDB, older MySQL) and
    #    on freshly booted servers (< 1 h uptime) where the rate is unreliable.
    hourly_rate = None
    recommended_redo = None
    if redo_log_capacity is not None and physical_memory is not None:
        if uptime >= REDO_LOG_MIN_UPTIME:
            hourly_rate = os_log_written / (uptime / 3600)
            recommended_redo = recommended_redo_log_capacity(
                hourly_rate,
                physical_memory,
            )
            redo_h = lib.human.bytes2human(redo_log_capacity)
            rec_h = lib.human.bytes2human(recommended_redo)
            rate_h = lib.human.bytes2human(int(hourly_rate))
            if redo_log_capacity < recommended_redo * REDO_LOG_TOLERANCE:
                state = lib.base.get_worst(state, STATE_WARN)
                sections.append(
                    f'`innodb_redo_log_capacity` ({redo_h}) is below the'
                    f' workload-based target of {rec_h} (hourly InnoDB log'
                    f' write rate: {rate_h}/h on a host with'
                    f' {lib.human.bytes2human(physical_memory)} RAM)'
                    f'{lib.base.state2str(STATE_WARN, prefix=" ")}.'
                )
                recommendations.append(
                    f'Raise `innodb_redo_log_capacity` to {rec_h} or more.'
                    f' Tradeoff: higher capacity means longer crash recovery'
                )
            else:
                sections.append(
                    f'`innodb_redo_log_capacity` ({redo_h}) matches the'
                    f' workload-based target ({rec_h} for'
                    f' {rate_h}/h on'
                    f' {lib.human.bytes2human(physical_memory)} RAM).'
                )
        else:
            sections.append(
                f'`innodb_redo_log_capacity` ({lib.human.bytes2human(redo_log_capacity)});'
                f' uptime < 1 h, workload-based sizing recommendation'
                f' deferred.'
            )
    elif redo_log_capacity is None:
        # MariaDB / MySQL < 8.0.30: no innodb_redo_log_capacity to size. The
        # underlying innodb_log_file_size is still emitted as perfdata.
        sections.append(
            f'`innodb_log_file_size` ({lib.human.bytes2human(log_file_size)});'
            f' redo-log sizing check skipped on this server (no'
            f' `innodb_redo_log_capacity`).'
        )

    if recommendations:
        sections.append(
            'Recommendations:\n' + '\n'.join(f'* {r}' for r in recommendations)
        )

    msg = '\n\n'.join(sections)

    perfdata = ''
    perfdata += lib.base.get_perfdata(
        'mysql_innodb_buffer_pool_size',
        buffer_size,
        uom='B',
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_innodb_data_size',
        innodb_data_size,
        uom='B',
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_innodb_log_file_size',
        log_file_size,
        uom='B',
        _min=0,
    )
    if redo_log_capacity is not None:
        perfdata += lib.base.get_perfdata(
            'mysql_innodb_redo_log_capacity',
            redo_log_capacity,
            uom='B',
            _min=0,
        )
    if hourly_rate is not None:
        perfdata += lib.base.get_perfdata(
            'mysql_innodb_os_log_written_per_hour',
            int(hourly_rate),
            uom='B',
            _min=0,
        )
    if recommended_redo is not None:
        perfdata += lib.base.get_perfdata(
            'mysql_innodb_redo_log_capacity_recommended',
            recommended_redo,
            uom='B',
            _min=0,
        )

    # over and out
    lib.base.oao(msg, state, perfdata, always_ok=args.ALWAYS_OK)


if __name__ == '__main__':
    try:
        main()
    except Exception:
        lib.base.cu()
