mysqlnd-qc-php-pattern-based-caching-1

  • Quickstart and
    Examples
  • Pattern based caching

  • Pattern based caching
  • Pattern based caching

    Pattern based caching

    An application has three options for telling
    PECL/mysqlnd_qc whether a particular statement shall be used. The
    most basic approach is to cache all statements by setting
    mysqlnd_qc.cache_by_default = 1. This approach is
    often of little practical value. But it enables users to make a
    quick estimation about the maximum performance gains from caching.
    An application designed to use a cache may be able to prefix
    selected statements with the appropriate SQL hints. However,
    altering an applications source code may not always be possible or
    desired, for example, to avoid problems with software updates.
    Therefore, PECL/mysqlnd_qc allows setting a callback which decides
    if a query is to be cached.

    The callback is installed with the mysqlnd_qc_set_is_select() function. The
    callback is given the statement string of every statement inspected
    by the plugin. Then, the callback can decide whether to cache the
    function. The callback is supposed to return
    FALSE if the statement shall not be
    cached. A return value of TRUE makes
    the plugin try to add the statement into the cache. The cache entry
    will be given the default TTL ( mysqlnd_qc.ttl). If the callback returns a
    numerical value it is used as the TTL instead of the global
    default.

    Example #1 Setting a callback with mysqlnd_qc_set_is_select()

    mysqlnd_qc.enable_qc=1
    mysqlnd_qc.collect_statistics=1
    
    <?php
    /* callback which decides if query is cached */
    function is_select($query) {
        static 
    $patterns = array(
          
    /* true - use default from mysqlnd_qc.ttl */
          
    "@SELECT\s+.*\s+FROM\s+test@ismU" => true,
          
    /* 3 - use TTL = 3 seconds */
          
    "@SELECT\s+.*\s+FROM\s+news@ismU" => 3
        
    );

        /* check if query does match pattern */
        
    foreach ($patterns as $pattern => $ttl) {
            if (
    preg_match($pattern$query)) {
                
    printf("is_select(%45s): cache\n"$query);
                return 
    $ttl;
            }
        }
        
    printf("is_select(%45s): do not cache\n"$query);
        return 
    false;
    }
    /* install callback */
    mysqlnd_qc_set_is_select("is_select");

    /* Connect, create and populate test table */
    $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)");

    /* cache put */
    $mysqli->query("SELECT id FROM test WHERE id = 1");
    /* cache hit */
    $mysqli->query("SELECT id FROM test WHERE id = 1");
    /* cache put */
    $mysqli->query("SELECT * FROM test");

    $stats mysqlnd_qc_get_core_stats();
    printf("Cache put: %d\n"$stats['cache_put']);
    printf("Cache hit: %d\n"$stats['cache_hit']);
    ?>

    The above examples will output something similar to:

    is_select(                    DROP TABLE IF EXISTS test): do not cache
    is_select(                    CREATE TABLE test(id INT)): do not cache
    is_select(    INSERT INTO test(id) VALUES (1), (2), (3)): do not cache
    is_select(             SELECT id FROM test WHERE id = 1): cache
    is_select(             SELECT id FROM test WHERE id = 1): cache
    is_select(                           SELECT * FROM test): cache
    Cache put: 2
    Cache hit: 1
    

    The examples callback tests if a statement string
    matches a pattern. If this is the case, it either returns
    TRUE to cache the statement using the
    global default TTL or an alternative TTL.

    To minimize application changes the callback can
    put into and registered in an auto prepend file.