FromDual TechFeed (de)

Vor ein paar Tagen hatte ich wieder einmal mit einer MySQL Cluster Replikation zu tun. Ich habe das schon eine Weile nicht mehr angelangt und war somit vorbereitet, wieder einmal ein paar Überraschungen zu erleben.

Diejenige, für welche MySQL Cluster - Cluster Ring-Replikationen das tägliche Brot ist, können diesen Artikel getrost überspringen. Alle anderen können möglicherweise von unseren Erfahrungen profitieren.

Wir hatten das folgende MySQL Cluster Konstrukt im Einsatz:

Mehr Informationen über solche Gebilde können in der MySQL Cluster Dokumentation gefunden werden.

Situationen welche zu einem Channel Fail-over führen:

Was sind die Problem, mit dem MySQL Cluster, welche zu einem Channel Fail-over führen:

• Der MySQL Master verliert die Verbindung zum MySQL Cluster (lost_event, gap).
• Der MySQL Master kann mit der Last auf dem MySQL Cluster nicht Schritt halten und verliert daher Events (gap). Ich bin wirklich etwas verwundert über das Argument in der Dokumentation [ 1 ], dass in einem solchen Fall ein Channel Fail-over durchgeführt werden kann oder soll. Weil, wenn der Master 1 mit der Last nicht mehr Schritt halten kann, warum soll es dann Master 2 noch können...?).

Was eine Lücke (gap / lost_event) ist, kann der folgenden Skizze entnommen werden:

Wie ein solches System aufgesetzt wird, wollen wir hier nicht betrachten. Die Anleitung dazu können der MySQL Cluster Dokumentation entnommen werden.

Unsere Erkenntnisse

Unsere Erkenntnisse waren folgender Art:

Doppelte Primary Key Einträge

Wir erhielten einige doppelte Index Einträge für unsere AUTO_INCREMENT Primary Keys:

ERROR 1062 (23000): Duplicate entry '260' for key 'PRIMARY'

Der Grund dafür ist, dass wir ndb_autoincrement_prefetch_sz auf 256 gesetzt hatten, um eine bessere INSERT Performance zu erhalten.

Als Konsequenz erhält man, früher oder später, wenn man Daten in alle mysqld's auf beiden Clustern einfügt, einen Primary Key Konflikt.

Wir haben dieses Problem gelöst, indem wir die auto_increment_increment und auto_increment_offset Werte für die SQL Knoten auf beiden MySQL Clustern entsprechend gesetzt haben.

Empfohlene Channel Fail-over Methode funktioniert nur unter Last

Ein weiteres Problem, welches wir bereits vor langer Zeit bei einem anderen Kunden entdeckt haben, ist, dass das empfohlenen Channel Failover-Vorgehen [ 2 ]:

slave1> STOP SLAVE; slave1> SELECT MAX(epoch) AS latest FROM mysql.ndb_apply_status; master2> SELECT SUBSTRING_INDEX(File, '/', -1) AS master_log_file , Position AS master_log_pos FROM mysql.ndb_binlog_index WHERE epoch > <latest> ORDER BY epoch ASC LIMIT 1; slave2> CHANGE MASTER TO master_log_file='<master_log_file>', master_log_pos=<master_log_pos>;

nur funktioniert, wenn wir Last auf dem MySQL Cluster haben. Wenn wir gar keine Last haben, gibt diese Abfrage ein leeres Resultat zurück und es muss der Befehl SHOW MASTER STATUS dazu verwendet werden.

Dokumentationsfehler über log_slave_updates

Diese Situation haben wir fortwährend für Kanal ch2 und ch3 vom Cluster B auf den Cluster A wenn wir KEINE Last auf Cluster B haben und streng der Dokumentation gefolgt wird, wo für ein solches Gebilde gesagt wird: log_slave_updates DARF NICHT eingeschaltet (MUST NOT be enabled) [3] werden.

Ich habe dieses Problem mit ein paar Leuten diskutiert und bin zur Ansicht gekommen, dass die MySQL Cluster Dokumentation hier punktuell falsch ist. Der Master für eine 2 Cluster Replikation KANN log_slave_updates eingeschaltet haben und IMHO MUSS der Master einer 3 Cluster Replikation log_slave_updates eingeschaltet haben.

Aber trotzdem, wenn wir keine Last auf beiden Servern haben, müssen wir das Verfahren ändern um die korrekte Binary Log Position zu bestimmen, wenn wir den Kanal wechseln wollen. Das macht es etwas schwieriger wenn wir den Channel Fail-over automatisieren oder skripten wollen.

Leere Epochen

Früher, wenn ich mich recht erinnere, hat MySQL Cluster immer leere Epochen ins Binary Log geschrieben. Das hatte gewährleistet, dass immer eine gewisse Last auf den Kanälen geherrscht hat. Dann habe ich bei den MySQL Cluster Entwicklern reklamiert und diese haben dieses Problem behoben. Aber für die eben besprochene Situation wäre dieses Verhalten geradezu nützlich und würde Sinn machen. Also habe ich nach der ndb-log-empty-epochs Variablen Ausschau gehalten und gehofft, dass sie dieses Verhalten wieder ermöglicht. Aber irgendwie hat der MySQL Cluster für dieses Set-Up trotz der Verwendung dieser Variable keine leeren Epochen ans Binary Log gemeldet. Zumindest nicht in der kurzen Zeit, wo ich mich mit diesem Problem beschäftigt hatte.

Log_slave_updates wird auch durch den Binlog Injector Thread berücksichtigt

Eine weitere Erkenntnis mit log_slave_updates war, dass dieser Parameter entsprechend der Dokumentation, nur zum Zug kommt, wenn ein Slave auch als Master agiert [4]. Was heisst, dass er nur die Daten in sein Binary Log schreibt, wenn er die Daten direkt von seinem Master erhält. Es macht den Anschein, dass dies eine falsche Annahme ist. Ein Master scheint auch die Statements welche durch den Binlog Injector Thread [5] über den Cluster kommen, in sein Binary Log zu schreiben. Dies ist möglicherweise ein weiterer Dokumentationsfehler oder zumindest eine Dokumentationslücke.

Skip_slave_start sollte verwendet werden

Eine weiter Stolperfalle ist, dass ich vergessen habe, die Variable skip-slave-start zu setzen. Diese Variable sollte IMHO in einem solchen Set-up immer auf dem Slave gesetzt sein. Wenn ein Slave startet, kann er nicht wissen, ob er der aktive Channel sein wird oder nicht.

Zusammenfassung

• Überwache Deine Replikations-Kanäle.
• Nutze auto_increment_increment und auto_increment_offset wenn auf beide MySQL Cluster gleichzeitig geschrieben wird oder vermeideAUTO_INCREMENT.
• Verwende ein kluges Channel Fail-over Skript.
• Verwende log_slave_updates auf allen Mastern.
• Verwende skip_slave_start auf allen Slaves.

Das waren die Erkenntnisse unseres letzten Einsatzes mit MySQL Cluster Replikation und Fail-over Replikations-Kanälen. Wenn Sie weitere oder andere Erfahrungen gesammelt haben, wären wir froh, von Ihnen darüber zu hören.

Ein Skript zum automatisierten Fail-over der Kanäle kann unter Download gefunden werden.

Taxonomy upgrade extras: replicationMySQL Clusterfail-overchannelRing-Replikationgerman

Taxonomy upgrade extras: replicationMySQL Clusterfail-overchannelRing-Replikationgerman

Vor ein paar Tagen hatte ich wieder einmal mit einer MySQL Cluster Replikation zu tun. Ich habe das schon eine Weile nicht mehr angelangt und war somit vorbereitet, wieder einmal ein paar Überraschungen zu erleben.

Diejenige, für welche MySQL Cluster - Cluster Ring-Replikationen das tägliche Brot ist, können diesen Artikel getrost überspringen. Alle anderen können möglicherweise von unseren Erfahrungen profitieren.

Wir hatten das folgende MySQL Cluster Konstrukt im Einsatz:

Mehr Informationen über solche Gebilde können in der MySQL Cluster Dokumentation gefunden werden.

Situationen welche zu einem Channel Fail-over führen:

Was sind die Problem, mit dem MySQL Cluster, welche zu einem Channel Fail-over führen:

• Der MySQL Master verliert die Verbindung zum MySQL Cluster (lost_event, gap).
• Der MySQL Master kann mit der Last auf dem MySQL Cluster nicht Schritt halten und verliert daher Events (gap). Ich bin wirklich etwas verwundert über das Argument in der Dokumentation [ 1 ], dass in einem solchen Fall ein Channel Fail-over durchgeführt werden kann oder soll. Weil, wenn der Master 1 mit der Last nicht mehr Schritt halten kann, warum soll es dann Master 2 noch können...?).

