diff --git a/zabbix-send-mariadb-metrics.php b/zabbix-send-mariadb-metrics.php index 5de9f68..dddd6d3 100755 --- a/zabbix-send-mariadb-metrics.php +++ b/zabbix-send-mariadb-metrics.php @@ -1,211 +1,211 @@ #!/usr/bin/env php 0, ]; log_msg('INFO', "Waiting DAEMON_SLEEP_SECONDS_INIT=$DAEMON_SLEEP_SECONDS_INIT seconds for the initial warmup."); sleep($DAEMON_SLEEP_SECONDS_INIT); /* DATABASE SETUP START */ $mysqli = null; mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); /* DATABASE SETUP END */ /* DAEMON START */ $i = 0; while (true) { // Start again the database connection, if needed. if (!$mysqli) { $mysqli = mysqli_start(); } // Run the Zabbix discovery of database names. // Run this always at startup. // Run this also again after some loops. // We reduce the amount of times we run the Discovery, as micro-optimization. if (($i % $DAEMON_DISCOVERY_EVERY_N_LOOPS) === 0) { log_msg('INFO', "Zabbix Discovery of database names..."); zabbix_discovery_databases($mysqli); log_msg('INFO', "Zabbix Discovery of database names... Completed"); } // Send amount of current MariaDB connections. - $mariadb_connections = query_row($mysqli, "SHOW STATUS WHERE variable_name = 'Threads_connected'")->Value; + $mariadb_connections = (int) query_row($mysqli, "SHOW STATUS WHERE variable_name = 'Threads_connected'")->Value; zabbix_sender_key_value('er.mariadb.connections', $mariadb_connections); // Count all 'Sleep' queries. // Send the Zabbix items. zabbix_send_database_sleeps($mysqli); // Free the connection ASAP. // Since, if you want to monitor connections, // probably it means we don't have many free connections... $mysqli->close(); $mysqli = null; // Cleanup data foreach ($databases_info as $database_name => $database_info) { $databases_info[$database_name] = $databases_info_default; } $i++; // Do not flood the MariaDB server. log_msg('INFO', "Waiting DAEMON_SLEEP_SECONDS_LOOP={$DAEMON_SLEEP_SECONDS_LOOP} seconds for the next data collection #{$i}."); sleep($DAEMON_SLEEP_SECONDS_LOOP); } /* DAEMON END */ /* FUNCTIONS START */ function zabbix_send_database_sleeps(mysqli $mysqli): void { global $databases_info; global $databases_info_default; $results = query_results($mysqli, 'SHOW FULL PROCESSLIST'); foreach ($results as $row) { $db = $row->db; // Skip MariaDB "event schedulers". if ($db === null) { continue; } $databases_info[$db] ??= $databases_info_default; $database_command_lower = strtolower($row->Command); if ($database_command_lower === 'sleep') { $databases_info[$db]['sleeps']++; } } // Send all Zabbix values. foreach ($databases_info as $database_name => $database_info) { $zabbix_item_key = sprintf( 'er.mariadb.database[%s,sleeps]', $database_name ); zabbix_sender_key_value($zabbix_item_key, $database_info['sleeps']); } } function zabbix_discovery_databases(mysqli $mysqli): void { global $databases_info; global $databases_info_default; // Find all databases, for the Zabbix discovery. $results = query_results($mysqli, 'SHOW DATABASES'); foreach ($results as $row) { $db = $row->Database; $databases_info[$db] ??= $databases_info_default; } // Populate the Zabbix Discovery, sending all database names in a single JSON. // TODO: Run this less than once per hour. $payload = [ 'data' => [], ]; foreach ($databases_info as $database_name => $database_info) { $payload['data'][] = [ '{#DB}' => $database_name, ]; } $value = json_encode($payload); zabbix_sender_key_value('er.mariadb.database.discovery', $value); } function query_results(mysqli $mysqli, string $query): Generator { $result = $mysqli->query($query); while ($row = $result->fetch_object()) { yield $row; } $result->close(); } function query_row(mysqli $mysqli, string $query): stdclass { foreach (query_results($mysqli, $query) as $row) { return $row; } throw new Exception("No results from this query:\n{$query}"); } function zabbix_sender_key_value(string $key, string $value): void { global $ZABBIX_CONFIG; $command = sprintf('zabbix_sender -c %s --key %s --value %s', escapeshellarg($ZABBIX_CONFIG), escapeshellarg($key), escapeshellarg($value) ); // Execute the command and capture the output for logging purposes. ob_start(); // Include the original command in the logger, and its output. echo '$ ' . $command; echo "\n"; passthru($command); $log_msg = ob_get_contents(); ob_end_clean(); log_msg('DEBUG', $log_msg); } function mysqli_start(): mysqli { // Connect to MariaDB. // Use 'null' as hostname to just use the Unix socket, // so we can auto-magically login. $mysqli = new mysqli(null, 'root'); $mysqli->set_charset('utf8mb4'); return $mysqli; } function getenv_or_default_int(string $envname, int $default): int { $value = getenv_or_default($envname, (string)$default); if (!is_numeric($value)) { throw new Exception("Failed to parse ENV variable $envname. Received $value. Not an integer."); } return (int)$value; } function getenv_or_default(string $envname, string $default): string { $value = getenv($envname); if ($value === false) { $value = $default; } log_msg('DEBUG', "Got ENV $envname={$value}."); return $value; } function log_msg(string $severity, string $msg): void { $severity = sprintf('%-5s', $severity); $lines = explode("\n", $msg); foreach ($lines as $i => $line) { if ($i) { $line = " $line"; } echo "[$severity] $line\n"; } } /* FUNCTIONS END */