Finding cache candidates

目录搜索

文字

A statement should be considered for caching if it is executed often and has a long run time. Cache candidates are found by creating a list of statements sorted by the product of the number of executions multiplied by the statements run time. The function mysqlnd_qc_get_query_trace_log() returns a query log which help with the task.

Collecting a query trace is a slow operation. Thus, it is disabled by default. The PHP configuration directive mysqlnd_qc.collect_query_trace is used to enable it. The functions trace contains one entry for every query issued before the function is called.

Example #1 Collecting a query trace

mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_query_trace=1

  <?php
 
 $mysqli  = new  mysqli ( "host" ,  "user" ,  "password" ,  "schema" ,  "port" ,  "socket" );

 
 for ( $i  =  0 ;  $i  <  2 ;  $i ++) {
     $res  =  $mysqli -> query ( "SELECT 1 AS _one FROM DUAL" );
     $res -> free ();
}

 
 var_dump ( mysqlnd_qc_get_query_trace_log ());
 ?>

以上例程会输出：

array(2) {
  [0]=>
  array(8) {
    ["query"]=>
    string(26) "SELECT 1 AS _one FROM DUAL"
    ["origin"]=>
    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
    ["run_time"]=>
    int(0)
    ["store_time"]=>
    int(25)
    ["eligible_for_caching"]=>
    bool(false)
    ["no_table"]=>
    bool(false)
    ["was_added"]=>
    bool(false)
    ["was_already_in_cache"]=>
    bool(false)
  }
  [1]=>
  array(8) {
    ["query"]=>
    string(26) "SELECT 1 AS _one FROM DUAL"
    ["origin"]=>
    string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
    ["run_time"]=>
    int(0)
    ["store_time"]=>
    int(8)
    ["eligible_for_caching"]=>
    bool(false)
    ["no_table"]=>
    bool(false)
    ["was_added"]=>
    bool(false)
    ["was_already_in_cache"]=>
    bool(false)
  }
}

Assorted information is given in the trace. Among them timings and the origin of the query call. The origin property holds a code backtrace to identify the source of the query. The depth of the backtrace can be limited with the PHP configuration directive mysqlnd_qc.query_trace_bt_depth . The default depth is 3.

Example #2 Setting the backtrace depth with the mysqlnd_qc.query_trace_bt_depth ini setting

mysqlnd_qc.enable_qc=1
mysqlnd_qc.collect_query_trace=1

  <?php
 
 $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)" );

 
 for ( $i  =  0 ;  $i  <  3 ;  $i ++) {
     $res  =  $mysqli -> query ( "SELECT id FROM test WHERE id = "  .  $mysqli -> real_escape_string ( $i ));
     $res -> free ();
}

 $trace  =  mysqlnd_qc_get_query_trace_log ();
 $summary  = array();
foreach ( $trace  as  $entry ) {
    if (!isset( $summary [ $entry [ 'query' ]])) {
         $summary [ $entry [ 'query' ]] = array(
             "executions"  =>  1 ,
             "time"        =>  $entry [ 'run_time' ] +  $entry [ 'store_time' ],
        );
    } else {
         $summary [ $entry [ 'query' ]][ 'executions' ]++;
         $summary [ $entry [ 'query' ]][ 'time' ] +=  $entry [ 'run_time' ] +  $entry [ 'store_time' ];
    }
}

foreach ( $summary  as  $query  =>  $details ) {
     printf ( "%45s: %5dms (%dx)\n" ,
     $query ,  $details [ 'time' ],  $details [ 'executions' ]);
}
 ?>

以上例程的输出类似于：

DROP TABLE IF EXISTS test:     0ms (1x)
                    CREATE TABLE test(id INT):     0ms (1x)
    INSERT INTO test(id) VALUES (1), (2), (3):     0ms (1x)
             SELECT id FROM test WHERE id = 0:    25ms (1x)
             SELECT id FROM test WHERE id = 1:    10ms (1x)
             SELECT id FROM test WHERE id = 2:     9ms (1x)