mysqlnd-qc-php-cache-candidates-2

  • Quickstart and
    Examples
  • Finding cache candidates

  • Finding cache candidates
  • 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)