21.9.7.7. mysqlnd_qc Functions

<?phpvar_dump(mysqlnd_qc_get_available_handlers());?>   

The above examples will output:

array(5) {  ["default"]=>  array(2) {    ["version"]=>    string(5) "1.0.0"    ["version_number"]=>    int(100000)  }  ["user"]=>  array(2) {    ["version"]=>    string(5) "1.0.0"    ["version_number"]=>    int(100000)  }  ["APC"]=>  array(2) {    ["version"]=>    string(5) "1.0.0"    ["version_number"]=>    int(100000)  }  ["MEMCACHE"]=>  array(2) {    ["version"]=>    string(5) "1.0.0"    ["version_number"]=>    int(100000)  }  ["sqlite"]=>  array(2) {    ["version"]=>    string(5) "1.0.0"    ["version_number"]=>    int(100000)  }}

The example shows the output from the built-in default storage handler. Other storage handler may report different data.

<?php/* Populate the cache, e.g. using mysqli */$mysqli = new mysqli("host", "user", "password", "schema");$mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/SELECT id FROM test");/* Display cache information */var_dump(mysqlnd_qc_get_cache_info());?>   

The above examples will output:

array(4) { ["num_entries"]=> int(1) ["handler"]=> string(7) "default" ["handler_version"]=> string(5) "1.0.0" ["data"]=> array(1) {   ["Localhost via UNIX socket 3306 user schema|/*qc=on*/SELECT id FROM test"]=>   array(2) {     ["statistics"]=>     array(11) {       ["rows"]=>       int(6)       ["stored_size"]=>       int(101)       ["cache_hits"]=>       int(0)       ["run_time"]=>       int(471)       ["store_time"]=>       int(27)       ["min_run_time"]=>       int(0)       ["max_run_time"]=>       int(0)       ["min_store_time"]=>       int(0)       ["max_store_time"]=>       int(0)       ["avg_run_time"]=>       int(0)       ["avg_store_time"]=>       int(0)     }     ["metadata"]=>     array(1) {       [0]=>       array(8) {         ["name"]=>         string(2) "id"         ["orig_name"]=>         string(2) "id"         ["table"]=>         string(4) "test"         ["orig_table"]=>         string(4) "test"         ["db"]=>         string(4) "schema"         ["max_length"]=>         int(1)         ["length"]=>         int(11)         ["type"]=>         int(3)       }     }   } }}

<?php/* Enable collection of statistics - default: disabled */ini_set("mysqlnd_qc.collect_statistics", 1);/* Enable collection of all timing related statistics -default: enabled but overruled by mysqlnd_qc.collect_statistics = 0 */ini_set("mysqlnd_qc.collect_time_statistics", 1);/* Populate the cache, e.g. using mysqli */$mysqli = new mysqli('host', 'user', 'password', 'schema');/* Cache miss and cache put */$mysqli->query("/*qc=on*/SELECT id FROM test");/* Cache hit */$mysqli->query("/*qc=on*/SELECT id FROM test");/* Display core statistics */var_dump(mysqlnd_qc_get_core_stats());?>   

The above examples will output:

array(26) {  ["cache_hit"]=>  string(1) "1"  ["cache_miss"]=>  string(1) "1"  ["cache_put"]=>  string(1) "1"  ["query_should_cache"]=>  string(1) "2"  ["query_should_not_cache"]=>  string(1) "0"  ["query_not_cached"]=>  string(1) "0"  ["query_could_cache"]=>  string(1) "2"  ["query_found_in_cache"]=>  string(1) "1"  ["query_uncached_other"]=>  string(1) "0"  ["query_uncached_no_table"]=>  string(1) "0"  ["query_uncached_no_result"]=>  string(1) "0"  ["query_uncached_use_result"]=>  string(1) "0"  ["query_aggr_run_time_cache_hit"]=>  string(1) "4"  ["query_aggr_run_time_cache_put"]=>  string(3) "395"  ["query_aggr_run_time_total"]=>  string(3) "399"  ["query_aggr_store_time_cache_hit"]=>  string(1) "2"  ["query_aggr_store_time_cache_put"]=>  string(1) "8"  ["query_aggr_store_time_total"]=>  string(2) "10"  ["receive_bytes_recorded"]=>  string(2) "65"  ["receive_bytes_replayed"]=>  string(2) "65"  ["send_bytes_recorded"]=>  string(2) "29"  ["send_bytes_replayed"]=>  string(2) "29"  ["slam_stale_refresh"]=>  string(1) "0"  ["slam_stale_hit"]=>  string(1) "0"  ["request_counter"]=>  int(1)  ["process_hash"]=>  int(3547549858)}

mysqlnd_qc.collect_normalized_query_trace=1   

<?php/* 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)");/* not cached */$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();/* cache put */$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 2");var_dump($res->fetch_assoc());$res->free();/* cache hit */$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 2");var_dump($res->fetch_assoc());$res->free();var_dump(mysqlnd_qc_get_normalized_query_trace_log());?>   

The above examples will output:

