21 Tipps zur Optimierung von MySQL

Heute hat mich ein Freund gefragt, wie man MySQL optimiert. Ich habe es nach meinen Vorstellungen sortiert und es kann grob in 21 Richtungen unterteilt werden. Es gibt auch einige Details (Tabelle Cache, Tabellendesign, Index Design, Terminal Cache usw.) Wird jetzt nicht aufgeführt. Für ein System ist es auch ein gutes System, in der Lage zu sein, Folgendes in der frühen Phase abzuschließen.

1. Stellen Sie sicher, dass genügend Speicher vorhanden ist

Der wichtigste Faktor für die effiziente Ausführung der Datenbank ist ausreichend Speicher Es ist groß, Daten können zwischengespeichert werden und Aktualisierungen können zuerst im Speicher abgeschlossen werden. Allerdings haben verschiedene Unternehmen unterschiedliche Speicheranforderungen. Es wird empfohlen, dass der Speicher 15–25 % der Daten ausmachen sollte. Bei besonders wichtigen Daten sollte der Speicher grundsätzlich 80 % der Datenbankgröße erreichen.

2. Benötigen Sie mehr und schnellere CPUs

MySQL 5.6 kann 64 Kerne nutzen, aber jede MySQL-Abfrage kann nur auf einer CPU ausgeführt werden, daher wären mehr CPUs erforderlich Parallelität.

3. Wählen Sie ein geeignetes Betriebssystem

Laut offiziellen Empfehlungen ist Solaris am meisten zu empfehlen. Für die tatsächliche Produktion werden CentOS und REHL empfohlen Für 6 und höher ist natürlich auch Oracle Linux eine gute Wahl. Obwohl Windows seit MySQL 5.5 optimiert wurde, wird die Verwendung von Windows in einer Umgebung mit hoher Parallelität nicht empfohlen

4. Systemparameter ordnungsgemäß optimieren

Ändern Sie das Dateihandle ulimit -n default 1024 ist zu klein

Prozessanzahl limit ulimit -u Verschiedene Versionen sind unterschiedlich

NUMA deaktivieren numctl -interleave=all

5. Wählen Sie den entsprechenden Speicherzuweisungsalgorithmus

Die Standardspeicherzuweisung ist c's malloc. Jetzt gibt es viele optimierte Speicherzuweisungsalgorithmen:

jemalloc und tcmalloc

Ab MySQL 5.5 wird die deklarierte interne Speichermethode unterstützt.

　[mysqld_safe]

malloc-lib = tcmalloc

Oder direkt auf die so-Datei zeigen

[mysqld_safe]

malloc- lib=/usr/local/lib/libtcmalloc_minimal.so

6. Verwenden Sie ein schnelleres Speichergerät SSD oder eine Solid-State-Karte

Speichermedien haben großen Einfluss auf die Geschwindigkeit zufälliger Lese- und Schreibaktualisierungen von MySQL. Das Aufkommen einer neuen Generation von Speichergeräten, Solid-State-SSDs und Solid-State-Karten, hat auch MySQL zum Glänzen gebracht, und Taobao hat sich im IOE gut geschlagen.

7. Wählen Sie ein gutes Dateisystem

Wir empfehlen XFS und Ext4. Wenn Sie noch ext2 oder ext3 verwenden, aktualisieren Sie bitte so schnell wie möglich. Empfohlen wird XFS, hierbei handelt es sich ebenfalls um ein Dateisystem, das Linux in Zukunft unterstützen wird.

Empfohlenes Dateisystem: , nobarrier)

Ext4-Parameter mounten:

ext4 (rw, noatime, nodiratime, nobarrier, data=ordered)

Wenn Sie SSD oder Solid-State-Disk verwenden, müssen Sie Folgendes berücksichtigen:

　• innodb_page_size = 4K

　• Innodb_flush_neighbors = 0

　9. Wählen Sie den entsprechenden IO-Zeitplan

Wenn normal, verwenden Sie bitte die Frist. Die Standardeinstellung ist noop.

echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

10. Wählen Sie den entsprechenden Raid Karten-Cache-Strategie

Bitte verwenden Sie ein aktiviertes Raid und aktivieren Sie es. WriteBack ist gut für die Beschleunigung von Redo-Log, Binärlog und Datendatei.

11. Abfrage-Cache deaktivieren

Der Abfrage-Cache selbst kann im Innodb-Pufferpool zwischengespeichert werden aktiviert, aktualisiert und geschrieben. Die Überprüfung des Abfragecaches erhöht den Schreibaufwand.

In MySQL 5.6 ist der Abfragecache deaktiviert.

12. Thread-Pool verwenden