Was eine Lücke (gap / lost_event) ist, kann der folgenden Skizze entnommen werden:

Wie ein solches System aufgesetzt wird, wollen wir hier nicht betrachten. Die Anleitung dazu können der MySQL Cluster Dokumentation entnommen werden.

Unsere Erkenntnisse

Unsere Erkenntnisse waren folgender Art:

Doppelte Primary Key Einträge

Wir erhielten einige doppelte Index Einträge für unsere AUTO_INCREMENT Primary Keys:

ERROR 1062 (23000): Duplicate entry '260' for key 'PRIMARY'

Der Grund dafür ist, dass wir ndb_autoincrement_prefetch_sz auf 256 gesetzt hatten, um eine bessere INSERT Performance zu erhalten.

Als Konsequenz erhält man, früher oder später, wenn man Daten in alle mysqld's auf beiden Clustern einfügt, einen Primary Key Konflikt.

Wir haben dieses Problem gelöst, indem wir die auto_increment_increment und auto_increment_offset Werte für die SQL Knoten auf beiden MySQL Clustern entsprechend gesetzt haben.

Empfohlene Channel Fail-over Methode funktioniert nur unter Last

Ein weiteres Problem, welches wir bereits vor langer Zeit bei einem anderen Kunden entdeckt haben, ist, dass das empfohlenen Channel Failover-Vorgehen [ 2 ]:

slave1> STOP SLAVE; slave1> SELECT MAX(epoch) AS latest FROM mysql.ndb_apply_status; master2> SELECT SUBSTRING_INDEX(File, '/', -1) AS master_log_file , Position AS master_log_pos FROM mysql.ndb_binlog_index WHERE epoch > <latest> ORDER BY epoch ASC LIMIT 1; slave2> CHANGE MASTER TO master_log_file='<master_log_file>', master_log_pos=<master_log_pos>;

nur funktioniert, wenn wir Last auf dem MySQL Cluster haben. Wenn wir gar keine Last haben, gibt diese Abfrage ein leeres Resultat zurück und es muss der Befehl SHOW MASTER STATUS dazu verwendet werden.

Dokumentationsfehler über log_slave_updates

Diese Situation haben wir fortwährend für Kanal ch2 und ch3 vom Cluster B auf den Cluster A wenn wir KEINE Last auf Cluster B haben und streng der Dokumentation gefolgt wird, wo für ein solches Gebilde gesagt wird: log_slave_updates DARF NICHT eingeschaltet (MUST NOT be enabled) [3] werden.

Ich habe dieses Problem mit ein paar Leuten diskutiert und bin zur Ansicht gekommen, dass die MySQL Cluster Dokumentation hier punktuell falsch ist. Der Master für eine 2 Cluster Replikation KANN log_slave_updates eingeschaltet haben und IMHO MUSS der Master einer 3 Cluster Replikation log_slave_updates eingeschaltet haben.

Aber trotzdem, wenn wir keine Last auf beiden Servern haben, müssen wir das Verfahren ändern um die korrekte Binary Log Position zu bestimmen, wenn wir den Kanal wechseln wollen. Das macht es etwas schwieriger wenn wir den Channel Fail-over automatisieren oder skripten wollen.

Leere Epochen

Früher, wenn ich mich recht erinnere, hat MySQL Cluster immer leere Epochen ins Binary Log geschrieben. Das hatte gewährleistet, dass immer eine gewisse Last auf den Kanälen geherrscht hat. Dann habe ich bei den MySQL Cluster Entwicklern reklamiert und diese haben dieses Problem behoben. Aber für die eben besprochene Situation wäre dieses Verhalten geradezu nützlich und würde Sinn machen. Also habe ich nach der ndb-log-empty-epochs Variablen Ausschau gehalten und gehofft, dass sie dieses Verhalten wieder ermöglicht. Aber irgendwie hat der MySQL Cluster für dieses Set-Up trotz der Verwendung dieser Variable keine leeren Epochen ans Binary Log gemeldet. Zumindest nicht in der kurzen Zeit, wo ich mich mit diesem Problem beschäftigt hatte.

Log_slave_updates wird auch durch den Binlog Injector Thread berücksichtigt

Eine weitere Erkenntnis mit log_slave_updates war, dass dieser Parameter entsprechend der Dokumentation, nur zum Zug kommt, wenn ein Slave auch als Master agiert [4]. Was heisst, dass er nur die Daten in sein Binary Log schreibt, wenn er die Daten direkt von seinem Master erhält. Es macht den Anschein, dass dies eine falsche Annahme ist. Ein Master scheint auch die Statements welche durch den Binlog Injector Thread [5] über den Cluster kommen, in sein Binary Log zu schreiben. Dies ist möglicherweise ein weiterer Dokumentationsfehler oder zumindest eine Dokumentationslücke.

Skip_slave_start sollte verwendet werden

Eine weiter Stolperfalle ist, dass ich vergessen habe, die Variable skip-slave-start zu setzen. Diese Variable sollte IMHO in einem solchen Set-up immer auf dem Slave gesetzt sein. Wenn ein Slave startet, kann er nicht wissen, ob er der aktive Channel sein wird oder nicht.

Zusammenfassung

• Überwache Deine Replikations-Kanäle.
• Nutze auto_increment_increment und auto_increment_offset wenn auf beide MySQL Cluster gleichzeitig geschrieben wird oder vermeideAUTO_INCREMENT.
• Verwende ein kluges Channel Fail-over Skript.
• Verwende log_slave_updates auf allen Mastern.
• Verwende skip_slave_start auf allen Slaves.

Das waren die Erkenntnisse unseres letzten Einsatzes mit MySQL Cluster Replikation und Fail-over Replikations-Kanälen. Wenn Sie weitere oder andere Erfahrungen gesammelt haben, wären wir froh, von Ihnen darüber zu hören.

Ein Skript zum automatisierten Fail-over der Kanäle kann unter Download gefunden werden.

Vor ein paar Wochen haben wir einem unserer Kunden unser MyEnv gezeigt. Er war sehr interessiert daran und hat uns geraten MyEnv einer breiten Öffentlichkeit bekannter zu machen. In Tat und Wahrheit ist das MyEnv bereits seit mehreren Jahren verfügbar und kann frei heruntergeladen werden...

Aber bis anhin konnten wir uns noch kein Herz fassen es gross einer breiten Öffentlichkeit anzukündigen weil es noch überhaupt nicht Endbenutzer tauglich war. Nachdem ich ein Wochenende damit verbracht habe, das MyEnv benutzerfreundlicher zu machen, einige Zeilen Code zu konsolidieren, altes Zeugs zu löschen, etc., denke ich, dass es jetzt akzeptable für die Öffentlichkeit, aber noch nicht perfekt ist (release early release often?).

Was ist MyEnv?

MyEnv ist ein Set von Skripten um bequem mehrere MySQL oder MariaDB Datenbankinstanzen auf ein und dem selben Server zu betreiben. Es können sogar mehrere Datenbanken mit verschiedenen Binaryversionen betrieben werden.

Meines Erachtens ist MyEnv bequemer als mysqld_multi und bietet mehr Funktionalität.

Wann soll MyEnv eingesetzt werden?

MyEnv macht Sinn, sobald mehrere MySQL Datenbanken auf einem Server installiert werden sollen. Das ist üblicherweise der Fall wenn:

• viele verschiedene kleine Datenbanken von mehreren Servern auf einen Server konsolidiert werden sollen oder,
• wenn die Daten mehrerer Kunden aus Sicherheitsgründen oder auf Kundenwunsch in verschiedenen Datenbanken gehalten werden müssen. Zum Beispiel wenn eine Applikation als Software-as-a-Service (SaaS) Lösung angeboten wird.

MyEnv hat den Vorteil gegenüber Virtualiserungslösungen (wie VMWare oder VirtualBox), dass der Overhead der virtuellen Maschine und des zusätzlichen Betriebssystems weg fällt. Somit können mehrere MySQL/MariaDB Datenbanken auf weniger Maschinen betrieben werden.

Probiert es also aus. Wenn Euch das MyEnv von Nutzen ist, sind wir froh. Bitte lasst uns auch wissen, wenn Ihr auf Probleme mit dem MyEnv stosst, wenn Ihr Bugs findet oder wenn Ihr einen Verbesserungsvorschlag habt.

Wenn allgemeine Fragen zur Konsolidierung von MySQL Datenbankservern oder MySQL in Software-as-a-Service (SaaS) Umgebungen bestehen, würde Euch unser MySQL Beratungsteam, gerne zur Seite stehen...

Weitere Informationen finden Sie in der MyEnv Dokumentation.

Taxonomy upgrade extras: mysqlenvironmentmulti instancedatabasevirtualizationconsolidationSaaSMyEnv

