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',
