#!/usr/bin/env python3
# -*- 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__ = '2026083001'

DESCRIPTION = """Checks the InnoDB buffer pool and redo log sizing in MySQL/MariaDB. Compares
the configured `innodb_buffer_pool_size` against the actual InnoDB data and index sizes, and
reports how far the checkpoint has run through the redo log, which is what tells a redo log
that is too small for its workload from one that is merely small. On a server that publishes
`Innodb_checkpoint_age` and `Innodb_checkpoint_max_age` those decide the state; on one that
publishes neither, the average redo write rate since startup is compared against the redo log
size instead, and a workload that writes through the whole log within an hour is reported. The
redo log knob is `innodb_redo_log_capacity` on MySQL 8.0.30+ and `innodb_log_file_size` (times
`innodb_log_files_in_group`, where that variable still exists) on MariaDB and older MySQL.
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 the checkpoint runs closer
to the end of the redo log than the thresholds allow. The redo log size mysqltuner would
recommend for the host's RAM is reported as advice without raising a state, because it is a
floor for the RAM tier rather than a measurement of this server. On freshly booted servers
(less than one hour of uptime) the write-rate comparison is deferred, because the rate is an
average since startup and not yet meaningful."""

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

# The hourly `Innodb_os_log_written` rate is an average since startup, so it needs at
# least an hour of uptime to mean anything.
REDO_LOG_MIN_UPTIME = 3600