Vor ein paar Wochen haben wir einem unserer Kunden unser MyEnv gezeigt. Er war sehr interessiert daran und hat uns geraten MyEnv einer breiten Öffentlichkeit bekannter zu machen. In Tat und Wahrheit ist das MyEnv bereits seit mehreren Jahren verfügbar und kann frei heruntergeladen werden...

Aber bis anhin konnten wir uns noch kein Herz fassen es gross einer breiten Öffentlichkeit anzukündigen weil es noch überhaupt nicht Endbenutzer tauglich war. Nachdem ich ein Wochenende damit verbracht habe, das MyEnv benutzerfreundlicher zu machen, einige Zeilen Code zu konsolidieren, altes Zeugs zu löschen, etc., denke ich, dass es jetzt akzeptable für die Öffentlichkeit, aber noch nicht perfekt ist (release early release often?).

Was ist MyEnv?

MyEnv ist ein Set von Skripten um bequem mehrere MySQL oder MariaDB Datenbankinstanzen auf ein und dem selben Server zu betreiben. Es können sogar mehrere Datenbanken mit verschiedenen Binaryversionen betrieben werden.

Meines Erachtens ist MyEnv bequemer als mysqld_multi und bietet mehr Funktionalität.

Wann soll MyEnv eingesetzt werden?

MyEnv macht Sinn, sobald mehrere MySQL Datenbanken auf einem Server installiert werden sollen. Das ist üblicherweise der Fall wenn:

• viele verschiedene kleine Datenbanken von mehreren Servern auf einen Server konsolidiert werden sollen oder,
• wenn die Daten mehrerer Kunden aus Sicherheitsgründen oder auf Kundenwunsch in verschiedenen Datenbanken gehalten werden müssen. Zum Beispiel wenn eine Applikation als Software-as-a-Service (SaaS) Lösung angeboten wird.

MyEnv hat den Vorteil gegenüber Virtualiserungslösungen (wie VMWare oder VirtualBox), dass der Overhead der virtuellen Maschine und des zusätzlichen Betriebssystems weg fällt. Somit können mehrere MySQL/MariaDB Datenbanken auf weniger Maschinen betrieben werden.

Probiert es also aus. Wenn Euch das MyEnv von Nutzen ist, sind wir froh. Bitte lasst uns auch wissen, wenn Ihr auf Probleme mit dem MyEnv stosst, wenn Ihr Bugs findet oder wenn Ihr einen Verbesserungsvorschlag habt.

Wenn allgemeine Fragen zur Konsolidierung von MySQL Datenbankservern oder MySQL in Software-as-a-Service (SaaS) Umgebungen bestehen, würde Euch unser MySQL Beratungsteam, gerne zur Seite stehen...

Weitere Informationen finden Sie in der MyEnv Dokumentation.

Taxonomy upgrade extras: mysqlenvironmentmulti instancedatabasevirtualizationconsolidationSaaSMyEnv

Taxonomy upgrade extras: mysqlmyenvenvironmentmulti instancedatabasevirtualizationconsolidationSaaSgerman

Vor ein paar Wochen haben wir einem unserer Kunden unser MyEnv gezeigt. Er war sehr interessiert daran und hat uns geraten MyEnv einer breiten Öffentlichkeit bekannter zu machen. In Tat und Wahrheit ist das MyEnv bereits seit mehreren Jahren verfügbar und kann frei heruntergeladen werden...

Aber bis anhin konnten wir uns noch kein Herz fassen es gross einer breiten Öffentlichkeit anzukündigen weil es noch überhaupt nicht Endbenutzer tauglich war. Nachdem ich ein Wochenende damit verbracht habe, das MyEnv benutzerfreundlicher zu machen, einige Zeilen Code zu konsolidieren, altes Zeugs zu löschen, etc., denke ich, dass es jetzt akzeptable für die Öffentlichkeit, aber noch nicht perfekt ist (release early release often?).

Was ist MyEnv?

MyEnv ist ein Set von Skripten um bequem mehrere MySQL oder MariaDB Datenbankinstanzen auf ein und dem selben Server zu betreiben. Es können sogar mehrere Datenbanken mit verschiedenen Binaryversionen betrieben werden.

Meines Erachtens ist MyEnv bequemer als mysqld_multi und bietet mehr Funktionalität.

Wann soll MyEnv eingesetzt werden?

MyEnv macht Sinn, sobald mehrere MySQL Datenbanken auf einem Server installiert werden sollen. Das ist üblicherweise der Fall wenn:

• viele verschiedene kleine Datenbanken von mehreren Servern auf einen Server konsolidiert werden sollen oder,
• wenn die Daten mehrerer Kunden aus Sicherheitsgründen oder auf Kundenwunsch in verschiedenen Datenbanken gehalten werden müssen. Zum Beispiel wenn eine Applikation als Software-as-a-Service (SaaS) Lösung angeboten wird.

MyEnv hat den Vorteil gegenüber Virtualiserungslösungen (wie VMWare oder VirtualBox), dass der Overhead der virtuellen Maschine und des zusätzlichen Betriebssystems weg fällt. Somit können mehrere MySQL/MariaDB Datenbanken auf weniger Maschinen betrieben werden.

Probiert es also aus. Wenn Euch das MyEnv von Nutzen ist, sind wir froh. Bitte lasst uns auch wissen, wenn Ihr auf Probleme mit dem MyEnv stosst, wenn Ihr Bugs findet oder wenn Ihr einen Verbesserungsvorschlag habt.

Wenn allgemeine Fragen zur Konsolidierung von MySQL Datenbankservern oder MySQL in Software-as-a-Service (SaaS) Umgebungen bestehen, würde Euch unser MySQL Beratungsteam, gerne zur Seite stehen...

Weitere Informationen finden Sie in der MyEnv Dokumentation.

Einer unserer Kunden hat mich kürzlich gefragt, was wir ihm empfehlen: WAMP oder LAMP? Also Windows oder Linux zusammen mit Apache, MySQL und PHP.

Im weiteren hat er angemerkt, sein Lieferant habe gesagt, dass MySQL Cluster NICHT auf Windows läuft...

Das ist eine nicht triviale Frage. Was wir in so einem Fall raten, ist folgende Punkte zu beachten:

• Ca. 80% der MySQL-Installationen laufen auf Linux! Es ist also auf Linux mit den wenigsten Problemen zu rechnen. Weitere Plattformen, welche man von Zeit zu Zeit bei Kunden antrifft sind: Windows, BSD-Derivate und Solaris (in etwa in dieser Reihenfolge).
• MySQL als Firma und die MySQL-Mitarbeiter haben am meisten Know-How auf Linux, Mac, Solaris und Windows (in etwa in dieser Reihenfolge).
• Also rein statistisch gesehen hat man mit Linux wahrscheinlich am wenigsten Ärger oder Probleme zu erwarten.
• Wie der Name LAMP schon sagt werden diese 4 Komponenten häufig zusammen eingesetzt und sollten daher am besten ineinander eingeschliffen sein.
• Das Linux für den professionellen Einsatz besser geeignet ist als Windows, würde ich persönlich bejahen (ich bin Linux-biased). Aber da scheiden sich bekanntlich die Geister! Windows-Admins werden jetzt wahrscheinlich einen roten Kopf kriegen... :)
• Grundsätzlich denke ich aber, dass die Unterschiede zwischen Windows und Linux was Stabilität, Sicherheit und Performance anbelangen, heute marginal sind.
• Was die Sicherheit anbelangt ist sicher PHP das grösste Risiko (respektive das, was die Entwickler damit anstellen).
• Wenn Kunden ganz freaky Sachen machen wollen, denke ich, das Linux wegen seiner Flexibilität besser geeignet ist als Windows (siehe Facebook und Konsorten...).
• Meines Erachtens das Wichtigste Kriterium ist das Know-How sowohl im Betrieb als auch, eingeschränkt, in der Entwicklung. Ich denke, dass dieses den grössten Einfluss hat. Also: Wenn der Betrieb keine Ahnung von Linux hat: Finger davon lassen!
• Die Aussage des Lieferanten ist teilweise richtig. Früher lief MySQL Cluster ausschliesslich auf Linux (und anderen Unixoiden). Seit MySQL Cluster 7.1 wird aber mehr Gewicht auf Windows gelegt und Oracle hat MySQL Cluster 7.1 sogar als GA deklariert (GA = General Available = Production Ready). MySQL Support hat darüber aber die Nase gerümpft und den Kopf geschüttelt...
• Oracle positioniert ja das Produkt MySQL neu gegen Windows SQL-Server auf Windows und wird voraussichtlich sehr viel Entwicklungstätigkeit da rein stecken!
• Achtung: MySQL Cluster ist KEINE General-Purpose Datenbank. Man kann also NICHT einfach denken, ich baue meine Applikation und hänge dann einen MySQL Cluster drunter. Bei MySQL Cluster wird eine Applikation spezifisch auf dieses Produkt zugeschnitten! Anders verhält es sich mit einem geclusterten MySQL (MySQL HA, geht sowohl unter Windows als auch unter unixoiden Systemen).

