Im Artikel „MySQL MyISAM: Performance-Probleme auf der Spur“ sind wir dem Problem der gesperrten MyISAM-Tabellen auf die Spur gekommen. Eine einfache Möglichkeit, die SQL-Locks mittels Timeout zu umgehen und letztlich auch zu protokollieren, haben wir ausprobiert. Im Artikel „Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler“ haben wir mittels Textfile einen Fehlercache gebaut, so dass wir die fehlgeschlagenen Befehle nachholen können. Da dies nach wie vor nicht unseren Bedürfnissen genügt, werden wir heute einen automatisierten Cache auf Datenbankebene erstellen. Auf Datenbankebene? Ja, denn wenn ein Insert oder Update auf eine MyISAM-Tabelle gesperrt ist, bedeutet dies ja nicht, dass der SQL-Server nicht in eine andere Tabelle speichern kann. Und dieses verhalten machen wir uns heute zu Nutze.
In unserem C#-Programm haben wir aktuell folgende Update-Funktion:
string sSQL = „“;
try
{
using (MySqlConnection cn = new MySqlConnection(sConnection))
{
//Update-Befehl, der fehlschlagen kann
sSQL = @“UPDATE … „;
try
{
cn.Open();
using (MySqlCommand cmd = new MySqlCommand(sSQL, cn))
{
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
logfile(„MySQL FEHLER: “ + ex.Message);
MySQL_Error_logfile(sSQL);
}
}
}
catch (Exception ex)
{
logfile(„FEHLER: “ + ex.Message);
}
Im Fehlerfall schreiben wir ein Logfile; den eigentlichen SQL-Befehl schreiben in der Funktion „MySQL_Error_logfile“ in ein Textfile.
private static void MySQL_Error_logfile(string sText)
{
string sLogfilePfad = „logs\“;
try
{
//Abschliessendes Semikolon
if (!sText.EndsWith(„;“)) sText += „;“;
//Pfad hinzufügen
string sFile = @sLogfilePfad + „MySQL_“ + DateTime.Now.ToString(„yyyyMMdd“) + „.txt“;
//Erzeuge die Datei
FileStream outStream = new FileStream(sFile, FileMode.Append);
StreamWriter myWriter = new StreamWriter(outStream, System.Text.Encoding.Default);
myWriter.WriteLine(sText);
myWriter.Close();
}
catch (Exception ex)
{
Console.WriteLine(„FEHLER in MySQL-logfile: “ + ex.Message);
}
}
Die Funktion „MySQL_Error_logfile“ macht also nichts anderes, als ein Textfile zu schreiben. Es sorgt dafür, dass jeder SQL-Befehl ein abschließendes Semikolon hat für die Ausführung mehrerer Befehle hintereinander. Zudem erstellt es ein neues Logfile, sollte noch keines existieren. Um die bestehenden Programme möglichst wenig ändern zu müssen, wird der zu programmierende Cache auf der Basis der Funktion „MySQL_Error_logfile“ realisiert, da bereits jeder Task im Fehlerfall bereits hier seine Daten abliefert.
Wir legen also eine weitere Tabelle an. Ich habe sie „error_cache“ genannt. Es würde bereits eine Spalte genügen; ich persönlich bin jedoch ein Freund von eindeutigen ID-Feldern in einer Tabelle. Da ich MySQL 5.5.x nutze, habe ich die Standard-Storage-Engine „InnoDB“ für diese Tabelle nicht geändert.
DROP TABLE IF EXISTS `products`.`error_cache`;
CREATE TABLE `products`.`error_cache` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`befehl` text NOT NULL,
`fehler` tinyint(3) unsigned NOT NULL DEFAULT ‚0‘,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Hier die modifzierte Funktion “MySQL_Error_logfile”. Diese schreibt nun einen MySQL-Fehler in den Cache. Die Hilfsfunktion „SQLEscape“ maskiert vorhandene Hochkommas.
private static void MySQL_Error_logfile(string sSQLFehler)
{
string sSQL = „“;
try
{
//Schreiben des fehlheschlagenen Befehls in die MySQL-Tabelle
using (MySqlConnection cn = new MySqlConnection(sConnection))
{
//Update-Befehl, der fehlschlagen ist
//Hochkommas müssen maskiert werden
sSQL = @“INSERT into error_cache (befehl) VALUES (‚“ + SQLEscape(sSQLFehler) + „‚);“;
try
{
cn.Open();
using (MySqlCommand cmd = new MySqlCommand(sSQL, cn))
{
cmd.ExecuteNonQuery();
}
}
catch
{
//Sollte beim Schreiben ein Fehler auftreten,
//schreiben wir ihn in ein Logfile
logfile(„MySQL FEHLER beim Schreiben in Cache: “ + sSQL);
}
}
}
catch (Exception ex)
{
Console.WriteLine(„FEHLER in MySQL-logfile: “ + ex.Message);
}
}
public static string SQLEscape(string sValue)
{
// SQL Encoding: r, n, x00, x1a, Backslash, einfache und doppelte Hochkommas
//benötigt: using System.Text.RegularExpressions;
if (sValue == null) return null;
else return Regex.Replace(sValue, @“[rnx00x1a\'““]“, @“$0″);
}
Doch damit ist es noch nicht getan. Denn uns hilft es wenig, wenn die MySQL-Fehler in einer Tabelle auflaufen; sie müssen auch nach dem Tabellen-Lock wieder abgearbeitet werden. Hierzu schreiben wir zwei weitere Funktionen:
private static void Clear_MySQL_Error_Cache()
{
//Hole die fehlerhaften Befehle
ArrayList list = GetErrorCache();
string sSQL=““;
if (list.Count > 0)
{
//Es sind Fehler vorhanden
foreach (ArrayList buffer in list)
{
using (MySqlConnection conn = new MySqlConnection(sConnection))
{
try
{
//Arbeite nun den SQL-Befehl ab
conn.Open();
//der fehlgeschlagene Befehl aus der Datenbank
sSQL= buffer[1].ToString();
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}
//Lösche nun den Eintrag
conn.Close();
conn.Open();
sSQL = „DELETE FROM error_cache WHERE id=“ + buffer[0].ToString();
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}
}
catch
{
//Der Befehl hat wieder nicht geklappt. Erhöhe den Fehlercount
//um Endlosschleife zu verhindern
conn.Close();
conn.Open();
sSQL = „UPDATE error_cache SET fehler=fehler+1 WHERE id=“ + buffer[0].ToString();
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
private static ArrayList GetErrorCache()
{
ArrayList list = new ArrayList();
try
{
using (MySqlConnection conn = new MySqlConnection(sConnection))
{
MySqlDataReader rdr = null;
conn.Open();
//Fehleranzahl hier maximal 3
string sSQL = „SELECT id, befehl FROM error_cache WHERE fehler < 3 ORDER BY fehler“;
using (MySqlCommand cmd = new MySqlCommand(sSQL, conn))
{
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
ArrayList buffer = new ArrayList();
buffer.Add(rdr[„id“].ToString());
buffer.Add(rdr[„Befehl“].ToString());
//Der Rückgabe ArrayList hinzufügen
list.Add(buffer);
}
}
}
}
}
catch { }
return list;
}
Aufzurufen ist die Cache-Abarbeitung nun mit „Clear_MySQL_Error_Cache();“. Die Funktion holt sich durch die Hilfsfunktion „GetErrorCache()“ alle Befehle aus dem Cache, die eine Fehleranzahl < 3 besitzen. Somit werden alle Befehle maximal dreimal probiert. Es ist davon auszugehen, dass ein Befehl, der dreimal als fehlerhaft vom SQL-Server zurückgegeben wurde, einen Fehler im Befehl selbst beinhaltet. Eine Verbesserung wäre hier bereits beim Schreiben des Befehles in den Cache sicherzustellen, dass nur Befehle in den Cache gelangen, die aufgrund eines Lock-Timeout entstehen.
Ich persönlich bevorzuge diese Lösung, da ich nun an zentraler Stelle sehe, ob es auch fehlerhafte SQL-Statements aus den Jobs gibt.
Wenn die die Hilfsfunktion „GetErrorCache()“ der Funktion „Clear_MySQL_Error_Cache()“ Werte zurückgibt, arbeitet diese alle Werte hintereinander ab. Wenn kein anderer Task die Tabellen blockiert, werden alle Befehle nacheinander abgearbeitet. Wenn weiterhin ein Lock vorhanden ist, schlägt das SQL-Statement erneut fehl, der Fehler-Counter wird erhöht.
Bewertung dieses Vorschlages
Ich bin mir bewusst, dass dieses Vorgehen nicht optimal ist. Aber es erfüllt seinen Zweck. Der C#-Code kann noch weiter optimiert werden. Je nach Anwendungsfall ist auch eine Datenbank-Lösung vorzuziehen. Die Umstellung der Tabelle in eine InnoDB-Tabelle werden wird zu einem späteren Zeitpunkt beschreiten.
Wer sich noch weiter mit einem Tabellen-Lock oder Commit beschäftigen möchte, dem empfehle ich die ausführliche Dokumentation unter mysql.com.
Alle Artikel aus dieser Serie
MySQL MyISAM: Performance-Probleme auf der Spur
Einfacher MySQL Fehler-Cache für MyISAM Lock Fehler
MyISAM-Tabellen-Locks in Cache abfangen und nacharbeiten