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.
Beispiel #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());
?>
Oben gezeigte Beispiele erzeugen eine ähnliche Ausgabe wie:
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.
Beispiel #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());
?>
Oben gezeigte Beispiele erzeugen eine ähnliche Ausgabe wie:
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 = ?.
Beispiel #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());
?>
Oben gezeigte Beispiele erzeugen eine ähnliche Ausgabe wie:
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.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 30/01/2003, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/php-rf-mysqlnd-qc.cache-efficiency.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.