Taxonomy upgrade extras: wamplampgerman

Einer unserer Kunden hat mich kürzlich gefragt, was wir ihm empfehlen: WAMP oder LAMP? Also Windows oder Linux zusammen mit Apache, MySQL und PHP.

Im weiteren hat er angemerkt, sein Lieferant habe gesagt, dass MySQL Cluster NICHT auf Windows läuft...

Das ist eine nicht triviale Frage. Was wir in so einem Fall raten, ist folgende Punkte zu beachten:

• Ca. 80% der MySQL-Installationen laufen auf Linux! Es ist also auf Linux mit den wenigsten Problemen zu rechnen. Weitere Plattformen, welche man von Zeit zu Zeit bei Kunden antrifft sind: Windows, BSD-Derivate und Solaris (in etwa in dieser Reihenfolge).
• MySQL als Firma und die MySQL-Mitarbeiter haben am meisten Know-How auf Linux, Mac, Solaris und Windows (in etwa in dieser Reihenfolge).
• Also rein statistisch gesehen hat man mit Linux wahrscheinlich am wenigsten Ärger oder Probleme zu erwarten.
• Wie der Name LAMP schon sagt werden diese 4 Komponenten häufig zusammen eingesetzt und sollten daher am besten ineinander eingeschliffen sein.
• Das Linux für den professionellen Einsatz besser geeignet ist als Windows, würde ich persönlich bejahen (ich bin Linux-biased). Aber da scheiden sich bekanntlich die Geister! Windows-Admins werden jetzt wahrscheinlich einen roten Kopf kriegen... :)
• Grundsätzlich denke ich aber, dass die Unterschiede zwischen Windows und Linux was Stabilität, Sicherheit und Performance anbelangen, heute marginal sind.
• Was die Sicherheit anbelangt ist sicher PHP das grösste Risiko (respektive das, was die Entwickler damit anstellen).
• Wenn Kunden ganz freaky Sachen machen wollen, denke ich, das Linux wegen seiner Flexibilität besser geeignet ist als Windows (siehe Facebook und Konsorten...).
• Meines Erachtens das Wichtigste Kriterium ist das Know-How sowohl im Betrieb als auch, eingeschränkt, in der Entwicklung. Ich denke, dass dieses den grössten Einfluss hat. Also: Wenn der Betrieb keine Ahnung von Linux hat: Finger davon lassen!
• Die Aussage des Lieferanten ist teilweise richtig. Früher lief MySQL Cluster ausschliesslich auf Linux (und anderen Unixoiden). Seit MySQL Cluster 7.1 wird aber mehr Gewicht auf Windows gelegt und Oracle hat MySQL Cluster 7.1 sogar als GA deklariert (GA = General Available = Production Ready). MySQL Support hat darüber aber die Nase gerümpft und den Kopf geschüttelt...
• Oracle positioniert ja das Produkt MySQL neu gegen Windows SQL-Server auf Windows und wird voraussichtlich sehr viel Entwicklungstätigkeit da rein stecken!
• Achtung: MySQL Cluster ist KEINE General-Purpose Datenbank. Man kann also NICHT einfach denken, ich baue meine Applikation und hänge dann einen MySQL Cluster drunter. Bei MySQL Cluster wird eine Applikation spezifisch auf dieses Produkt zugeschnitten! Anders verhält es sich mit einem geclusterten MySQL (MySQL HA, geht sowohl unter Windows als auch unter unixoiden Systemen).

Taxonomy upgrade extras: wamplampgerman

Taxonomy upgrade extras: wamplampgerman

Einer unserer Kunden hat mich kürzlich gefragt, was wir ihm empfehlen: WAMP oder LAMP? Also Windows oder Linux zusammen mit Apache, MySQL und PHP.

Im weiteren hat er angemerkt, sein Lieferant habe gesagt, dass MySQL Cluster NICHT auf Windows läuft...

Das ist eine nicht triviale Frage. Was wir in so einem Fall raten, ist folgende Punkte zu beachten:

• Ca. 80% der MySQL-Installationen laufen auf Linux! Es ist also auf Linux mit den wenigsten Problemen zu rechnen. Weitere Plattformen, welche man von Zeit zu Zeit bei Kunden antrifft sind: Windows, BSD-Derivate und Solaris (in etwa in dieser Reihenfolge).
• MySQL als Firma und die MySQL-Mitarbeiter haben am meisten Know-How auf Linux, Mac, Solaris und Windows (in etwa in dieser Reihenfolge).
• Also rein statistisch gesehen hat man mit Linux wahrscheinlich am wenigsten Ärger oder Probleme zu erwarten.
• Wie der Name LAMP schon sagt werden diese 4 Komponenten häufig zusammen eingesetzt und sollten daher am besten ineinander eingeschliffen sein.
• Das Linux für den professionellen Einsatz besser geeignet ist als Windows, würde ich persönlich bejahen (ich bin Linux-biased). Aber da scheiden sich bekanntlich die Geister! Windows-Admins werden jetzt wahrscheinlich einen roten Kopf kriegen... :)
• Grundsätzlich denke ich aber, dass die Unterschiede zwischen Windows und Linux was Stabilität, Sicherheit und Performance anbelangen, heute marginal sind.
• Was die Sicherheit anbelangt ist sicher PHP das grösste Risiko (respektive das, was die Entwickler damit anstellen).
• Wenn Kunden ganz freaky Sachen machen wollen, denke ich, das Linux wegen seiner Flexibilität besser geeignet ist als Windows (siehe Facebook und Konsorten...).
• Meines Erachtens das Wichtigste Kriterium ist das Know-How sowohl im Betrieb als auch, eingeschränkt, in der Entwicklung. Ich denke, dass dieses den grössten Einfluss hat. Also: Wenn der Betrieb keine Ahnung von Linux hat: Finger davon lassen!
• Die Aussage des Lieferanten ist teilweise richtig. Früher lief MySQL Cluster ausschliesslich auf Linux (und anderen Unixoiden). Seit MySQL Cluster 7.1 wird aber mehr Gewicht auf Windows gelegt und Oracle hat MySQL Cluster 7.1 sogar als GA deklariert (GA = General Available = Production Ready). MySQL Support hat darüber aber die Nase gerümpft und den Kopf geschüttelt...
• Oracle positioniert ja das Produkt MySQL neu gegen Windows SQL-Server auf Windows und wird voraussichtlich sehr viel Entwicklungstätigkeit da rein stecken!
• Achtung: MySQL Cluster ist KEINE General-Purpose Datenbank. Man kann also NICHT einfach denken, ich baue meine Applikation und hänge dann einen MySQL Cluster drunter. Bei MySQL Cluster wird eine Applikation spezifisch auf dieses Produkt zugeschnitten! Anders verhält es sich mit einem geclusterten MySQL (MySQL HA, geht sowohl unter Windows als auch unter unixoiden Systemen).

Bei einem Kunden sind wir auf ein nettes Beispiel gestossen, wie der MySQL Optimizer schummelt, wenn er mit dem MySQL Cluster zusammen arbeiten sollte. Dieser Kunde hat Abfragen, welche auf dem Entwicklungssystem nicht sonderlich langsam gelaufen sind. Aber wenn er diese Abfragen auf dem Abnahmetest-System (mit wesentlich mehr Daten) ausführt, benötigen diese viel zu viel Zeit, was für ihn nicht akzeptabel ist, da diese Abfragen mehrmals pro Sekunde abgesetzt werden können.

Was ist genau passiert?

Zu aller erst haben wir uns den Ausführungsplan der Abfragen angeschaut, welcher durch den MySQL Optimizer generiert wird:

EXPLAIN SELECT t0.*, t1.* FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where with pushed condition | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Der Ausführungsplan schaut auf den ersten Blick nahezu perfekt aus. 10 Datensätze für die erste Tabelle (t0) und anschliessend ein Nested Loop Join über t1 und t2. Aber das Slow Query Log zeigt ein etwas anderes Bild:

# Query_time: 8.784943 Lock_time: 0.000367 Rows_sent: 1 Rows_examined: 40052

40052 Datensätze werden angelangt (Rows_examined) für ein Resultat von nur einem EINZIGEN Datensatz (Rows_sent). Gemäss dem Ausführungsplan sollten wir nicht mehr als 10 bis 30 Datensätze anlangen müssen (hängt davon ab, wie gezählt wird).

Der SHOW SESSION STATUS Befehl vor und nach der Abfrage gibt uns folgende Information:

SHOW SESSION STATUS LIKE 'ndb%count';

Vorher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 0 | | Ndb_scan_count | 0 | +--------------------------------+-----------+

Nachher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 40052 | | Ndb_scan_count | 8 | +--------------------------------+-----------+
Ndb_execute_count: Zeigt die Anzahl der Round-Trips zum NDB Kernel, welche durch die Operationen durchgeführt wurden. [1] Ndb_scan_count: Diese Variable beinhaltet die Gesamtzahl ausgeführter Scans. [2]

Gemäss den GLOBAL STATUS VARIABLES verursachen wir 40052 Round-Trips zum Cluster und 8 Scans. Aus dem Ausführungsplan erwarte ich kleiner gleich 21 Round-Trips und 2 bis 3 Scans.

Mit dieser Information haben wir immherhin eine erste Spur gefunden, warum es so lange dauern könnte. Eine kleine Schätzung zeigt, dass wir auf der richtigen Fährte sind:

60 µs (ping) pro Round-Trip x 40052 Round-Trips = 2.4 Sekunden.

Mit 2.4 Sekunden sind wir zumindest bei der richtigen Grössenordnung für die Problemsuche angelangt.

Aber warum in aller Welt macht er 40k Round-Trips, wenn wir ca. 20 erwarten?

Versuchen wir händisch auszuführen, was der Optimizer tun sollte:

SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; 40044 rows in set (0.78 sec) +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | t0 | ref | ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+

Aha! Der Ausführungsplan schaut gleich aus. Und die Abfrage gibt 40044 Datensätze (in 0.78 s) zurück anstatt nur 10!!! Also hat uns der Optimizer angeschummelt!

SHOW SESSION STATUS zeigt:

Ndb_execute_count = 1 Ndb_scan_count = 1

Der zweite Schritt verursacht dann die grosse Anzahl von Round-Trips (und verbraucht die meiste Zeit):

SELECT COUNT(*) FROM t0 JOIN t1 ON t1.t0_id = t0.id WHERE t0.organization_id = 157 AND t0.type = 'User'; 7 rows in set (8.73 sec) Ndb_execute_count = 40044 Ndb_scan_count = 1

Der letzte Schritt verursacht weitere 7 Round-Trips (7 Datensätze aus Schritt 2) und 6 Scans:

SELECT COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) Ndb_execute_count = 7 Ndb_scan_count = 6

Schluss: Der MySQL Optimizer hat die falsche Entscheidung getroffen, als er mit Tabelle t0 anfing.

Wir werden selber Optimizer

Jetzt wollen wir es besser machen... Welche Möglichkeiten haben wir?

Wir könnten anfangen mit:

• Tabelle t2, indem wir eine Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessender Filterung auf dem Kriterium t2.productnumber LIKE '%3301', welches ein "schlechter" Filter ist, machen.
• Tabelle t0, indem wir einen Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessener Filterung auf organization_id = 157 ODER type = 'User' (zur Zeit gibt es KEINEN kombinierten Index auf beide Attribute zusammen) machen.
• Tabelle t1, indem wir einen Full-table-Scan machen (was der Cluster, so wie es aussieht, sowieso macht).

Somit suchen wir als besten Anfangspunk zuerst das kleinste Resultat, weil Round-Trips in MySQL Cluster Joins weh tun:

SELECT COUNT(*) FROM t2 WHERE t2.productnumber LIKE '%3301'; --> 1 Datensatz SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; --> 40044 Datensätze (das kennen wir bereits und wissen, dass es schlecht ist) SELECT COUNT(*) FROM t1; --> 325 Datensätze

Mit Tabelle t2 in dieser speziellen Abfrage anzufangen und einen Full-Table-Scan zu riskieren sollte eine wesentlich besser Antwortzeit geben. Mehr über diese Technik finden Sie unter [3].

Um zu sehen, ob unsere Idee eine bessere Antwortzeit ergibt, zwingen wir den MySQL Optimizer den STRAIGHT_JOIN [4] Hint zu verwenden:

SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t2.t0_id | 1 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count = 3 Ndb_scan_count = 1
SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.t0_id | 1 | | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where with pushed condition | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count 3 Ndb_scan_count 1

Es schaut so aus als ob Variante 2 und 3 mehr oder minder gleichwertig sind. Und wir haben die Abfrage von ca. 9 Sekunden auf ca. 20 ms (f=450!) verbessert.

Unglücklicherweise konnte der Kunde diese Lösung nicht akzeptieren, weil sein generisches Framework Abfrage-Hints nicht erlaubt und seine Applikation generisch auch für andere Datenbank-Backends sein soll.

Was mir als Alternative weiter in den Sinn kam, ist eine VIEW anzulegen um den Hint zu kapseln:

CREATE VIEW t2_t1_t0_v AS SELECT STRAIGHT_JOIN t0.*, t1.*, t2.productnumber FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id;

Aber es sieht danach aus, dass die Abfrage derart umgeschrieben wird, dass der Hint ignoriert wird:

EXPLAIN SELECT COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User'; +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Wenn die Abfrage wie folgt (mit Hint) abgesetzt wird, wird sie wieder in der richtigen Reihenfolge ausgeführt. Aber das wollen wir ja gerade wegen unserem Kunden vermeiden...

SELECT STRAIGHT_JOIN COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User';

