PHP - Manual: Finding cache candidates

2024-12-21

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)

add a note

User Contributed Notes

There are no user contributed notes for this page.

官方地址：https://www.php.net/manual/en/mysqlnd-qc.cache-candidates.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络数据库 JAVA 面试 NoSQL C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 PHP 8.1 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 ADORecordSet对象 adodb手册 opcache预加载 Composer的Packagist资源 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet mysql面试题 PHP json解析（json_decode）页面工具 php中几种注释规范 [鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式 [原创]诡异的php-fpm超时问题纯php实现DES以及TripleDES加密算法 PHP5 扩展SOAP 调用 webservice 两千行PHP学习笔记

略微加速

PHP官方手册 - 互联网笔记

Finding cache candidates

User Contributed Notes