Frage Welche Konfigurationsoptionen für MySQL bieten die größten Geschwindigkeitsverbesserungen?


Welche Konfigurationsoptionen für MySQL bieten die größten Geschwindigkeitsverbesserungen?

Ich frage mich über tatsächliche Konfigurationsdatei Verbesserungen, Tabellentypen, Hardware-Setups, Replikation, etc. Alles andere als die Struktur der Abfrage und Tabelle (diese sind leicht auf der Website und Stack Overflow zu finden). Sind Dinge wie Abfrage-Cache-Einstellungen, was gab Ihnen die höchste Geschwindigkeit? Wie wäre es mit Antrieben? ist es besser, es auf einem externen RAID oder intern zu haben? Hat die Replikation Ihnen eine bessere Leistung gebracht, insbesondere beim Lesen großer Abfragen?

Welche anderen Einstellungen / Änderungen haben Sie vorgenommen, um die Leistung von MySQL zu verbessern?

Hinweis: Ich weiß, dass diese sehr abhängig von der Nutzung sind (dh kleine Website vs Data Warehouse), aber wie ich denke, die meisten von uns arbeiten wahrscheinlich auf einer Vielzahl von Websites / Systemen, ist es gut, eine Vielzahl von Techniken kennen zu lernen Situationen. Außerdem denke ich, dass einige Techniken zwischen Situationen übertragen werden können.


29


Ursprung


Nicht vollständig verwandt, aber Sie sollten InnoDB für den Master verwenden. Sie können auf MyISAM-Slaves replizieren und deren integrierte Volltextsuche verwenden, wodurch Text-Lookups wesentlich schneller als LIKE ausgeführt werden können - Neil McGuigan


Antworten:


Hier sind meine Empfehlungen (Ihre Millage kann variieren)

  • Verwenden Sie Hardware-RAID. Dies steht im Gegensatz zu meinen Empfehlungen, Software-RAID in anderen Posts zu verwenden. Dies ist jedoch eine spezielle Situation, in der Sie die Hardware-RAID-Karte verwenden möchten. Insbesondere möchten Sie das batteriegestützte NVRAM auf der RAID-Karte, um die Zeit für die fsync die Protokolldatei auf die Festplatte zu reduzieren.
  • Verwenden Sie NUR RAID 1- oder RAID 10-Volumes. Die Kosten für RAID 5 oder 6-Schreibvorgänge sind zu hoch, um sie in einer gemischten Lese- / Schreib-Arbeitslast zu tolerieren.
  • Verwenden Sie separate LUNs für die Daten-, Protokoll- und Tmp-Volumes. Diese sollten alle von den Betriebssystem- und Swap-Volumes getrennt sein.
  • Benutzen InnoDB.
  • Verwenden Sie innodb_file_per_table
  • Verwenden Sie ein 64-Bit-Betriebssystem
  • Setzen Sie Ihren InnoDB-Pufferpool auf ~ 80% Ihres verfügbaren Arbeitsspeichers
  • Legen Sie Ihre Protokolldateien auf 1/4 der Größe Ihres Pufferpools fest, dh zwischen 2 und 4 Protokolldateien. Größere Protokolldateien bedeuten langsamere Zeiten für das Herunterfahren und Wiederherstellen, ermöglichen es Ihnen jedoch, große Datenbankabbilder schneller wiederherzustellen.
  • log_slow_queries, log-queries-not-using-indexes, set-variable = long_query_time = 1, untersuchen Sie jede Abfrage in diesem Protokoll, refaktorieren Sie Ihr Schema, um Tabellen-Scans und tmp-Tabellen möglichst zu vermeiden.

20





Wieder einmal hat es Dave Cheney wirklich aus dem Park geschafft. Ich kann wirklich nichts zu seiner Antwort auf Ihre Frage hinzufügen. Ich möchte jedoch darauf hinweisen, was Sie nicht gefragt haben. Wie mir Jeremy Zawodny und Peter Zaitsev vor Jahren beigebracht haben, wird Ihr ROI für die Zeit, die Sie für das Aufspüren und Optimieren von fehlerhaften Abfragen aufgewendet haben, Ihren ROI für die Zeit, die für Konfigurationsänderungen benötigt wird, um das Zehnfache reduzieren. Sicher, Sie möchten keine schlechte Konfiguration, kein falsches RAID-Setup oder nicht genügend RAM. Aber unter den exzellenten und sogar marginalen MySQL DBAs sind schlechte Abfragen (normalerweise von Entwicklern / Frameworks, nicht vom DBA) chronisch Bedingung, wo schlechte Konfiguration ist erträglich ein.

