function perfmon_get_mysql_performance_variables in Performance monitor 8

Same name and namespace in other branches

1. 7 perfmon.inc \perfmon_get_mysql_performance_variables()

Describes mysql performance variables.

File

./perfmon.module, line 382

Stand-alone perfmon test system.

Code

function perfmon_get_mysql_performance_variables() {
  $variables = perfmon_get_mysqlvariables();
  $status = perfmon_get_mysqlstatus();
  $performance_variables = array();
  $uptime = $status['Uptime'];
  $performance_variables['uptime'] = array(
    'value' => $uptime,
    'title' => t('MySQL Uptime'),
    'display_value' => _perfmon_seconds_to_time($uptime),
    'message' => t('MySQL Server Uptime'),
    'class' => 'ok',
  );
  if ($uptime < 86400) {
    $performance_variables['uptime']['message'] = t('MySQL started within last 24 hours - recommendations may be inaccurate');
    $performance_variables['uptime']['class'] = 'error';
  }
  $memory_global = $variables['key_buffer_size'] + $variables['tmp_table_size'] + $variables['innodb_buffer_pool_size'] + $variables['query_cache_size'] + $variables['innodb_additional_mem_pool_size'] + $variables['innodb_log_buffer_size'];
  $performance_variables['memory_global'] = array(
    'value' => $memory_global,
    'title' => t('Global Memory'),
    'display_value' => _perfmon_format_bytes($memory_global),
    'message' => t('Global memory size (key_buffer_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size)'),
    'class' => 'ok',
  );
  $memory_per_connection = $variables['read_buffer_size'] + $variables['read_rnd_buffer_size'] + $variables['sort_buffer_size'] + $variables['join_buffer_size'];
  $performance_variables['memory_per_connection'] = array(
    'value' => $memory_per_connection,
    'title' => t('Memory per connection'),
    'display_value' => _perfmon_format_bytes($memory_per_connection),
    'message' => t('Memory per connection (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size)'),
    'class' => 'ok',
  );
  $performance_variables['max_connections'] = array(
    'value' => $variables['max_connections'],
    'title' => 'Max connections',
    'display_value' => $variables['max_connections'],
    'message' => t('Max user connections ( max_connections )'),
    'class' => 'ok',
  );
  $max_memory = $performance_variables['memory_global']['value'] + $performance_variables['max_connections']['value'] * $performance_variables['memory_per_connection']['value'];
  $performance_variables['max_memory'] = array(
    'value' => $max_memory,
    'title' => 'Max. Memory',
    'display_value' => _perfmon_format_bytes($max_memory),
    'message' => t('Max Mysql memory (Global memory + Memory per connection * Max connections). Check that this variable lower than 85% of server RAM.'),
    'class' => 'ok',
  );
  $performance_variables['query_cache_size'] = array(
    'value' => $variables['query_cache_size'],
    'title' => t('Query cache size'),
    'display_value' => _perfmon_format_bytes($variables['query_cache_size']),
    'message' => t('Query cache size ( query_cache_size ). Recommended 128M'),
    'class' => 'ok',
  );
  if ($variables['query_cache_size'] != 1024 * 1024 * 128) {
    $performance_variables['query_cache_size']['message'] = t('Query cache size query_cache_size. Recommended 128M. Increasing the query_cache size over 128M may reduce performance.');
    $performance_variables['query_cache_size']['class'] = 'error';
  }
  $query_cache_efficiency = $status['Qcache_hits'] / ($status['Com_select'] + $status['Qcache_hits']);
  $performance_variables['query_cache_efficiency'] = array(
    'value' => $query_cache_efficiency,
    'title' => t('Query cache efficiency'),
    'display_value' => round($query_cache_efficiency * 100, 2) . '%',
    'message' => t('Query cache efficiency'),
    'class' => 'ok',
  );
  if ($query_cache_efficiency < 0.2) {
    $performance_variables['query_cache_efficiency']['message'] = t('Query cache efficiency. This parameter is low try to increase query_cache_limit.');
    $performance_variables['query_cache_efficiency']['class'] = 'error';
  }
  $query_cache_prunes_per_day = $status['Qcache_lowmem_prunes'] / ($status['Uptime'] / 86400);
  $performance_variables['query_cache_prunes_per_day'] = array(
    'value' => $query_cache_prunes_per_day,
    'title' => t('Query cache prunes per day'),
    'display_value' => round($query_cache_prunes_per_day, 2),
    'message' => t('Query cache prunes per day. If value grows rapidly you need to increase query_cache_size.'),
    'class' => 'ok',
  );
  $total_sorts = $status['Sort_scan'] + $status['Sort_range'];
  $performance_variables['total_sorts'] = array(
    'value' => $total_sorts,
    'title' => t('Total sorts'),
    'display_value' => $total_sorts,
    'message' => t('Total sorts count.'),
    'class' => 'ok',
  );
  $total_sorts_temp = $status['Sort_merge_passes'] / $performance_variables['total_sorts']['value'];
  $performance_variables['total_sorts_temp'] = array(
    'value' => $total_sorts_temp,
    'title' => t('Total sorts using disk'),
    'display_value' => round($total_sorts_temp * 100, 2) . '%',
    'message' => t('Sorts requiring temporary tables. If value grater than 10% then you need to increase sort_buffer_size and read_rnd_buffer_size.'),
    'class' => 'ok',
  );
  if ($total_sorts_temp > 0.1) {
    $performance_variables['total_sorts_temp']['class'] = 'error';
  }
  $created_tmp_tables_dsk = $status['Created_tmp_disk_tables'] / $status['Created_tmp_tables'];
  $performance_variables['created_tmp_tables_dsk'] = array(
    'value' => $created_tmp_tables_dsk,
    'title' => t('Temporary tables created on disk'),
    'display_value' => round($created_tmp_tables_dsk * 100, 2) . '%',
    'message' => t('Temporary tables created on disk. If value grater than 30% then you need to increase tmp_table_size and tmp_table_size. When making adjustments, make tmp_table_size/max_heap_table_size equal.'),
    'class' => 'ok',
  );
  if ($created_tmp_tables_dsk > 0.3) {
    $performance_variables['created_tmp_tables_dsk']['class'] = 'error';
  }
  $open_files = $status['Open_files'] / $variables['open_files_limit'];
  $performance_variables['open_files'] = array(
    'value' => $open_files,
    'title' => t('Open files'),
    'display_value' => round($open_files * 100, 2) . '%',
    'message' => t('Open file limit used. If value grater than 85% then you need to increase open_files_limit.'),
    'class' => 'ok',
  );
  if ($open_files > 0.85) {
    $performance_variables['open_files']['class'] = 'error';
  }
  $innodb_buffer_efficiency = 1 - $status['Innodb_buffer_pool_reads'] / $status['Innodb_buffer_pool_read_requests'];
  $performance_variables['innodb_buffer_efficiency'] = array(
    'value' => $innodb_buffer_efficiency,
    'title' => t('Innodb buffer pool efficiency'),
    'display_value' => round($innodb_buffer_efficiency * 100, 2) . '%',
    'message' => t('InnoDB Buffer pool read cache effiency. If value lower than 95% then you need to increase innodb_buffer_pool_size.'),
    'class' => 'ok',
  );
  if ($innodb_buffer_efficiency < 0.95) {
    $performance_variables['innodb_buffer_efficiency']['class'] = 'error';
  }
  $performance_variables['innodb_flush_log_at_trx_commit'] = array(
    'value' => $variables['innodb_flush_log_at_trx_commit'],
    'title' => t('innodb_flush_log_at_trx_commit'),
    'display_value' => $variables['innodb_flush_log_at_trx_commit'],
    'message' => t('innodb_flush_log_at_trx_commit. Recommended value is 2.'),
    'class' => 'ok',
  );
  if ($variables['innodb_flush_log_at_trx_commit'] != 2) {
    $performance_variables['innodb_flush_log_at_trx_commit']['class'] = 'error';
  }
  return $performance_variables;
}