# How full the redo log is allowed to get before it is worth reporting, as a percentage
# of `Innodb_checkpoint_max_age`. Both numbers come from InnoDB itself rather than from
# a rule of thumb (MariaDB `storage/innobase/log/log0log.cc`, `log_t::set_capacity()`):
#
# - at `max_modified_age_async`, which is `margin - margin / 8` and therefore 87.5% of
#   `max_checkpoint_age`, InnoDB starts flushing pages ahead more aggressively to keep
#   the checkpoint moving (`mtr0mtr.cc`, `mtr_flush_ahead()`)
# - at `max_checkpoint_age` itself, `log_t::checkpoint_margin()` puts a synchronous wait
#   into every write operation, which is the stall an administrator notices
#
# `Innodb_checkpoint_max_age` is derived from the redo log size, so a checkpoint age
# close to it is what "the redo log is too small for this workload" actually looks like.
#
# Measured against MariaDB 11.8.8 with a 4 MiB redo log under a sustained insert load:
# the checkpoint age does not climb through the band, it jumps to the limit and stays
# pinned at 100.0-100.1% for as long as the load runs, because the synchronous wait is
# what holds it there. The critical threshold is therefore 99 rather than 100: a range
# of `100` only alerts above 100, and the value sits on exactly 100.0 often enough for
# that to be missed.
DEFAULT_CRIT = '99'
DEFAULT_WARN = '87.5'


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(
        description=DESCRIPTION,
        epilog=lib.args.epilog(__file__),
        formatter_class=lib.args.HelpFormatter,
    )

    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(
        '-c',
        '--critical',
        help='CRIT threshold for how far the checkpoint has run through the redo '
        'log, in percent of `Innodb_checkpoint_max_age`. '
        'From 100 InnoDB puts a synchronous wait into every write operation, and a redo log that is too small sits pinned there under load. '
        'Only evaluated on a server that publishes its checkpoint age. '
        'Supports Nagios ranges. '
        'Default: %(default)s',
        dest='CRIT',
        default=DEFAULT_CRIT,
    )

    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(
        '--no-perfdata',
        help=lib.args.help('--no-perfdata'),
        dest='NO_PERFDATA',
        action='store_true',
        default=False,
    )

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

    parser.add_argument(
        '-w',
        '--warning',
        help='WARN threshold for how far the checkpoint has run through the redo '
        'log, in percent of `Innodb_checkpoint_max_age`. '
        'At 87.5 InnoDB starts flushing pages ahead to keep the checkpoint moving. '
        'Only evaluated on a server that publishes its checkpoint age. '
        'Supports Nagios ranges. '
        'Default: %(default)s',
        dest='WARN',
        default=DEFAULT_WARN,
    )

    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_log_files_in_group'
            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_checkpoint_age'
            or variable_name like 'Innodb_checkpoint_max_age'
            or 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 redo log
    # recommendation). 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. Deliberate
    # deviation from MySQLTuner: the workload-based target is applied to
    # `innodb_log_file_size` as well, so MariaDB and MySQL < 8.0.30 get a
    # recommendation instead of nothing (see the comment at the sizing check).

    # 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'])
    # `innodb_log_file_size` was removed in MySQL 9.3.0, where
    # `innodb_redo_log_capacity` (MySQL 8.0.30+) is the only redo-log sizing knob.
    # It still exists on MariaDB and on MySQL < 9.3.0, so it may be absent.
    log_file_size_raw = myvar.get('innodb_log_file_size')
    log_file_size = int(log_file_size_raw) if log_file_size_raw is not None else None
    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
    )
    # `innodb_log_files_in_group` is gone on MariaDB 10.5+ (always one file) and
    # ignored on MySQL 8.0.30+, so it defaults to 1 - same as mysqltuner does.
    log_files_in_group = int(myvar.get('innodb_log_files_in_group') or 1) or 1
    # The knob that actually sizes the redo log on this server. MySQL 8.0.30+
    # exposes both variables, but only `innodb_redo_log_capacity` has an effect
    # there, so it wins.
    if redo_log_capacity is not None:
        redo_size = redo_log_capacity
        redo_knob = 'innodb_redo_log_capacity'
    elif log_file_size:
        redo_size = log_file_size * log_files_in_group
        redo_knob = 'innodb_log_file_size'
    else:
        redo_size = None
        redo_knob = 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)
    # MariaDB publishes both, MySQL neither (`ha_innodb.cc`). Absent means this
    # server cannot answer how full its redo log runs, not that it runs empty.
    checkpoint_age = int(mystat.get('Innodb_checkpoint_age') or 0)
    checkpoint_max_age = int(mystat.get('Innodb_checkpoint_max_age') 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. Redo log pressure. What "the redo log is too small" means is that the
    #    checkpoint cannot keep up with it, and MariaDB publishes exactly that:
    #    `Innodb_checkpoint_age` against `Innodb_checkpoint_max_age`, the latter
    #    derived from the redo log size in `log_t::set_capacity()`. Measuring the
    #    mechanism beats guessing at it from a write rate, so where those two are
    #    there they decide the state.
    #
    #    MySQL exports neither, only `Innodb_os_log_written`. There the question
    #    becomes how often the workload writes its way through the whole redo log:
    #    a server that writes more redo per hour than the log holds wraps it at
    #    least once an hour and keeps the checkpoint under permanent pressure.
    #
    #    mysqltuner's RAM-tier minimum stays a recommendation and no longer sets a
    #    state. It is a floor, not a measurement: below 2 GiB of RAM it targets
    #    100 MiB whatever the server does, so it fires on an idle database whose
    #    redo log is nowhere near full, and mysqltuner itself never reaches this
    #    code on MariaDB (`mysqltuner.pl` gates it on `mysql_version_ge(8, 0, 30)`
    #    and on `innodb_redo_log_capacity` being defined).
    hourly_rate = None
    recommended_redo = None
    checkpoint_pct = None
    if redo_size is not None:
        redo_h = lib.human.bytes2human(redo_size)
        if checkpoint_max_age:
            checkpoint_pct = checkpoint_age / checkpoint_max_age * 100
            item_state = lib.base.get_state(
                checkpoint_pct, args.WARN, args.CRIT, _operator='range'
            )
            state = lib.base.get_worst(state, item_state)
            sections.append(
                f'The redo log ({redo_knob}, {redo_h}) is'
                f' {checkpoint_pct:.1f}% through its checkpoint age'
                f' ({lib.human.bytes2human(checkpoint_age)} of'
                f' {lib.human.bytes2human(checkpoint_max_age)})'
                f'{lib.base.state2str(item_state, prefix=" ")}.'
            )
            if item_state != STATE_OK:
                recommendations.append(
                    f'Raise `{redo_knob}`: the checkpoint is running this close to '
                    f'the end of the redo log, so InnoDB is flushing pages ahead to '
                    f'keep up and stalls every write once it arrives. Tradeoff: a '
                    f'larger redo log means longer crash recovery'
                )
        elif uptime < REDO_LOG_MIN_UPTIME:
            sections.append(
                f'The redo log ({redo_knob}, {redo_h}); uptime < 1 h, so the write'
                f' rate is not yet meaningful.'
            )
        else:
            # No checkpoint age published: judge by how long the workload takes to
            # write its way through the whole redo log.
            hourly_rate = os_log_written / (uptime / 3600)
            rate_h = lib.human.bytes2human(int(hourly_rate))
            item_state = STATE_WARN if hourly_rate >= redo_size else STATE_OK
            state = lib.base.get_worst(state, item_state)
            # How long the log holds at that rate. A server writing nothing would
            # divide by zero on the way there, and its log holds forever anyway.
            if hourly_rate > 0:
                holds = lib.human.seconds2human(int(redo_size / hourly_rate * 3600))
            else:
                holds = 'unlimited'
            sections.append(
                f'The redo log ({redo_knob}, {redo_h}) holds {holds} of redo at the'
                f' average write rate since startup ({rate_h}/h)'
                f'{lib.base.state2str(item_state, prefix=" ")}.'
            )
            if item_state != STATE_OK:
                recommendations.append(
                    f'Raise `{redo_knob}`: the workload writes through the whole '
                    f'redo log at least once an hour, which keeps the checkpoint '
                    f'under permanent pressure. Tradeoff: a larger redo log means '
                    f'longer crash recovery'
                )

        # The floor mysqltuner would recommend, as advice rather than as a state.
        if physical_memory is not None and uptime >= REDO_LOG_MIN_UPTIME:
            hourly_rate = os_log_written / (uptime / 3600)
            recommended_redo = recommended_redo_log_capacity(
                hourly_rate, physical_memory
            )
            if redo_size < recommended_redo:
                rec_h = lib.human.bytes2human(recommended_redo)
                if redo_knob == 'innodb_log_file_size' and log_files_in_group > 1:
                    # Pre-8.0.30 MySQL splits the redo log over several files, so
                    # the per-file value the admin has to set is not the target.
                    # MySQL 8.0.30+ still reports the variable, but ignores it.
                    per_file_h = lib.human.bytes2human(
                        recommended_redo // log_files_in_group
                    )
                    recommendations.append(
                        f'For reference, mysqltuner would size `{redo_knob}` at '
                        f'{per_file_h} or more on a host with '
                        f'{lib.human.bytes2human(physical_memory)} of RAM '
                        f'({rec_h} across the {log_files_in_group} files of '
                        f'`innodb_log_files_in_group`). That is a floor for the RAM '
                        f'tier, not a measurement of this workload'
                    )
                else:
                    recommendations.append(
                        f'For reference, mysqltuner would size `{redo_knob}` at '
                        f'{rec_h} or more on a host with '
                        f'{lib.human.bytes2human(physical_memory)} of RAM. That is a '
                        f'floor for the RAM tier, not a measurement of this workload'
                    )

    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,
    )
    if log_file_size is not None:
        # Absent on MySQL >= 9.3.0 (removed there in favour of
        # `innodb_redo_log_capacity`).
        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,
        )
    if checkpoint_pct is not None:
        # The series that says whether the redo log is big enough for what the
        # server does, which the sizes above cannot say on their own.
        perfdata += lib.base.get_perfdata(
            'mysql_innodb_checkpoint_age_percent',
            round(checkpoint_pct, 1),
            uom='%',
            warn=args.WARN,
            crit=args.CRIT,
            _min=0,
        )
        perfdata += lib.base.get_perfdata(
            'mysql_innodb_checkpoint_age',
            checkpoint_age,
            uom='B',
            _min=0,
            _max=checkpoint_max_age,
        )

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


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