☷
Startseite » Forum » Programmierer » Sprachen » PHP » Seite 3

» Benchmark: mysql, mysqli - Buffer, Cache und Prepared Stmt

Benchmark: Prepared Statements in ArrayNeuen Thread eröffnenNeue Antwort erstellenproblem mit imagesetpixel
AutorNachricht
mgutt
Administrator 

Name: Marc
Geschlecht:
Anmeldedatum: 28.08.2004
Beiträge: 52421
Wohnort: Lohmar


Meine eBay-Auktionen:
09.10.2009, 22:42
zitieren
Hallo,

ich habe mir mal die Mühe gemacht und die verschiedensten Varianten, wie man eine Abfrage gestalten könnte, in einen Benchmark gepackt und ausgewertet.

Hier erstmal die umfangreichen Logs:
Zitat/*
// vars
$id = 1111;
$email = 'info@example.org';
$note = "\x00sadipscing\x00 \x1aelitr\x1a, sed diam nonumy. \nnamet\n, \rconsetetur\r Lorem 'dolor' ipsum \sit\\";
// db
$db = mysql_connect('localhost', 'user', 'pw') OR exit();
mysql_select_db('db');
$db = new mysqli('localhost', 'user', 'pw', 'db') OR exit();
// with cache:
// SQL_CACHE
// without cache:
// SQL_NO_CACHE
// with buffer:
// mysql: mysql_query
// mysqli: MYSQLI_STORE_RESULT
// mysqli ps: use $stmt->store_result()
// without buffer:
// mysql: mysql_unbuffered_query
// mysqli: MYSQLI_USE_RESULT
// mysqli ps: don't use $stmt->store_result()

######### SQL_CACHE #########

// mysql select with $query, sprintf and SQL_CACHE
// run: 0.202927241327
// run: 0.20274058342
// fastest run: 0.17111992836
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf and SQL_CACHE
// run: 0.202936003208
// run: 0.202630057341
// fastest run: 0.198309898376
if (!($result = mysql_query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
)))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with $query and SQL_CACHE
// run: 0.203236801625
// run: 0.203072795869
// fastest run: 0.194211006165
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . mysql_real_escape_string($email) . "'
AND note = '" . mysql_real_escape_string($note) . "'
LIMIT 100";
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with SQL_CACHE
// run: 0.203366568087
// run: 0.203140323161
// fastest run: 0.200508832932
if (!($result = mysql_query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . mysql_real_escape_string($email) . "'
AND note = '" . mysql_real_escape_string($note) . "'
LIMIT 100"
))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with $query, sprintf, mysql_unbuffered_query and SQL_CACHE
// run: 0.169497578144
// run: 0.168652458192
// fastest run: 0.166500806808
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_unbuffered_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf, mysql_unbuffered_query and SQL_CACHE
// run: 0.169313673974
// run: 0.168320553303
// fastest run: 0.165802001953
if (!($result = mysql_unbuffered_query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
)))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysqli select with $query, sprintf, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203386082652
// run: 0.203128581048
// fastest run: 0.197916984558
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.204141454701
// run: 0.203148534297
// fastest run: 0.201350927353
if (!($result = $db->query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
)))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203663911817
// run: 0.205211720468
// fastest run: 0.19709610939
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100";
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203260421758
// run: 0.203011736868
// fastest run: 0.19740986824
if (!($result = $db->query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100"
))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, sprintf, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.169879922866
// run: 0.171357295512
// fastest run: 0.166729927063
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.169101116657
// run: 0.169385228156
// fastest run: 0.165516138077
if (!($result = $db->query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
), MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.172444839478
// run: 0.171396617889
// fastest run: 0.156838178635
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100";
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.173195414545
// run: 0.172171146867
// fastest run: 0.167582035065
if (!($result = $db->query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100"
, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with prepared statement, dynamic fields and SQL_CACHE
// run: 0.313724272253
// run: 0.313278152942
// fastest run: 0.301989078522
if ($stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$row[] = &$fields[$field->name];
}
$meta->close();
call_user_func_array(array($stmt, 'bind_result'), $fields);
$i = 0;
while ($stmt->fetch()) {
foreach ($fields as $k => $v) {
$data[$i][$k] = $v;
echo('');
}
$i++;
}
$stmt->close();
}

// mysqli select with prepared statement, fixed fields and SQL_CACHE
// run: 0.278509860038
// run: 0.274894304277
// fastest run: 0.259500980377
if ($stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');
$stmt->close();
}

// mysqli select with prepared statement outside of the loop, fix fields and SQL_CACHE
// run: 0.208601517682
// run: 0.20873098612
// fastest run: 0.200495004654
$stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
);
//loop begins here to be able to change WHERE parms
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');

// mysqli select with prepared statement outside of the loop, fix fields, store_result and SQL_CACHE
// run: 0.242778592114
// fastest run: 0.23714017868

// mysqli select with prepared statement, SQL_CACHE and store_result
// will be slower than with fix fields

######### SQL_NO_CACHE #########

// mysql select with $query, sprintf and SQL_NO_CACHE
// run: 0.250889410975
// fastest run: 0.249036073685
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf and SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysql select with $query and SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysql select with SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysqli select with $query, sprintf, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// run: 0.251598188879
// fastest run: 0.246910095215
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, sprintf, SQL_NO_CACHE and MYSQLI_USE_RESULT
// run: 0.214941697124
// fastest run: 0.211448907852
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with prepared statement, dynamic fields and SQL_NO_CACHE
// run: 0.313881859775
// fastest run: 0.29983997345
if ($stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$row[] = &$fields[$field->name];
}
$meta->close();
call_user_func_array(array($stmt, 'bind_result'), $fields);
$i = 0;
while ($stmt->fetch()) {
foreach ($fields as $k => $v) {
$data[$i][$k] = $v;
echo('');
}
$i++;
}
$stmt->close();
}

// mysqli select with prepared statement, fixed fields and SQL_NO_CACHE
// run: 0.275153286459
// fastest run: 0.256268024445
if ($stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');
$stmt->close();
}

// mysqli select with prepared statement outside of the loop, fix fields and SQL_NO_CACHE
// run: 0.208914251327
// run: 0.208983807565
// fastest run: 0.198924064636
$stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
);
//loop begins here to be able to change WHERE parms
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');

// mysqli select with prepared statement outside of the loop, fix fields, store_result and SQL_NO_CACHE
// run: 0.242572655678
// fastest run: 0.233605146408

// mysqli select with prepared statement, SQL_NO_CACHE and store_result
// will be slower than with fix fields
*/


