Quick script to display InnoDB inserts/updates/deletes/reads per second in real time

innodb-realtime-stats.php

<?php

/**
 * Quick script to display InnoDB inserts/updates/deletes/reads per second in real time.
 *
 * Sample output:
 *
 *                 Current         Average
 * Inserts       40,368.63       20,214.55 /s
 * Updates           19.98         4837.12 /s
 * Deletes            9.99            7.02 /s
 * Reads         40,441.56       50,209.11 /s
 */

function boldGreen(string $text): string {
    return "\033[1;32m{$text}\033[0m";
}

function faint(string $text): string {
    return "\033[2m{$text}\033[0m";
}

function formatNumber(float $number, int $columns): string {

    $formattedNumber = number_format($number, 2);
    [$left, $right] = explode('.', $formattedNumber);

    $padLeft = $columns - 3; // '.' + 2 decimal places

    return boldGreen(str_pad($left, $padLeft, ' ', STR_PAD_LEFT)) . '.' . faint($right);
}

$pdo = new PDO('mysql:host=localhost', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$types = ['inserts', 'updates', 'deletes', 'reads'];

$sampleCount = 0;
$total = [
    'inserts' => 0.0,
    'updates' => 0.0,
    'deletes' => 0.0,
    'reads'   => 0.0,
];

echo "                Current         Average\n";

for (;;) {
    $s = $pdo->query('SHOW ENGINE INNODB STATUS');
    $status = $s->fetch(PDO::FETCH_ASSOC)['Status'];

    $p = strpos($status, 'ROW OPERATIONS');
    if ($p === false) die('error');

    $status = substr($status, $p);

    preg_match_all('/([0-9\.]+) (inserts|updates|deletes|reads)\/s/', $status, $matches, PREG_SET_ORDER);

    $values = [];
    foreach ($matches as [, $count, $type]) {
        if (isset($values[$type])) {
            // Counts may be included twice in the output: once for rows, once for system rows;
            // in this case, ignore the second row
            continue;
        }

        $values[$type] = (float) $count;
    }

    if (array_keys($values) !== $types) {
        echo 'Error: expected ' . implode('/', $types) . ', got: ' . implode('/', array_keys($values)) . "\n";
        exit(1);
    }

    $sampleCount++;
    foreach ($types as $type) {
        $total[$type] += $values[$type];
    }

    foreach ($types as $type) {
        printf(
            "%s %s %s /s\n",
            str_pad(ucfirst($type), 7),
            formatNumber($values[$type], 15),
            formatNumber($total[$type] / $sampleCount, 15),
        );
    }

    sleep(1);
    echo "\033[4A";
}