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:
 *
 * Inserts       40,368.63 /s
 * Updates           19.98 /s
 * Deletes            9.99 /s
 * Reads         40,441.56 /s
 */

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

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

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

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);

    $displayed = [];

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

        $displayed[$type] = true;

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

        printf(
            "%s %s.%s /s\n",
            str_pad(ucfirst($type), 7, ' ', STR_PAD_RIGHT),
            boldGreen(str_pad($left, 12, ' ', STR_PAD_LEFT)),
            faint($right),
        );
    }

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