8.8.4.8 Measuring cache efficiency

Copyright 1997-2018 the PHP Documentation Group.

PECL/mysqlnd_qc offers three ways to measure the cache efficiency. The function mysqlnd_qc_get_normalized_query_trace_log returns statistics aggregated by the normalized query string, mysqlnd_qc_get_cache_info gives storage handler specific information which includes a list of all cached items, depending on the storage handler. Additionally, the core of PECL/mysqlnd_qc collects high-level summary statistics aggregated per PHP process. The high-level statistics are returned by mysqlnd_qc_get_core_stats.

The functions mysqlnd_qc_get_normalized_query_trace_log and mysqlnd_qc_get_core_stats will not collect data unless data collection has been enabled through their corresponding PHP configuration directives. Data collection is disabled by default for performance considerations. It is configurable with the mysqlnd_qc.time_statistics option, which determines if timing information should be collected. Collection of time statistics is enabled by default but only performed if data collection as such has been enabled. Recording time statistics causes extra system calls. In most cases, the benefit of the monitoring outweighs any potential performance penalty of the additional system calls.

Example 8.320 Collecting statistics data with the mysqlnd_qc.time_statistics ini setting

mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_statistics=1

<?php
/* connect to MySQL */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
/* dummy queries */
for ($i = 1; $i <= 4; $i++) {
    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
    $res   = $mysqli->query($query);
    
    $res->free();
}
var_dump(mysqlnd_qc_get_core_stats());
?>

The above examples will output something similar to:

array(26) {
  ["cache_hit"]=>
  string(1) "2"
  ["cache_miss"]=>
  string(1) "2"
  ["cache_put"]=>
  string(1) "2"
  ["query_should_cache"]=>
  string(1) "4"
  ["query_should_not_cache"]=>
  string(1) "3"
  ["query_not_cached"]=>
  string(1) "3"
  ["query_could_cache"]=>
  string(1) "4"
  ["query_found_in_cache"]=>
  string(1) "2"
  ["query_uncached_other"]=>
  string(1) "0"
  ["query_uncached_no_table"]=>
  string(1) "0"
  ["query_uncached_no_result"]=>
  string(1) "0"
  ["query_uncached_use_result"]=>
  string(1) "0"
  ["query_aggr_run_time_cache_hit"]=>
  string(2) "28"
  ["query_aggr_run_time_cache_put"]=>
  string(3) "900"
  ["query_aggr_run_time_total"]=>
  string(3) "928"
  ["query_aggr_store_time_cache_hit"]=>
  string(2) "14"
  ["query_aggr_store_time_cache_put"]=>
  string(2) "40"
  ["query_aggr_store_time_total"]=>
  string(2) "54"
  ["receive_bytes_recorded"]=>
  string(3) "136"
  ["receive_bytes_replayed"]=>
  string(3) "136"
  ["send_bytes_recorded"]=>
  string(2) "84"
  ["send_bytes_replayed"]=>
  string(2) "84"
  ["slam_stale_refresh"]=>
  string(1) "0"
  ["slam_stale_hit"]=>
  string(1) "0"
  ["request_counter"]=>
  int(1)
  ["process_hash"]=>
  int(1929695233)
}

For a quick overview, call mysqlnd_qc_get_core_stats. It delivers cache usage, cache timing and traffic related statistics. Values are aggregated on a per process basis for all queries issued by any PHP MySQL API call.

Some storage handler, such as the default handler, can report cache entries, statistics related to the entries and meta data for the underlying query through the mysqlnd_qc_get_cache_info function. Please note, that the information returned depends on the storage handler. Values are aggregated on a per process basis.

Example 8.321 Example mysqlnd_qc_get_cache_info usage

mysqlnd_qc.enable_qc=1

<?php
/* connect to MySQL */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
/* dummy queries to fill the query trace */
for ($i = 1; $i <= 4; $i++) {
    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
    $res   = $mysqli->query($query);
  
    $res->free();
}
var_dump(mysqlnd_qc_get_cache_info());
?>

The above examples will output something similar to:

