21 Tipps zur Optimierung von MySQL

Heute hat mich ein Freund gefragt, wie ich MySQL optimieren kann, und es kann grob in 21 Richtungen unterteilt werden. Es gibt einige Details (Tabellencache, Tabellendesign, Indexdesign, Terminalcache usw.), die hier nicht aufgeführt sind. Für ein System ist es 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

Die Datenbank kann effizient ausgeführt werden. Der wichtigste Faktor ist, dass der Speicher groß genug sein muss Daten werden zwischengespeichert, Aktualisierungen können auch 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. Mehr und schnellere CPUs erforderlich

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 diejenigen nach 6 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 die entsprechende IO-Planung

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

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

10. Wählen Sie die entsprechende Raid-Karten-Cache-Strategie

Bitte verwenden Sie einen aktiven Raid und aktivieren Sie WriteBack, was sich gut zur Beschleunigung des Redo-Logs eignet, binär Protokoll 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, da sonst Swap verwendet wird LOG-Aktualisierungsmechanismus

　Redo Logs:

　- innodb_flush_log_at_trx_commit = 1 // Am sichersten

　- innodb_flush_log_at_trx_commit = 2 // Bessere Leistung

　- innodb_flush_ log_at_ trx_commit = 0 // beste Stimmung

Binlog:

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

Datendatei:

innodb_flush_method = O_DIRECT

15. Bitte verwenden Sie die Innodb-Tabelle

Es können mehr Ressourcen genutzt werden und die Online-Änderungsvorgänge wurden 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 beste Tipp, mehr als 4G Redo-Protokoll zuzuweisen, und das offizielles MySQL5.5-Redo Das Protokoll darf 4G nicht ü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 Empfehlung: innodb_thread_concurrency =16 – 32

5.6 Empfehlung 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.