1<?php 2 3# ============================================================================ 4# This program is part of $PROJECT_NAME$ 5# License: GPL License (see COPYING) 6# Copyright 2008-$CURRENT_YEAR$ Baron Schwartz, 2012-$CURRENT_YEAR$ Percona 7# Authors: 8# Baron Schwartz, Roman Vynar 9# ============================================================================ 10 11# ============================================================================ 12# To make this code testable, we need to prevent code from running when it is 13# included from the test script. The test script and this file have different 14# filenames, so we can compare them. In some cases $_SERVER['SCRIPT_FILENAME'] 15# seems not to be defined, so we skip the check -- this check should certainly 16# pass in the test environment. 17# ============================================================================ 18if ( !array_key_exists('SCRIPT_FILENAME', $_SERVER) 19 || basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME']) ) { 20 21# ============================================================================ 22# CONFIGURATION 23# ============================================================================ 24# Define MySQL connection constants in config.php. Arguments explicitly passed 25# in from Cacti will override these. However, if you leave them blank in Cacti 26# and set them here, you can make life easier. Instead of defining parameters 27# here, you can define them in another file named the same as this file, with a 28# .cnf extension. 29# ============================================================================ 30$mysql_user = 'cactiuser'; 31$mysql_pass = 'cactiuser'; 32$mysql_port = 3306; 33$mysql_socket = NULL; 34$mysql_flags = 0; 35$mysql_ssl = FALSE; # Whether to use SSL to connect to MySQL. 36$mysql_ssl_key = '/etc/pki/tls/certs/mysql/client-key.pem'; 37$mysql_ssl_cert = '/etc/pki/tls/certs/mysql/client-cert.pem'; 38$mysql_ssl_ca = '/etc/pki/tls/certs/mysql/ca-cert.pem'; 39$mysql_connection_timeout = 5; 40 41$heartbeat = FALSE; # Whether to use pt-heartbeat table for repl. delay calculation. 42$heartbeat_utc = FALSE; # Whether pt-heartbeat is run with --utc option. 43$heartbeat_server_id = 0; # Server id to associate with a heartbeat. Leave 0 if no preference. 44$heartbeat_table = 'percona.heartbeat'; # db.tbl. 45 46$cache_dir = '/tmp'; # If set, this uses caching to avoid multiple calls. 47$poll_time = 300; # Adjust to match your polling interval. 48$timezone = null; # If not set, uses the system default. Example: "UTC" 49$chk_options = array ( 50 'innodb' => true, # Do you want to check InnoDB statistics? 51 'master' => true, # Do you want to check binary logging? 52 'slave' => true, # Do you want to check slave status? 53 'procs' => true, # Do you want to check SHOW PROCESSLIST? 54 'get_qrt' => true, # Get query response times from Percona Server or MariaDB? 55); 56 57$use_ss = FALSE; # Whether to use the script server or not 58$debug = FALSE; # Define whether you want debugging behavior. 59$debug_log = FALSE; # If $debug_log is a filename, it'll be used. 60 61# ============================================================================ 62# You should not need to change anything below this line. 63# ============================================================================ 64$version = '$VERSION$'; 65 66# ============================================================================ 67# Include settings from an external config file. 68# ============================================================================ 69if ( file_exists('/usr/local/etc/cacti/' . basename(__FILE__) . '.cnf' ) ) { 70 require('/usr/local/etc/cacti/' . basename(__FILE__) . '.cnf'); 71 debug('Found configuration file /usr/local/etc/cacti/' . basename(__FILE__) . '.cnf'); 72} 73elseif ( file_exists(__FILE__ . '.cnf' ) ) { 74 require(__FILE__ . '.cnf'); 75 debug('Found configuration file ' . __FILE__ . '.cnf'); 76} 77 78# Make this a happy little script even when there are errors. 79$no_http_headers = true; 80ini_set('implicit_flush', false); # No output, ever. 81if ( $debug ) { 82 ini_set('display_errors', true); 83 ini_set('display_startup_errors', true); 84 ini_set('error_reporting', 2147483647); 85} 86else { 87 ini_set('error_reporting', E_ERROR); 88} 89ob_start(); # Catch all output such as notices of undefined array indexes. 90function error_handler($errno, $errstr, $errfile, $errline) { 91 print("$errstr at $errfile line $errline\n"); 92 debug("$errstr at $errfile line $errline"); 93} 94# ============================================================================ 95# Set up the stuff we need to be called by the script server. 96# ============================================================================ 97if ( $use_ss ) { 98 if ( file_exists( dirname(__FILE__) . "/../include/global.php") ) { 99 # See issue 5 for the reasoning behind this. 100 debug("including " . dirname(__FILE__) . "/../include/global.php"); 101 include_once(dirname(__FILE__) . "/../include/global.php"); 102 } 103 elseif ( file_exists( dirname(__FILE__) . "/../include/config.php" ) ) { 104 # Some Cacti installations don't have global.php. 105 debug("including " . dirname(__FILE__) . "/../include/config.php"); 106 include_once(dirname(__FILE__) . "/../include/config.php"); 107 } 108} 109 110# ============================================================================ 111# Set the default timezone either to the configured, system timezone, or the 112# default set above in the script. 113# ============================================================================ 114if ( function_exists("date_default_timezone_set") 115 && function_exists("date_default_timezone_get") ) { 116 $tz = ($timezone ? $timezone : @date_default_timezone_get()); 117 if ( $tz ) { 118 @date_default_timezone_set($tz); 119 } 120} 121 122# ============================================================================ 123# Make sure we can also be called as a script. 124# ============================================================================ 125if (!isset($called_by_script_server)) { 126 debug($_SERVER["argv"]); 127 array_shift($_SERVER["argv"]); # Strip off this script's filename 128 $options = parse_cmdline($_SERVER["argv"]); 129 validate_options($options); 130 $result = ss_get_mysql_stats($options); 131 debug($result); 132 if ( !$debug ) { 133 # Throw away the buffer, which ought to contain only errors. 134 ob_end_clean(); 135 } 136 else { 137 ob_end_flush(); # In debugging mode, print out the errors. 138 } 139 140 # Split the result up and extract only the desired parts of it. 141 $wanted = explode(',', $options['items']); 142 $output = array(); 143 foreach ( explode(' ', $result) as $item ) { 144 if ( in_array(substr($item, 0, 2), $wanted) ) { 145 $output[] = $item; 146 } 147 } 148 debug(array("Final result", $output)); 149 print(implode(' ', $output)); 150} 151 152# ============================================================================ 153# End "if file was not included" section. 154# ============================================================================ 155} 156 157# ============================================================================ 158# Work around the lack of array_change_key_case in older PHP. 159# ============================================================================ 160if ( !function_exists('array_change_key_case') ) { 161 function array_change_key_case($arr) { 162 $res = array(); 163 foreach ( $arr as $key => $val ) { 164 $res[strtolower($key)] = $val; 165 } 166 return $res; 167 } 168} 169 170# ============================================================================ 171# Validate that the command-line options are here and correct 172# ============================================================================ 173function validate_options($options) { 174 $opts = array('host', 'items', 'user', 'pass', 'nocache', 'port', 'server-id'); 175 # Show help 176 if ( array_key_exists('help', $options) ) { 177 usage(''); 178 } 179 180 # Required command-line options 181 foreach ( array('host', 'items') as $option ) { 182 if ( !isset($options[$option]) || !$options[$option] ) { 183 usage("Required option --$option is missing"); 184 } 185 } 186 foreach ( $options as $key => $val ) { 187 if ( !in_array($key, $opts) ) { 188 usage("Unknown option --$key"); 189 } 190 } 191} 192 193# ============================================================================ 194# Print out a brief usage summary 195# ============================================================================ 196function usage($message) { 197 global $mysql_user, $mysql_pass, $mysql_port; 198 199 $usage = <<<EOF 200$message 201Usage: php ss_get_mysql_stats.php --host <host> --items <item,...> [OPTION] 202 203 --host MySQL host 204 --items Comma-separated list of the items whose data you want 205 --user MySQL username 206 --pass MySQL password 207 --port MySQL port 208 --socket MySQL socket 209 --flags MySQL flags 210 --connection-timeout MySQL connection timeout 211 --server-id Server id to associate with a heartbeat if heartbeat usage is enabled 212 --nocache Do not cache results in a file 213 --help Show usage 214 215EOF; 216 die($usage); 217} 218 219# ============================================================================ 220# Parse command-line arguments, in the format --arg value --arg value, and 221# return them as an array ( arg => value ) 222# ============================================================================ 223function parse_cmdline( $args ) { 224 $options = array(); 225 while (list($tmp, $p) = each($args)) { 226 if (strpos($p, '--') === 0) { 227 $param = substr($p, 2); 228 $value = null; 229 $nextparam = current($args); 230 if ($nextparam !== false && strpos($nextparam, '--') !==0) { 231 list($tmp, $value) = each($args); 232 } 233 $options[$param] = $value; 234 } 235 } 236 if ( array_key_exists('host', $options) ) { 237 $options['host'] = substr($options['host'], 0, 4) == 'tcp:' ? substr($options['host'], 4) : $options['host']; 238 } 239 debug($options); 240 return $options; 241} 242 243# ============================================================================ 244# This is the main function. Some parameters are filled in from defaults at the 245# top of this file. 246# ============================================================================ 247function ss_get_mysql_stats( $options ) { 248 # Process connection options. 249 global $debug, $mysql_user, $mysql_pass, $cache_dir, $poll_time, $chk_options, 250 $mysql_port, $mysql_socket, $mysql_flags, 251 $mysql_ssl, $mysql_ssl_key, $mysql_ssl_cert, $mysql_ssl_ca, 252 $mysql_connection_timeout, 253 $heartbeat, $heartbeat_table, $heartbeat_server_id, $heartbeat_utc; 254 255 $user = isset($options['user']) ? $options['user'] : $mysql_user; 256 $pass = isset($options['pass']) ? $options['pass'] : $mysql_pass; 257 $host = $options['host']; 258 $port = isset($options['port']) ? $options['port'] : $mysql_port; 259 $socket = isset($options['socket']) ? $options['socket'] : $mysql_socket; 260 $flags = isset($options['flags']) ? $options['flags'] : $mysql_flags; 261 $connection_timeout = isset($options['connection-timeout']) ? $options['connection-timeout'] : $mysql_connection_timeout; 262 $heartbeat_server_id = isset($options['server-id']) ? $options['server-id'] : $heartbeat_server_id; 263 264 $sanitized_host = str_replace(array(":", "/"), array("", "_"), $host); 265 $cache_file = "$cache_dir/$sanitized_host-mysql_cacti_stats.txt" . ($port != 3306 ? ":$port" : ''); 266 debug("Cache file is $cache_file"); 267 268 # First, check the cache. 269 $fp = null; 270 if ( $cache_dir && !array_key_exists('nocache', $options) ) { 271 if ( $fp = fopen($cache_file, 'a+') ) { 272 $locked = flock($fp, 1); # LOCK_SH 273 if ( $locked ) { 274 if ( filesize($cache_file) > 0 275 && filectime($cache_file) + ($poll_time/2) > time() 276 && ($arr = file($cache_file)) 277 ) {# The cache file is good to use. 278 debug("Using the cache file"); 279 fclose($fp); 280 return $arr[0]; 281 } 282 else { 283 debug("The cache file seems too small or stale"); 284 # Escalate the lock to exclusive, so we can write to it. 285 if ( flock($fp, 2) ) { # LOCK_EX 286 # We might have blocked while waiting for that LOCK_EX, and 287 # another process ran and updated it. Let's see if we can just 288 # return the data now: 289 if ( filesize($cache_file) > 0 290 && filectime($cache_file) + ($poll_time/2) > time() 291 && ($arr = file($cache_file)) 292 ) {# The cache file is good to use. 293 debug("Using the cache file"); 294 fclose($fp); 295 return $arr[0]; 296 } 297 ftruncate($fp, 0); # Now it's ready for writing later. 298 } 299 } 300 } 301 else { 302 $fp = null; 303 debug("Couldn't lock the cache file, ignoring it"); 304 } 305 } 306 else { 307 $fp = null; 308 debug("Couldn't open the cache file"); 309 } 310 } 311 else { 312 debug("Caching is disabled."); 313 } 314 315 # Connect to MySQL. 316 debug(array('Connecting to', $host, $port, $user, $pass)); 317 if ( !extension_loaded('mysqli') ) { 318 debug("PHP MySQLi extension is not loaded"); 319 die("PHP MySQLi extension is not loaded"); 320 } 321 if ( $mysql_ssl ) { 322 $conn = mysqli_init(); 323 $conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, $connection_timeout); 324 mysqli_ssl_set($conn, $mysql_ssl_key, $mysql_ssl_cert, $mysql_ssl_ca, NULL, NULL); 325 mysqli_real_connect($conn, $host, $user, $pass, NULL, $port, $socket, $flags); 326 } 327 else { 328 $conn = mysqli_init(); 329 $conn->options(MYSQLI_OPT_CONNECT_TIMEOUT, $connection_timeout); 330 mysqli_real_connect($conn, $host, $user, $pass, NULL, $port, $socket, $flags); 331 } 332 if ( mysqli_connect_errno() ) { 333 debug("MySQL connection failed: " . mysqli_connect_error()); 334 die("ERROR: " . mysqli_connect_error()); 335 } 336 337 # MySQL server version. 338 # The form of this version number is main_version * 10000 + minor_version * 100 + sub_version 339 # i.e. version 5.5.44 is 50544. 340 $mysql_version = mysqli_get_server_version($conn); 341 debug("MySQL server version is " . $mysql_version); 342 343 # Set up variables. 344 $status = array( # Holds the result of SHOW STATUS, SHOW INNODB STATUS, etc 345 # Define some indexes so they don't cause errors with += operations. 346 'relay_log_space' => null, 347 'binary_log_space' => null, 348 'current_transactions' => 0, 349 'locked_transactions' => 0, 350 'active_transactions' => 0, 351 'innodb_locked_tables' => 0, 352 'innodb_tables_in_use' => 0, 353 'innodb_lock_structs' => 0, 354 'innodb_lock_wait_secs' => 0, 355 'innodb_sem_waits' => 0, 356 'innodb_sem_wait_time_ms' => 0, 357 # Values for the 'state' column from SHOW PROCESSLIST (converted to 358 # lowercase, with spaces replaced by underscores) 359 'State_closing_tables' => 0, 360 'State_copying_to_tmp_table' => 0, 361 'State_end' => 0, 362 'State_freeing_items' => 0, 363 'State_init' => 0, 364 'State_locked' => 0, 365 'State_login' => 0, 366 'State_preparing' => 0, 367 'State_reading_from_net' => 0, 368 'State_sending_data' => 0, 369 'State_sorting_result' => 0, 370 'State_statistics' => 0, 371 'State_updating' => 0, 372 'State_writing_to_net' => 0, 373 'State_none' => 0, 374 'State_other' => 0, # Everything not listed above 375 ); 376 377 # Get SHOW STATUS and convert the name-value array into a simple 378 # associative array. 379 $result = run_query("SHOW /*!50002 GLOBAL */ STATUS", $conn); 380 foreach ( $result as $row ) { 381 $status[$row[0]] = $row[1]; 382 } 383 384 # Get SHOW VARIABLES and do the same thing, adding it to the $status array. 385 $result = run_query("SHOW VARIABLES", $conn); 386 foreach ( $result as $row ) { 387 $status[$row[0]] = $row[1]; 388 } 389 390 # Get SHOW SLAVE STATUS, and add it to the $status array. 391 if ( $chk_options['slave'] ) { 392 # Leverage lock-free SHOW SLAVE STATUS if available 393 $result = run_query("SHOW SLAVE STATUS NONBLOCKING", $conn); 394 if ( !$result ) { 395 $result = run_query("SHOW SLAVE STATUS NOLOCK", $conn); 396 if ( !$result ) { 397 $result = run_query("SHOW SLAVE STATUS", $conn); 398 } 399 } 400 $slave_status_rows_gotten = 0; 401 foreach ( $result as $row ) { 402 $slave_status_rows_gotten++; 403 # Must lowercase keys because different MySQL versions have different 404 # lettercase. 405 $row = array_change_key_case($row, CASE_LOWER); 406 $status['relay_log_space'] = $row['relay_log_space']; 407 $status['slave_lag'] = $row['seconds_behind_master']; 408 409 # Check replication heartbeat, if present. 410 if ( $heartbeat ) { 411 if ( $heartbeat_utc ) { 412 $now_func = 'UNIX_TIMESTAMP(UTC_TIMESTAMP)'; 413 } 414 else { 415 $now_func = 'UNIX_TIMESTAMP()'; 416 } 417 $result2 = run_query( 418 "SELECT MAX($now_func - ROUND(UNIX_TIMESTAMP(ts)))" 419 . " AS delay FROM $heartbeat_table" 420 . " WHERE $heartbeat_server_id = 0 OR server_id = $heartbeat_server_id", $conn); 421 $slave_delay_rows_gotten = 0; 422 foreach ( $result2 as $row2 ) { 423 $slave_delay_rows_gotten++; 424 if ( $row2 && is_array($row2) 425 && array_key_exists('delay', $row2) ) 426 { 427 $status['slave_lag'] = $row2['delay']; 428 } 429 else { 430 debug("Couldn't get slave lag from $heartbeat_table"); 431 } 432 } 433 if ( $slave_delay_rows_gotten == 0 ) { 434 debug("Got nothing from heartbeat query"); 435 } 436 } 437 438 # Scale slave_running and slave_stopped relative to the slave lag. 439 $status['slave_running'] = ($row['slave_sql_running'] == 'Yes') 440 ? $status['slave_lag'] : 0; 441 $status['slave_stopped'] = ($row['slave_sql_running'] == 'Yes') 442 ? 0 : $status['slave_lag']; 443 } 444 if ( $slave_status_rows_gotten == 0 ) { 445 debug("Got nothing from SHOW SLAVE STATUS"); 446 } 447 } 448 449 # Get SHOW MASTER STATUS, and add it to the $status array. 450 if ( $chk_options['master'] 451 && array_key_exists('log_bin', $status) 452 && $status['log_bin'] == 'ON' 453 ) { # See issue #8 454 $binlogs = array(0); 455 $result = run_query("SHOW MASTER LOGS", $conn); 456 foreach ( $result as $row ) { 457 $row = array_change_key_case($row, CASE_LOWER); 458 # Older versions of MySQL may not have the File_size column in the 459 # results of the command. Zero-size files indicate the user is 460 # deleting binlogs manually from disk (bad user! bad!). 461 if ( array_key_exists('file_size', $row) && $row['file_size'] > 0 ) { 462 $binlogs[] = $row['file_size']; 463 } 464 } 465 if (count($binlogs)) { 466 $status['binary_log_space'] = to_int(array_sum($binlogs)); 467 } 468 } 469 470 # Get SHOW PROCESSLIST and aggregate it by state, then add it to the array 471 # too. 472 if ( $chk_options['procs'] ) { 473 $result = run_query('SHOW PROCESSLIST', $conn); 474 foreach ( $result as $row ) { 475 $state = $row['State']; 476 if ( is_null($state) ) { 477 $state = 'NULL'; 478 } 479 if ( $state == '' ) { 480 $state = 'none'; 481 } 482 # MySQL 5.5 replaces the 'Locked' state with a variety of "Waiting for 483 # X lock" types of statuses. Wrap these all back into "Locked" because 484 # we don't really care about the type of locking it is. 485 $state = preg_replace('/^(Table lock|Waiting for .*lock)$/', 'Locked', $state); 486 $state = str_replace(' ', '_', strtolower($state)); 487 if ( array_key_exists("State_$state", $status) ) { 488 increment($status, "State_$state", 1); 489 } 490 else { 491 increment($status, "State_other", 1); 492 } 493 } 494 } 495 496 # Get SHOW ENGINES to be able to determine whether InnoDB is present. 497 $engines = array(); 498 $result = run_query("SHOW ENGINES", $conn); 499 foreach ( $result as $row ) { 500 $engines[$row[0]] = $row[1]; 501 } 502 503 # Get SHOW INNODB STATUS and extract the desired metrics from it, then add 504 # those to the array too. 505 if ( $chk_options['innodb'] 506 && array_key_exists('InnoDB', $engines) 507 && $engines['InnoDB'] == 'YES' 508 || $engines['InnoDB'] == 'DEFAULT' 509 ) { 510 $result = run_query("SHOW /*!50000 ENGINE*/ INNODB STATUS", $conn); 511 $istatus_text = $result[0]['Status']; 512 $istatus_vals = get_innodb_array($istatus_text, $mysql_version); 513 514 # Get response time histogram from Percona Server or MariaDB if enabled. 515 if ( $chk_options['get_qrt'] 516 && (( isset($status['have_response_time_distribution']) 517 && $status['have_response_time_distribution'] == 'YES') 518 || (isset($status['query_response_time_stats']) 519 && $status['query_response_time_stats'] == 'ON')) ) 520 { 521 debug('Getting query time histogram'); 522 $i = 0; 523 $result = run_query( 524 "SELECT `count`, ROUND(total * 1000000) AS total " 525 . "FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME " 526 . "WHERE `time` <> 'TOO LONG'", 527 $conn); 528 foreach ( $result as $row ) { 529 if ( $i > 13 ) { 530 # It's possible that the number of rows returned isn't 14. 531 # Don't add extra status counters. 532 break; 533 } 534 $count_key = sprintf("Query_time_count_%02d", $i); 535 $total_key = sprintf("Query_time_total_%02d", $i); 536 $status[$count_key] = $row['count']; 537 $status[$total_key] = $row['total']; 538 $i++; 539 } 540 # It's also possible that the number of rows returned is too few. 541 # Don't leave any status counters unassigned; it will break graphs. 542 while ( $i <= 13 ) { 543 $count_key = sprintf("Query_time_count_%02d", $i); 544 $total_key = sprintf("Query_time_total_%02d", $i); 545 $status[$count_key] = 0; 546 $status[$total_key] = 0; 547 $i++; 548 } 549 } 550 else { 551 debug('Not getting time histogram because it is not enabled'); 552 } 553 554 # Override values from InnoDB parsing with values from SHOW STATUS, 555 # because InnoDB status might not have everything and the SHOW STATUS is 556 # to be preferred where possible. 557 $overrides = array( 558 'Innodb_buffer_pool_pages_data' => 'database_pages', 559 'Innodb_buffer_pool_pages_dirty' => 'modified_pages', 560 'Innodb_buffer_pool_pages_free' => 'free_pages', 561 'Innodb_buffer_pool_pages_total' => 'pool_size', 562 'Innodb_data_fsyncs' => 'file_fsyncs', 563 'Innodb_data_pending_reads' => 'pending_normal_aio_reads', 564 'Innodb_data_pending_writes' => 'pending_normal_aio_writes', 565 'Innodb_os_log_pending_fsyncs' => 'pending_log_flushes', 566 'Innodb_pages_created' => 'pages_created', 567 'Innodb_pages_read' => 'pages_read', 568 'Innodb_pages_written' => 'pages_written', 569 'Innodb_rows_deleted' => 'rows_deleted', 570 'Innodb_rows_inserted' => 'rows_inserted', 571 'Innodb_rows_read' => 'rows_read', 572 'Innodb_rows_updated' => 'rows_updated', 573 'Innodb_buffer_pool_reads' => 'pool_reads', 574 'Innodb_buffer_pool_read_requests' => 'pool_read_requests', 575 ); 576 577 # If the SHOW STATUS value exists, override... 578 foreach ( $overrides as $key => $val ) { 579 if ( array_key_exists($key, $status) ) { 580 debug("Override $key"); 581 $istatus_vals[$val] = $status[$key]; 582 } 583 } 584 585 # Now copy the values into $status. 586 foreach ( $istatus_vals as $key => $val ) { 587 $status[$key] = $istatus_vals[$key]; 588 } 589 } 590 591 # Make table_open_cache backwards-compatible (issue 63). 592 if ( array_key_exists('table_open_cache', $status) ) { 593 $status['table_cache'] = $status['table_open_cache']; 594 } 595 596 # Compute how much of the key buffer is used and unflushed (issue 127). 597 $status['Key_buf_bytes_used'] 598 = big_sub($status['key_buffer_size'], 599 big_multiply($status['Key_blocks_unused'], 600 $status['key_cache_block_size'])); 601 $status['Key_buf_bytes_unflushed'] 602 = big_multiply($status['Key_blocks_not_flushed'], 603 $status['key_cache_block_size']); 604 605 if ( array_key_exists('unflushed_log', $status) 606 && $status['unflushed_log'] 607 ) { 608 # TODO: I'm not sure what the deal is here; need to debug this. But the 609 # unflushed log bytes spikes a lot sometimes and it's impossible for it to 610 # be more than the log buffer. 611 debug("Unflushed log: $status[unflushed_log]"); 612 $status['unflushed_log'] 613 = max($status['unflushed_log'], $status['innodb_log_buffer_size']); 614 } 615 616 # Define the variables to output. I use shortened variable names so maybe 617 # it'll all fit in 1024 bytes for Cactid and Spine's benefit. Strings must 618 # have some non-hex characters (non a-f0-9) to avoid a Cacti bug. This list 619 # must come right after the word MAGIC_VARS_DEFINITIONS. The Perl script 620 # parses it and uses it as a Perl variable. 621 $keys = array( 622 'Key_read_requests' => 'gg', 623 'Key_reads' => 'gh', 624 'Key_write_requests' => 'gi', 625 'Key_writes' => 'gj', 626 'history_list' => 'gk', 627 'innodb_transactions' => 'gl', 628 'read_views' => 'gm', 629 'current_transactions' => 'gn', 630 'locked_transactions' => 'go', 631 'active_transactions' => 'gp', 632 'pool_size' => 'gq', 633 'free_pages' => 'gr', 634 'database_pages' => 'gs', 635 'modified_pages' => 'gt', 636 'pages_read' => 'gu', 637 'pages_created' => 'gv', 638 'pages_written' => 'gw', 639 'file_fsyncs' => 'gx', 640 'file_reads' => 'gy', 641 'file_writes' => 'gz', 642 'log_writes' => 'hg', 643 'pending_aio_log_ios' => 'hh', 644 'pending_aio_sync_ios' => 'hi', 645 'pending_buf_pool_flushes' => 'hj', 646 'pending_chkp_writes' => 'hk', 647 'pending_ibuf_aio_reads' => 'hl', 648 'pending_log_flushes' => 'hm', 649 'pending_log_writes' => 'hn', 650 'pending_normal_aio_reads' => 'ho', 651 'pending_normal_aio_writes' => 'hp', 652 'ibuf_inserts' => 'hq', 653 'ibuf_merged' => 'hr', 654 'ibuf_merges' => 'hs', 655 'spin_waits' => 'ht', 656 'spin_rounds' => 'hu', 657 'os_waits' => 'hv', 658 'rows_inserted' => 'hw', 659 'rows_updated' => 'hx', 660 'rows_deleted' => 'hy', 661 'rows_read' => 'hz', 662 'Table_locks_waited' => 'ig', 663 'Table_locks_immediate' => 'ih', 664 'Slow_queries' => 'ii', 665 'Open_files' => 'ij', 666 'Open_tables' => 'ik', 667 'Opened_tables' => 'il', 668 'innodb_open_files' => 'im', 669 'open_files_limit' => 'in', 670 'table_cache' => 'io', 671 'Aborted_clients' => 'ip', 672 'Aborted_connects' => 'iq', 673 'Max_used_connections' => 'ir', 674 'Slow_launch_threads' => 'is', 675 'Threads_cached' => 'it', 676 'Threads_connected' => 'iu', 677 'Threads_created' => 'iv', 678 'Threads_running' => 'iw', 679 'max_connections' => 'ix', 680 'thread_cache_size' => 'iy', 681 'Connections' => 'iz', 682 'slave_running' => 'jg', 683 'slave_stopped' => 'jh', 684 'Slave_retried_transactions' => 'ji', 685 'slave_lag' => 'jj', 686 'Slave_open_temp_tables' => 'jk', 687 'Qcache_free_blocks' => 'jl', 688 'Qcache_free_memory' => 'jm', 689 'Qcache_hits' => 'jn', 690 'Qcache_inserts' => 'jo', 691 'Qcache_lowmem_prunes' => 'jp', 692 'Qcache_not_cached' => 'jq', 693 'Qcache_queries_in_cache' => 'jr', 694 'Qcache_total_blocks' => 'js', 695 'query_cache_size' => 'jt', 696 'Questions' => 'ju', 697 'Com_update' => 'jv', 698 'Com_insert' => 'jw', 699 'Com_select' => 'jx', 700 'Com_delete' => 'jy', 701 'Com_replace' => 'jz', 702 'Com_load' => 'kg', 703 'Com_update_multi' => 'kh', 704 'Com_insert_select' => 'ki', 705 'Com_delete_multi' => 'kj', 706 'Com_replace_select' => 'kk', 707 'Select_full_join' => 'kl', 708 'Select_full_range_join' => 'km', 709 'Select_range' => 'kn', 710 'Select_range_check' => 'ko', 711 'Select_scan' => 'kp', 712 'Sort_merge_passes' => 'kq', 713 'Sort_range' => 'kr', 714 'Sort_rows' => 'ks', 715 'Sort_scan' => 'kt', 716 'Created_tmp_tables' => 'ku', 717 'Created_tmp_disk_tables' => 'kv', 718 'Created_tmp_files' => 'kw', 719 'Bytes_sent' => 'kx', 720 'Bytes_received' => 'ky', 721 'innodb_log_buffer_size' => 'kz', 722 'unflushed_log' => 'lg', 723 'log_bytes_flushed' => 'lh', 724 'log_bytes_written' => 'li', 725 'relay_log_space' => 'lj', 726 'binlog_cache_size' => 'lk', 727 'Binlog_cache_disk_use' => 'll', 728 'Binlog_cache_use' => 'lm', 729 'binary_log_space' => 'ln', 730 'innodb_locked_tables' => 'lo', 731 'innodb_lock_structs' => 'lp', 732 'State_closing_tables' => 'lq', 733 'State_copying_to_tmp_table' => 'lr', 734 'State_end' => 'ls', 735 'State_freeing_items' => 'lt', 736 'State_init' => 'lu', 737 'State_locked' => 'lv', 738 'State_login' => 'lw', 739 'State_preparing' => 'lx', 740 'State_reading_from_net' => 'ly', 741 'State_sending_data' => 'lz', 742 'State_sorting_result' => 'mg', 743 'State_statistics' => 'mh', 744 'State_updating' => 'mi', 745 'State_writing_to_net' => 'mj', 746 'State_none' => 'mk', 747 'State_other' => 'ml', 748 'Handler_commit' => 'mm', 749 'Handler_delete' => 'mn', 750 'Handler_discover' => 'mo', 751 'Handler_prepare' => 'mp', 752 'Handler_read_first' => 'mq', 753 'Handler_read_key' => 'mr', 754 'Handler_read_next' => 'ms', 755 'Handler_read_prev' => 'mt', 756 'Handler_read_rnd' => 'mu', 757 'Handler_read_rnd_next' => 'mv', 758 'Handler_rollback' => 'mw', 759 'Handler_savepoint' => 'mx', 760 'Handler_savepoint_rollback' => 'my', 761 'Handler_update' => 'mz', 762 'Handler_write' => 'ng', 763 'innodb_tables_in_use' => 'nh', 764 'innodb_lock_wait_secs' => 'ni', 765 'hash_index_cells_total' => 'nj', 766 'hash_index_cells_used' => 'nk', 767 'total_mem_alloc' => 'nl', 768 'additional_pool_alloc' => 'nm', 769 'uncheckpointed_bytes' => 'nn', 770 'ibuf_used_cells' => 'no', 771 'ibuf_free_cells' => 'np', 772 'ibuf_cell_count' => 'nq', 773 'adaptive_hash_memory' => 'nr', 774 'page_hash_memory' => 'ns', 775 'dictionary_cache_memory' => 'nt', 776 'file_system_memory' => 'nu', 777 'lock_system_memory' => 'nv', 778 'recovery_system_memory' => 'nw', 779 'thread_hash_memory' => 'nx', 780 'innodb_sem_waits' => 'ny', 781 'innodb_sem_wait_time_ms' => 'nz', 782 'Key_buf_bytes_unflushed' => 'og', 783 'Key_buf_bytes_used' => 'oh', 784 'key_buffer_size' => 'oi', 785 'Innodb_row_lock_time' => 'oj', 786 'Innodb_row_lock_waits' => 'ok', 787 'Query_time_count_00' => 'ol', 788 'Query_time_count_01' => 'om', 789 'Query_time_count_02' => 'on', 790 'Query_time_count_03' => 'oo', 791 'Query_time_count_04' => 'op', 792 'Query_time_count_05' => 'oq', 793 'Query_time_count_06' => 'or', 794 'Query_time_count_07' => 'os', 795 'Query_time_count_08' => 'ot', 796 'Query_time_count_09' => 'ou', 797 'Query_time_count_10' => 'ov', 798 'Query_time_count_11' => 'ow', 799 'Query_time_count_12' => 'ox', 800 'Query_time_count_13' => 'oy', 801 'Query_time_total_00' => 'oz', 802 'Query_time_total_01' => 'pg', 803 'Query_time_total_02' => 'ph', 804 'Query_time_total_03' => 'pi', 805 'Query_time_total_04' => 'pj', 806 'Query_time_total_05' => 'pk', 807 'Query_time_total_06' => 'pl', 808 'Query_time_total_07' => 'pm', 809 'Query_time_total_08' => 'pn', 810 'Query_time_total_09' => 'po', 811 'Query_time_total_10' => 'pp', 812 'Query_time_total_11' => 'pq', 813 'Query_time_total_12' => 'pr', 814 'Query_time_total_13' => 'ps', 815 'wsrep_replicated_bytes' => 'pt', 816 'wsrep_received_bytes' => 'pu', 817 'wsrep_replicated' => 'pv', 818 'wsrep_received' => 'pw', 819 'wsrep_local_cert_failures' => 'px', 820 'wsrep_local_bf_aborts' => 'py', 821 'wsrep_local_send_queue' => 'pz', 822 'wsrep_local_recv_queue' => 'qg', 823 'wsrep_cluster_size' => 'qh', 824 'wsrep_cert_deps_distance' => 'qi', 825 'wsrep_apply_window' => 'qj', 826 'wsrep_commit_window' => 'qk', 827 'wsrep_flow_control_paused' => 'ql', 828 'wsrep_flow_control_sent' => 'qm', 829 'wsrep_flow_control_recv' => 'qn', 830 'pool_reads' => 'qo', 831 'pool_read_requests' => 'qp', 832 ); 833 834 # Return the output. 835 $output = array(); 836 foreach ($keys as $key => $short ) { 837 # If the value isn't defined, return -1 which is lower than (most graphs') 838 # minimum value of 0, so it'll be regarded as a missing value. 839 $val = isset($status[$key]) ? $status[$key] : -1; 840 $output[] = "$short:$val"; 841 } 842 $result = implode(' ', $output); 843 if ( $fp ) { 844 if ( fwrite($fp, $result) === FALSE ) { 845 die("Can't write '$cache_file'"); 846 } 847 fclose($fp); 848 } 849 return $result; 850} 851 852# ============================================================================ 853# Given INNODB STATUS text, returns a key-value array of the parsed text. Each 854# line shows a sample of the input for both standard InnoDB as you would find in 855# MySQL 5.0, and XtraDB or enhanced InnoDB from Percona if applicable. Note 856# that extra leading spaces are ignored due to trim(). 857# ============================================================================ 858function get_innodb_array($text, $mysql_version) { 859 $results = array( 860 'spin_waits' => array(), 861 'spin_rounds' => array(), 862 'os_waits' => array(), 863 'pending_normal_aio_reads' => null, 864 'pending_normal_aio_writes' => null, 865 'pending_ibuf_aio_reads' => null, 866 'pending_aio_log_ios' => null, 867 'pending_aio_sync_ios' => null, 868 'pending_log_flushes' => null, 869 'pending_buf_pool_flushes' => null, 870 'file_reads' => null, 871 'file_writes' => null, 872 'file_fsyncs' => null, 873 'ibuf_inserts' => null, 874 'ibuf_merged' => null, 875 'ibuf_merges' => null, 876 'log_bytes_written' => null, 877 'unflushed_log' => null, 878 'log_bytes_flushed' => null, 879 'pending_log_writes' => null, 880 'pending_chkp_writes' => null, 881 'log_writes' => null, 882 'pool_size' => null, 883 'free_pages' => null, 884 'database_pages' => null, 885 'modified_pages' => null, 886 'pages_read' => null, 887 'pages_created' => null, 888 'pages_written' => null, 889 'queries_inside' => null, 890 'queries_queued' => null, 891 'read_views' => null, 892 'rows_inserted' => null, 893 'rows_updated' => null, 894 'rows_deleted' => null, 895 'rows_read' => null, 896 'innodb_transactions' => null, 897 'unpurged_txns' => null, 898 'history_list' => null, 899 'current_transactions' => null, 900 'hash_index_cells_total' => null, 901 'hash_index_cells_used' => null, 902 'total_mem_alloc' => null, 903 'additional_pool_alloc' => null, 904 'last_checkpoint' => null, 905 'uncheckpointed_bytes' => null, 906 'ibuf_used_cells' => null, 907 'ibuf_free_cells' => null, 908 'ibuf_cell_count' => null, 909 'adaptive_hash_memory' => null, 910 'page_hash_memory' => null, 911 'dictionary_cache_memory' => null, 912 'file_system_memory' => null, 913 'lock_system_memory' => null, 914 'recovery_system_memory' => null, 915 'thread_hash_memory' => null, 916 'innodb_sem_waits' => null, 917 'innodb_sem_wait_time_ms' => null, 918 ); 919 $txn_seen = FALSE; 920 foreach ( explode("\n", $text) as $line ) { 921 $line = trim($line); 922 $row = preg_split('/ +/', $line); 923 924 # SEMAPHORES 925 if (strpos($line, 'Mutex spin waits') === 0 ) { 926 # Mutex spin waits 79626940, rounds 157459864, OS waits 698719 927 # Mutex spin waits 0, rounds 247280272495, OS waits 316513438 928 $results['spin_waits'][] = to_int($row[3]); 929 $results['spin_rounds'][] = to_int($row[5]); 930 $results['os_waits'][] = to_int($row[8]); 931 } 932 elseif (strpos($line, 'RW-shared spins') === 0 933 && strpos($line, ';') > 0 ) { 934 # RW-shared spins 3859028, OS waits 2100750; RW-excl spins 4641946, OS waits 1530310 935 $results['spin_waits'][] = to_int($row[2]); 936 $results['spin_waits'][] = to_int($row[8]); 937 $results['os_waits'][] = to_int($row[5]); 938 $results['os_waits'][] = to_int($row[11]); 939 } 940 elseif (strpos($line, 'RW-shared spins') === 0 && strpos($line, '; RW-excl spins') === FALSE) { 941 # Post 5.5.17 SHOW ENGINE INNODB STATUS syntax 942 # RW-shared spins 604733, rounds 8107431, OS waits 241268 943 $results['spin_waits'][] = to_int($row[2]); 944 $results['os_waits'][] = to_int($row[7]); 945 } 946 elseif (strpos($line, 'RW-excl spins') === 0) { 947 # Post 5.5.17 SHOW ENGINE INNODB STATUS syntax 948 # RW-excl spins 604733, rounds 8107431, OS waits 241268 949 $results['spin_waits'][] = to_int($row[2]); 950 $results['os_waits'][] = to_int($row[7]); 951 } 952 elseif (strpos($line, 'seconds the semaphore:') > 0) { 953 # --Thread 907205 has waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore: 954 increment($results, 'innodb_sem_waits', 1); 955 increment($results, 956 'innodb_sem_wait_time_ms', to_int($row[9]) * 1000); 957 } 958 959 # TRANSACTIONS 960 elseif ( strpos($line, 'Trx id counter') === 0 ) { 961 # The beginning of the TRANSACTIONS section: start counting 962 # transactions 963 if ( $mysql_version < 50600 ) { 964 # For versions prior 5.6: two decimals or one hex 965 # Trx id counter 0 1170664159 966 # Trx id counter 861B144C 967 $results['innodb_transactions'] = isset($row[4]) ? make_bigint( 968 $row[3], $row[4]) : base_convert($row[3], 16, 10); 969 } 970 else { 971 # For versions 5.6+ and MariaDB 10.x: one decimal 972 # Trx id counter 2903813 973 $results['innodb_transactions'] = $row[3]; 974 } 975 $txn_seen = TRUE; 976 } 977 elseif ( strpos($line, 'Purge done for trx') === 0 ) { 978 if ( $mysql_version < 50600 ) { 979 # For versions prior 5.6: two decimals or one hex 980 # Purge done for trx's n:o < 0 1170663853 undo n:o < 0 0 981 # Purge done for trx's n:o < 861B135D undo n:o < 0 982 $purged_to = $row[7] == 'undo' ? base_convert($row[6], 16, 10) : make_bigint($row[6], $row[7]); 983 } 984 else { 985 # For versions 5.6+ and MariaDB 10.x: one decimal 986 # Purge done for trx's n:o < 2903354 undo n:o < 0 state: running but idle 987 $purged_to = $row[6]; 988 } 989 $results['unpurged_txns'] 990 = big_sub($results['innodb_transactions'], $purged_to); 991 } 992 elseif (strpos($line, 'History list length') === 0 ) { 993 # History list length 132 994 $results['history_list'] = to_int($row[3]); 995 } 996 elseif ( $txn_seen && strpos($line, '---TRANSACTION') === 0 ) { 997 # ---TRANSACTION 0, not started, process no 13510, OS thread id 1170446656 998 increment($results, 'current_transactions', 1); 999 if ( strpos($line, 'ACTIVE') > 0 ) { 1000 increment($results, 'active_transactions', 1); 1001 } 1002 } 1003 elseif ( $txn_seen && strpos($line, '------- TRX HAS BEEN') === 0 ) { 1004 # ------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED: 1005 increment($results, 'innodb_lock_wait_secs', to_int($row[5])); 1006 } 1007 elseif ( strpos($line, 'read views open inside InnoDB') > 0 ) { 1008 # 1 read views open inside InnoDB 1009 $results['read_views'] = to_int($row[0]); 1010 } 1011 elseif ( strpos($line, 'mysql tables in use') === 0 ) { 1012 # mysql tables in use 2, locked 2 1013 increment($results, 'innodb_tables_in_use', to_int($row[4])); 1014 increment($results, 'innodb_locked_tables', to_int($row[6])); 1015 } 1016 elseif ( $txn_seen && strpos($line, 'lock struct(s)') > 0 ) { 1017 # 23 lock struct(s), heap size 3024, undo log entries 27 1018 # LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5 1019 # LOCK WAIT 2 lock struct(s), heap size 368 1020 if ( strpos($line, 'LOCK WAIT') === 0 ) { 1021 increment($results, 'innodb_lock_structs', to_int($row[2])); 1022 increment($results, 'locked_transactions', 1); 1023 } 1024 else { 1025 increment($results, 'innodb_lock_structs', to_int($row[0])); 1026 } 1027 } 1028 1029 # FILE I/O 1030 elseif (strpos($line, ' OS file reads, ') > 0 ) { 1031 # 8782182 OS file reads, 15635445 OS file writes, 947800 OS fsyncs 1032 $results['file_reads'] = to_int($row[0]); 1033 $results['file_writes'] = to_int($row[4]); 1034 $results['file_fsyncs'] = to_int($row[8]); 1035 } 1036 elseif (strpos($line, 'Pending normal aio reads:') === 0 ) { 1037 # Pending normal aio reads: 0, aio writes: 0, 1038 $results['pending_normal_aio_reads'] = to_int($row[4]); 1039 $results['pending_normal_aio_writes'] = to_int($row[7]); 1040 } 1041 elseif (strpos($line, 'ibuf aio reads') === 0 ) { 1042 # ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 1043 $results['pending_ibuf_aio_reads'] = to_int($row[3]); 1044 $results['pending_aio_log_ios'] = to_int($row[6]); 1045 $results['pending_aio_sync_ios'] = to_int($row[9]); 1046 } 1047 elseif ( strpos($line, 'Pending flushes (fsync)') === 0 ) { 1048 # Pending flushes (fsync) log: 0; buffer pool: 0 1049 $results['pending_log_flushes'] = to_int($row[4]); 1050 $results['pending_buf_pool_flushes'] = to_int($row[7]); 1051 } 1052 1053 # INSERT BUFFER AND ADAPTIVE HASH INDEX 1054 elseif (strpos($line, 'Ibuf for space 0: size ') === 0 ) { 1055 # Older InnoDB code seemed to be ready for an ibuf per tablespace. It 1056 # had two lines in the output. Newer has just one line, see below. 1057 # Ibuf for space 0: size 1, free list len 887, seg size 889, is not empty 1058 # Ibuf for space 0: size 1, free list len 887, seg size 889, 1059 $results['ibuf_used_cells'] = to_int($row[5]); 1060 $results['ibuf_free_cells'] = to_int($row[9]); 1061 $results['ibuf_cell_count'] = to_int($row[12]); 1062 } 1063 elseif (strpos($line, 'Ibuf: size ') === 0 ) { 1064 # Ibuf: size 1, free list len 4634, seg size 4636, 1065 $results['ibuf_used_cells'] = to_int($row[2]); 1066 $results['ibuf_free_cells'] = to_int($row[6]); 1067 $results['ibuf_cell_count'] = to_int($row[9]); 1068 if (strpos($line, 'merges')) { 1069 $results['ibuf_merges'] = to_int($row[10]); 1070 } 1071 } 1072 elseif (strpos($line, ', delete mark ') > 0 && strpos($prev_line, 'merged operations:') === 0 ) { 1073 # Output of show engine innodb status has changed in 5.5 1074 # merged operations: 1075 # insert 593983, delete mark 387006, delete 73092 1076 $results['ibuf_inserts'] = to_int($row[1]); 1077 $results['ibuf_merged'] = to_int($row[1]) + to_int($row[4]) + to_int($row[6]); 1078 } 1079 elseif (strpos($line, ' merged recs, ') > 0 ) { 1080 # 19817685 inserts, 19817684 merged recs, 3552620 merges 1081 $results['ibuf_inserts'] = to_int($row[0]); 1082 $results['ibuf_merged'] = to_int($row[2]); 1083 $results['ibuf_merges'] = to_int($row[5]); 1084 } 1085 elseif (strpos($line, 'Hash table size ') === 0 ) { 1086 # In some versions of InnoDB, the used cells is omitted. 1087 # Hash table size 4425293, used cells 4229064, .... 1088 # Hash table size 57374437, node heap has 72964 buffer(s) <-- no used cells 1089 $results['hash_index_cells_total'] = to_int($row[3]); 1090 $results['hash_index_cells_used'] 1091 = strpos($line, 'used cells') > 0 ? to_int($row[6]) : '0'; 1092 } 1093 1094 # LOG 1095 elseif (strpos($line, " log i/o's done, ") > 0 ) { 1096 # 3430041 log i/o's done, 17.44 log i/o's/second 1097 # 520835887 log i/o's done, 17.28 log i/o's/second, 518724686 syncs, 2980893 checkpoints 1098 # TODO: graph syncs and checkpoints 1099 $results['log_writes'] = to_int($row[0]); 1100 } 1101 elseif ($mysql_version < 50700 && strpos($line, " pending log writes, ") > 0 ) { 1102 # 0 pending log writes, 0 pending chkp writes 1103 $results['pending_log_writes'] = to_int($row[0]); 1104 $results['pending_chkp_writes'] = to_int($row[4]); 1105 } 1106 elseif ($mysql_version >= 50700 && strpos($line, " pending log flushes, ") > 0 ) { 1107 # Post 5.7.x SHOW ENGINE INNODB STATUS syntax 1108 # 0 pending log flushes, 0 pending chkp writes 1109 $results['pending_log_writes'] = to_int($row[0]); 1110 $results['pending_chkp_writes'] = to_int($row[4]); 1111 } 1112 elseif (strpos($line, "Log sequence number") === 0 ) { 1113 # This number is NOT printed in hex in InnoDB plugin. 1114 # Log sequence number 13093949495856 //plugin 1115 # Log sequence number 125 3934414864 //normal 1116 $results['log_bytes_written'] 1117 = isset($row[4]) 1118 ? make_bigint($row[3], $row[4]) 1119 : to_int($row[3]); 1120 } 1121 elseif (strpos($line, "Log flushed up to") === 0 ) { 1122 # This number is NOT printed in hex in InnoDB plugin. 1123 # Log flushed up to 13093948219327 1124 # Log flushed up to 125 3934414864 1125 $results['log_bytes_flushed'] 1126 = isset($row[5]) 1127 ? make_bigint($row[4], $row[5]) 1128 : to_int($row[4]); 1129 } 1130 elseif (strpos($line, "Last checkpoint at") === 0 ) { 1131 # Last checkpoint at 125 3934293461 1132 $results['last_checkpoint'] 1133 = isset($row[4]) 1134 ? make_bigint($row[3], $row[4]) 1135 : to_int($row[3]); 1136 } 1137 1138 # BUFFER POOL AND MEMORY 1139 elseif ( $mysql_version < 50700 && strpos($line, "Total memory allocated") === 0 && strpos($line, "in additional pool allocated") > 0 ) { 1140 # Total memory allocated 29642194944; in additional pool allocated 0 1141 # Total memory allocated by read views 96 1142 $results['total_mem_alloc'] = to_int($row[3]); 1143 $results['additional_pool_alloc'] = to_int($row[8]); 1144 } 1145 elseif ( $mysql_version >= 50700 && strpos($line, "Total large memory allocated") === 0 ) { 1146 # Post 5.7.x SHOW ENGINE INNODB STATUS syntax 1147 # Total large memory allocated 2198863872 1148 $results['total_mem_alloc'] = to_int($row[4]); 1149 $results['additional_pool_alloc'] = 0; 1150 } 1151 elseif(strpos($line, 'Adaptive hash index ') === 0 ) { 1152 # Adaptive hash index 1538240664 (186998824 + 1351241840) 1153 $results['adaptive_hash_memory'] = to_int($row[3]); 1154 } 1155 elseif(strpos($line, 'Page hash ') === 0 ) { 1156 # Page hash 11688584 1157 $results['page_hash_memory'] = to_int($row[2]); 1158 } 1159 elseif(strpos($line, 'Dictionary cache ') === 0 ) { 1160 # Dictionary cache 145525560 (140250984 + 5274576) 1161 $results['dictionary_cache_memory'] = to_int($row[2]); 1162 } 1163 elseif(strpos($line, 'File system ') === 0 ) { 1164 # File system 313848 (82672 + 231176) 1165 $results['file_system_memory'] = to_int($row[2]); 1166 } 1167 elseif(strpos($line, 'Lock system ') === 0 ) { 1168 # Lock system 29232616 (29219368 + 13248) 1169 $results['lock_system_memory'] = to_int($row[2]); 1170 } 1171 elseif(strpos($line, 'Recovery system ') === 0 ) { 1172 # Recovery system 0 (0 + 0) 1173 $results['recovery_system_memory'] = to_int($row[2]); 1174 } 1175 elseif(strpos($line, 'Threads ') === 0 ) { 1176 # Threads 409336 (406936 + 2400) 1177 $results['thread_hash_memory'] = to_int($row[1]); 1178 } 1179 elseif(strpos($line, 'innodb_io_pattern ') === 0 ) { 1180 # innodb_io_pattern 0 (0 + 0) 1181 $results['innodb_io_pattern_memory'] = to_int($row[1]); 1182 } 1183 elseif (strpos($line, "Buffer pool size ") === 0 ) { 1184 # The " " after size is necessary to avoid matching the wrong line: 1185 # Buffer pool size 1769471 1186 # Buffer pool size, bytes 28991012864 1187 $results['pool_size'] = to_int($row[3]); 1188 } 1189 elseif (strpos($line, "Free buffers") === 0 ) { 1190 # Free buffers 0 1191 $results['free_pages'] = to_int($row[2]); 1192 } 1193 elseif (strpos($line, "Database pages") === 0 ) { 1194 # Database pages 1696503 1195 $results['database_pages'] = to_int($row[2]); 1196 } 1197 elseif (strpos($line, "Modified db pages") === 0 ) { 1198 # Modified db pages 160602 1199 $results['modified_pages'] = to_int($row[3]); 1200 } 1201 elseif (strpos($line, "Pages read ahead") === 0 ) { 1202 # Must do this BEFORE the next test, otherwise it'll get fooled by this 1203 # line from the new plugin (see samples/innodb-015.txt): 1204 # Pages read ahead 0.00/s, evicted without access 0.06/s 1205 # TODO: No-op for now, see issue 134. 1206 } 1207 elseif (strpos($line, "Pages read") === 0 ) { 1208 # Pages read 15240822, created 1770238, written 21705836 1209 $results['pages_read'] = to_int($row[2]); 1210 $results['pages_created'] = to_int($row[4]); 1211 $results['pages_written'] = to_int($row[6]); 1212 } 1213 1214 # ROW OPERATIONS 1215 elseif (strpos($line, 'Number of rows inserted') === 0 ) { 1216 # Number of rows inserted 50678311, updated 66425915, deleted 20605903, read 454561562 1217 $results['rows_inserted'] = to_int($row[4]); 1218 $results['rows_updated'] = to_int($row[6]); 1219 $results['rows_deleted'] = to_int($row[8]); 1220 $results['rows_read'] = to_int($row[10]); 1221 } 1222 elseif (strpos($line, " queries inside InnoDB, ") > 0 ) { 1223 # 0 queries inside InnoDB, 0 queries in queue 1224 $results['queries_inside'] = to_int($row[0]); 1225 $results['queries_queued'] = to_int($row[4]); 1226 } 1227 $prev_line = $line; 1228 } 1229 1230 foreach ( array('spin_waits', 'spin_rounds', 'os_waits') as $key ) { 1231 $results[$key] = to_int(array_sum($results[$key])); 1232 } 1233 $results['unflushed_log'] 1234 = big_sub($results['log_bytes_written'], $results['log_bytes_flushed']); 1235 $results['uncheckpointed_bytes'] 1236 = big_sub($results['log_bytes_written'], $results['last_checkpoint']); 1237 1238 return $results; 1239} 1240 1241 1242# ============================================================================ 1243# Returns a bigint from two ulint. This is tested in 1244# t/mysql_stats.php and copied, without tests, to ss_get_by_ssh.php. 1245# ============================================================================ 1246function make_bigint ($hi, $lo) { 1247 debug(array($hi, $lo)); 1248 $hi = $hi ? $hi : '0'; # Handle empty-string or whatnot 1249 $lo = $lo ? $lo : '0'; 1250 return big_add(big_multiply($hi, 4294967296), $lo); 1251} 1252 1253# ============================================================================ 1254# Extracts the numbers from a string. You can't reliably do this by casting to 1255# an int, because numbers that are bigger than PHP's int (varies by platform) 1256# will be truncated. And you can't use sprintf(%u) either, because the maximum 1257# value that will return on some platforms is 4022289582. So this just handles 1258# them as a string instead. It extracts digits until it finds a non-digit and 1259# quits. This is tested in t/mysql_stats.php and copied, without tests, to 1260# ss_get_by_ssh.php. 1261# ============================================================================ 1262function to_int ( $str ) { 1263 debug($str); 1264 global $debug; 1265 preg_match('{(\d+)}', $str, $m); 1266 if ( isset($m[1]) ) { 1267 return $m[1]; 1268 } 1269 elseif ( $debug ) { 1270 print_r(debug_backtrace()); 1271 } 1272 else { 1273 return 0; 1274 } 1275} 1276 1277# ============================================================================ 1278# Wrap mysqli_query in error-handling, and instead of returning the result, 1279# return an array of arrays in the result. 1280# ============================================================================ 1281function run_query($sql, $conn) { 1282 global $debug; 1283 debug($sql); 1284 $result = @mysqli_query($conn, $sql); 1285 if ( $debug && strpos($sql, 'SHOW SLAVE STATUS ') === false ) { 1286 $error = @mysqli_error($conn); 1287 if ( $error ) { 1288 debug(array($sql, $error)); 1289 die("SQLERR $error in $sql"); 1290 } 1291 } 1292 $array = array(); 1293 $count = @mysqli_num_rows($result); 1294 if ( $count > 10000 ) { 1295 debug('Abnormal number of rows returned: ' . $count); 1296 } 1297 else { 1298 while ( $row = @mysqli_fetch_array($result) ) { 1299 $array[] = $row; 1300 } 1301 } 1302 debug(array($sql, $array)); 1303 return $array; 1304} 1305 1306# ============================================================================ 1307# Safely increments a value that might be null. 1308# ============================================================================ 1309function increment(&$arr, $key, $howmuch) { 1310 debug(array($key, $howmuch)); 1311 if ( array_key_exists($key, $arr) && isset($arr[$key]) ) { 1312 $arr[$key] = big_add($arr[$key], $howmuch); 1313 } 1314 else { 1315 $arr[$key] = $howmuch; 1316 } 1317} 1318 1319# ============================================================================ 1320# Multiply two big integers together as accurately as possible with reasonable 1321# effort. This is tested in t/mysql_stats.php and copied, without tests, to 1322# ss_get_by_ssh.php. $force is for testability. 1323# ============================================================================ 1324function big_multiply ($left, $right, $force = null) { 1325 if ( function_exists("gmp_mul") && (is_null($force) || $force == 'gmp') ) { 1326 debug(array('gmp_mul', $left, $right)); 1327 return gmp_strval( gmp_mul( $left, $right )); 1328 } 1329 elseif ( function_exists("bcmul") && (is_null($force) || $force == 'bc') ) { 1330 debug(array('bcmul', $left, $right)); 1331 return bcmul( $left, $right ); 1332 } 1333 else { # Or $force == 'something else' 1334 debug(array('sprintf', $left, $right)); 1335 return sprintf("%.0f", $left * $right); 1336 } 1337} 1338 1339# ============================================================================ 1340# Subtract two big integers as accurately as possible with reasonable effort. 1341# This is tested in t/mysql_stats.php and copied, without tests, to 1342# ss_get_by_ssh.php. $force is for testability. 1343# ============================================================================ 1344function big_sub ($left, $right, $force = null) { 1345 debug(array($left, $right)); 1346 if ( is_null($left) ) { $left = 0; } 1347 if ( is_null($right) ) { $right = 0; } 1348 if ( function_exists("gmp_sub") && (is_null($force) || $force == 'gmp')) { 1349 debug(array('gmp_sub', $left, $right)); 1350 return gmp_strval( gmp_sub( $left, $right )); 1351 } 1352 elseif ( function_exists("bcsub") && (is_null($force) || $force == 'bc')) { 1353 debug(array('bcsub', $left, $right)); 1354 return bcsub( $left, $right ); 1355 } 1356 else { # Or $force == 'something else' 1357 debug(array('to_int', $left, $right)); 1358 return to_int($left - $right); 1359 } 1360} 1361 1362# ============================================================================ 1363# Add two big integers together as accurately as possible with reasonable 1364# effort. This is tested in t/mysql_stats.php and copied, without tests, to 1365# ss_get_by_ssh.php. $force is for testability. 1366# ============================================================================ 1367function big_add ($left, $right, $force = null) { 1368 if ( is_null($left) ) { $left = 0; } 1369 if ( is_null($right) ) { $right = 0; } 1370 if ( function_exists("gmp_add") && (is_null($force) || $force == 'gmp')) { 1371 debug(array('gmp_add', $left, $right)); 1372 return gmp_strval( gmp_add( $left, $right )); 1373 } 1374 elseif ( function_exists("bcadd") && (is_null($force) || $force == 'bc')) { 1375 debug(array('bcadd', $left, $right)); 1376 return bcadd( $left, $right ); 1377 } 1378 else { # Or $force == 'something else' 1379 debug(array('to_int', $left, $right)); 1380 return to_int($left + $right); 1381 } 1382} 1383 1384# ============================================================================ 1385# Writes to a debugging log. 1386# ============================================================================ 1387function debug($val) { 1388 global $debug_log; 1389 if ( !$debug_log ) { 1390 return; 1391 } 1392 if ( $fp = fopen($debug_log, 'a+') ) { 1393 $trace = debug_backtrace(); 1394 $calls = array(); 1395 $i = 0; 1396 $line = 0; 1397 $file = ''; 1398 foreach ( debug_backtrace() as $arr ) { 1399 if ( $i++ ) { 1400 $calls[] = "$arr[function]() at $file:$line"; 1401 } 1402 $line = array_key_exists('line', $arr) ? $arr['line'] : '?'; 1403 $file = array_key_exists('file', $arr) ? $arr['file'] : '?'; 1404 } 1405 if ( !count($calls) ) { 1406 $calls[] = "at $file:$line"; 1407 } 1408 fwrite($fp, date('Y-m-d H:i:s') . ' ' . implode(' <- ', $calls)); 1409 fwrite($fp, "\n" . var_export($val, TRUE) . "\n"); 1410 fclose($fp); 1411 } 1412 else { # Disable logging 1413 print("Warning: disabling debug logging to $debug_log\n"); 1414 $debug_log = FALSE; 1415 } 1416} 1417 1418