Jetzt entspricht ein Daten mehr als 5 App-Szenarien, aber MySQL verfügt über eine Funktion, mit der die Leistung abnimmt, wenn die Anzahl der Verbindungen zunimmt, also für diejenigen mit mehr mehr als 200 Verbindungen Bitte erwägen Sie die Verwendung eines Thread-Pools in zukünftigen Szenarien. Dies ist eine großartige Erfindung.

13. Speicher richtig anpassen

13.1 Speicherzuweisung für Verbindungen reduzieren

Verbindungen können mit thread_cache_size zwischengespeichert werden, was nicht so leistungsstark ist wie der Thread-Pool. Der von der Datenbank für die Verbindung zugewiesene Speicher ist wie folgt:

max_used_connections * (

read_buffer_size +

read_rnd_buffer_size +

join_buffer_size +

sort_buffer_size +

binlog_cache_size +

thread_stack +

2 * net_buffer_length …

)

　13.2 Erstellen Sie einen größeren Pufferpool

Weisen Sie innodb_buffer_pool_size 60-80 % des Speichers zu. Dies sollte die Datengröße nicht überschreiten und nicht mehr als 80 % zuweisen, andernfalls wird Swap verwendet.

14. Wählen Sie vernünftigerweise den Mechanismus für logarithmische Refreshs

Redo-Protokoll:

- innodb_flush_log_at_trx_commit = 1 // die sicherste

- InnoDb_flush_log_trx_commn = 2 // Bessere Leistung

- innodb_flush_log_at_trx_commit = 0 // Beste Leistung

Binlog:

binlog_sync = 1 Erfordert Gruppen-Commit-Unterstützung. Wenn Sie diese Funktion nicht haben, können Sie binlog_sync= in Betracht ziehen 0, um bessere Ergebnisse zu erzielen.

Datendatei:

innodb_flush_method = O_DIRECT

15. Bitte verwenden Sie die Innodb-Tabelle

Es können mehr Ressourcen genutzt werden und der Online-Änderungsvorgang wurde verbessert . Derzeit werden auch nicht-chinesische Volltexte und der Memcache-API-Zugriff unterstützt. Es ist derzeit die beste Engine für MySQL.

Wenn Sie noch in MyISAM sind, denken Sie bitte über einen schnellen Wechsel nach.

16. Richten Sie ein größeres Redo-Protokoll ein

In der Vergangenheit, als Percona 5.5 und das offizielle MySQL 5.5 um Leistung konkurrierten, war der Gewinnertipp, mehr als 4G Redo-Protokoll zuzuweisen, und Das offizielle MySQL5.5-Redo-Protokoll darf 4G nicht überschreiten. Seit MySQL 5.6 kann es 4G überschreiten. Normalerweise überschreitet die Gesamtgröße des Redo-Protokolls 500 MB. Sie können die Menge des generierten Redo-Logs beobachten und die Menge des Redo-Logs, die länger als eine Stunde ist, zuordnen.

17. Festplatten-IO optimieren

Innodb_io_capactiy Konfigurieren Sie einfach 800 unter Sas 15000 U/min und mehr als 2000 unter SSD.

In MySQL 5.6:

innodb_lru_scan_ Depth = innodb_io_capacity / innodb_buffer_pool_instances

innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

　1 8. Verwenden Sie einen unabhängigen Tabellenbereich

Derzeit sind die neuen Funktionen unabhängige Tabellenbereichsunterstützung:

Tabellenbereichsrecycling abschneiden

Tabellenbereichsübertragung

Bessere Optimierung der Fragmentierung bei der Verwaltung Leistungssteigerungen,

Insgesamt ist es sinnlos, unabhängige Tabellenbereiche zu verwenden.

19. Konfigurieren Sie eine angemessene Parallelität

innodb_thread_concurrency = Parallelität ist der am häufigsten geänderte Parameter in Innodb. Verschiedene Versionen können Änderungen in verschiedenen Nebenversionen aufweisen. Allgemeine Empfehlung:

Bei Verwendung eines Thread-Pools:

innodb_thread_concurrency = 0 ist in Ordnung.

Wenn kein Thread-Pool vorhanden ist:

5.5 Empfohlen: innodb_thread_concurrency =16 – 32

5.6 Empfohlen innodb_thread_concurrency = 36

20. Transaktionen optimieren. Isolationsstufe

Die Standardeinstellung ist „Wiederholbares Lesen“

Es wird empfohlen, das Format „Read committed Binlog“ zu verwenden, das gemischt oder Zeilen verwendet

Niedrigere Isolationsstufe = bessere Leistung

21. Achten Sie auf die Überwachung

Keine Umgebung kann von der Überwachung getrennt werden. Wenn es keine Überwachung gibt, kann es sich um einen Blinden und einen Elefanten handeln. Es wird empfohlen, die Überwachung mit zabbix+mpm aufzubauen.

Das obige ist der detaillierte Inhalt von21 Tipps zur Optimierung von MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!