mysqlnd-qc-php-cache-efficiency-6

  • Quickstart and
    Examples
  • Measuring cache efficiency

  • Measuring cache efficiency
  • Measuring cache efficiency

    Measuring cache efficiency

    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 #1 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 #2 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 #3 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.