Php/docs/mysqlnd-qc.cache-candidates

来自菜鸟教程
跳转至:导航、​搜索

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/* connect to MySQL */$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");/* dummy queries to fill the query trace */for ($i = 0; $i < 2; $i++) {    $res = $mysqli->query("SELECT 1 AS _one FROM DUAL");    $res->free();}/* dump trace */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/* 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 = 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)