array(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "2"}array(1) {  ["id"]=>  string(1) "2"}array(4) {  [0]=>  array(9) {    ["query"]=>    string(25) "DROP TABLE IF EXISTS test"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [1]=>  array(9) {    ["query"]=>    string(27) "CREATE TABLE test (id INT )"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [2]=>  array(9) {    ["query"]=>    string(40) "INSERT INTO test (id ) VALUES (? ), (? )"    ["occurences"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["avg_run_time"]=>    int(0)    ["min_run_time"]=>    int(0)    ["max_run_time"]=>    int(0)    ["avg_store_time"]=>    int(0)    ["min_store_time"]=>    int(0)    ["max_store_time"]=>    int(0)  }  [3]=>  array(9) {    ["query"]=>    string(31) "SELECT id FROM test WHERE id =?"    ["occurences"]=>    int(2)    ["eligible_for_caching"]=>    bool(true)    ["avg_run_time"]=>    int(159)    ["min_run_time"]=>    int(12)    ["max_run_time"]=>    int(307)    ["avg_store_time"]=>    int(10)    ["min_store_time"]=>    int(8)    ["max_store_time"]=>    int(13)  }}

mysqlnd_qc.collect_query_trace=1   

<?php/* 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)");/* not cached */$res = $mysqli->query("SELECT id FROM test WHERE id = 1");var_dump($res->fetch_assoc());$res->free();/* cache put */$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 2");var_dump($res->fetch_assoc());$res->free();/* cache hit */$res = $mysqli->query("/*" . MYSQLND_QC_ENABLE_SWITCH . "*/" . "SELECT id FROM test WHERE id = 2");var_dump($res->fetch_assoc());$res->free();var_dump(mysqlnd_qc_get_query_trace_log());?>   

The above examples will output:

array(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "2"}array(1) {  ["id"]=>  string(1) "2"}array(6) {  [0]=>  array(8) {    ["query"]=>    string(25) "DROP TABLE IF EXISTS test"    ["origin"]=>    string(102) "#0 qc.php(4): mysqli->query('DROP TABLE IF E...')#1 {main}"    ["run_time"]=>    int(0)    ["store_time"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(false)  }  [1]=>  array(8) {    ["query"]=>    string(25) "CREATE TABLE test(id INT)"    ["origin"]=>    string(102) "#0 qc.php(5): mysqli->query('CREATE TABLE te...')#1 {main}"    ["run_time"]=>    int(0)    ["store_time"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(false)  }  [2]=>  array(8) {    ["query"]=>    string(36) "INSERT INTO test(id) VALUES (1), (2)"    ["origin"]=>    string(102) "#0 qc.php(6): mysqli->query('INSERT INTO tes...')#1 {main}"    ["run_time"]=>    int(0)    ["store_time"]=>    int(0)    ["eligible_for_caching"]=>    bool(false)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(false)  }  [3]=>  array(8) {    ["query"]=>    string(32) "SELECT id FROM test WHERE id = 1"    ["origin"]=>    string(102) "#0 qc.php(9): mysqli->query('SELECT id FROM ...')#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)  }  [4]=>  array(8) {    ["query"]=>    string(41) "/*qc=on*/SELECT id FROM test WHERE id = 2"    ["origin"]=>    string(103) "#0 qc.php(14): mysqli->query('/*qc=on*/SELECT...')#1 {main}"    ["run_time"]=>    int(311)    ["store_time"]=>    int(13)    ["eligible_for_caching"]=>    bool(true)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(true)    ["was_already_in_cache"]=>    bool(false)  }  [5]=>  array(8) {    ["query"]=>    string(41) "/*qc=on*/SELECT id FROM test WHERE id = 2"    ["origin"]=>    string(103) "#0 qc.php(19): mysqli->query('/*qc=on*/SELECT...')#1 {main}"    ["run_time"]=>    int(13)    ["store_time"]=>    int(8)    ["eligible_for_caching"]=>    bool(true)    ["no_table"]=>    bool(false)    ["was_added"]=>    bool(false)    ["was_already_in_cache"]=>    bool(true)  }}

<?php/* Cache all accesses to tables with the name "new%" in schema/database "db_example" for 1 second */if (!mysqlnd_qc_set_cache_condition(MYSQLND_QC_CONDITION_META_SCHEMA_PATTERN, "db_example.new%", 1)) {  die("Failed to set cache condition!");}$mysqli = new mysqli("host", "user", "password", "db_example", "port");/* cached although no SQL hint given  */$mysqli->query("SELECT id, title FROM news");$pdo_mysql = new PDO("mysql:host=host;dbname=db_example;port=port", "user", "password");/* not cached: no SQL hint, no pattern match */$pdo_mysql->query("SELECT id, title FROM latest_news");/* cached: TTL 1 second, pattern match */$pdo_mysql->query("SELECT id, title FROM news");?>

<?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;}mysqlnd_qc_set_is_select("is_select");/* Connect, create and populate test table */$mysqli = new mysqli("host", "user", "password", "schema");$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");?>   

The above examples will output:

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

The example shows the output from the built-in default storage handler. Other storage handler may report different data.

<?phpvar_dump(mysqlnd_qc_set_storage_handler("memcache"));if (true === mysqlnd_qc_set_storage_handler("default"))  printf("Default storage handler activated");/* Catchable fatal error */var_dump(mysqlnd_qc_set_storage_handler("unknown"));?>   

The above examples will output:

bool(true)Default storage handler activatedCatchable fatal error: mysqlnd_qc_set_storage_handler(): Unknown handler 'unknown' in (file) on line (line)