Gefällt mirTeilentwittern 1x  bearbeitet
Verfasst am: 09.10.2009, 22:59
zitieren
Daten:
PHP Version 5.2.9-1nmm1
MySQL Version 5.0.51a
AMD Phenom QuadCore
8 GB Arbeitsspeicher
750 GB Festplatte SATA
Hardware-RAID 1

Die DB wurde einmal pro Seitenimpression konnektiert, die Abfrage 50 mal durch eine Schleife gejagt und das ganze 100x wiederholt. Die Zeit ist dann der Schnitt aus allen 100 Versuchen.

Fazit:
Die Vorteile von Prepared Statements konnte ich in diesem Fall nicht ermitteln. Im Gegenteil, selbst wenn man sie außerhalb des Loops vorbereitete und im Loop dann nur noch den WHERE-Teil und die Ausführung durchläuft, ändert das nichts großartig am Ergebnis.

PS soll man wenn aber sowieso nur einsetzen, wenn man eine Schleife mit INSERTs oder ähnliches hat. Bei SELECTs sollte es denke ich eher kaum dazu kommen, da man dort immer noch mit IN() oder INNER SELECTs arbeiten kann.

Der Unterschied zwischen mysql und mysqli ist praktisch nicht mehr messbar. Allerdings sollte man in jedem Fall mysqli vorziehen, da diese Datenbankverwaltung die einzige ist, die durch PHP noch weiterentwickelt wird und sie unterstützt auch weit aus mehr Funktionen.

In Sachen Performance erkennt man schnell, dass man soweit es möglich ist, den Cache von SQL aktivieren sollte. Weiterhin sollte man mit der Option MYSQLI_USE_RESULT arbeiten, um den Buffer von MySQL zu unterdrücken. Dabei ist allerdings die Erklärung von MySQL zu dem Thema zu beachten:
http://dev.mysql.com/doc/refman/5.1/de/mysql-use-result.html

Die wichtigsten Passagen dazu:
ZitatDemgegenüber sollten Sie mysql_use_result() nicht verwenden, wenn Sie jede Zeile auf der Clientseite aufwändig vearbeiten ... Dies würde den Server sperren und verhindern, dass andere Threads Tabellen aktualisieren können, aus denen die Daten abgerufen werden.

D.h. wenn man z.B. Daten aus der DB ausliest und dann nachträglich das Template füttert und die Daten aber vorher noch mal durch diverse Funktionen jagt. Solange diese Schleife nämlich läuft, kann kein anderer auf die Tabelle zugreifen. Allerdings gilt das wohl "nur" für MyISAM. In InnoDB sollte das angeblich keine Auswirkungen haben und man kann es wohl immer einsetzen.

Ansonsten ist diese Passage denke ich logisch:
ZitatDie Funktionen mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows() oder mysql_affected_rows() können nicht mit einer Ergebnisrückgabe von mysql_use_result() benutzt werden. Ebenso wenig können Sie andere Anfragen absetzen, bevor die Funktion mysql_use_result() ihre Arbeit beendet hat. (Immerhin liefert mysql_num_rows() ein akkurates Ergebnis, wenn Sie alle Zeilen abgeholt haben.)

Da man direkt ausliest, ohne erstmal MySQL das Ergebnis vorbereiten zu lassen, wird man natürlich nicht erfahren, wie viele Reihen das Ergebnis z.B. umfasst, außer man hat es schon durchlaufen.

Alles zum Theme SQL_CACHE findet man hier und den dort folgenden Unterkapiteln:
http://dev.mysql.com/doc/refman/5.1/de/query-cache.html

