Optimierte prepared Statements cachen?

Hi,
mal eher etwas theoretisches, was in der Praxis nur bedingt Performance Vorteile bringen würde. Aber die Theorie, ob dies möglich wäre interessiert mich hier mehr als der Vorteil den es bringen würde. ^^
Beschreibung:
Wenn man prepared Statements benutzt, werden die SQL Statements von der Datenbank optimiert und immer nur die optimierte Form ausgeführt. Wenn man die optimierte Form zwischenspeichern könnte im Dateisystem, müsste man diese nicht mehr compilieren, sondern nur laden, etwa als serialisiertes Objekt.
Geht aber nur wenn man das compilierte Statement auch nach der Verbindung weiter benutzen kann und es nicht mit der Verbindung zusammenhängt bzw. von der Datenbank zwischengespeichert wird und man nur eine Resource zurück bekommt.
PS: Die Datenbank basiert auf MySQL.
Was denkt Ihr, ist so etwas überhaupt möglich, die optimierte Form eines prepared Statements zu speichern?

Das was die Datenbank zuerst macht, wenn sie eine Anfrage bekommt, ist, dass sie einen Querry Execution Plan (QEP) erstellt. Dies kann recht aufwendig sein und von daher cachen Datenbanken diesen Plan. Wenn jetzt für die gleiche Anfrage mit unterschiedlichen Parametern unterschiedliche Texte kommen, weil die Anfrage mit den Parametern zusammen gebastelt wurde, anstatt die Parameter an bind variablen zu übergeben, erkennen die meisten Datenbanken nicht, das es sich in Wirklichkeit um die gleiche Anfrage handelt.
Aber warum möchtest Du den QEP speichern / cachen und überläßt es nicht einfach der Datenbank?

Könnte doch interessant sein, wenn ein solcher QEP (genau das Wort hat mich noch gefehlt ^^) aus der Datenbank geholt und gespeichert werden könnte. Dann müsste für jedes komplexe Statement nicht mehr der Plan bei jedem Seitenaufruf erstellt werden, sondern könnte dauerhaft gecached werden und wiederverwendet.
Etwas anderes wäre natürlich wenn MySQL die Sachen auch nach dem Ende einer Verbindung cached. Dann wäre es relativ sinnlos, da eine Neuerstellung nur nach Datenbankneustart nötig wäre.
Aber wenn MySQL bei jeder neuen Verbindung die Pläne neu erstellen muss, wäre es doch interessant der Datenbank die Pläne zu geben, die bei der vorherigen Verbindung schon erstellt wurden.
Aber wie gesagt: Performance Technisch halte ich das für unnötig bzw. der Kosten/Nutzen Faktor ist hier viel zu schlecht. Schließlich muss der gecachte Plan auch gespeichert/geladen werden.

Man muss bei prepared statements auch ein wenig darauf achten, von welcher Schicht man spricht, da es dabei sowohl Client- als auch Serverseitige Aktionen gibt.
Richtig ist, dass Serverseitig der QEP nur gecached wird, solange die Verbindung versteht. Das ist vermutlich auch gut so. Bei Fat Clients bleibt die Verbindung ja bestehen, solange die Anwendung läuft und es kann gecached werden. Mit jedem Start der Anwendung wird also von vorne angefangen, was sehr wichtig ist, damit der QEP an die neuen Bedingungen angepasst wird. Den QEP extern zu serialisieren, wäre nicht so günstig, da so nicht mehr auf Änderungen in den Statistiken reagiert wird, z.B. durch einen regelmässigen ANALYZE Lauf.
Anders stellt es sich dar, wenn man keinen Fat Client hat. Wenn bei jedem Request eine neue Verbindung geöffnet wird, sind prepared Statements meist überflüssig, da sowohl in der DB als auch in der Anwendung Berechnungen durchgeführt werden müssen. Dies kann mann nur umgehen, in dem man Connection Pools einsetzt, oder z.B. FastCGI verwendet.
Im J2EE Umfeld machen es die Applikationsserver z.B. so, dass sie einen ConnectionPool vorhalten, in dem die prepared Statements schon Clientseitig gecached werden, zusätzlich zum Serverseitigem Cache.

Wegen Statistiken und ähnliches noch ne Zwischenfrage:
Ist es in MySQL nötig, irgendwelche Sachen regelmäßig durchzuführen? (Bei DB2 kenne ich zum Beispiel die RunStats, welche man nach größeren Insert Mengen ausführen sollte um die Indizes neu aufzubauen. Bzw. Reorganisationen um die Daten zu defragmentieren)
Ist es denn nun möglich die optimierten Statements (daher die Pläne) zu serialisieren und extern zu speicher?
Dass es eigentlich weniger Nutzen hat hab ich mir schon gedacht, da der Aufwand eigentlich zu hoch ist. Nur ist mir die Idee mal gekommen und mich hat es interessiert ob es rein technisch möglich ist.
PS: PHP 5 benutz ich übrigens, also würde bei jedem neuen Skriptaufruf die prepared Statements neu aufbereitet werden (wenn ich es richtig von dir verstanden hab).

