Teilen Sie 101 MySQL-Debugging- und Optimierungstipps

MySQL ist eine leistungsstarke Open-Source-Datenbank. Mit der wachsenden Anzahl datenbankbasierter Anwendungen stößt MySQL an seine Grenzen. Hier sind 101 Tipps zum Tuning und Optimieren Ihrer MySQL-Installation. Einige der Tipps beziehen sich speziell auf Ihre Installation, die Ideen sind jedoch allgemeiner Natur. Ich habe sie in mehrere Kategorien unterteilt, um Ihnen dabei zu helfen, mehr MySQL-Tuning- und Optimierungstechniken zu beherrschen.

MySQL Server-Hardware- und Betriebssystemoptimierung:

1. Sorgen Sie für ausreichend physischen Speicher, um die gesamte InnoDB-Datei in den Speicher zu laden. Der Zugriff auf Dateien im Speicher ist viel schneller als der Zugriff auf sie von der Festplatte.
2. Vermeiden Sie unbedingt die Verwendung von Swap-Partitionen – Swap wird von der Festplatte gelesen, was langsam ist.
3. Verwenden Sie batteriebetriebenen RAM (Hinweis: RAM steht für Random Access Memory).
4. Verwenden Sie erweitertes RAID (Hinweis: Redundant Arrays of Inexpensive Disks) – vorzugsweise RAID10 oder höher.
5. Vermeiden Sie RAID5 (Hinweis: eine Speicherlösung, die Speicherleistung, Datensicherheit und Speicherkosten berücksichtigt) – Die Sicherstellung der Überprüfung der Datenbankintegrität hat ihren Preis.
6. Trennen Sie Betriebssystem- und Datenpartitionen nicht nur logisch, sondern auch physisch – die Lese- und Schreibvorgänge des Betriebssystems wirken sich auf die Leistung der Datenbank aus.
7. Legen Sie temporären MySQL-Speicherplatz und Replikationsprotokolle auf anderen Partitionen ab als die Daten. Dies beeinträchtigt die Datenbankleistung, wenn das Datenbank-Backend Daten auf der Festplatte liest und schreibt.
8. Mehr Speicherplatz bedeutet mehr Geschwindigkeit.
9. Bessere und schnellere Festplatten.
10. Verwenden Sie SAS (Hinweis: Serial Attached SCSI) statt SATA (Hinweis: SATA, d. h. Festplatte mit seriellem Anschluss).
11. Kleinere Festplatten sind schneller als größere Festplatten, insbesondere in RAID-Konfigurationen.
12. Verwenden Sie einen batteriegepufferten Cache-RAID-Controller.
13. Vermeiden Sie die Verwendung von Software-Festplatten-Arrays.
14. Erwägen Sie die Verwendung von Solid-State-IO-Karten (keine Festplattenlaufwerke) für Datenpartitionen – diese Karten unterstützen Schreibgeschwindigkeiten von 2 GB/s für nahezu jede Datenmenge.
15. Setzen Sie die Swappiness in Linux auf 0 – Es gibt keinen Grund, Dateien in einem Datenbankserver zwischenzuspeichern, dies ist ein Vorteil für einen Server oder Desktop.
16. Mounten Sie Dateisysteme wenn möglich mit noatime und nodirtime – es gibt keinen Grund, Änderungszeiten beim Zugriff auf Datenbankdateien zu aktualisieren.
17. Verwenden Sie das XFS-Dateisystem – ein schnelleres und kleineres Dateisystem als ext3 mit vielen Journaling-Optionen. Bei ext3 hat sich gezeigt, dass es mit MySQL Probleme mit der Doppelpufferung gibt.
18. Optimieren Sie die Journaling- und Puffervariablen des XFS-Dateisystems – für höchste Leistungsstandards.
19. Verwenden Sie unter Linux den NOOP- oder DEADLINE-IO-Scheduler – im Vergleich zu den NOOP- und DEADLINE-Schedulern sind die CFQ- und ANTICIPATORY-Scheduler sehr langsam.
20. Verwenden Sie ein 64-Bit-Betriebssystem – MySQL bietet mehr Speicherunterstützung und -nutzung.
21. Entfernen Sie nicht verwendete Pakete und Daemons auf dem Server – weniger Ressourcenverbrauch.
22. Legen Sie den Host, der MySQL verwendet, und Ihren MySQL-Host in eine Hosts-Datei – keine DNS-Suche.
23. Beenden Sie niemals zwangsweise einen MySQL-Prozess. Dadurch beschädigen Sie die Datenbank und den laufenden Sicherungsvorgang.
24. Reservieren Sie Server für MySQL – Hintergrundprozesse und andere Dienste können die Zeit reduzieren, in der die Datenbank die CPU nutzt.

