SHOW STATUS / SHOW VARIABLES による取得とセクション定義

-- グローバルステータス変数一覧（累積カウンタ）
SHOW GLOBAL STATUS;

-- グローバル設定変数一覧（初期化パラメータ）
SHOW GLOBAL VARIABLES;

-- キーワードで絞り込み
SHOW GLOBAL STATUS LIKE 'Innodb_buffer%';
SHOW GLOBAL VARIABLES LIKE 'innodb%';

-- information_schema 経由（SQL で加工可能）
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE 'Threads%';

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'innodb_buffer%';

SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Threads_connected',
    'Threads_running',
    'Threads_created',
    'Threads_cached',
    'Connections',
    'Max_used_connections',
    'Aborted_connects',
    'Aborted_clients',
    'Connection_errors_max_connections'
);

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- バッファプールヒット率を計算
SELECT
    (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
        AS buffer_pool_hit_rate_pct
FROM (
    SELECT
        MAX(CASE WHEN Variable_name = 'Innodb_buffer_pool_reads'
            THEN VARIABLE_VALUE + 0 END) AS Innodb_buffer_pool_reads,
        MAX(CASE WHEN Variable_name = 'Innodb_buffer_pool_read_requests'
            THEN VARIABLE_VALUE + 0 END) AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS
    WHERE Variable_name IN (
        'Innodb_buffer_pool_reads',
        'Innodb_buffer_pool_read_requests'
    )
) t;

SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Questions',
    'Com_select',
    'Com_insert',
    'Com_update',
    'Com_delete',
    'Com_commit',
    'Com_rollback',
    'Slow_queries',
    'Select_full_join',
    'Select_full_range_join',
    'Select_scan',
    'Sort_merge_passes',
    'Created_tmp_disk_tables',
    'Created_tmp_tables'
);

-- レプリケーション遅延と状態（MySQL 8.0+）
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source : レプリカの遅延秒数（0が理想）
-- Replica_IO_Running     : IO スレッドが動いているか
-- Replica_SQL_Running    : SQL スレッドが動いているか

-- InnoDB redo ログ書き込み統計
SHOW GLOBAL STATUS LIKE 'Innodb_os_log%';
SHOW GLOBAL STATUS LIKE 'Innodb_log%';

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size',
    'innodb_log_file_size',
    'innodb_log_buffer_size',
    'innodb_flush_log_at_trx_commit',
    'innodb_flush_method',
    'innodb_io_capacity',
    'innodb_io_capacity_max',
    'max_connections',
    'thread_cache_size',
    'query_cache_size',
    'sort_buffer_size',
    'join_buffer_size',
    'tmp_table_size',
    'max_heap_table_size',
    'long_query_time',
    'slow_query_log',
    'binlog_format',
    'transaction_isolation'
);

-- スナップショット保存テーブルを作成
CREATE TABLE IF NOT EXISTS mysql_status_snapshot (
    snap_id   INT AUTO_INCREMENT PRIMARY KEY,
    snap_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    var_name  VARCHAR(128),
    var_value BIGINT
);

-- スナップショット取得
INSERT INTO mysql_status_snapshot (var_name, var_value)
SELECT VARIABLE_NAME, VARIABLE_VALUE + 0
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
    'Questions', 'Com_commit', 'Com_rollback',
    'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests',
    'Slow_queries', 'Threads_connected'
);

-- 2スナップショット間の差分計算（snap_id = 1 と 2 の差）
SELECT
    a.var_name,
    b.var_value - a.var_value AS delta,
    b.snap_time,
    a.snap_time,
    TIMESTAMPDIFF(SECOND, a.snap_time, b.snap_time) AS elapsed_sec
FROM mysql_status_snapshot a
JOIN mysql_status_snapshot b ON a.var_name = b.var_name
WHERE a.snap_id = 1 AND b.snap_id = 2;