(Ich habe eine Weile nach diesen Adjektiven gegraben und bin immer noch nicht zufrieden mit denen, die ich ausgewählt habe.)

Ich möchte noch einmal betonen, dass, wenn Ihre Entwickler ein ORM verwenden, wie es in Frameworks wie Ruby on Rails und Django üblich ist, Sie wirklich die Abfragen überwachen müssen, die Ihre DB treffen. Wenn Entwickler aufhören, über SQL nachzudenken und die DB abstrahieren lassen, dann schleichen sich diese wirklich ein. Ich liebe die beiden Frameworks, die ich gerade erwähnt habe. (Wählen Sie mich nicht wegen schlechtem Sprechen ab.) Das macht Query Sleuthing sehr wichtig. (Lesen Sie: Arbeitssicherheit)


11





Wenige andere Dinge (die in Dave Cheneys Antwort nicht erwähnt wurden)

  • Versuchen Sie, innodb_flush_method auf O_DIRECT zu setzen, um eine doppelte Pufferung der Daten zu vermeiden. Vermeiden Sie dies, wenn Ihre RAID-Karte keinen schreibgeschützten Schreibcache hat oder Ihre Daten sich in einem SAN befinden.

  • Spielen Sie auch mit innodb_thread_concurrency. Ich glaube, dass der Standardwert 8 ist, aber es lohnt sich, dies zu optimieren, um zu sehen, ob es die Leistung verbessert

  • Stellen Sie sicher, dass der Abfragecache aktiviert ist, und überprüfen Sie die Statistiken, um festzustellen, wie hoch die Trefferquote ist. Wenn es gut ist, versuche es zu erhöhen, um zu sehen, ob es die Trefferquote verbessert.

  • Abhängig von den ausgeführten Anwendungen können Sie möglicherweise die Standardisolationsstufe ändern. Der Standardwert ist REPEATABLE_READ, aber READ_COMMITTED bietet möglicherweise eine bessere Leistung

  • Wenn Ihre Anweisungen hauptsächlich UPDATEs und DELETEs sind, könnten Sie versuchen, den Cache auf dem Slave zu initialisieren, indem Sie eine SELECT-Abfrage ausführen, die die Ergebnismenge zurückgibt, die geändert werden soll. Besuche die mk-slave-prefetch Werkzeug, das dies für Sie tun wird

  • Sehen Sie sich neben MyISAM und InnoDB auch andere Speicher-Engines an


4





Kann nichts über Hardware sagen, aber Sie könnten geben http://blog.mysqltuner.com ein Versuch. Es ist ein Perl-Skript, das Ihre MySQL-Einstellungen analysiert.

Sie können eine Beispielausgabe an sehen http://www.thomas-krenn.com/de/wiki/MySQL_Performance_Tuning#mysqltuner.pl


3





Die erste allgemeine Sache, die Sie tun sollten, ist die Speicherparameter zu betrachten. Die Standardeinstellungen für MySQL sind sehr konservativ. Egal welche Engine Sie verwenden, Sie müssen wahrscheinlich eine Anzahl der Speicherparameter um das Zehn- oder sogar Hundertfache erhöhen.

Als nächstes sollten Sie sich den Tabellen-Cache ansehen. Der Standardwert ist 64, was nur nützlich ist, wenn Sie nicht mehr als 60 Tabellen haben. Du wirst das sehr weit bringen wollen.

Als drittes sollten Sie sich die Thread- und Verbindungsparameter ansehen. Der Standardwert wait_timeout ist für die meisten webbasierten Anwendungen sehr lang und kann auf etwa 30 Sekunden reduziert werden. Dies wird auch die Speichernutzung verbessern, da MySQL Verbindungen früher ernten wird und viel weniger in einem 'Schlaf' Zustand herumliegen wird.


1