function get_innodb_array($text) { $results = array('spin_waits' => array(), 'spin_rounds' => array(), 'os_waits' => array(), 'pending_normal_aio_reads' => null, 'pending_normal_aio_writes' => null, 'pending_ibuf_aio_reads' => null, 'pending_aio_log_ios' => null, 'pending_aio_sync_ios' => null, 'pending_log_flushes' => null, 'pending_buf_pool_flushes' => null, 'file_reads' => null, 'file_writes' => null, 'file_fsyncs' => null, 'ibuf_inserts' => null, 'ibuf_merged' => null, 'ibuf_merges' => null, 'log_bytes_written' => null, 'unflushed_log' => null, 'log_bytes_flushed' => null, 'pending_log_writes' => null, 'pending_chkp_writes' => null, 'log_writes' => null, 'pool_size' => null, 'free_pages' => null, 'database_pages' => null, 'modified_pages' => null, 'pages_read' => null, 'pages_created' => null, 'pages_written' => null, 'queries_inside' => null, 'queries_queued' => null, 'read_views' => null, 'rows_inserted' => null, 'rows_updated' => null, 'rows_deleted' => null, 'rows_read' => null, 'innodb_transactions' => null, 'unpurged_txns' => null, 'history_list' => null, 'current_transactions' => null, 'hash_index_cells_total' => null, 'hash_index_cells_used' => null, 'total_mem_alloc' => null, 'additional_pool_alloc' => null, 'last_checkpoint' => null, 'uncheckpointed_bytes' => null, 'ibuf_used_cells' => null, 'ibuf_free_cells' => null, 'ibuf_cell_count' => null, 'adaptive_hash_memory' => null, 'page_hash_memory' => null, 'dictionary_cache_memory' => null, 'file_system_memory' => null, 'lock_system_memory' => null, 'recovery_system_memory' => null, 'thread_hash_memory' => null, 'innodb_sem_waits' => null, 'innodb_sem_wait_time_ms' => null); $txn_seen = FALSE; foreach (explode("\n", $text) as $line) { $line = trim($line); $row = preg_split('/ +/', $line); # SEMAPHORES if (strpos($line, 'Mutex spin waits') === 0) { # Mutex spin waits 79626940, rounds 157459864, OS waits 698719 # Mutex spin waits 0, rounds 247280272495, OS waits 316513438 $results['spin_waits'][] = to_int($row[3]); $results['spin_rounds'][] = to_int($row[5]); $results['os_waits'][] = to_int($row[8]); } elseif (strpos($line, 'RW-shared spins') === 0) { # RW-shared spins 3859028, OS waits 2100750; RW-excl spins 4641946, OS waits 1530310 $results['spin_waits'][] = to_int($row[2]); $results['spin_waits'][] = to_int($row[8]); $results['os_waits'][] = to_int($row[5]); $results['os_waits'][] = to_int($row[11]); } elseif (strpos($line, 'seconds the semaphore:') > 0) { # --Thread 907205 has waited at handler/ha_innodb.cc line 7156 for 1.00 seconds the semaphore: increment($results, 'innodb_sem_waits', 1); increment($results, 'innodb_sem_wait_time_ms', to_int($row[9]) * 1000); } elseif (strpos($line, 'Trx id counter') === 0) { # The beginning of the TRANSACTIONS section: start counting # transactions # Trx id counter 0 1170664159 # Trx id counter 861B144C $results['innodb_transactions'] = make_bigint($row[3], $row[4]); $txn_seen = TRUE; } elseif (strpos($line, 'Purge done for trx') === 0) { # Purge done for trx's n:o < 0 1170663853 undo n:o < 0 0 # Purge done for trx's n:o < 861B135D undo n:o < 0 $purged_to = make_bigint($row[6], $row[7] == 'undo' ? null : $row[7]); $results['unpurged_txns'] = big_sub($results['innodb_transactions'], $purged_to); } elseif (strpos($line, 'History list length') === 0) { # History list length 132 $results['history_list'] = to_int($row[3]); } elseif ($txn_seen && strpos($line, '---TRANSACTION') === 0) { # ---TRANSACTION 0, not started, process no 13510, OS thread id 1170446656 increment($results, 'current_transactions', 1); if (strpos($line, 'ACTIVE') > 0) { increment($results, 'active_transactions', 1); } } elseif ($txn_seen && strpos($line, '------- TRX HAS BEEN') === 0) { # ------- TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED: increment($results, 'innodb_lock_wait_secs', to_int($row[5])); } elseif (strpos($line, 'read views open inside InnoDB') > 0) { # 1 read views open inside InnoDB $results['read_views'] = to_int($row[0]); } elseif (strpos($line, 'mysql tables in use') === 0) { # mysql tables in use 2, locked 2 increment($results, 'innodb_tables_in_use', to_int($row[4])); increment($results, 'innodb_locked_tables', to_int($row[6])); } elseif ($txn_seen && strpos($line, 'lock struct(s)') > 0) { # 23 lock struct(s), heap size 3024, undo log entries 27 # LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5 # LOCK WAIT 2 lock struct(s), heap size 368 if (strpos($line, 'LOCK WAIT') === 0) { increment($results, 'innodb_lock_structs', to_int($row[2])); increment($results, 'locked_transactions', 1); } else { increment($results, 'innodb_lock_structs', to_int($row[0])); } } elseif (strpos($line, ' OS file reads, ') > 0) { # 8782182 OS file reads, 15635445 OS file writes, 947800 OS fsyncs $results['file_reads'] = to_int($row[0]); $results['file_writes'] = to_int($row[4]); $results['file_fsyncs'] = to_int($row[8]); } elseif (strpos($line, 'Pending normal aio reads:') === 0) { # Pending normal aio reads: 0, aio writes: 0, $results['pending_normal_aio_reads'] = to_int($row[4]); $results['pending_normal_aio_writes'] = to_int($row[7]); } elseif (strpos($line, 'ibuf aio reads') === 0) { # ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 $results['pending_ibuf_aio_reads'] = to_int($row[3]); $results['pending_aio_log_ios'] = to_int($row[6]); $results['pending_aio_sync_ios'] = to_int($row[9]); } elseif (strpos($line, 'Pending flushes (fsync)') === 0) { # Pending flushes (fsync) log: 0; buffer pool: 0 $results['pending_log_flushes'] = to_int($row[4]); $results['pending_buf_pool_flushes'] = to_int($row[7]); } elseif (strpos($line, 'Ibuf for space 0: size ') === 0) { # Older InnoDB code seemed to be ready for an ibuf per tablespace. It # had two lines in the output. Newer has just one line, see below. # Ibuf for space 0: size 1, free list len 887, seg size 889, is not empty # Ibuf for space 0: size 1, free list len 887, seg size 889, $results['ibuf_used_cells'] = to_int($row[5]); $results['ibuf_free_cells'] = to_int($row[9]); $results['ibuf_cell_count'] = to_int($row[12]); } elseif (strpos($line, 'Ibuf: size ') === 0) { # Ibuf: size 1, free list len 4634, seg size 4636, $results['ibuf_used_cells'] = to_int($row[2]); $results['ibuf_free_cells'] = to_int($row[6]); $results['ibuf_cell_count'] = to_int($row[9]); } elseif (strpos($line, ' merged recs, ') > 0) { # 19817685 inserts, 19817684 merged recs, 3552620 merges $results['ibuf_inserts'] = to_int($row[0]); $results['ibuf_merged'] = to_int($row[2]); $results['ibuf_merges'] = to_int($row[5]); } elseif (strpos($line, 'Hash table size ') === 0) { # In some versions of InnoDB, the used cells is omitted. # Hash table size 4425293, used cells 4229064, .... # Hash table size 57374437, node heap has 72964 buffer(s) <-- no used cells $results['hash_index_cells_total'] = to_int($row[3]); $results['hash_index_cells_used'] = strpos($line, 'used cells') > 0 ? to_int($row[6]) : '0'; } elseif (strpos($line, " log i/o's done, ") > 0) { # 3430041 log i/o's done, 17.44 log i/o's/second # 520835887 log i/o's done, 17.28 log i/o's/second, 518724686 syncs, 2980893 checkpoints # TODO: graph syncs and checkpoints $results['log_writes'] = to_int($row[0]); } elseif (strpos($line, " pending log writes, ") > 0) { # 0 pending log writes, 0 pending chkp writes $results['pending_log_writes'] = to_int($row[0]); $results['pending_chkp_writes'] = to_int($row[4]); } elseif (strpos($line, "Log sequence number") === 0) { # This number is NOT printed in hex in InnoDB plugin. # Log sequence number 13093949495856 //plugin # Log sequence number 125 3934414864 //normal $results['log_bytes_written'] = isset($row[4]) ? make_bigint($row[3], $row[4]) : to_int($row[3]); } elseif (strpos($line, "Log flushed up to") === 0) { # This number is NOT printed in hex in InnoDB plugin. # Log flushed up to 13093948219327 # Log flushed up to 125 3934414864 $results['log_bytes_flushed'] = isset($row[5]) ? make_bigint($row[4], $row[5]) : to_int($row[4]); } elseif (strpos($line, "Last checkpoint at") === 0) { # Last checkpoint at 125 3934293461 $results['last_checkpoint'] = isset($row[4]) ? make_bigint($row[3], $row[4]) : to_int($row[3]); } elseif (strpos($line, "Total memory allocated") === 0) { # Total memory allocated 29642194944; in additional pool allocated 0 $results['total_mem_alloc'] = to_int($row[3]); $results['additional_pool_alloc'] = to_int($row[8]); } elseif (strpos($line, 'Adaptive hash index ') === 0) { # Adaptive hash index 1538240664 (186998824 + 1351241840) $results['adaptive_hash_memory'] = to_int($row[3]); } elseif (strpos($line, 'Page hash ') === 0) { # Page hash 11688584 $results['page_hash_memory'] = to_int($row[2]); } elseif (strpos($line, 'Dictionary cache ') === 0) { # Dictionary cache 145525560 (140250984 + 5274576) $results['dictionary_cache_memory'] = to_int($row[2]); } elseif (strpos($line, 'File system ') === 0) { # File system 313848 (82672 + 231176) $results['file_system_memory'] = to_int($row[2]); } elseif (strpos($line, 'Lock system ') === 0) { # Lock system 29232616 (29219368 + 13248) $results['lock_system_memory'] = to_int($row[2]); } elseif (strpos($line, 'Recovery system ') === 0) { # Recovery system 0 (0 + 0) $results['recovery_system_memory'] = to_int($row[2]); } elseif (strpos($line, 'Threads ') === 0) { # Threads 409336 (406936 + 2400) $results['thread_hash_memory'] = to_int($row[1]); } elseif (strpos($line, 'innodb_io_pattern ') === 0) { # innodb_io_pattern 0 (0 + 0) $results['innodb_io_pattern_memory'] = to_int($row[1]); } elseif (strpos($line, "Buffer pool size ") === 0) { # The " " after size is necessary to avoid matching the wrong line: # Buffer pool size 1769471 # Buffer pool size, bytes 28991012864 $results['pool_size'] = to_int($row[3]); } elseif (strpos($line, "Free buffers") === 0) { # Free buffers 0 $results['free_pages'] = to_int($row[2]); } elseif (strpos($line, "Database pages") === 0) { # Database pages 1696503 $results['database_pages'] = to_int($row[2]); } elseif (strpos($line, "Modified db pages") === 0) { # Modified db pages 160602 $results['modified_pages'] = to_int($row[3]); } elseif (strpos($line, "Pages read ahead") === 0) { # Must do this BEFORE the next test, otherwise it'll get fooled by this # line from the new plugin (see samples/innodb-015.txt): # Pages read ahead 0.00/s, evicted without access 0.06/s # TODO: No-op for now, see issue 134. } elseif (strpos($line, "Pages read") === 0) { # Pages read 15240822, created 1770238, written 21705836 $results['pages_read'] = to_int($row[2]); $results['pages_created'] = to_int($row[4]); $results['pages_written'] = to_int($row[6]); } elseif (strpos($line, 'Number of rows inserted') === 0) { # Number of rows inserted 50678311, updated 66425915, deleted 20605903, read 454561562 $results['rows_inserted'] = to_int($row[4]); $results['rows_updated'] = to_int($row[6]); $results['rows_deleted'] = to_int($row[8]); $results['rows_read'] = to_int($row[10]); } elseif (strpos($line, " queries inside InnoDB, ") > 0) { # 0 queries inside InnoDB, 0 queries in queue $results['queries_inside'] = to_int($row[0]); $results['queries_queued'] = to_int($row[4]); } } foreach (array('spin_waits', 'spin_rounds', 'os_waits') as $key) { $results[$key] = to_int(array_sum($results[$key])); } $results['unflushed_log'] = big_sub($results['log_bytes_written'], $results['log_bytes_flushed']); $results['uncheckpointed_bytes'] = big_sub($results['log_bytes_written'], $results['last_checkpoint']); return $results; }
<?php require 'test-more.php'; require '../scripts/ss_get_mysql_stats.php'; $debug = true; is(make_bigint('0', '1170663853'), '1170663853', 'make_bigint 0 1170663853'); is(make_bigint('1', '504617703'), '4799584999', 'make_bigint 1 504617703'); is(make_bigint('EF861B144C'), '1028747105356', 'make_bigint EF861B144C'); is(big_sub('74900191315', '1170664159'), '73729527156', 'big_sub 1170664159 74900191315'); is(big_sub('74900191315', '1170664159', 'something else'), '73729527156', 'big_sub 1170664159 74900191315 forced'); is(big_multiply('74900191315', '1170664159'), '87682969474713583616', 'big_multiply 74900191315 and 1170664159'); is(big_multiply('74900191315', '1170664159', 'something else'), '87682969474713583616', 'big_multiply 74900191315 and 1170664159 forced'); is_deeply(get_innodb_array(file_get_contents('samples/innodb-001.txt')), array('spin_waits' => '8317256878', 'spin_rounds' => '247280272495', 'os_waits' => '1962880678', 'innodb_transactions' => '1170664159', 'unpurged_txns' => '306', 'history_list' => '9', 'current_transactions' => '36', 'pending_normal_aio_reads' => '0', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '1', 'pending_aio_log_ios' => '2', 'pending_aio_sync_ios' => '3', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'file_reads' => '5985113', 'file_writes' => '633045221', 'file_fsyncs' => '537534629', 'ibuf_inserts' => '19817685', 'ibuf_merged' => '19817684', 'ibuf_merges' => '3552620', 'log_bytes_written' => '540805326864', 'unflushed_log' => '0', 'log_bytes_flushed' => '540805326864', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'log_writes' => '520835887', 'pool_size' => '720896', 'free_pages' => '0', 'database_pages' => '638423', 'modified_pages' => '118', 'pages_read' => '28593890', 'pages_created' => '5375161', 'pages_written' => '154670836', 'queries_inside' => '0', 'queries_queued' => '0', 'read_views' => '1', 'rows_inserted' => '544159502', 'rows_updated' => '355138902', 'rows_deleted' => '50580680', 'rows_read' => '1911833505287', 'hash_index_cells_total' => '23374853', 'hash_index_cells_used' => '21238151', 'total_mem_alloc' => '13102052218', 'additional_pool_alloc' => '1048576', 'last_checkpoint' => '540805205461', 'uncheckpointed_bytes' => '121403', 'ibuf_used_cells' => '1', 'ibuf_free_cells' => '4634', 'ibuf_cell_count' => '4636', 'adaptive_hash_memory' => '1538240664', 'page_hash_memory' => '11688584', 'dictionary_cache_memory' => '145525560', 'file_system_memory' => '313848', 'lock_system_memory' => '29232616', 'recovery_system_memory' => '1', 'thread_hash_memory' => '409336', 'innodb_io_pattern_memory' => '1', 'innodb_sem_waits' => NULL, 'innodb_sem_wait_time_ms' => NULL), 'samples/innodb-001.txt'); is_deeply(get_innodb_array(file_get_contents('samples/innodb-002.txt')), array('spin_waits' => '88127914', 'spin_rounds' => '157459864', 'os_waits' => '4329779', 'pending_normal_aio_reads' => '1', 'pending_normal_aio_writes' => '2', 'pending_ibuf_aio_reads' => '1', 'pending_aio_log_ios' => '2', 'pending_aio_sync_ios' => '3', 'pending_log_flushes' => '1', 'pending_buf_pool_flushes' => '2', 'file_reads' => '8782182', 'file_writes' => '15635445', 'file_fsyncs' => '947800', 'ibuf_inserts' => '17549044', 'ibuf_merged' => '15956910', 'ibuf_merges' => '1676050', 'log_bytes_written' => '13093949495856', 'unflushed_log' => '1276529', 'log_bytes_flushed' => '13093948219327', 'pending_log_writes' => '1', 'pending_chkp_writes' => '2', 'log_writes' => '3430041', 'pool_size' => '1769471', 'free_pages' => '1', 'database_pages' => '1696503', 'modified_pages' => '160602', 'pages_read' => '15240822', 'pages_created' => '1770238', 'pages_written' => '21705836', 'queries_inside' => '1', 'queries_queued' => '2', 'read_views' => '1', 'rows_inserted' => '50678311', 'rows_updated' => '66425915', 'rows_deleted' => '20605903', 'rows_read' => '454561562', 'innodb_transactions' => '1028747105356', 'unpurged_txns' => '1026497183983', 'history_list' => '132', 'current_transactions' => '2', 'hash_index_cells_total' => '57374437', 'hash_index_cells_used' => 0, 'total_mem_alloc' => '29642194944', 'additional_pool_alloc' => '0', 'last_checkpoint' => '13093217877062', 'uncheckpointed_bytes' => '731618794', 'ibuf_used_cells' => '10204', 'ibuf_free_cells' => '157151', 'ibuf_cell_count' => '167356', 'adaptive_hash_memory' => '1654507416', 'page_hash_memory' => '28688008', 'dictionary_cache_memory' => '116312344', 'file_system_memory' => '172560', 'lock_system_memory' => '71719560', 'recovery_system_memory' => '0', 'thread_hash_memory' => '407576', 'innodb_sem_waits' => NULL, 'innodb_sem_wait_time_ms' => NULL), 'samples/innodb-002.txt'); is_deeply(get_innodb_array(file_get_contents('samples/innodb-006.txt')), array('spin_waits' => '31', 'spin_rounds' => '220', 'os_waits' => '17', 'innodb_transactions' => '3411', 'unpurged_txns' => '11', 'history_list' => '19', 'current_transactions' => '2', 'active_transactions' => '2', 'innodb_tables_in_use' => '1', 'innodb_locked_tables' => '1', 'locked_transactions' => 1, 'innodb_lock_structs' => '9', 'pending_normal_aio_reads' => '0', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'file_reads' => '42', 'file_writes' => '168', 'file_fsyncs' => '149', 'ibuf_inserts' => '0', 'ibuf_merged' => '0', 'ibuf_merges' => '0', 'log_bytes_written' => '103216', 'unflushed_log' => '0', 'log_bytes_flushed' => '103216', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'log_writes' => '72', 'pool_size' => '512', 'free_pages' => '476', 'database_pages' => '35', 'modified_pages' => '0', 'pages_read' => '33', 'pages_created' => '48', 'pages_written' => '148', 'queries_inside' => '0', 'queries_queued' => '0', 'read_views' => '2', 'rows_inserted' => '5', 'rows_updated' => '0', 'rows_deleted' => '0', 'rows_read' => '10', 'innodb_lock_wait_secs' => '32', 'hash_index_cells_total' => '17393', 'hash_index_cells_used' => '0', 'total_mem_alloc' => '20557306', 'additional_pool_alloc' => '744704', 'last_checkpoint' => '103216', 'uncheckpointed_bytes' => '0', 'ibuf_used_cells' => '1', 'ibuf_free_cells' => '0', 'ibuf_cell_count' => '2', 'adaptive_hash_memory' => NULL, 'page_hash_memory' => NULL, 'dictionary_cache_memory' => NULL, 'file_system_memory' => NULL, 'lock_system_memory' => NULL, 'recovery_system_memory' => NULL, 'thread_hash_memory' => NULL, 'innodb_sem_waits' => NULL, 'innodb_sem_wait_time_ms' => NULL), 'samples/innodb-006.txt'); is_deeply(get_innodb_array(file_get_contents('samples/innodb-009.txt')), array('spin_waits' => '820880', 'spin_rounds' => '3373874', 'os_waits' => '32601', 'pending_normal_aio_reads' => '0', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0', 'file_reads' => '1516536', 'file_writes' => '268607', 'file_fsyncs' => '27641', 'ibuf_used_cells' => '9909', 'ibuf_free_cells' => '36366', 'ibuf_cell_count' => '46276', 'ibuf_inserts' => '403495', 'ibuf_merged' => '391709', 'ibuf_merges' => '94372', 'hash_index_cells_total' => '24902177', 'hash_index_cells_used' => '12151667', 'log_bytes_written' => '541333186407', 'log_bytes_flushed' => '541333186407', 'last_checkpoint' => '541046660013', 'pending_log_writes' => '0', 'pending_chkp_writes' => '0', 'log_writes' => '19521', 'total_mem_alloc' => '13711864112', 'additional_pool_alloc' => '5884416', 'pool_size' => '768000', 'free_pages' => '60', 'database_pages' => '743356', 'modified_pages' => '177204', 'pages_read' => '1580077', 'pages_created' => '7462', 'pages_written' => '276034', 'queries_inside' => '3', 'queries_queued' => '0', 'rows_inserted' => '430539', 'rows_updated' => '251931', 'rows_deleted' => '257631', 'rows_read' => '83306576', 'innodb_transactions' => '4106483684', 'unpurged_txns' => '1293', 'current_transactions' => '23', 'active_transactions' => '21', 'unflushed_log' => '0', 'uncheckpointed_bytes' => '286526394', 'read_views' => NULL, 'history_list' => NULL, 'adaptive_hash_memory' => NULL, 'page_hash_memory' => NULL, 'dictionary_cache_memory' => NULL, 'file_system_memory' => NULL, 'lock_system_memory' => NULL, 'recovery_system_memory' => NULL, 'thread_hash_memory' => NULL, 'innodb_sem_waits' => NULL, 'innodb_sem_wait_time_ms' => NULL), 'samples/innodb-009.txt'); /* TODO: I am not sure anymore what this file is meant to test. Got pulled away * in the midst of working on it, now can't remember what I was doing. is_deeply( get_innodb_array(file_get_contents('samples/innodb-014.txt')), array( 'spin_waits' => '335', 'spin_rounds' => '1682', 'os_waits' => '210', 'pending_normal_aio_reads' => '0', 'pending_normal_aio_writes' => '0', 'pending_ibuf_aio_reads' => '0', 'pending_aio_log_ios' => '0', 'pending_aio_sync_ios' => '0', 'pending_log_flushes' => '0', 'pending_buf_pool_flushes' => '0',