<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title>Finding cache candidates</title> </head> <body><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.cache-efficiency.html">Measuring cache efficiency</a></div> <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div><hr /><div id="mysqlnd-qc.cache-candidates" class="section"> <h2 class="title">Finding cache candidates</h2> <p class="para"> 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 <span class="function"><a href="function.mysqlnd-qc-get-query-trace-log.html" class="function">mysqlnd_qc_get_query_trace_log()</a></span> returns a query log which help with the task. </p> <p class="para"> Collecting a query trace is a slow operation. Thus, it is disabled by default. The PHP configuration directive <a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.collect-query-trace" class="link"><em>mysqlnd_qc.collect_query_trace</em></a> is used to enable it. The functions trace contains one entry for every query issued before the function is called. </p> <p class="para"> <div class="example" id="example-1854"> <p><strong>Example #1 Collecting a query trace</strong></p> <div class="example-contents"> <div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_query_trace=1</pre> </div> </div> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br /></span><span style="color: #FF8000">/* connect to MySQL */<br /></span><span style="color: #0000BB">$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"schema"</span><span style="color: #007700">, </span><span style="color: #DD0000">"port"</span><span style="color: #007700">, </span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* dummy queries to fill the query trace */<br /></span><span style="color: #007700">for (</span><span style="color: #0000BB">$i </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">; </span><span style="color: #0000BB">$i </span><span style="color: #007700">< </span><span style="color: #0000BB">2</span><span style="color: #007700">; </span><span style="color: #0000BB">$i</span><span style="color: #007700">++) {<br /> </span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT 1 AS _one FROM DUAL"</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br />}<br /><br /></span><span style="color: #FF8000">/* dump trace */<br /></span><span style="color: #0000BB">var_dump</span><span style="color: #007700">(</span><span style="color: #0000BB">mysqlnd_qc_get_query_trace_log</span><span style="color: #007700">());<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above examples will output:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> 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) } } </pre></div> </div> </div> </p> <p class="para"> 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 <a href="mysqlnd-qc.configuration.html#ini.mysqlnd-qc.query-trace-bt-depth" class="link"><em>mysqlnd_qc.query_trace_bt_depth</em></a>. The default depth is <em>3</em>. </p> <p class="para"> <div class="example" id="example-1855"> <p><strong>Example #2 Setting the backtrace depth with the <em>mysqlnd_qc.query_trace_bt_depth</em> ini setting</strong></p> <div class="example-contents"> <div class="inicode"><pre class="inicode">mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_query_trace=1</pre> </div> </div> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br /></span><span style="color: #FF8000">/* connect to MySQL */<br /></span><span style="color: #0000BB">$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"host"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"schema"</span><span style="color: #007700">, </span><span style="color: #DD0000">"port"</span><span style="color: #007700">, </span><span style="color: #DD0000">"socket"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"DROP TABLE IF EXISTS test"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"CREATE TABLE test(id INT)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1), (2), (3)"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* dummy queries to fill the query trace */<br /></span><span style="color: #007700">for (</span><span style="color: #0000BB">$i </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">; </span><span style="color: #0000BB">$i </span><span style="color: #007700">< </span><span style="color: #0000BB">3</span><span style="color: #007700">; </span><span style="color: #0000BB">$i</span><span style="color: #007700">++) {<br /> </span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT id FROM test WHERE id = " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">real_escape_string</span><span style="color: #007700">(</span><span style="color: #0000BB">$i</span><span style="color: #007700">));<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">free</span><span style="color: #007700">();<br />}<br /><br /></span><span style="color: #0000BB">$trace </span><span style="color: #007700">= </span><span style="color: #0000BB">mysqlnd_qc_get_query_trace_log</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$summary </span><span style="color: #007700">= array();<br />foreach (</span><span style="color: #0000BB">$trace </span><span style="color: #007700">as </span><span style="color: #0000BB">$entry</span><span style="color: #007700">) {<br /> if (!isset(</span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]])) {<br /> </span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]] = array(<br /> </span><span style="color: #DD0000">"executions" </span><span style="color: #007700">=> </span><span style="color: #0000BB">1</span><span style="color: #007700">,<br /> </span><span style="color: #DD0000">"time" </span><span style="color: #007700">=> </span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'run_time'</span><span style="color: #007700">] + </span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'store_time'</span><span style="color: #007700">],<br /> );<br /> } else {<br /> </span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]][</span><span style="color: #DD0000">'executions'</span><span style="color: #007700">]++;<br /> </span><span style="color: #0000BB">$summary</span><span style="color: #007700">[</span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'query'</span><span style="color: #007700">]][</span><span style="color: #DD0000">'time'</span><span style="color: #007700">] += </span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'run_time'</span><span style="color: #007700">] + </span><span style="color: #0000BB">$entry</span><span style="color: #007700">[</span><span style="color: #DD0000">'store_time'</span><span style="color: #007700">];<br /> }<br />}<br /><br />foreach (</span><span style="color: #0000BB">$summary </span><span style="color: #007700">as </span><span style="color: #0000BB">$query </span><span style="color: #007700">=> </span><span style="color: #0000BB">$details</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"%45s: %5dms (%dx)\n"</span><span style="color: #007700">,<br /> </span><span style="color: #0000BB">$query</span><span style="color: #007700">, </span><span style="color: #0000BB">$details</span><span style="color: #007700">[</span><span style="color: #DD0000">'time'</span><span style="color: #007700">], </span><span style="color: #0000BB">$details</span><span style="color: #007700">[</span><span style="color: #DD0000">'executions'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents"><p>The above examples will output something similar to:</p></div> <div class="example-contents screen"> <div class="cdata"><pre> 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) </pre></div> </div> </div> </p> </div><hr /><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-qc.slam-defense.html">Slam defense</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-qc.cache-efficiency.html">Measuring cache efficiency</a></div> <div class="up"><a href="mysqlnd-qc.quickstart.html">Quickstart and Examples</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div></body></html>