MySQL-Konfiguration:

25. Verwenden Sie beim Schreiben innodb_flush_method=O_DIRECT, um doppelte Pufferung zu vermeiden.
26. Vermeiden Sie die Verwendung von O_DIRECT und des EXT3-Dateisystems – Sie serialisieren alle Schreibvorgänge.
27. Weisen Sie genügend innodb_buffer_pool_size zu, um die gesamte InnoDB-Datei in den Speicher zu laden – lesen Sie weniger von der Festplatte.
28. Stellen Sie den Parameter innodb_log_file_size nicht zu groß ein. Dadurch ist eine schnellere Ausführung und mehr Speicherplatz möglich. Das Löschen weiterer Protokolle ist normalerweise eine gute Sache, da dadurch die Zeit zum Wiederherstellen der Datenbank nach einem Absturz verkürzt wird.
29. Mischen Sie die Parameter innodb_thread_concurrency und thread_concurrency nicht – diese beiden Werte sind nicht kompatibel.
30. Weisen Sie dem Parameter max_connections eine sehr kleine Zahl zu – zu viele Verbindungen können RAM verbrauchen und den MySQL-Server blockieren.
31. Halten Sie den Thread_Cache auf einem relativ hohen Wert, etwa 16, um eine Verlangsamung beim Öffnen von Verbindungen zu vermeiden.
32. Verwenden Sie den Parameter „skip-name-resolve“, um die DNS-Suche zu entfernen.
33. Wenn Ihre Abfragen sich wiederholen und die Daten sich nicht oft ändern, können Sie die Abfrage-Zwischenspeicherung verwenden. Wenn sich Ihre Daten jedoch häufig ändern, wird die Verwendung des Abfragecaches Sie frustrieren.
34. Erhöhen Sie den Wert temp_table_size, um das Schreiben auf die Festplatte zu verhindern
35. Erhöhen Sie max_heap_table_size, um das Schreiben auf die Festplatte zu verhindern
36. Setzen Sie den Wert für sort_buffer_size nicht zu hoch, da Ihnen sonst schnell der Speicher ausgeht.
37. Bestimmen Sie die Größe des key_buffer basierend auf den Werten key_read_requests und key_reads. Im Allgemeinen sollten key_read_requests höher sein als der Wert key_reads, da Sie den key_buffer sonst nicht effizient nutzen können.
38. Wenn Sie innodb_flush_log_at_trx_commit auf 0 setzen, verbessert sich die Leistung. Wenn Sie jedoch den Standardwert (1) beibehalten, müssen Sie die Datenintegrität sicherstellen und dafür sorgen, dass die Replikation nicht ins Stocken gerät.
39. Sie benötigen eine Testumgebung, um Ihre Konfiguration zu testen und sie häufig neu zu starten, ohne die normale Produktion zu beeinträchtigen.

Optimierung des MySQL-Modus:

