Monitoring

Plugin activity can be monitored using the mysqlnd trace log, mysqlnd statistics, mysqlnd_ms plugin statistics and external PHP debugging tools. Use of the trace log should be limited to debugging. It is recommended to use the plugins statistics for monitoring.

Writing a trace log is a slow operation. If using an external PHP debugging tool, please refer to the vendors manual about its performance impact and the type of information collected. In many cases, external debugging tools will provide call stacks. Often, a call stack or a trace log is more difficult to interpret than the statistics provided by the plugin.

Plugin statistics tell how often which kind of cluster node has been used (slave or master), why the node was used, if lazy connections have been used and if global transaction ID injection has been performed. The monitoring information provided enables user to verify plugin decisions and to plan their cluster resources based on usage pattern. The function mysqlnd_ms_get_stats is used to access the statistics. Please, see the functions description for a list of available statistics.

Statistics are collected on a per PHP process basis. Their scope is a PHP process. Depending on the PHP deployment model a process may serve one or multiple web requests. If using CGI model, a PHP process serves one web request. If using FastCGI or pre-fork web server models, a PHP process usually serves multiple web requests. The same is the case with a threaded web server. Please, note that threads running in parallel can update the statistics in parallel. Thus, if using a threaded PHP deployment model, statistics can be changed by more than one script at a time. A script cannot rely on the fact that it sees only its own changes to statistics.

Example #1 Verify plugin activity in a non-threaded deployment model

mysqlnd_ms.enable=1
mysqlnd_ms.collect_statistics=1
<?php
/* Load balanced following "myapp" section rules from the plugins config file (not shown) */
$mysqli = new mysqli("myapp""username""password""database");
if (
mysqli_connect_errno())
  
/* Of course, your error handling is nicer... */
  
die(sprintf("[%d] %s\n"mysqli_connect_errno(), mysqli_connect_error()));

$stats_before mysqlnd_ms_get_stats();
if (
$res $mysqli->query("SELECT 'Read request' FROM DUAL")) {
  
var_dump($res->fetch_all());
}
$stats_after mysqlnd_ms_get_stats();
if (
$stats_after['use_slave'] <= $stats_before['use_slave']) {
  echo 
"According to the statistics the read request has not been run on a slave!";
}
?>

Statistics are aggregated for all plugin activities and all connections handled by the plugin. It is not possible to tell how much a certain connection handle has contributed to the overall statistics.

Utilizing PHPs register_shutdown_function function or the auto_append_file PHP configuration directive it is easily possible to dump statistics into, for example, a log file when a script finishes. Instead of using a log file it is also possible to send the statistics to an external monitoring tool for recording and display.

Example #2 Recording statistics during shutdown

mysqlnd_ms.enable=1
mysqlnd_ms.collect_statistics=1
error_log=/tmp/php_errors.log
<?php
function check_stats() {
  
$msg str_repeat("-"80) . "\n";
  
$msg .= var_export(mysqlnd_ms_get_stats(), true) . "\n";
  
$msg .= str_repeat("-"80) . "\n";
  
error_log($msg);
}
register_shutdown_function("check_stats");
?>