function perfmon_get_mysql_performance_variables in Performance monitor 7
Same name and namespace in other branches
- 8 perfmon.module \perfmon_get_mysql_performance_variables()
Describes mysql performance variables.
1 call to perfmon_get_mysql_performance_variables()
- perfmon_mysql_page in ./
perfmon.pages.inc - Mysql settings page.
File
- ./
perfmon.inc, line 396 - 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;
}