mysqlnd-qc-php-set-user-handlers-9

  • Quickstart and
    Examples
  • Beyond TTL: user-defined storage

  • Beyond TTL: user-defined storage
  • Beyond TTL: user-defined storage

    Beyond TTL: user-defined storage

    The query cache plugin supports the use of
    user-defined storage handler. User-defined storage handler can use
    arbitrarily complex invalidation algorithms and support arbitrary
    storage media.

    All user-defined storage handlers have to provide a
    certain interface. The functions of the user-defined storage
    handler will be called by the core of the cache plugin. The
    necessary interface consists of seven public functions. Both
    procedural and object oriented user-defined storage handler must
    implement the same set of functions.

    Example #1 Using a user-defined storage
    handler

    <?php
    /* Enable default caching of all statements */
    ini_set("mysqlnd_qc.cache_by_default"1);

    /* Procedural user defined storage handler functions */

    $__cache = array();

    function get_hash($host_info$port$user$db$query) {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        return md5(sprintf("%s%s%s%s%s"$host_info$port$user$db$query));
    }

    function find_query_in_cache($key) {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        if (isset($__cache[$key])) {
            
    $tmp $__cache[$key];
            if (
    $tmp["valid_until"] < time()) {
                unset(
    $__cache[$key]);
                
    $ret NULL;
            } else {
                
    $ret $__cache[$key]["data"];
            }
        } else {
            
    $ret NULL;
        }

        return $ret;
    }

    function return_to_cache($key) {
        
    /*
         Called on cache hit after cached data has been processed,
         may be used for reference counting
        */
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());
    }

    function add_query_to_cache_if_not_exists($key$data$ttl$run_time$store_time$row_count) {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        $__cache[$key] = array(
            
    "data"               => $data,
            
    "row_count"          => $row_count,
            
    "valid_until"        => time() + $ttl,
            
    "hits"               => 0,
            
    "run_time"           => $run_time,
            
    "store_time"         => $store_time,
            
    "cached_run_times"   => array(),
            
    "cached_store_times" => array(),
        );

        return TRUE;
    }

    function query_is_select($query) {
        
    printf("\t%s('%s'): "__FUNCTION__$query);

        $ret FALSE;
        if (
    stristr($query"SELECT") !== FALSE) {
            
    /* cache for 5 seconds */
            
    $ret 5;
        }

        printf("%s\n", (FALSE === $ret) ? "FALSE" $ret);
        return 
    $ret;
    }

    function update_query_run_time_stats($key$run_time$store_time) {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        if (isset($__cache[$key])) {
            
    $__cache[$key]['hits']++;
            
    $__cache[$key]["cached_run_times"][] = $run_time;
            
    $__cache[$key]["cached_store_times"][] = $store_time;
        }
    }

    function get_stats($key NULL) {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        if ($key && isset($__cache[$key])) {
            
    $stats $__cache[$key];
        } else {
            
    $stats = array();
            foreach (
    $__cache as $key => $details) {
                
    $stats[$key] = array(
                   
    'hits'              => $details['hits'],
                   
    'bytes'             => strlen($details['data']),
                   
    'uncached_run_time' => $details['run_time'],
                   
    'cached_run_time'   => (count($details['cached_run_times']))
                                          ? 
    array_sum($details['cached_run_times']) / count($details['cached_run_times'])
                                          : 
    0,
                );
            }
        }

        return $stats;
    }

    function clear_cache() {
        global 
    $__cache;
        
    printf("\t%s(%d)\n"__FUNCTION__func_num_args());

        $__cache = array();
        return 
    TRUE;
    }

    /* Install procedural user-defined storage handler */
    if (!mysqlnd_qc_set_user_handlers("get_hash""find_query_in_cache",
        
    "return_to_cache""add_query_to_cache_if_not_exists",
        
    "query_is_select""update_query_run_time_stats""get_stats""clear_cache")) {
      
            
    printf("Failed to install user-defined storage handler\n");
    }

    /* 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)");

    printf("\nCache put/cache miss\n");

    $res $mysqli->query("SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();

    /* Delete record to verify we get our data from the cache */
    $mysqli->query("DELETE FROM test WHERE id = 1");

    printf("\nCache hit\n");

    $res $mysqli->query("SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();

    printf("\nDisplay cache statistics\n");
    var_dump(mysqlnd_qc_get_cache_info());

    printf("\nFlushing cache, cache put/cache miss");
    var_dump(mysqlnd_qc_clear_cache());

    $res $mysqli->query("SELECT id FROM test WHERE id = 1");
    var_dump($res->fetch_assoc());
    $res->free();
    ?>

    The above examples will output something similar to:

            query_is_select('DROP TABLE IF EXISTS test'): FALSE
            query_is_select('CREATE TABLE test(id INT)'): FALSE
            query_is_select('INSERT INTO test(id) VALUES (1), (2)'): FALSE
    
    Cache put/cache miss
            query_is_select('SELECT id FROM test WHERE id = 1'): 5
            get_hash(5)
            find_query_in_cache(1)
            add_query_to_cache_if_not_exists(6)
    array(1) {
      ["id"]=>
      string(1) "1"
    }
            query_is_select('DELETE FROM test WHERE id = 1'): FALSE
    
    Cache hit
            query_is_select('SELECT id FROM test WHERE id = 1'): 5
            get_hash(5)
            find_query_in_cache(1)
            return_to_cache(1)
            update_query_run_time_stats(3)
    array(1) {
      ["id"]=>
      string(1) "1"
    }
    
    Display cache statistics
            get_stats(0)
    array(4) {
      ["num_entries"]=>
      int(1)
      ["handler"]=>
      string(4) "user"
      ["handler_version"]=>
      string(5) "1.0.0"
      ["data"]=>
      array(1) {
        ["18683c177dc89bb352b29965d112fdaa"]=>
        array(4) {
          ["hits"]=>
          int(1)
          ["bytes"]=>
          int(71)
          ["uncached_run_time"]=>
          int(398)
          ["cached_run_time"]=>
          int(4)
        }
      }
    }
    
    Flushing cache, cache put/cache miss    clear_cache(0)
    bool(true)
            query_is_select('SELECT id FROM test WHERE id = 1'): 5
            get_hash(5)
            find_query_in_cache(1)
            add_query_to_cache_if_not_exists(6)
    NULL