40. Halten Sie Ihre Datenbank organisiert.
41. Archivierung alter Daten – Entfernen redundanter zurückgegebener Zeilen oder Suchanfragen.
42. Indizieren Sie Ihre Daten.
43. Verwenden Sie Indizes, Vergleiche und Abfragen nicht zu häufig.
44. Komprimieren Sie die Datentypen TEXT und BLOB – um Speicherplatz zu sparen und die Anzahl der Festplattenlesevorgänge zu reduzieren.
45. Sowohl UTF 8 als auch UTF16 sind weniger effizient als Latin1.
46. ​​Verwenden Sie Auslöser sparsam.
47. Reduzieren Sie redundante Daten auf ein Minimum – wiederholen Sie keine unnötigen Daten.
48. Verwenden Sie verknüpfte Tabellen anstelle von erweiterten Zeilen.
49. Achten Sie auf den Datentyp und verwenden Sie in Ihren realen Daten den kleinstmöglichen.
50. Wenn in Abfragen häufig andere Daten verwendet werden, BLOB/TEXT-Daten jedoch nicht, trennen Sie BLOB/TEXT-Daten von anderen Daten.
51. Überprüfen und optimieren Sie Tabellen regelmäßig.
52. InnoDB-Tabellenoptimierung häufig neu schreiben.
53. Manchmal ist es schneller, den Index beim Hinzufügen der Spalte zu löschen und ihn dann wieder hinzuzufügen.
54. Verwenden Sie für unterschiedliche Anforderungen unterschiedliche Speicher-Engines.
55. Verwenden Sie die Archivspeicher-Engine für Protokolltabellen oder Prüftabellen – diese lassen sich effizienter beschreiben.
56. Sitzungsdaten werden im Cache (Memcache) statt in MySQL gespeichert – Durch das Caching können Werte automatisch aufgefüllt werden und Sie vermeiden die Erstellung räumlich-zeitlicher Daten, die in MySQL nur schwer zu lesen und zu schreiben sind.
57. Verwenden Sie VARCHAR statt CHAR, wenn Sie Zeichenfolgen mit variabler Länge speichern – das spart Platz, da CHAR eine feste Länge hat, VARCHAR jedoch keine feste Länge (UTF8 ist hiervon nicht betroffen).
58. Nehmen Sie schrittweise Musteränderungen vor – eine kleine Änderung kann große Auswirkungen haben.
59. Testen Sie alle Modi in der Entwicklungsumgebung und berücksichtigen Sie Produktionsänderungen.
60. Ändern Sie keine zufälligen Werte in Ihren Konfigurationsdateien, dies kann katastrophale Auswirkungen haben.
61. Manchmal ist in MySQL-Konfigurationen weniger mehr.
62. Verwenden Sie im Zweifelsfall eine allgemeine MySQL-Konfigurationsdatei.

Abfrageoptimierung:

63. Verwenden Sie das Protokoll langsamer Abfragen, um langsame Abfragen zu finden.
64. Verwenden Sie den Ausführungsplan, um festzustellen, ob die Abfrage ordnungsgemäß ausgeführt wird.
65. Testen Sie Ihre Abfragen immer, um zu sehen, ob sie optimal ausgeführt werden – die Leistung kann im Laufe der Zeit variieren.
66. Vermeiden Sie die Verwendung von count(*) auf der gesamten Tabelle, da dies möglicherweise die gesamte Tabelle sperrt.
67. Gestalten Sie Abfragen konsistent, damit nachfolgende ähnliche Abfragen den Abfrage-Cache verwenden können.
68. Verwenden Sie bei Bedarf GROUP BY anstelle von DISTINCT.
69. Verwenden Sie indizierte Spalten in WHERE-, GROUP BY- und ORDER BY-Klauseln.
70. Halten Sie die Indizes einfach und nehmen Sie nicht dieselbe Spalte in mehrere Indizes auf.
71. Manchmal verwendet MySQL den falschen Index. Verwenden Sie in diesem Fall USE INDEX.
72. Überprüfen Sie, ob bei der Verwendung von SQL_MODE=STRICT Probleme vorliegen.
73. Verwenden Sie für Indexfelder mit weniger als 5 Datensätzen LIMIT statt OR in UNION.
74. Um SELECT vor dem Aktualisieren zu vermeiden, verwenden Sie INSERT ON DUPLICATE KEY oder INSERT IGNORE anstelle von UPDATE.
75. Verwenden Sie nicht MAX, sondern indizierte Felder und eine ORDER BY-Klausel.
76. Vermeiden Sie die Verwendung von ORDER BY RAND().
77. LIMIT M,N kann Abfragen in manchen Fällen tatsächlich verlangsamen; verwenden Sie es sparsam.
78. Verwenden Sie UNION anstelle von Unterabfragen in der WHERE-Klausel.
79. Verwenden Sie für UPDATES den SHARE MODE, um exklusive Sperren zu verhindern.
80. Denken Sie vor dem Neustart von MySQL daran, Ihre Datenbank aufzuwärmen, um sicherzustellen, dass Ihre Daten im Speicher sind und Abfragen schnell sind.
81. Verwenden Sie DROP TABLE, CREATE TABLE DELETE FROM, um alle Daten aus der Tabelle zu löschen.
82. Minimieren Sie die Daten. Die Verwendung von * ist beim Abfragen der benötigten Daten sehr zeitaufwändig.
83. Um den Overhead zu reduzieren, sollten Sie dauerhafte Verbindungen anstelle von Mehrfachverbindungen in Betracht ziehen.
84. Benchmarken Sie Abfragen, einschließlich der Auslastung des Servers. Manchmal kann eine einfache Abfrage andere Abfragen beeinflussen.
85. Wenn die Belastung Ihres Servers zunimmt, verwenden Sie SHOW PROCESSLIST, um langsame und problematische Abfragen anzuzeigen.
86. Testen Sie alle verdächtigen Abfragen an den gespiegelten Daten, die in der Entwicklungsumgebung generiert wurden.

MySQL-Sicherungsprozess:

87. Sichern Sie vom sekundären Replikationsserver.
88. Stoppen Sie die Replikation während der Sicherung, um Inkonsistenzen bei Datenabhängigkeiten und Fremdschlüsseleinschränkungen zu vermeiden.
89. Stoppen Sie MySQL vollständig und sichern Sie die Datenbankdateien.
90. Wenn Sie MySQL Dump zur Sicherung verwenden, sichern Sie auch die Binärprotokolldateien, um sicherzustellen, dass die Replikation nicht unterbrochen wird.
91. Vertrauen Sie keinen LVM-Snapshots – diese führen wahrscheinlich zu Dateninkonsistenzen, die Ihnen in Zukunft Probleme bereiten.
92. Exportieren Sie Daten tabellenweise, um die Wiederherstellung einzelner Tabellen zu erleichtern – wenn die Daten von anderen Tabellen isoliert sind.
93. Bitte verwenden Sie –opt, wenn Sie mysqldump verwenden.
94. Überprüfen und optimieren Sie Tabellen, bevor Sie sie sichern.
95. Um den Import zu beschleunigen, deaktivieren Sie vorübergehend Fremdschlüsseleinschränkungen während des Imports.
96. Um den Import zu beschleunigen, deaktivieren Sie vorübergehend die Eindeutigkeitsprüfung während des Imports.
97. Berechnen Sie die Größe der Datenbank, Tabellen und Indizes nach jedem Backup, um das Wachstum der Datengröße besser überwachen zu können.
98. Überwachen Sie Replikationsinstanzen mithilfe automatisierter Planungsskripte auf Fehler und Latenzen.
99. Führen Sie regelmäßig Backups durch.
100. Testen Sie Ihre Backups regelmäßig.
Das Finale 101: Implementierung der MySQL-Überwachung: Monitis stellt die weltweit erste kostenlose On-Demand-MySQL-Überwachung vor.