Gemäss MySQL ist das kein Bug sonderen ein "Feature Request"... :-( Mehr Informationen können Sie finden unter MySQL Bug #28700.

Risiken

• Ein Problem mit Hints ist, das sie zukünftigen MySQL Releases/Features die Möglichkeit nehmen, Ihre volle Pracht zu entfalten, wenn Sie denn irgendwann einmal in ferner Zukunft implementiert werden sollten. Zum Beispiel wenn BKA (batched key access) [9] oder Push-Down-Joins [5] wahr werden oder wenn der Optimizer schlauer wird und die MySQL Cluster Datenverteilung kennt und damit besser Schätzungen macht. Wir verbauen uns damit möglicherweise besser Ausführungspläne in der Zukunft.
• Ein zweiter Problem ist, dass das Beginnen mit einem Full-Table-Scan auf t2 nicht skaliert, wenn die Menge der Daten in Zukunft grösser wird. An einem bestimmten Punkt wird der Full-Table-Scan langsamer werden als 40k Round-Trips.
• Was wir in diesem Artikel überhaupt noch nicht angeschaut haben, ist dass es noch grosses Verbesserungspotential bei der Daten- und Tabellenstruktur für das Filterkriterium auf t2 gibt. Eine Suche nach "%xyz% ist für die Datenbank schlecht und führt zu einem Scan. Wenn der Inhalt des Feldes gedreht wird in "zyx%' kann eine Suche den Index nutzen und ein Full-Table-Scan wird vermieden.
• Wenn der Trick mit der VIEW funktioniert hätte, gäbe es ein weiteres Problem: Weil die VIEW im SQL Node gespeichert wird und nicht im MySQL Cluster, muss sichergestellt werden, das ALLE SQL Nodes synchron gehalten werden und dass das Backup der VIEWs richtig gemacht wird. Dies ist nicht ganz einfach mit MySQL Cluster in Kombination mit Objekten, welche auf den SQL Nodes gespeichert sind.
• Wenn das Filterkriterium auf t2 nicht so selektiv wäre, würde der Trick so nicht funktionieren.

Was haben wir daraus gelernt?

• Die Menge der Testdaten ist relevant. Wenn Sie wesentlich mehr Daten auf dem Produktionssystem als auf dem Testsystem haben werden Sie nicht auf die Performance-Probleme stossen, bis sie Life gehen. Also füllen Sie Ihr Testsystem with der selbe Menge an Daten, wie Ihr Produktivsystem, oder zumindest Ihr Acceptance-Test-System.
• Der MySQL Optimizer schummelt, weil ihm die wirkliche Datenverteilung nicht bekannt ist. Der MySQL Optimizer arbeitet zur Zeit am besten mit MyISAM/Maria zusammen. Ein "guter" Ausführungsplan für die selbe Abfrage mit MyISAM/Maria Tabellen sieht wie folgt aus:
  +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 325 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 3 | Using where | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+
  

  Wir könnten auch diese Möglichkeit noch probieren...

• Die ndb_%count Status Variable ist irgendwie nicht korrekt implementiert oder die Dokumentation beschreibt nicht das aktuelle verhalten. Siehe auch MySQL bug #52940.
• Hints und VIEWs arbeiten nicht wie erwartet zusammen.
• MySQL Cluster führt mehr Scans als erwartet aus. Aber wir können dies zumindest mit der ndb_%count Status Variablen nachweisen.

Weitere Untersuchungen

In der Dokumentation sind einige Parameter zu finden (ndb_index_stat_cache_entries, ndb_index_stat_enable und ndb_index_stat_update_freq [7]) welche den Eindruck vermitteln, dass Sie hier helfen könnten. Aber wir haben keine Möglichkeit gefunden, wie wir mit diesen Variablen das Verhalten des MySQL Optimizers beeinflussen können. Wenn uns jemand einen Hinweis oder ein Beispiel dazu zukommen lässt, würde uns dies sehr freuen.

MySQL Cluster Beratung von FromDual

Wenn Sie mehr über MySQL Cluster und Abfrageoptimierung wissen möchten, würde es uns freuen, Sie als unseren Kunden besuchen zu dürfen.

Literatur [1] Ndb_execute_count
[2] Ndb_scan_count
[3] Dan Tow: SQL Tuning
[4] STRAIGHT_JOIN
[5] Trying out MySQL Push-Down-Join (SPJ) preview
[6] Bug #52940: ndb_*_count status variables ARE session related in contrary what docu says.
[7] ndb_index_stat%
[8] Lesen Sie auch den sehr informativen Artikel von Johan Andersson über das selbe Thema: Optimizing Queries on Cluster
[9] Batched Key Access (BKA) [10] How does the MySQL Optimizer work [11] Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN Taxonomy upgrade extras: mysqlviewMySQL ClusterhintOptimizergerman

Bei einem Kunden sind wir auf ein nettes Beispiel gestossen, wie der MySQL Optimizer schummelt, wenn er mit dem MySQL Cluster zusammen arbeiten sollte. Dieser Kunde hat Abfragen, welche auf dem Entwicklungssystem nicht sonderlich langsam gelaufen sind. Aber wenn er diese Abfragen auf dem Abnahmetest-System (mit wesentlich mehr Daten) ausführt, benötigen diese viel zu viel Zeit, was für ihn nicht akzeptabel ist, da diese Abfragen mehrmals pro Sekunde abgesetzt werden können.

Was ist genau passiert?

Zu aller erst haben wir uns den Ausführungsplan der Abfragen angeschaut, welcher durch den MySQL Optimizer generiert wird:

EXPLAIN SELECT t0.*, t1.* FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where with pushed condition | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Der Ausführungsplan schaut auf den ersten Blick nahezu perfekt aus. 10 Datensätze für die erste Tabelle (t0) und anschliessend ein Nested Loop Join über t1 und t2. Aber das Slow Query Log zeigt ein etwas anderes Bild:

# Query_time: 8.784943 Lock_time: 0.000367 Rows_sent: 1 Rows_examined: 40052

40052 Datensätze werden angelangt (Rows_examined) für ein Resultat von nur einem EINZIGEN Datensatz (Rows_sent). Gemäss dem Ausführungsplan sollten wir nicht mehr als 10 bis 30 Datensätze anlangen müssen (hängt davon ab, wie gezählt wird).

Der SHOW SESSION STATUS Befehl vor und nach der Abfrage gibt uns folgende Information:

SHOW SESSION STATUS LIKE 'ndb%count';

Vorher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 0 | | Ndb_scan_count | 0 | +--------------------------------+-----------+

Nachher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 40052 | | Ndb_scan_count | 8 | +--------------------------------+-----------+
Ndb_execute_count: Zeigt die Anzahl der Round-Trips zum NDB Kernel, welche durch die Operationen durchgeführt wurden. [1] Ndb_scan_count: Diese Variable beinhaltet die Gesamtzahl ausgeführter Scans. [2]

Gemäss den GLOBAL STATUS VARIABLES verursachen wir 40052 Round-Trips zum Cluster und 8 Scans. Aus dem Ausführungsplan erwarte ich kleiner gleich 21 Round-Trips und 2 bis 3 Scans.

Mit dieser Information haben wir immherhin eine erste Spur gefunden, warum es so lange dauern könnte. Eine kleine Schätzung zeigt, dass wir auf der richtigen Fährte sind:

60 µs (ping) pro Round-Trip x 40052 Round-Trips = 2.4 Sekunden.

Mit 2.4 Sekunden sind wir zumindest bei der richtigen Grössenordnung für die Problemsuche angelangt.

Aber warum in aller Welt macht er 40k Round-Trips, wenn wir ca. 20 erwarten?

Versuchen wir händisch auszuführen, was der Optimizer tun sollte:

SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; 40044 rows in set (0.78 sec) +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | t0 | ref | ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+

Aha! Der Ausführungsplan schaut gleich aus. Und die Abfrage gibt 40044 Datensätze (in 0.78 s) zurück anstatt nur 10!!! Also hat uns der Optimizer angeschummelt!

SHOW SESSION STATUS zeigt:

Ndb_execute_count = 1 Ndb_scan_count = 1

Der zweite Schritt verursacht dann die grosse Anzahl von Round-Trips (und verbraucht die meiste Zeit):

SELECT COUNT(*) FROM t0 JOIN t1 ON t1.t0_id = t0.id WHERE t0.organization_id = 157 AND t0.type = 'User'; 7 rows in set (8.73 sec) Ndb_execute_count = 40044 Ndb_scan_count = 1

Der letzte Schritt verursacht weitere 7 Round-Trips (7 Datensätze aus Schritt 2) und 6 Scans:

SELECT COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) Ndb_execute_count = 7 Ndb_scan_count = 6

Schluss: Der MySQL Optimizer hat die falsche Entscheidung getroffen, als er mit Tabelle t0 anfing.

Wir werden selber Optimizer

Jetzt wollen wir es besser machen... Welche Möglichkeiten haben wir?

Wir könnten anfangen mit:

• Tabelle t2, indem wir eine Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessender Filterung auf dem Kriterium t2.productnumber LIKE '%3301', welches ein "schlechter" Filter ist, machen.
• Tabelle t0, indem wir einen Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessener Filterung auf organization_id = 157 ODER type = 'User' (zur Zeit gibt es KEINEN kombinierten Index auf beide Attribute zusammen) machen.
• Tabelle t1, indem wir einen Full-table-Scan machen (was der Cluster, so wie es aussieht, sowieso macht).

Somit suchen wir als besten Anfangspunk zuerst das kleinste Resultat, weil Round-Trips in MySQL Cluster Joins weh tun:

SELECT COUNT(*) FROM t2 WHERE t2.productnumber LIKE '%3301'; --> 1 Datensatz SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; --> 40044 Datensätze (das kennen wir bereits und wissen, dass es schlecht ist) SELECT COUNT(*) FROM t1; --> 325 Datensätze

Mit Tabelle t2 in dieser speziellen Abfrage anzufangen und einen Full-Table-Scan zu riskieren sollte eine wesentlich besser Antwortzeit geben. Mehr über diese Technik finden Sie unter [3].

Um zu sehen, ob unsere Idee eine bessere Antwortzeit ergibt, zwingen wir den MySQL Optimizer den STRAIGHT_JOIN [4] Hint zu verwenden:

SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t2.t0_id | 1 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count = 3 Ndb_scan_count = 1
SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.t0_id | 1 | | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where with pushed condition | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count 3 Ndb_scan_count 1

Es schaut so aus als ob Variante 2 und 3 mehr oder minder gleichwertig sind. Und wir haben die Abfrage von ca. 9 Sekunden auf ca. 20 ms (f=450!) verbessert.

Unglücklicherweise konnte der Kunde diese Lösung nicht akzeptieren, weil sein generisches Framework Abfrage-Hints nicht erlaubt und seine Applikation generisch auch für andere Datenbank-Backends sein soll.

Was mir als Alternative weiter in den Sinn kam, ist eine VIEW anzulegen um den Hint zu kapseln:

CREATE VIEW t2_t1_t0_v AS SELECT STRAIGHT_JOIN t0.*, t1.*, t2.productnumber FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id;

Aber es sieht danach aus, dass die Abfrage derart umgeschrieben wird, dass der Hint ignoriert wird:

EXPLAIN SELECT COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User'; +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Wenn die Abfrage wie folgt (mit Hint) abgesetzt wird, wird sie wieder in der richtigen Reihenfolge ausgeführt. Aber das wollen wir ja gerade wegen unserem Kunden vermeiden...

SELECT STRAIGHT_JOIN COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User';

