IT. Expert System.

PHP

Finding 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());
?>

The above examples will output:

 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']);
}
?>

The above examples will output something similar to:

      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) 



Content

Android Reference

Java basics

Java Enterprise Edition (EE)

Java Standard Edition (SE)

SQL

HTML

PHP

CSS

Java Script

MYSQL

JQUERY

VBS

REGEX

C

C++

C#

Design patterns

RFC (standard status)

RFC (proposed standard status)

RFC (draft standard status)

RFC (informational status)

RFC (experimental status)

RFC (best current practice status)

RFC (historic status)

RFC (unknown status)

IT dictionary

License.
All information of this service is derived from the free sources and is provided solely in the form of quotations. This service provides information and interfaces solely for the familiarization (not ownership) and under the "as is" condition.
Copyright 2016 © ELTASK.COM. All rights reserved.
Site is optimized for mobile devices.
Downloads: 129 / 158702023. Delta: 0.05661 с