array(4) {
  ["num_entries"]=>
  int(2)
  ["handler"]=>
  string(7) "default"
  ["handler_version"]=>
  string(5) "1.0.0"
  ["data"]=>
  array(2) {
    ["Localhost via UNIX socket
3306
root
test|/*qc=on*/SELECT id FROM test WHERE id = 1"]=>
    array(2) {
      ["statistics"]=>
      array(11) {
        ["rows"]=>
        int(1)
        ["stored_size"]=>
        int(71)
        ["cache_hits"]=>
        int(1)
        ["run_time"]=>
        int(391)
        ["store_time"]=>
        int(27)
        ["min_run_time"]=>
        int(16)
        ["max_run_time"]=>
        int(16)
        ["min_store_time"]=>
        int(8)
        ["max_store_time"]=>
        int(8)
        ["avg_run_time"]=>
        int(8)
        ["avg_store_time"]=>
        int(4)
      }
      ["metadata"]=>
      array(1) {
        [0]=>
        array(8) {
          ["name"]=>
          string(2) "id"
          ["orig_name"]=>
          string(2) "id"
          ["table"]=>
          string(4) "test"
          ["orig_table"]=>
          string(4) "test"
          ["db"]=>
          string(4) "test"
          ["max_length"]=>
          int(1)
          ["length"]=>
          int(11)
          ["type"]=>
          int(3)
        }
      }
    }
    ["Localhost via UNIX socket
3306
root
test|/*qc=on*/SELECT id FROM test WHERE id = 0"]=>
    array(2) {
      ["statistics"]=>
      array(11) {
        ["rows"]=>
        int(0)
        ["stored_size"]=>
        int(65)
        ["cache_hits"]=>
        int(1)
        ["run_time"]=>
        int(299)
        ["store_time"]=>
        int(13)
        ["min_run_time"]=>
        int(11)
        ["max_run_time"]=>
        int(11)
        ["min_store_time"]=>
        int(6)
        ["max_store_time"]=>
        int(6)
        ["avg_run_time"]=>
        int(5)
        ["avg_store_time"]=>
        int(3)
      }
      ["metadata"]=>
      array(1) {
        [0]=>
        array(8) {
          ["name"]=>
          string(2) "id"
          ["orig_name"]=>
          string(2) "id"
          ["table"]=>
          string(4) "test"
          ["orig_table"]=>
          string(4) "test"
          ["db"]=>
          string(4) "test"
          ["max_length"]=>
          int(0)
          ["length"]=>
          int(11)
          ["type"]=>
          int(3)
        }
      }
    }
  }
}

It is possible to further break down the granularity of statistics to the level of the normalized statement string. The normalized statement string is the statements string with all parameters replaced with question marks. For example, the two statements SELECT id FROM test WHERE id = 0 and SELECT id FROM test WHERE id = 1 are normalized into SELECT id FROM test WHERE id = ?. Their both statistics are aggregated into one entry for SELECT id FROM test WHERE id = ?.

Example 8.322 Example mysqlnd_qc_get_normalized_query_trace_log usage

mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_normalized_query_trace=1

<?php
/* connect to MySQL */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
/* dummy queries to fill the query trace */
for ($i = 1; $i <= 4; $i++) {
    $query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
    $res   = $mysqli->query($query);
  
    $res->free();
}
var_dump(mysqlnd_qc_get_normalized_query_trace_log());
?>

The above examples will output something similar to:

array(4) {
  [0]=>
  array(9) {
    ["query"]=>
    string(25) "DROP TABLE IF EXISTS test"
    ["occurences"]=>
    int(0)
    ["eligible_for_caching"]=>
    bool(false)
    ["avg_run_time"]=>
    int(0)
    ["min_run_time"]=>
    int(0)
    ["max_run_time"]=>
    int(0)
    ["avg_store_time"]=>
    int(0)
    ["min_store_time"]=>
    int(0)
    ["max_store_time"]=>
    int(0)
  }
  [1]=>
  array(9) {
    ["query"]=>
    string(27) "CREATE TABLE test (id INT )"
    ["occurences"]=>
    int(0)
    ["eligible_for_caching"]=>
    bool(false)
    ["avg_run_time"]=>
    int(0)
    ["min_run_time"]=>
    int(0)
    ["max_run_time"]=>
    int(0)
    ["avg_store_time"]=>
    int(0)
    ["min_store_time"]=>
    int(0)
    ["max_store_time"]=>
    int(0)
  }
  [2]=>
  array(9) {
    ["query"]=>
    string(46) "INSERT INTO test (id ) VALUES (? ), (? ), (? )"
    ["occurences"]=>
    int(0)
    ["eligible_for_caching"]=>
    bool(false)
    ["avg_run_time"]=>
    int(0)
    ["min_run_time"]=>
    int(0)
    ["max_run_time"]=>
    int(0)
    ["avg_store_time"]=>
    int(0)
    ["min_store_time"]=>
    int(0)
    ["max_store_time"]=>
    int(0)
  }
  [3]=>
  array(9) {
    ["query"]=>
    string(31) "SELECT id FROM test WHERE id =?"
    ["occurences"]=>
    int(4)
    ["eligible_for_caching"]=>
    bool(true)
    ["avg_run_time"]=>
    int(179)
    ["min_run_time"]=>
    int(11)
    ["max_run_time"]=>
    int(393)
    ["avg_store_time"]=>
    int(12)
    ["min_store_time"]=>
    int(7)
    ["max_store_time"]=>
    int(25)
  }
}

The source distribution of PECL/mysqlnd_qc contains a directory web/ in which web based monitoring scripts can be found which give an example how to write a cache monitor. Please, follow the instructions given in the source.

Since PECL/mysqlnd_qc 1.1.0 it is possible to write statistics into a log file. Please, see mysqlnd_qc.collect_statistics_log_file.