Gemäss MySQL ist das kein Bug sonderen ein "Feature Request"... :-( Mehr Informationen können Sie finden unter MySQL Bug #28700.

Risiken

• Ein Problem mit Hints ist, das sie zukünftigen MySQL Releases/Features die Möglichkeit nehmen, Ihre volle Pracht zu entfalten, wenn Sie denn irgendwann einmal in ferner Zukunft implementiert werden sollten. Zum Beispiel wenn BKA (batched key access) [9] oder Push-Down-Joins [5] wahr werden oder wenn der Optimizer schlauer wird und die MySQL Cluster Datenverteilung kennt und damit besser Schätzungen macht. Wir verbauen uns damit möglicherweise besser Ausführungspläne in der Zukunft.
• Ein zweiter Problem ist, dass das Beginnen mit einem Full-Table-Scan auf t2 nicht skaliert, wenn die Menge der Daten in Zukunft grösser wird. An einem bestimmten Punkt wird der Full-Table-Scan langsamer werden als 40k Round-Trips.
• Was wir in diesem Artikel überhaupt noch nicht angeschaut haben, ist dass es noch grosses Verbesserungspotential bei der Daten- und Tabellenstruktur für das Filterkriterium auf t2 gibt. Eine Suche nach "%xyz% ist für die Datenbank schlecht und führt zu einem Scan. Wenn der Inhalt des Feldes gedreht wird in "zyx%' kann eine Suche den Index nutzen und ein Full-Table-Scan wird vermieden.
• Wenn der Trick mit der VIEW funktioniert hätte, gäbe es ein weiteres Problem: Weil die VIEW im SQL Node gespeichert wird und nicht im MySQL Cluster, muss sichergestellt werden, das ALLE SQL Nodes synchron gehalten werden und dass das Backup der VIEWs richtig gemacht wird. Dies ist nicht ganz einfach mit MySQL Cluster in Kombination mit Objekten, welche auf den SQL Nodes gespeichert sind.
• Wenn das Filterkriterium auf t2 nicht so selektiv wäre, würde der Trick so nicht funktionieren.

Was haben wir daraus gelernt?

• Die Menge der Testdaten ist relevant. Wenn Sie wesentlich mehr Daten auf dem Produktionssystem als auf dem Testsystem haben werden Sie nicht auf die Performance-Probleme stossen, bis sie Life gehen. Also füllen Sie Ihr Testsystem with der selbe Menge an Daten, wie Ihr Produktivsystem, oder zumindest Ihr Acceptance-Test-System.
• Der MySQL Optimizer schummelt, weil ihm die wirkliche Datenverteilung nicht bekannt ist. Der MySQL Optimizer arbeitet zur Zeit am besten mit MyISAM/Maria zusammen. Ein "guter" Ausführungsplan für die selbe Abfrage mit MyISAM/Maria Tabellen sieht wie folgt aus:
  +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 325 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 3 | Using where | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+
  

  Wir könnten auch diese Möglichkeit noch probieren...

• Die ndb_%count Status Variable ist irgendwie nicht korrekt implementiert oder die Dokumentation beschreibt nicht das aktuelle verhalten. Siehe auch MySQL bug #52940.
• Hints und VIEWs arbeiten nicht wie erwartet zusammen.
• MySQL Cluster führt mehr Scans als erwartet aus. Aber wir können dies zumindest mit der ndb_%count Status Variablen nachweisen.

Weitere Untersuchungen

In der Dokumentation sind einige Parameter zu finden (ndb_index_stat_cache_entries, ndb_index_stat_enable und ndb_index_stat_update_freq [7]) welche den Eindruck vermitteln, dass Sie hier helfen könnten. Aber wir haben keine Möglichkeit gefunden, wie wir mit diesen Variablen das Verhalten des MySQL Optimizers beeinflussen können. Wenn uns jemand einen Hinweis oder ein Beispiel dazu zukommen lässt, würde uns dies sehr freuen.

MySQL Cluster Beratung von FromDual

Wenn Sie mehr über MySQL Cluster und Abfrageoptimierung wissen möchten, würde es uns freuen, Sie als unseren Kunden besuchen zu dürfen.

Literatur [1] Ndb_execute_count
[2] Ndb_scan_count
[3] Dan Tow: SQL Tuning
[4] STRAIGHT_JOIN
[5] Trying out MySQL Push-Down-Join (SPJ) preview
[6] Bug #52940: ndb_*_count status variables ARE session related in contrary what docu says.
[7] ndb_index_stat%
[8] Lesen Sie auch den sehr informativen Artikel von Johan Andersson über das selbe Thema: Optimizing Queries on Cluster
[9] Batched Key Access (BKA) [10] How does the MySQL Optimizer work [11] Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN Taxonomy upgrade extras: mysqlviewMySQL ClusterhintOptimizergerman

Taxonomy upgrade extras: mysqlviewMySQL ClusterhintOptimizergerman

Bei einem Kunden sind wir auf ein nettes Beispiel gestossen, wie der MySQL Optimizer schummelt, wenn er mit dem MySQL Cluster zusammen arbeiten sollte. Dieser Kunde hat Abfragen, welche auf dem Entwicklungssystem nicht sonderlich langsam gelaufen sind. Aber wenn er diese Abfragen auf dem Abnahmetest-System (mit wesentlich mehr Daten) ausführt, benötigen diese viel zu viel Zeit, was für ihn nicht akzeptabel ist, da diese Abfragen mehrmals pro Sekunde abgesetzt werden können.

Was ist genau passiert?

Zu aller erst haben wir uns den Ausführungsplan der Abfragen angeschaut, welcher durch den MySQL Optimizer generiert wird:

EXPLAIN SELECT t0.*, t1.* FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where with pushed condition | +-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Der Ausführungsplan schaut auf den ersten Blick nahezu perfekt aus. 10 Datensätze für die erste Tabelle (t0) und anschliessend ein Nested Loop Join über t1 und t2. Aber das Slow Query Log zeigt ein etwas anderes Bild:

# Query_time: 8.784943 Lock_time: 0.000367 Rows_sent: 1 Rows_examined: 40052

40052 Datensätze werden angelangt (Rows_examined) für ein Resultat von nur einem EINZIGEN Datensatz (Rows_sent). Gemäss dem Ausführungsplan sollten wir nicht mehr als 10 bis 30 Datensätze anlangen müssen (hängt davon ab, wie gezählt wird).

Der SHOW SESSION STATUS Befehl vor und nach der Abfrage gibt uns folgende Information:

SHOW SESSION STATUS LIKE 'ndb%count';

Vorher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 0 | | Ndb_scan_count | 0 | +--------------------------------+-----------+

Nachher:

+--------------------------------+-----------+ | Variable_name | Value | +--------------------------------+-----------+ | Ndb_execute_count | 40052 | | Ndb_scan_count | 8 | +--------------------------------+-----------+
Ndb_execute_count: Zeigt die Anzahl der Round-Trips zum NDB Kernel, welche durch die Operationen durchgeführt wurden. [1] Ndb_scan_count: Diese Variable beinhaltet die Gesamtzahl ausgeführter Scans. [2]

Gemäss den GLOBAL STATUS VARIABLES verursachen wir 40052 Round-Trips zum Cluster und 8 Scans. Aus dem Ausführungsplan erwarte ich kleiner gleich 21 Round-Trips und 2 bis 3 Scans.

Mit dieser Information haben wir immherhin eine erste Spur gefunden, warum es so lange dauern könnte. Eine kleine Schätzung zeigt, dass wir auf der richtigen Fährte sind:

60 µs (ping) pro Round-Trip x 40052 Round-Trips = 2.4 Sekunden.

Mit 2.4 Sekunden sind wir zumindest bei der richtigen Grössenordnung für die Problemsuche angelangt.

Aber warum in aller Welt macht er 40k Round-Trips, wenn wir ca. 20 erwarten?

Versuchen wir händisch auszuführen, was der Optimizer tun sollte:

SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; 40044 rows in set (0.78 sec) +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+ | t0 | ref | ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | +-------+------+---------------+--------+---------+-------+------+-----------------------------------+

Aha! Der Ausführungsplan schaut gleich aus. Und die Abfrage gibt 40044 Datensätze (in 0.78 s) zurück anstatt nur 10!!! Also hat uns der Optimizer angeschummelt!

SHOW SESSION STATUS zeigt:

Ndb_execute_count = 1 Ndb_scan_count = 1

Der zweite Schritt verursacht dann die grosse Anzahl von Round-Trips (und verbraucht die meiste Zeit):

SELECT COUNT(*) FROM t0 JOIN t1 ON t1.t0_id = t0.id WHERE t0.organization_id = 157 AND t0.type = 'User'; 7 rows in set (8.73 sec) Ndb_execute_count = 40044 Ndb_scan_count = 1

Der letzte Schritt verursacht weitere 7 Round-Trips (7 Datensätze aus Schritt 2) und 6 Scans:

SELECT COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (8.78 sec) Ndb_execute_count = 7 Ndb_scan_count = 6

Schluss: Der MySQL Optimizer hat die falsche Entscheidung getroffen, als er mit Tabelle t0 anfing.

Wir werden selber Optimizer

Jetzt wollen wir es besser machen... Welche Möglichkeiten haben wir?

Wir könnten anfangen mit:

• Tabelle t2, indem wir eine Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessender Filterung auf dem Kriterium t2.productnumber LIKE '%3301', welches ein "schlechter" Filter ist, machen.
• Tabelle t0, indem wir einen Full-Table-Scan mit anschliessender Filterung oder einen Index-Scan mit anschliessener Filterung auf organization_id = 157 ODER type = 'User' (zur Zeit gibt es KEINEN kombinierten Index auf beide Attribute zusammen) machen.
• Tabelle t1, indem wir einen Full-table-Scan machen (was der Cluster, so wie es aussieht, sowieso macht).

Somit suchen wir als besten Anfangspunk zuerst das kleinste Resultat, weil Round-Trips in MySQL Cluster Joins weh tun:

SELECT COUNT(*) FROM t2 WHERE t2.productnumber LIKE '%3301'; --> 1 Datensatz SELECT COUNT(*) FROM t0 WHERE t0.organization_id = 157 AND t0.type = 'User'; --> 40044 Datensätze (das kennen wir bereits und wissen, dass es schlecht ist) SELECT COUNT(*) FROM t1; --> 325 Datensätze

Mit Tabelle t2 in dieser speziellen Abfrage anzufangen und einen Full-Table-Scan zu riskieren sollte eine wesentlich besser Antwortzeit geben. Mehr über diese Technik finden Sie unter [3].

Um zu sehen, ob unsere Idee eine bessere Antwortzeit ergibt, zwingen wir den MySQL Optimizer den STRAIGHT_JOIN [4] Hint zu verwenden:

SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t0 ON t2.t0_id = t0.id JOIN t1 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t2.t0_id | 1 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count = 3 Ndb_scan_count = 1
SELECT STRAIGHT_JOIN COUNT(*) FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id WHERE t2.productnumber LIKE '%3301' AND t0.organization_id = 157 AND t0.type = 'User'; 1 row in set (0.02 sec) +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t2 | ALL | PRODUCTNUMBER | NULL | NULL | NULL | 1099 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t2.t0_id | 1 | | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where with pushed condition | +----+-------------+-------+--------+---------------------+---------+---------+----------+------+-----------------------------------+ Ndb_execute_count 3 Ndb_scan_count 1

Es schaut so aus als ob Variante 2 und 3 mehr oder minder gleichwertig sind. Und wir haben die Abfrage von ca. 9 Sekunden auf ca. 20 ms (f=450!) verbessert.

Unglücklicherweise konnte der Kunde diese Lösung nicht akzeptieren, weil sein generisches Framework Abfrage-Hints nicht erlaubt und seine Applikation generisch auch für andere Datenbank-Backends sein soll.

Was mir als Alternative weiter in den Sinn kam, ist eine VIEW anzulegen um den Hint zu kapseln:

CREATE VIEW t2_t1_t0_v AS SELECT STRAIGHT_JOIN t0.*, t1.*, t2.productnumber FROM t2 JOIN t1 ON t1.t0_id = t2.t0_id JOIN t0 ON t1.t0_id = t0.id;

Aber es sieht danach aus, dass die Abfrage derart umgeschrieben wird, dass der Hint ignoriert wird:

EXPLAIN SELECT COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User'; +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+ | 1 | SIMPLE | t0 | ref | PRIMARY,ORG_ID,TYPE | ORG_ID | 5 | const | 10 | Using where with pushed condition | | 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | t0.id | 1 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-----------------------------------+

Wenn die Abfrage wie folgt (mit Hint) abgesetzt wird, wird sie wieder in der richtigen Reihenfolge ausgeführt. Aber das wollen wir ja gerade wegen unserem Kunden vermeiden...

SELECT STRAIGHT_JOIN COUNT(*) FROM t2_t1_t0_v WHERE productnumber LIKE '%3301' AND organization_id = 157 AND type = 'User';

Gemäss MySQL ist das kein Bug sonderen ein "Feature Request"... :-( Mehr Informationen können Sie finden unter MySQL Bug #28700.

Risiken

• Ein Problem mit Hints ist, das sie zukünftigen MySQL Releases/Features die Möglichkeit nehmen, Ihre volle Pracht zu entfalten, wenn Sie denn irgendwann einmal in ferner Zukunft implementiert werden sollten. Zum Beispiel wenn BKA (batched key access) [9] oder Push-Down-Joins [5] wahr werden oder wenn der Optimizer schlauer wird und die MySQL Cluster Datenverteilung kennt und damit besser Schätzungen macht. Wir verbauen uns damit möglicherweise besser Ausführungspläne in der Zukunft.
• Ein zweiter Problem ist, dass das Beginnen mit einem Full-Table-Scan auf t2 nicht skaliert, wenn die Menge der Daten in Zukunft grösser wird. An einem bestimmten Punkt wird der Full-Table-Scan langsamer werden als 40k Round-Trips.
• Was wir in diesem Artikel überhaupt noch nicht angeschaut haben, ist dass es noch grosses Verbesserungspotential bei der Daten- und Tabellenstruktur für das Filterkriterium auf t2 gibt. Eine Suche nach "%xyz% ist für die Datenbank schlecht und führt zu einem Scan. Wenn der Inhalt des Feldes gedreht wird in "zyx%' kann eine Suche den Index nutzen und ein Full-Table-Scan wird vermieden.
• Wenn der Trick mit der VIEW funktioniert hätte, gäbe es ein weiteres Problem: Weil die VIEW im SQL Node gespeichert wird und nicht im MySQL Cluster, muss sichergestellt werden, das ALLE SQL Nodes synchron gehalten werden und dass das Backup der VIEWs richtig gemacht wird. Dies ist nicht ganz einfach mit MySQL Cluster in Kombination mit Objekten, welche auf den SQL Nodes gespeichert sind.
• Wenn das Filterkriterium auf t2 nicht so selektiv wäre, würde der Trick so nicht funktionieren.

Was haben wir daraus gelernt?

• Die Menge der Testdaten ist relevant. Wenn Sie wesentlich mehr Daten auf dem Produktionssystem als auf dem Testsystem haben werden Sie nicht auf die Performance-Probleme stossen, bis sie Life gehen. Also füllen Sie Ihr Testsystem with der selbe Menge an Daten, wie Ihr Produktivsystem, oder zumindest Ihr Acceptance-Test-System.
• Der MySQL Optimizer schummelt, weil ihm die wirkliche Datenverteilung nicht bekannt ist. Der MySQL Optimizer arbeitet zur Zeit am besten mit MyISAM/Maria zusammen. Ein "guter" Ausführungsplan für die selbe Abfrage mit MyISAM/Maria Tabellen sieht wie folgt aus:
  +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+ | 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 325 | | | 1 | SIMPLE | t2 | ref | PRODUCTNUMBER | PRODUCTNUMBER | 5 | t1.t0_id | 3 | Using where | | 1 | SIMPLE | t0 | eq_ref | PRIMARY,ORG_ID,TYPE | PRIMARY | 4 | t1.t0_id | 1 | Using where | +----+-------------+-------+--------+---------------------+---------------+---------+----------+------+-------------+
  

  Wir könnten auch diese Möglichkeit noch probieren...

• Die ndb_%count Status Variable ist irgendwie nicht korrekt implementiert oder die Dokumentation beschreibt nicht das aktuelle verhalten. Siehe auch MySQL bug #52940.
• Hints und VIEWs arbeiten nicht wie erwartet zusammen.
• MySQL Cluster führt mehr Scans als erwartet aus. Aber wir können dies zumindest mit der ndb_%count Status Variablen nachweisen.

Weitere Untersuchungen

In der Dokumentation sind einige Parameter zu finden (ndb_index_stat_cache_entries, ndb_index_stat_enable und ndb_index_stat_update_freq [7]) welche den Eindruck vermitteln, dass Sie hier helfen könnten. Aber wir haben keine Möglichkeit gefunden, wie wir mit diesen Variablen das Verhalten des MySQL Optimizers beeinflussen können. Wenn uns jemand einen Hinweis oder ein Beispiel dazu zukommen lässt, würde uns dies sehr freuen.

MySQL Cluster Beratung von FromDual

Wenn Sie mehr über MySQL Cluster und Abfrageoptimierung wissen möchten, würde es uns freuen, Sie als unseren Kunden besuchen zu dürfen.

Literatur [1] Ndb_execute_count
[2] Ndb_scan_count
[3] Dan Tow: SQL Tuning
[4] STRAIGHT_JOIN
[5] Trying out MySQL Push-Down-Join (SPJ) preview
[6] Bug #52940: ndb_*_count status variables ARE session related in contrary what docu says.
[7] ndb_index_stat%
[8] Lesen Sie auch den sehr informativen Artikel von Johan Andersson über das selbe Thema: Optimizing Queries on Cluster
[9] Batched Key Access (BKA) [10] How does the MySQL Optimizer work [11] Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN