Measuring cache efficiency
PECL/mysqlnd_qc offers three ways to measure the cache efficiency. The function mysqlnd_qc_get_normalized_query_trace_log() returns statistics aggregated by the normalized query string, mysqlnd_qc_get_cache_info() gives storage handler specific information which includes a list of all cached items, depending on the storage handler. Additionally, the core of PECL/mysqlnd_qc collects high-level summary statistics aggregated per PHP process. The high-level statistics are returned by mysqlnd_qc_get_core_stats().
The functions mysqlnd_qc_get_normalized_query_trace_log() and mysqlnd_qc_get_core_stats() will not collect data unless data collection has been enabled through their corresponding PHP configuration directives. Data collection is disabled by default for performance considerations. It is configurable with the mysqlnd_qc.time_statistics option, which determines if timing information should be collected. Collection of time statistics is enabled by default but only performed if data collection as such has been enabled. Recording time statistics causes extra system calls. In most cases, the benefit of the monitoring outweighs any potential performance penalty of the additional system calls.
Example #1 Collecting statistics data with the mysqlnd_qc.time_statistics ini setting
mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_statistics=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 */
for ($i = 1; $i <= 4; $i++) {
$query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
$res = $mysqli->query($query);
$res->free();
}
var_dump(mysqlnd_qc_get_core_stats());
?>
Exemplele de mai sus vor afișa ceva similar cu:
array(26) {
["cache_hit"]=>
string(1) "2"
["cache_miss"]=>
string(1) "2"
["cache_put"]=>
string(1) "2"
["query_should_cache"]=>
string(1) "4"
["query_should_not_cache"]=>
string(1) "3"
["query_not_cached"]=>
string(1) "3"
["query_could_cache"]=>
string(1) "4"
["query_found_in_cache"]=>
string(1) "2"
["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(2) "28"
["query_aggr_run_time_cache_put"]=>
string(3) "900"
["query_aggr_run_time_total"]=>
string(3) "928"
["query_aggr_store_time_cache_hit"]=>
string(2) "14"
["query_aggr_store_time_cache_put"]=>
string(2) "40"
["query_aggr_store_time_total"]=>
string(2) "54"
["receive_bytes_recorded"]=>
string(3) "136"
["receive_bytes_replayed"]=>
string(3) "136"
["send_bytes_recorded"]=>
string(2) "84"
["send_bytes_replayed"]=>
string(2) "84"
["slam_stale_refresh"]=>
string(1) "0"
["slam_stale_hit"]=>
string(1) "0"
["request_counter"]=>
int(1)
["process_hash"]=>
int(1929695233)
}
For a quick overview, call mysqlnd_qc_get_core_stats(). It delivers cache usage, cache timing and traffic related statistics. Values are aggregated on a per process basis for all queries issued by any PHP MySQL API call.
Some storage handler, such as the default handler, can report cache entries, statistics related to the entries and meta data for the underlying query through the mysqlnd_qc_get_cache_info() function. Please note, that the information returned depends on the storage handler. Values are aggregated on a per process basis.
Example #2 Example mysqlnd_qc_get_cache_info() usage
mysqlnd_qc.enable_qc=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 = 1; $i <= 4; $i++) {
$query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
$res = $mysqli->query($query);
$res->free();
}
var_dump(mysqlnd_qc_get_cache_info());
?>
Exemplele de mai sus vor afișa ceva similar cu:
array(4) {
["num_entries"]=>
int(2)
["handler"]=>
string(7) "default"
["handler_version"]=>
string(5) "1.0.0"
["data"]=>
array(2) {
["Localhost via UNIX socket
3306
root
test|/*qc=on*/SELECT id FROM test WHERE id = 1"]=>
array(2) {
["statistics"]=>
array(11) {
["rows"]=>
int(1)
["stored_size"]=>
int(71)
["cache_hits"]=>
int(1)
["run_time"]=>
int(391)
["store_time"]=>
int(27)
["min_run_time"]=>
int(16)
["max_run_time"]=>
int(16)
["min_store_time"]=>
int(8)
["max_store_time"]=>
int(8)
["avg_run_time"]=>
int(8)
["avg_store_time"]=>
int(4)
}
["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) "test"
["max_length"]=>
int(1)
["length"]=>
int(11)
["type"]=>
int(3)
}
}
}
["Localhost via UNIX socket
3306
root
test|/*qc=on*/SELECT id FROM test WHERE id = 0"]=>
array(2) {
["statistics"]=>
array(11) {
["rows"]=>
int(0)
["stored_size"]=>
int(65)
["cache_hits"]=>
int(1)
["run_time"]=>
int(299)
["store_time"]=>
int(13)
["min_run_time"]=>
int(11)
["max_run_time"]=>
int(11)
["min_store_time"]=>
int(6)
["max_store_time"]=>
int(6)
["avg_run_time"]=>
int(5)
["avg_store_time"]=>
int(3)
}
["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) "test"
["max_length"]=>
int(0)
["length"]=>
int(11)
["type"]=>
int(3)
}
}
}
}
}
It is possible to further break down the granularity of statistics
to the level of the normalized statement string.
The normalized statement string is the statements string with all parameters
replaced with question marks. For example, the two statements
SELECT id FROM test WHERE id = 0 and
SELECT id FROM test WHERE id = 1 are normalized into
SELECT id FROM test WHERE id = ?. Their both
statistics are aggregated into one entry for
SELECT id FROM test WHERE id = ?.
Example #3 Example mysqlnd_qc_get_normalized_query_trace_log() usage
mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_normalized_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 = 1; $i <= 4; $i++) {
$query = sprintf("/*%s*/SELECT id FROM test WHERE id = %d", MYSQLND_QC_ENABLE_SWITCH, $i % 2);
$res = $mysqli->query($query);
$res->free();
}
var_dump(mysqlnd_qc_get_normalized_query_trace_log());
?>
Exemplele de mai sus vor afișa ceva similar cu:
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(46) "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(4)
["eligible_for_caching"]=>
bool(true)
["avg_run_time"]=>
int(179)
["min_run_time"]=>
int(11)
["max_run_time"]=>
int(393)
["avg_store_time"]=>
int(12)
["min_store_time"]=>
int(7)
["max_store_time"]=>
int(25)
}
}
The source distribution of PECL/mysqlnd_qc contains a directory
web/ in which web based monitoring
scripts can be found which give an example how to write a cache monitor.
Please, follow the instructions given in the source.
Since PECL/mysqlnd_qc 1.1.0 it is possible to write statistics into a
log file. Please, see
mysqlnd_qc.collect_statistics_log_file.
add a note