> Im J2EE Umfeld machen es die Applikationsserver z.B. so, dass sie einen ConnectionPool vorhalten
Das gleiche gilt auch für PHP (mysql_pconnect) und eigentlich mir alle anderen bekannten Webframeworks.

Original von DrakeL
Wegen Statistiken und ähnliches noch ne Zwischenfrage:
Ist es in MySQL nötig, irgendwelche Sachen regelmäßig durchzuführen? (Bei DB2 kenne ich zum Beispiel die RunStats, welche man nach größeren Insert Mengen ausführen sollte um die Indizes neu aufzubauen. Bzw. Reorganisationen um die Daten zu defragmentieren)

Ich bin mir nicht ganz sicher. Die Tage habe ich es über PSQL gelesen, dass man in den älteren Versionen ANAYLZE immer manuell aufrufen musste, in der aktuellen es aber automatisch passiert.
Bei MySQL sollte ANALYZE auch regelmässig aufgerufen werden. Ich habe aber noch nicht gefunden, ob dies automatisch geschieht, oder man es manuell machen muss.

Original von DrakeL

Ist es denn nun möglich die optimierten Statements (daher die Pläne) zu serialisieren und extern zu speicher?
Serverseitig ist mit keine DB bekannt, wo das möglich wäre. Clientseitig kann man es vermutlich in jeder Sprache machen, bringt dort aber nichts, da mit jedem Start der Anwendung das Statement nicht mehr gültig wäre.

Original von DrakeL

PS: PHP 5 benutz ich übrigens, also würde bei jedem neuen Skriptaufruf die prepared Statements neu aufbereitet werden (wenn ich es richtig von dir verstanden hab).
Ja, sowohl Serverseitig als auch Clientseitig. Serverseitig kannst Du es umgehen, in dem Du einen Connection-Pool verwendest, siehe Hinweis von Undeadable. Clientseitig könntest Du es durch den Wechsel von mod_php auf FastCGI umgehen.

Ok, dann Danke für die Antworten.
Falls jemand etwas wegen dem Analyse bei MySQL weiß (also ob und wie man dieses ausführt), kann er es ja noch posten oder mit per PN schicken, würde mich interessieren (schau aber auch gleich mal selbst in der Doku nach, vielleicht finde ich ja auf die schnelle was).

Nutzt du DB2 LUW oder z/OS?
Der Querycash auf eine Tabelle wird nur geflusht wenn die Tabelle geändert wird, ansonsten bleibt der bestehen.

#!/usr/bin/php
$sql['username']='root';
$sql['password']='';
$dbc = mysql_connect("localhost",$sql['username'],$sql['password']) or die("Cannot establish a connection to the database.");
echo "Connected,...
\n";
$resdb=mysql_query("SHOW databases");
if ($resdb) {
while ($rowdb=mysql_fetch_assoc($resdb)) {
if ($rowdb['Database'] == 'information_schema')
continue;
$resuse=mysql_query("USE ".$rowdb['Database']);
if ($resuse) {
echo "USE ".$rowdb['Database']."
\n";
$restb=mysql_query("SHOW TABLES");
if ($restb) {
while ($rowtb=mysql_fetch_row($restb)) {
echo "Table: ".$rowtb[0]."
\n";
$res=mysql_query("REPAIR TABLE ".$rowtb[0]);
if (!$res) {
echo mysql_errno()." ". mysql_error();
}
$row=mysql_fetch_assoc($res);
echo "Repair : ".$row['Msg_type'] ." ".$row['Msg_text']."
\n";
$res=mysql_query("OPTIMIZE TABLE ".$rowtb[0]);
if (!$res) {
echo mysql_errno()." ". mysql_error();
}

$row=mysql_fetch_assoc($res);
echo "Optimize : ".$row['Msg_type'] ." ".$row['Msg_text']."
\n";
$res=mysql_query("ANALYZE TABLE ".$rowtb[0]);
if (!$res) {
echo mysql_errno()." ". mysql_error();
}

$row=mysql_fetch_assoc($res);
echo "Analyze : ".$row['Msg_type'] ." ".$row['Msg_text']."
\n";
}

} else echo "SHOW TABLES failed: ".$rowdb['Database']."
\n";
} else echo "USE ".$rowdb['Database']." failed
\n";
}
} else echo "SHOW DB failed
\n";
?>
Das ist eine altes Wartungsscript, aus den zeiten wo mein mySQL sich noch regelmässig weggehängt hat. Das Repair, sollte raus, oder der Admin sollte benachrichtigt werden, das optimize entspricht einem REORG mit Inline RUNSTATS im DB2 und das Analyze am ende, ist überflüssig.
Das ganze als CronJob auf eine Zeit wo wenig los ist, und die Tabellen werden regelmäßig überprüft und aufgeräumt,...
Sowas wie einen precompiler der ein DBRM erzeugt und mit statischem SQL arbeitet, wo der zugriffspfad irgendwo fest abgelegt wird, hab ich für mySQL noch nicht gefunden :/ obwohl ich denke das mysqli mit prepare+bind der sache am nächsten kommt.

Das kam heute im Blog des Teams des SQL Server 2005:
What Query Plans are in SQL Server's Memory?
www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory
Ich denke, dass es andere Datenbanken ähnlich machen. Vielleicht kannst du damit was anfangen.