Pauschal kann man sagen, dass der Cache sich immer da lohnt, wo die Datenbank mehr SELECTs als UPDATEs/INSERTs hat, da nach jeder Aktualisierung der Cache erneuert wird, ist die dann folgende Abfrage zwar langsamer, allerdings ist der 2., 3., 4., etc. dann wiederrum schneller, so dass der Vorteil mit dem Verhältnis zwischen SELECT und UPDATE/INSERT proportional ansteigt.

Gruß
▲pn email
programmierer-forum.de
Gast 
09.10.2009, 22:59
zitieren

Mach mit!

Wenn Dir die Beiträge zum Thread "Benchmark: mysql, mysqli - Buffer, Cache und Prepared Stmt" gefallen haben oder Du noch Fragen hast oder Ergänzungen machen möchtest, solltest Du Dich gleich bei uns anmelden:



Registrierte Mitglieder genießen die folgenden Vorteile:
✔ kostenlose Mitgliedschaft
✔ keine Werbung
✔ direkter Austausch mit Gleichgesinnten
✔ neue Fragen stellen oder Diskussionen starten
✔ schnelle Hilfe bei Problemen
✔ Bilder und Videos hochladen
✔ und vieles mehr...

▲
Neuen Thread eröffnenNeue Antwort erstellen
Startseite » Forum » Programmierer » Sprachen » PHP » Seite 3
Ähnliche BeiträgeRe:
√		Letzter Beitrag
 Einfaches MySQL Benchmark PHP Skript (SELECT BENCHMARK)
Einfaches Skript um MySQl zu benchmarken: <?php $link = mysqli_connect&#40;"localhost", "root", ""&#41;; /&#x2A; check connection &#x2A;/ if &#40;mysqli_connect_errno&#40;&#41;&#41;...
[Allgemein]von mgutt		0
271		19.01.2017, 12:19
mgutt
Benchmark: Prepared Statements in Array
Hi, hier die Resultate: /* // version 1 // 1. run 0.14501937151 // 2. run 0.145541801453 // 3. run 0.141622686386 // fastest run 0.0934751033783 while ($field = $meta->fetch_field()) { $row[] =...
[PHP]von mgutt		0
501		09.10.2009, 18:39
mgutt
include(), MySQL-Cache und Race Conditions
Manche nutzen include() um MySQL Caches einzubinden. D.h. erst werden die Daten mit SELECT aus der MySQL Datenbank ausgelesen, mit var_export() aufbereitet und dann in eine Datei geschrieben. Diese kann man dann sehr performant per include()...
[PHP]von mgutt		0
293		07.09.2014, 12:56
mgutt
HTML-Seite in Cache schreiben um MySQL-Abfragen zu sparen
Das geht ganz einfach. Hier ein Beispiel: <?php // ab hier html kopf, menü, usw., eben alles was nicht in den cache soll, weil es sich z.b. ändert, wenn sich jemand einloggt ?> <html> <head> </head> <body> <div...
[PHP]von mgutt		1
526		26.02.2012, 11:33
mgutt
3D Benchmark
Wollte mal gucken was eure PC´s so hergeben mein pc system: Asus Crosshair ATI 2900 GT Kingstone HyperX DDR1066 2GB standart SPD mit CL 5 xD und kleiner rein hier mal nen 3Dmark03 run mit meiner neuen CPU davor AMD X2 BE 2300 ca 21000...
von R3DEX		79
3.464		28.04.2011, 05:26
nfs_freak
JetFlash Transcend 16 GB Benchmark
Vielleicht kann ja jemand die Info gebrauchen. Hab den Stick...
von mgutt		0
149		09.05.2014, 07:22
mgutt
Intel X25-M G2 160 GB Benchmark (SSDSA2M160G2GC)
Hi, von meiner neuen Platte hab ich mal ein Benchmark gemacht: HD Tune Pro: INTEL SSDSA2M160G2GC Benchmark Test capacity: full Read transfer rate Transfer Rate Minimum : 128.2 MB/s Transfer Rate Maximum : 198.2 MB/s Transfer Rate...
von mgutt		4
637		21.03.2011, 18:23
mgutt
preg_replace / preg_match Benchmark
Danach hatte ich eigentlich gesucht und bin auf etwas vergleichbares gestoßen: http://benchmark.nophia.de/benchmarks-textverarbeitung-k-2-texte-mit-mindestens-zwei-vokalen-b-21.html Dazu hätte ich direkt einen Verbesserungsvorschlag: <?PHP ...
[PHP]von mgutt		0
464		26.02.2009, 13:25
mgutt
SSD Benchmark: 4K Schreiben ist langsam?
Ich habe eine 850 Pro und nach einem Test mit AS SSD erscheint mir der 4K Schreibwert zu gering zu sein. Erst mal hier der Wert meiner alten 840...
von mgutt		5
1.453		08.12.2015, 17:52
Headshot91
SanDisk Extreme 16 GB Benchmark
Vielleicht kann ja jemand die Info gebrauchen. Hab den Stick...
von mgutt		0
112		09.05.2014, 07:23
mgutt
© 2004 - 2025 www.programmierer-forum.de | Communities | Impressum |