Share
Die Unterschiede zwischen gut geschriebenem und nicht geschriebenem SQL sind gewaltig, und in der Produktion an einem Standort mit hohem Bedarf verursachen sie gravierende Auswirkungen auf die Leistung und die Zuverlässigkeit des Services. In diesem Handbuch werde ich besprechen, wie Sie schnelle Abfragen schreiben und welche Faktoren dazu beitragen, dass sie langsam laufen.
Heute wird viel über Big Data und neue Technologien gesprochen. NoSQL- und Cloud-basierte Lösungen sind großartig, aber viele gängige Web-Software (wie WordPress, PhpBB, Drupal, VBulletin Forum-Software usw.) können immer noch unter MySQL ausgeführt werden. Die Migration zu diesen neueren Lösungen ist möglicherweise nicht so einfach wie das Optimieren der bereits in der Produktion vorhandenen Konfiguration. Außerdem ist die Leistung von MySQL sehr gut, besonders die Percona-Version.
Machen Sie nicht den üblichen Fehler, immer mehr Rechenleistung zu verwenden, wenn Sie das Problem langsamer Abfragen und hohe Serverauslastung angehen, anstatt die eigentlichen Probleme zu lösen. Das Hinzufügen von CPU-Leistung, SSDs oder RAM ist eine Form der Optimierung, wenn Sie möchten, aber ich werde hier nicht darüber sprechen. Ohne eine optimierte Site werden sich die Probleme exponentiell vervielfachen, wenn Sie mit den Hardwaregewinnen wachsen. Es ist also keine solide langfristige Lösung.
Gute SQL-Kenntnisse sind für Webentwickler immer ein wichtiges Werkzeug. Da der Fix oft so einfach ist wie das Hinzufügen eines Index oder das leichte Ändern der Verwendung der Tabelle, ist es wirklich hilfreich zu wissen, wie Sie Ihr RDBMS gut verwenden. In diesem Fall konzentrieren wir uns auf eine beliebte Open-Source-Datenbank, die häufig in Verbindung mit PHP verwendet wird. Dies ist MySQL.
Webentwickler, Datenbankarchitekten / Datenbankadministratoren und Systemadministratoren, die mit MySQL vertraut sind. Wenn Sie mit MySQL als Neuling nicht vertraut sind, wird dieser Leitfaden wahrscheinlich nicht viel Sinn machen, aber ich werde versuchen, es für Neulinge bei MySQL so informativ wie möglich zu halten.
Ich empfehle, die Schritte in Ihrer eigenen MySQL-Datenbank auszuprobieren (sichern Sie zuerst alles!). Wenn Sie keine Datenbank haben, an der Sie arbeiten können, werden ggf. Beispielschema für Datenbankschemas bereitgestellt.
Das Sichern von MySQL ist mit der mysqldump Befehlszeilendienstprogramm:
bash $ mysqldump myTable> myTable-backup.sql
Sie können mehr über mysqldump erfahren.
Zusammenfassend und in keiner Reihenfolge der Wichtigkeit spielen die folgenden Faktoren entscheidende Faktoren für die Abfrage- und Serverleistung:
Accentsconagua
Woher Klausel (und Verwendung interner MySQL - Funktionen wie OB und DATUM zum Beispiel)Sortieren nach Wir werden uns mit allen diesen Bereichen in diesem Leitfaden befassen. Wenn Sie es nicht bereits verwenden, installieren Sie bitte Percona. Dies ist ein Drop-In-Ersatz für MySQL, der zu einer erheblichen Leistungssteigerung führt. Um einen Benchmark von Percona vs. MySQL zu sehen, schauen Sie sich diesen Vergleich an.
Indizes werden von MySQL verwendet, um Zeilen mit bestimmten Spaltenwerten schnell zu finden, beispielsweise innerhalb von WOHER. Ohne einen Index muss MySQL mit der ersten Zeile beginnen und dann die gesamte Tabelle durchlesen, um die relevanten Zeilen zu finden. Je größer der Tisch, desto mehr Kosten.
Wenn die Tabelle über einen Index für die betreffenden Spalten verfügt, kann MySQL schnell die Position bestimmen, zu der in der Mitte der Datendatei gesucht werden soll, ohne alle Daten betrachten zu müssen. Dies ist viel schneller als das sequentielle Lesen jeder Zeile.
Wenn Ihre Skriptsprache eine Verbindung zur Datenbank herstellt, wenn Sie dauerhafte Verbindungen konfiguriert haben, kann sie eine vorhandene Verbindung wiederverwenden, ohne eine neue erstellen zu müssen. Dies ist optimal für die Verwendung in der Produktion und muss aktiviert werden.
PHP-Benutzer können im PHP-Handbuch mehr lesen.
Die schnellste und effektivste Methode, die ich zur Behebung dieses Problems gefunden habe, besteht in der Verwendung eines Schlüsselwertpaares wie Memcached oder Redis.
Mit Memcache Sie können den Inhalt Ihrer Abfrage beispielsweise mit folgendem Befehl zwischenspeichern:
"php connect ('localhost', 11211); $ cacheResult = $ cache-> get ('Schlüsselname'); if ($ cacheResult) //… keine Abfrage von $ result = $ cacheResult erforderlich; else //… führe deine Abfrage aus $ mysqli = mysqli ('p: localhost', 'username', 'password', 'table'); // p: an hostname für persistancy voranstellen $ sql = 'SELECT * FROM postet LEFT JOIN userInfo using (UID) WHERE posts.post_type =' post '|| posts.post_type = 'article' ORDER BY Spalte LIMIT 50 '; $ result = $ mysqli-> query ($ sql); $ memc-> set ('Schlüsselname', $ result-> fetch_array (), MEMCACHE_COMPRESSED, 86400);
// Das $ cacheResult an template übergeben $ template-> allow ('posts', $ cacheResult);
?> "
Nun zum Beispiel LINKS VERBINDEN Die Abfrage wird nur einmal alle 86.400 Sekunden (24 Stunden) ausgeführt, wodurch der MySQL-Server erheblich entlastet wird und die gleichzeitigen Verbindungen reduziert werden.
Hinweis: Voranstellen p: zu Ihrem Host-Argument in MySQLi für dauerhafte Verbindungen.
Wenn Ihre Daten groß werden oder die Nachfrage nach Serviceleistungen steigt, kann Panik einsetzen. Eine schnelle Behebung, mit der sichergestellt wird, dass Ihr Service online bleibt, kann zu einer Schere führen. Ich empfehle das jedoch nicht, da das Scheren von Daten Datenstrukturen anscheinend zu kompliziert macht. Und wie in diesem Artikel aus dem Percona-Blog sehr eloquent erklärt, Scheiße nicht.
Das Erstellen von Datenbankschemas ist nicht allzu schwierig, wenn Sie einige goldene Regeln akzeptieren, z. B. das Arbeiten mit den Einschränkungen und das Erkennen dessen, was effizient ist. Bilder in der Datenbank speichern als Klecks Datentypen zum Beispiel wird dringend davon abgeraten; Speichern eines Dateinamens in einem Varchar Datentyp Spalte ist weit überlegen.
Beim Erstellen Ihrer App muss sichergestellt werden, dass das Design für die erforderliche Verwendung korrekt ist. Halten Sie bestimmte Daten getrennt (z. B. Kategorien und Beiträge) und stellen Sie sicher, dass viele-zu-eins- oder Eins-zu-viele-Beziehungen problemlos mit IDs verknüpft werden können. Verwendung der UNBEKANNTER SCHLÜSSEL Die Einrichtung von MySQL ist ideal für die Kaskadierung von Datenkontingenzen zwischen Tabellen.
Denken Sie beim Erstellen Ihres Tisches an Folgendes:
SORTIEREN NACH in MySQL.EINZIGARTIG Indextyp für eindeutige Datensätze und verwenden DUPLICATE KEY UPDATE um einen Datums- oder Unix-Zeitstempel auf dem neuesten Stand zu halten, z. B. der letzten Prüfung der Zeile.INT Datentyp für ganzzahlige Zahlen. Wenn Sie die Länge nicht angeben, berechnet MySQL selbst, was erforderlich ist.Um effektiv zu optimieren, müssen wir uns drei grundlegende Datensätze zu Ihrer Anwendung ansehen:
Die Analyse kann auf verschiedene Arten erfolgen. Erstens nehmen wir den direktesten Weg, um unter der Motorhaube von MySQL-Abfragen zu suchen. Das erste Werkzeug in Ihrer Optimierungs-Toolbox ist ERKLÄREN. Verwenden Sie dies in Ihrer Abfrage vor dem WÄHLEN erhalten Sie die folgende Ausgabe:
sql mysql> EXPLAIN SELECT * FROM 'wp_posts' WHERE 'post_type' = 'post'; + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | id | select_type | Tabelle | Typ | mögliche_Tasten | Schlüssel | key_len | ref | Zeilen | Extra | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + | 1 | EINFACH | wp_posts | ref | type_status_date | type_status_date | 82 | const | 2 | Verwendung wo | + ---- + ------------- + ---------- + ------ + ------------ ------ + ------------------ + --------- + ------- + ------ + ------------- + 1 Reihe im Set (0,00 Sek.)
Die aufgeführten Spalten enthalten nützliche Informationen zu der ausgeführten Abfrage. Die Spalten, auf die Sie genau achten müssen, sind Mögliche_Tasten und Extra.
Mögliche_Tasten zeigt die Indizes an, die der MySQL-Engine für die Abfrage zur Verfügung stehen. Manchmal müssen Sie einen Index erzwingen, um sicherzustellen, dass die Abfrage auf die schnellste Weise ausgeführt wird.
Das Extra Spalte zeigt, ob eine Bedingung WOHER oder SORTIEREN NACH wurde benutzt. Am wichtigsten ist zu beachten, wenn Filesort verwenden erscheint. Betrachten Sie das folgende Beispiel:
sql EXPLAIN SELECT main_text FROM schreibt WHERE Benutzer = 'meinUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutral' && status_spam_system = 'neutral')) ORDER BY Datum DESC LIMIT 6430, 10
Diese Art von Abfrage kann aufgrund der Bedingung wo auf der Festplatte abgerufen werden ERKLÄREN:
sql id select_type tabellentyp possible_keys key key_len ref reihen extra 1 SIMPLE beweist ref index_user, index_status index_user 32 const 7800 Using where; Filesort verwenden
Diese Abfrage hat also die Möglichkeit, zwei Indizes zu verwenden, und derzeit schlägt sie wegen der Filesort verwenden in dem Extra.
Was Filesort verwenden Dies ist hier aus dem MySQL-Handbuch definiert:
„MySQL muss einen zusätzlichen Durchlauf durchführen, um herauszufinden, wie die Zeilen in sortierter Reihenfolge abgerufen werden. Die Sortierung erfolgt, indem alle Zeilen nach dem Join-Typ durchlaufen werden und der Sortierschlüssel und der Zeiger auf die Zeile für alle Zeilen gespeichert werden, die der WHERE-Klausel entsprechen. Die Schlüssel werden dann sortiert und die Zeilen werden in sortierter Reihenfolge abgerufen. ”
Dieser zusätzliche Pass verlangsamt Ihre App und muss unter allen Umständen vermieden werden. Ein weiterer entscheidender Punkt Extra Ergebnis zu vermeiden ist Temporär verwenden, Dies bedeutet, dass MySQL eine temporäre Tabelle für die Abfrage erstellen musste. Natürlich ist dies eine abscheuliche Verwendung von MySQL und muss unter allen Umständen vermieden werden, es sei denn, Sie können aufgrund der Datenanforderungen nicht weiter optimieren. In diesem Fall sollte die Abfrage in Redis oder Memcache zwischengespeichert und nicht von den Benutzern ausgeführt werden.
So beheben Sie das Problem mit Filesort verwenden Wir müssen sicherstellen, dass MySQL ein INDEX. Es hat mehrere Mögliche_Tasten zur Auswahl, aber MySQL kann nur einen Index in der letzten Abfrage verwenden. Obwohl Indizes aus mehreren Spalten bestehen können, ist die Umkehrung nicht wahr, obwohl Sie dem MySQL-Optimierungsprogramm Hinweise geben können, welche Indizes Sie erstellt haben.
Der MySQL-Optimierer verwendet Statistiken basierend auf den Abfragetabellen, um den besten Index für den Umfang der Abfrage auszuwählen. Dies geschieht auf der Grundlage der statistischen Logik des integrierten Optimierers. Bei Mehrfachauswahl kann dies jedoch nicht immer ohne Hinweis korrekt sein. Um sicherzustellen, dass der richtige Schlüssel verwendet wird (oder nicht verwendet wird), verwenden Sie die Taste FORCE INDEX, USE INDEX und IGNORE INDEX Schlüsselwörter in Ihrer Suchanfrage. Weitere Informationen zu Indexhinweisen finden Sie im MySQL-Handbuch.
Verwenden Sie den Befehl, um die Tabellenschlüssel anzuzeigen INDEX ANZEIGEN.
Sie können mehrere Hinweise für das Optimierungsprogramm angeben, beispielsweise:
sql SELECT * FROM table1 USE INDEX (col1_index, col2_index) WHERE col1 = 1 UND col2 = 2 AND col3 = 3;
Laufen ein ERKLÄREN zeigt Ihnen, welcher Index im Endergebnis verwendet wurde. Um das vorherige Beispiel zu korrigieren, fügen wir das hinzu USE INDEX wie so:
sql EXPLAIN SELECT haupttext FROM posts USE INDEX (index_user) WHERE user = 'meinUsername' && status = '1' && (status_spam_user = 'no_spam' || (status_spam_user = 'neutral' && status_spam_system = 'neutral')) ORDER BY datum DESC LIMIT 6430, 10
Nun, da MySQL die index_status Von der zu verwendenden Tabelle aus ist die Abfrage behoben.
sql id select_type tabellentyp possible_keys key key_len ref reihen extra 1 SIMPLE beweist ref index_user, index_status index_user 32 const 7800 Using where
Neben ERKLÄREN ist der BESCHREIBEN Stichwort. Mit BESCHREIBEN Sie können die Informationen einer Tabelle wie folgt anzeigen:
sql mysql> BESCHREIBEN Stadt; + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Feld | Typ | Null | Schlüssel | Standard | Extra | + ------------ + ---------- + ------ + ----- + --------- + - -------------- + | Id | int (11) | NEIN | PRI | NULL | auto_increment | | Name | char (35) | NEIN | | | | | Land | char (3) | NEIN | UNI | | | | Bezirk | char (20) | JA | MUL | | | | Bevölkerung | int (11) | NEIN | | 0 | | +------------+----------+------+-----+---------+----------------+
Sie erstellen Indizes in MySQL mit der INDEX ERSTELLEN Syntax. Es gibt einige Indexvarianten. VOLLER TEXT wird für Volltextsuchzwecke verwendet, und dann gibt es die EINZIGARTIG Typ, um sicherzustellen, dass Daten eindeutig bleiben.
Um Ihrer Tabelle einen Index hinzuzufügen, verwenden Sie zum Beispiel die folgende Syntax:
sql mysql> CREATE INDEX idx_start_of_username ON 'Benutzer' (Benutzername (10));
Dadurch wird ein Index für die Tabelle erstellt Benutzer, Dabei werden die ersten 10 Buchstaben der Spalte "Benutzername" verwendet, bei der es sich um einen Varchar-Datentyp handelt.
In diesem Fall müssen alle Suchvorgänge, die a WOHER Die Sortierung nach dem Benutzernamen, wobei die Übereinstimmung in den ersten 10 Zeichen enthalten ist, entspricht einer Suche nach der gesamten Tabelle.
Indizes haben einen großen Einfluss auf die Geschwindigkeit, mit der die Abfragedaten zurückgegeben werden. Das Festlegen eines Primärschlüssels und eines eindeutigen Indexes reicht im Allgemeinen nicht aus. Zusammengesetzte Schlüssel sind die eigentliche Nische in MySQL. Meistens erfordert dies eine A / B-Überprüfung ERKLÄREN.
Zum Beispiel, wenn wir zwei Spalten in unserem referenzieren müssen WOHER bedingt wäre ein zusammengesetzter Schlüssel ideal.
sql mysql> CREATE INDEX idx_composite ON Benutzer (Benutzername, aktiv);
Hier wird dieser Schlüssel auf der erstellt Nutzername Spalte aus dem vorherigen Beispiel und der Spalte aktiv, ein ENUM Datentyp, der angibt, ob das Benutzerkonto aktiv ist. Also jetzt beim Abfragen der Daten nach WOHER Der Benutzername ist gültig und das Konto ist aktiv = 1, Der Datensatz ist jetzt so optimiert, dass er besser damit umgehen kann.
Aktivieren Sie die Profilerstellung, um Ihre MySQL-Abfragen genauer zu untersuchen. Dies kann zur Laufzeit über erfolgen Set Profiling = 1, und dann Ausführen Ihrer Abfrage und Betrachten des Ergebnisses von Profile anzeigen.
Mit PDO hier ein Codeausschnitt, der genau das tut:
"php $ db-> query ('set profiling = 1'); $ db-> query ('Überschrift, Text und Tags aus Beiträgen auswählen'); $ rs = $ db-> query ('show profiles'); $ db-> query ('set profiling = 0'); // Profiling deaktivieren, nachdem die Abfrage ausgeführt wurde
$ Records = $ rs-> fetchAll (PDO :: FETCH_ASSOC); // Holen Sie sich die Ergebnisse aus der Profilerstellung
$ errmsg = $ rs-> errorInfo () [2]; // Fehler hier abfangen "
Wenn Sie kein PDO verwenden, können Sie dies auch mit tun Mysqli wie so:
"php $ db = new mysqli ($ host, $ username, $ password, $ dbname);
$ db-> query ('set profiling = 1'); $ db-> query ('Überschrift, Text, Tags aus Beiträgen auswählen'); if ($ result = $ db-> query ("SHOW-Profile", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ row); $ result-> close ();
if ($ result = $ db-> query ("Profil für Abfrage 1 anzeigen", MYSQLI_USE_RESULT)) while ($ row = $ result-> fetch_row ()) var_dump ($ row); $ result-> close ();
$ db-> query ('set profiling = 0'); "
Dadurch werden die Profilierungsdaten an Sie zurückgegeben, die die Ausführungszeit im zweiten Wert des assoziativen Arrays enthalten:
php array (3) [0] => string (1) "1" [1] => string (10) "0.00024300" [2] => string (17) "Überschrift, Text und Tags von Beiträgen auswählen" Die Abfrage dauerte 0,00024300 Sekunden. Das ist schnell genug, um sich keine Sorgen zu machen. Aber wenn die Zahlen ansteigen, müssen wir tiefer schauen.
Lernen Sie als Beispiel Ihre App kennen. Setzen Sie einen Check für einen DEBUGGEN Konstante im Datenbanktreiber der Datenbank für Abstraktionsschicht / Frameworks Ihrer Anwendung. Anschließend können Sie mit der Überwachung beginnen, indem Sie einen Profilfall aktivieren und das Ergebnis mit a ausgeben var_dump / print_r. Jetzt können Sie problemlos die Seiten Ihrer Website durchsuchen und ein Profil erstellen!
Aktivieren Sie die Protokollierung, um eine vollständige Prüfung Ihrer Abfragen durchzuführen. Einige Entwickler, mit denen ich gearbeitet habe, machen sich Sorgen, dass dies ein doppelseitiges Problem ist, da das Aktivieren der Protokollierung die Leistung geringfügig beeinflusst und die aufgezeichneten Werte daher etwas niedriger sind als in der Realität. Obwohl dies stimmt, zeigen viele Benchmarks, dass es keinen großen Unterschied gibt.
Um die Protokollierung in MySQL Version 5.1.6 zu aktivieren, verwenden Sie die globale log_slow_queries und kann eine Datei mit angeben slow_query_log_file global. Dies kann in der Laufzeit-Eingabeaufforderung so erfolgen:
bash set global log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log;
Sie können dies dauerhaft in einstellen /etc/my.cnf oder meine.ini Konfigurationsdatei für Ihren Server.
bash log_slow_queries = 1; slow_query_log_file = /dev/slow_query.log;
Nach dieser Änderung müssen Sie den MySQL-Server neu starten, z. Dienst MySQL Neustart auf Linux-Systemen.
In der neueren Version von MySQL 5.6.1, log_slow_queries ist veraltet und slow_query_log wird stattdessen verwendet. Aktivieren TABELLE als Ausgabetyp ermöglicht ein viel besseres Debugging-Erlebnis und kann in MySQL 5.6.1 und höher wie folgt ausgeführt werden:
bash log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1
long_query_time Gibt die Anzahl von Sekunden an, für die eine langsame Abfrage klassifiziert wird. Der Standardwert ist 10 und das Minimum 0. Er kann durch Angabe eines Gleitkommazahls Millisekundenwerte annehmen. hier habe ich es auf 1 Sekunde gesetzt. Jede Abfrage, die länger als 1 Sekunde dauert, wird in der TABELLE Ausgabeformat.
Dies meldet sich an der mysql.slow_log und mysql.general_log Tabellen innerhalb MySQL.
Um die Protokollierung zu deaktivieren, setzen Sie log_output zu KEINER.
log_queries_not_using_indexes ist ein nützlicher Boolescher Wert. Wenn er in Verbindung mit dem langsamen Abfrageprotokoll aktiviert wird, werden nur Abfragen protokolliert, von denen erwartet wird, dass sie alle Zeilen abrufen.
Diese Option bedeutet nicht immer, dass kein Index verwendet wird. Wenn eine Abfrage beispielsweise einen vollständigen Index-Scan verwendet, wird dies protokolliert, da der Index die Anzahl der Zeilen nicht einschränken würde.
Das Aktivieren der Protokollierung an einem Produktionsstandort mit Datenverkehr muss fast immer für kurze Zeit erfolgen, während die Last überwacht wird, um sicherzustellen, dass sie den Service nicht beeinträchtigt. Wenn Sie stark ausgelastet sind und dringend eine Lösung benötigen, beheben Sie das Problem an der Eingabeaufforderung mit PROZESSLISTE ANZEIGEN oder über die information_schema.PROCESSLIST Tabelle direkt, z.B.. select * from information_schema.PROCESSLIST;.
Wenn Sie alle Abfragen in der Produktion protokollieren, kann dies viel bedeuten. Dies ist eine bewährte Methode für Forschungszwecke, wenn Sie ein Projekt auditieren. Wenn Sie das Projekt jedoch tagelang laufen lassen, erhalten Sie oft nicht mehr brauchbare Daten, als dies bei 48 Stunden der Fall wäre ( erfassen Sie im Durchschnitt zumindest die Spitzenzeiten der Nutzung, um die Abfragen genauer zu betrachten und ein paar Vorschläge zur Häufigkeit zu erhalten..
Hinweis: Wenn Sie eine Website betreiben, die in der Hauptsaison und in der Nebensaison starker Verkehr und dann gar nicht viel Zeit hat (z. B. eine Sport-Website), sollten Sie logisch sein, wie Sie die Protokollierung betrachten. Gehen Sie nicht davon aus, dass die Website schnell arbeitet. Führen Sie eine Prüfung durch und richten Sie vor allem grafische Darstellungen ein.
Percona enthält einige großartige Tools, und pt-query-digest ist ein Befehlszeilentool zum Analysieren von Abfrageprotokollen, der Prozessliste oder von tcpdumps.
Sie können verwenden pt-query-digest auf folgende Weise:
Analysieren Sie eine * .log-Datei (die beispielsweise von Ihrer langsamen Abfrageprotokollierung ausgegeben wird):
bash $ pt-query-digest slow.log
Berichte über die langsamsten Abfragen von host1 in Echtzeit (sehr nützlich!):
bash $ pt-query-digest --processlist h = host1
Verwenden Sie tcpdump, um die langsamsten Abfragen von MySQL-Protokolldaten zu melden:
"bash $ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306> mysql.tcp.txt
$ pt-query-digest -type tcpdump mysql.tcp.txt "
Schließlich können wir langsame Abfragedaten zur späteren Überprüfung von einem Host auf einen anderen speichern. Hier speichern wir den Abfrage-Digest für slow.log in host2:
bash $ pt-query-digest --review h = host2 --no-report slow.log
Um zu lernen, wie Sie die pt-query-digest Tool von Percona, lesen Sie die Handbuchseite.
Dieses Diagramm der InnoDB-Zeilenoperationen zeigt die Zeilenoperationen, die InnoDB ausgeführt hat: Aktualisieren, Lesen, Löschen und Einfügen.
Dies ist in der Tat ein großes Thema, und ich werde in diesem Handbuch nur kurz darauf eingehen, damit Sie mit der MySQL-Überwachung beginnen können. Im Allgemeinen ist es jedoch wichtig anzumerken, dass die Überwachung aller Dienste Ihrer Website ideal ist, um wirklich wissen zu können, was Ihre Leistung und Ihre Nutzung sind.
Um dies zu erreichen, empfehle ich die Einrichtung eines RRDTool-basierte Lösung wie Kakteen mit einer MySQL-Konfiguration. Holen Sie sich eine Vorlage für Cacti von den Jungs von Percona.
Wenn Sie Cacti eingerichtet haben und mit der Analyse Ihrer App beginnen können, lassen Sie etwas Zeit, damit sich die Diagramme aufbauen können. Nach ein paar Tagen sehen Sie den Tages- und Nachtrhythmus Ihres Datenverkehrs und sehen, wie voll der Server wirklich wird.
Wenn Sie nach automatisierten Warnungen und Auslösern suchen, sollten Sie sich mit der Konfiguration von monit, einem proaktiven Open-Source-Monitor für Unix-Systeme, beschäftigen. Mit monit können Sie Regeln für Ihren Server erstellen und sicherstellen, dass Sie benachrichtigt werden, wenn die Last ansteigt, sodass Sie sie während des Vorfalls einlesen können.
Das Protokollieren aller langsamen Abfragen, deren Abschluss mehr als eine Sekunde dauert, kann uns etwas sagen, aber es ist ebenso wichtig zu wissen, welche Abfragen hunderte Male ausgeführt werden. Selbst wenn diese Abfragen nur kurz ausgeführt werden können, ist der Aufwand für hohe Anforderungen auf dem Server noch immer hoch.
Aus diesem Grund ist es am wichtigsten, wenn Sie etwas aktualisieren und auf den neuesten Stand bringen, wenn Sie neue Datenbankarbeiten und -änderungen durchführen. Wir haben immer eine Richtlinie für meine Teams, nach einem Mittwoch bei einem Live-Projekt niemals Änderungen an neuen Funktionsdatenbanken zu synchronisieren. Dies muss zu Beginn der Woche, spätestens am Dienstag, erfolgen, damit alle Teams die entsprechenden Aktivitäten überwachen und unterstützen können.
Bevor Sie mit neuen Abfragen beginnen können, müssen Sie ein Benchmarking mit einem Lasttest-Tool wie z ab. Wenn Sie den Benchmark ausführen, müssen Sie das ansehen PROZESSLISTE ANZEIGEN, und ermöglicht auch die Protokollierung und Überwachung mit Systemwerkzeugen wie oben, kostenlos und Iostat. Dies ist ein entscheidender Schritt, bevor eine neue Anfrage in eine Live-Produktion aufgenommen wird. Es handelt sich jedoch nicht um einen 100% igen Test, da Live-Verkehr sich anders als ein berechneter Benchmark verhalten kann.
Zum Benchmarking mit ab, Stellen Sie sicher, dass Sie das Paket installiert haben, z.
bash #centos benutzer $ sudo yum install ab #debian / ubuntu benutzer $ sudo apt-get install ab
Jetzt können Sie mit dem Testen Ihrer App beginnen, zum Beispiel:
bash $ ab -k -c 350 -n 20000 my-domain.com/
Das -k meint das bleib am Leben die Verbindung und die -c 350 ist die Anzahl der gleichzeitigen Verbindungen, d. h. die Anzahl der Personen / Clients, die die Site auf einmal erreichen. Endlich, das -n 20000 ist die Anzahl der Anfragen, an die gestellt wird my-domain.com.
Wenn Sie den Befehl oben ausführen, treffen Sie also mit 350 gleichzeitigen Verbindungen http://my-domain.com/, bis 20.000 Anforderungen erfüllt werden. Dies geschieht mithilfe des Keep Alive-Headers.
Nachdem der Prozess die 20.000 Anforderungen abgeschlossen hat, erhalten Sie eine Rückmeldung zu den Statistiken. Auf diese Weise können Sie feststellen, wie gut die Site unter den von Ihnen verwendeten Parametern funktioniert, wenn Sie die obigen Parameter verwenden. Dies ist ein guter Weg, um automatisiert zu erfahren, ob Ihre Abfrage etwas geändert hat.
Der Anforderungsumfang und die Serverauslastung haben einen großen Einfluss auf die Leistung, und die Abfragezeit kann dadurch beeinträchtigt werden. Alles in allem sollten Sie das langsame Abfrageprotokoll aktivieren, um dies in der Produktion abzufangen. In der Regel müssen Sie für die Entwicklung sicherstellen, dass alle Abfragen auf einem inaktiven Server in Bruchteilen einer Millisekunde (0,0xx oder schneller) ausgeführt werden.
Umsetzung Memcache Dies hat erhebliche Auswirkungen auf Ihre Lastanforderungen und wird dazu verwendet, Ressourcen, die mit der Verarbeitung von Abfragen verbraucht wurden, ernsthaft zu entladen. Stellen Sie sicher, dass Sie verwenden Memcached effektiv und Benchmark Ihrer App mit einem heißen Cache (vorinstalliert mit Werten) und einem kalten.
Um zu vermeiden, dass bei einem leeren Cache mit der Produktion begonnen wird, ist ein Pre-Loader-Skript ein guter Weg, um sicherzustellen, dass der Cache gelesen wird, und es kommt nicht zu einer großen Anzahl von Anforderungen, die alle auf einmal eingehen, wenn eine Ausfallzeit aufgrund von zurückkehrt Überkapazitätsausfälle.
Nachdem Sie die Protokollierung aktiviert haben, haben Sie nun einige langsame Abfragen in Ihrer App gefunden. Lass uns sie reparieren! Zum Beispiel werde ich verschiedene allgemeine Probleme demonstrieren, auf die Sie stoßen werden, und die Logik, um sie zu beheben.
Wenn Sie noch keine langsamen Abfragen gefunden haben, prüfen Sie vielleicht, welche Einstellungen wo für die vorgenommen wurden long_query_time wenn Sie die Abfrageprotokollierungsmethode verwenden. Ansonsten haben Sie alle Ihre Fragen mit der Profilerstellung geprüft (Set Profiling = 1), machen Sie eine Liste der Abfragen, die länger als Bruchteile einer Millisekunde dauern (0,000x Sekunden), und beginnen Sie mit diesen.
Bei der Optimierung von MySQL-Abfragen stelle ich sechs häufige Probleme:
SORTIEREN NACH mit filesort.sql mysql> erklären select * aus Produkten, bei denen products.price> 4 und products.stock> 0 nach Namen geordnet sind; + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | id | select_type | Tabelle | Typ | mögliche_Tasten | Schlüssel | key_len | ref | Zeilen | Extra | + ---- + ------------- + ---------- + ------ + ------------ --- + ------ + --------- + ------ + ------ + --------------- -------------- + | 1 | EINFACH | Produkte | ALL | NULL | NULL | NULL | NULL | 1142 | Wo verwenden; Filesort verwenden | +----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
Das Vermeiden von Filesort ist hier aufgrund der ORDER BY Name. Unabhängig davon, welche Index-Permutation Sie verwenden, ist das Beste, was Sie erhalten Wo verwenden; Filesort verwenden in deiner Extra Säule. Um dies zu optimieren, speichern Sie das Ergebnis in Memcache oder ordnen Sie es in der Logikschicht Ihrer Anwendung an.
SORTIEREN NACH auf WOHER und ein LINKS
