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)