MySQL-Leistungsoptimierung

Für den gesamten Stapel sind Datenbankkenntnisse unverzichtbar, relationale Datenbank oder NoSQL, Speicherdatenbank oder teilweise Festplattenspeicherdatenbank, Objekt Es gibt viele Arten von gespeicherte Datenbanken oder Graphdatenbanken, aber die erste wesentliche Fähigkeit sollte MySQL sein. Vom Aufstieg von LAMP über die Entstehung von Mariadb bis hin zur Einführung von PG sind kompetente MySQL-Kenntnisse von großem Nutzen.

Es gibt viele Aspekte der MySQL-Datenbanktechnologie. Hier behandeln wir nur die notwendige Leistungsoptimierung. Wir empfehlen eine Bottom-up-Leistungsoptimierung, die hauptsächlich die Optimierung der Betriebsumgebung, der Konfigurationsparameter, der SQL-Leistung und des Systemarchitekturdesigns umfasst. .

Optimierung der Betriebsumgebung

Dies ist die Welt von Linux, und die Optimierung der MySQL-Laufumgebung wird oft zusammen mit der Optimierung des Linux-Kernels abgeschlossen. Natürlich spielt es auch eine gewisse Referenzrolle für Cloud-Service-RDS.

Passen Sie den Standard-IO-Planungsalgorithmus von Linux an.

Das übergeordnete Ziel des IO-Planers besteht darin, den Kopf immer in eine Richtung und dann in die andere Richtung zu bewegen In entgegengesetzter Richtung ist dies genau das Aufzugsmodell im wirklichen Leben, daher wird der E/A-Scheduler auch als Aufzugsalgorithmus bezeichnet. Es gibt mehrere Aufzugsalgorithmen für die E/A-Planung in Linux as (Vorausschauend), einer heißt cfq (Complete Fairness Queueing), einer heißt Deadline und einer heißt noop (No Operation). IO hat einen größeren Einfluss auf die Datenbank Wenn es sich um ein SSD- oder PCIe-SSD-Gerät handelt, müssen Sie die folgenden zwei Änderungsmethoden verwenden.

1. Die dynamische Online-Änderung schlägt nach dem Neustart fehl.

2. Ändern Sie /etc/grub.conf und machen Sie es dauerhaft.

echo “deadline” > /sys/block/sda/queue/scheduler

Ändern Sie die Konfigurationsdatei /etc/grub.conf und fügen Sie eine Konfiguration in die Kernelzeile ein, zum Beispiel:

Konzentrieren Sie sich hauptsächlich auf den Aufzugsparameter, wenn Sie den Kernel festlegen. Sie müssen das System neu starten, damit es wirksam wird.

elevator=deadline

Numa-Funktion deaktivieren

Die NUMA der Architektur der neuen Generation eignet sich nicht zum Ausführen von Datenbanken. NUMA dient der Verbesserung der Speicherauslastung, kann jedoch zu einer CPU-Belastung führen Der verbleibende Speicher reicht nicht aus und es tritt ein Auslagerungsproblem auf. Daher wird im Allgemeinen empfohlen, die NUMA-Planung zu deaktivieren.

2. Ändern Sie das Skript /etc/init.d/mysql oder mysqld_safe, um den NUMA-Planungsmechanismus beim Starten des mysqld-Prozesses festzulegen, z. B. numactl –interleave=all.

numa=off

Swapiness-Einstellungen ändern

Swapiness ist ein Kernel-Parameter von Linux, der zur Steuerung der Strategie zum Auslagern des physischen Speichers verwendet wird. Er ermöglicht einen Prozentwert, der Mindestwert ist 0 , das Maximum ist 100 und der Standardwert ist 60. Welche Auswirkung hat dieser Einstellungswert?

Das Setzen von vm.swappiness auf 0 bedeutet, dass Swap so wenig wie möglich verwendet wird, und 100 bedeutet, dass versucht wird, inaktive Speicherseiten in den Swap- oder Release-Cache auszulagern. Inaktiver Speicher bedeutet Speicher, der vom Programm zugeordnet, aber „über einen längeren Zeitraum“ nicht genutzt wird. Mit vmstat können wir sehen, wie viel inaktiver Speicher im System vorhanden ist.

Es wird empfohlen, diesen Wert auf 1 zu setzen. Die Einstellungsmethode ist wie folgt: Fügen Sie der Datei /etc/sysctl.conf eine Zeile hinzu.

# vmstat -a 1

vm.swappiness = 1

Dateideskriptor erweitern

Dies ist ein häufig geänderter Parameter, der von Programmen mit hoher Parallelität geändert wird.

2 . Ändern Sie die Konfigurationsdatei und sie wird dauerhaft wirksam.

ulimit -n 51200

在/etc/security/limits.conf配置文件中增加

* hardnofile 51200
 
* softnofile 51200

面向session的进程文件描述符的修改稍有不同，在云上的修改也略有差异，可以参见一样的“open too many files”

优化文件系统挂载参数。

对于文件系统，如无特殊要求，最好采用ext4.

文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。

noatime表示不记录访问时间,nodiratime不记录目录的访问时间。

barrier=0,表示关闭barrier功能.

barrier的主要目的是为了保证磁盘写数据的安全性,但是会降低性能。如果有BBU之类的电池备份电源保证控制卡不瞬间掉电,那么这个功能就可以放心大胆的关闭。

配置参数调优

my.cnf中的配置参数调优取决于业务，负载或硬件，在慢内存和快磁盘、高并发和写密集型负载情况下，都需要特殊的调整。

基本配置

query_cache_size

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

max_connections

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

back_log

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

Innodb配置

innodb_file_per_table

Diese Einstellung teilt InnoDB mit, ob es die Daten und Indizes aller Tabellen in einem gemeinsamen Tabellenbereich speichern muss (innodb_file_per_table = OFF) oder die Daten jeder Tabelle in einer separaten .ibd-Datei platzieren muss (innodb_file_per_table = ON). Mit einer Datei pro Tabelle können Sie Speicherplatz freigeben, wenn Sie Tabellen löschen, kürzen oder neu erstellen. Dies ist auch für einige erweiterte Funktionen erforderlich, beispielsweise für die Datenkomprimierung. Es bringt aber keinen Leistungsgewinn. In MySQL 5.6 ist der Standardwert dieser Eigenschaft ON.

innodb_flush_log_at_trx_commit

Der Standardwert ist 1, was darauf hinweist, dass InnoDB ACID-Funktionen vollständig unterstützt. Dieser Wert ist am besten geeignet, wenn es um die Datensicherheit geht, beispielsweise auf einem Masterknoten. Bei Systemen mit langsamen Festplattengeschwindigkeiten (Lesen und Schreiben) wird dies jedoch einen enormen Overhead mit sich bringen, da jedes Mal, wenn Änderungen im Redo-Log gelöscht werden, zusätzliche Fsyncs erforderlich sind. Ein Wert von 0 ist schneller, allerdings können bei einem Systemabsturz einige Daten verloren gehen, sodass ein Durchgang nur für Backup-Knoten gilt.

innodb_flush_method

Diese Konfiguration bestimmt, wie Daten und Protokolle auf die Festplatte geschrieben werden. Wenn Sie über einen Hardware-RAID-Controller verfügen und dessen unabhängiger Cache einen Rückschreibmechanismus verwendet und über einen Batteriestromausfallschutz verfügt, sollte er im Allgemeinen auf O_DIRECT eingestellt werden. Andernfalls sollte er in den meisten Fällen auf fdatasync (Standardwert) eingestellt werden. sysbench ist ein großartiges Tool, das Ihnen bei der Entscheidung für diese Option hilft.

innodb_log_buffer_size

Diese Konfiguration bestimmt den Puffer, der Transaktionen zugewiesen wird, die noch nicht ausgeführt wurden. Wenn die Transaktion jedoch binäre große Objekte oder große Textfelder enthält, sehen Sie sich die Statusvariable Innodb_log_waits an. Wenn sie nicht 0 ist, erhöhen Sie innodb_log_buffer_size.

Andere Konfigurationen

log_bin

Wenn der Datenbankserver als Backup-Knoten für den Masterknoten fungiert, ist die Aktivierung des Binärprotokolls erforderlich. Selbst wenn nur ein Server vorhanden ist, ist dies nützlich, wenn Sie eine Datenwiederherstellung zu einem bestimmten Zeitpunkt durchführen möchten. Nach der Erstellung wird das Binärprotokoll dauerhaft gespeichert. Wenn Ihnen der Speicherplatz nicht ausgehen soll, können Sie alte Dateien mit PURGE BINARY LOGS löschen oder „expire_logs_days“ festlegen, um anzugeben, nach wie vielen Tagen die Protokolle automatisch gelöscht werden. Die binäre Protokollierung ist nicht ohne Overhead. Daher wird empfohlen, diese Option zu deaktivieren, wenn Sie sie auf einem Replikatknoten, der nicht der Primärknoten ist, nicht benötigen.

interactive_timeout

Die Anzahl der Sekunden, die der Server auf eine Aktion bei einer interaktiven Verbindung wartet, bevor er diese schließt. Ein interaktiver Client ist als einer definiert, der die Option client_interactive für mysql_real_connect() verwendet. Der Standardwert ist 28800 und es wird empfohlen, ihn auf 7200 zu ändern.

table_open_cache

Jedes Mal, wenn MySQL eine Tabelle öffnet, liest es einige Daten in den table_open_cache-Cache. Wenn MySQL die entsprechenden Informationen in diesem Cache nicht finden kann, liest es sie von der Festplatte. Unter der Annahme, dass das System über 200 gleichzeitige Verbindungen verfügt, müssen Sie diesen Parameter auf 200*N setzen (N ist die Anzahl der für jede Verbindung erforderlichen Dateideskriptoren); wenn table_open_cache auf einen großen Wert eingestellt ist, wenn das System nicht so viele Dateien verarbeiten kann Deskriptorensymbol, dann schlägt der Client fehl und die Verbindung kann nicht hergestellt werden.

max_allowed_packet

Akzeptierte Paketgröße; es ist sicher, den Wert dieser Variablen zu erhöhen, da zusätzlicher Speicher nur bei Bedarf zugewiesen wird. MySQLd reserviert beispielsweise nur dann mehr Speicher, wenn Sie eine lange Abfrage durchführen oder MySQLd große Ergebniszeilen zurückgeben muss. Der kleine Standardwert für diese Variable ist eine Vorsichtsmaßnahme, um Fehlerpakete zwischen Client und Server zu erfassen und sicherzustellen, dass kein Speicherüberlauf durch versehentliche Verwendung großer Pakete verursacht wird

skip_name_resolve

Wenn der Client eine Verbindung zum Datenbankserver herstellt und DNS langsam ist, ist auch der Verbindungsaufbau langsam. Es wird daher empfohlen, die Option „skip_name_resolve“ zu deaktivieren, wenn Sie den Server starten, ohne eine DNS-Suche durchzuführen.

Optimierung von SQL-Anweisungen

Auf der Anwendungsebene können Sie durch die Zusammenarbeit von PT-Tool und langsamem Abfrageprotokoll die Anweisungen eines vollständigen Tabellenscans leicht identifizieren.

Grundprinzipien

• Vollständige Tabellenscans vermeiden

• Indizes erstellen

• Vermeiden Sie die Rückgabe großer Datenmengen an den Kunden. Wenn die Datenmenge zu groß ist, sollten Sie überlegen, ob die entsprechenden Anforderungen angemessen sind

• Versuchen Sie es Vermeiden Sie große Transaktionsvorgänge und verbessern Sie die Parallelitätsfähigkeiten des Systems

• Bevor Sie die Cursor-basierte Methode oder die temporäre Tabellenmethode verwenden, sollten Sie zunächst nach einer satzbasierten Lösung zur Lösung des Problems suchen Die satzbasierte Methode ist normalerweise effizienter. Vermeiden Sie die Verwendung von Cursorn, da diese weniger effizient sind.

Tipps

Zu den Bedingungen nach where

• sollten in der where-Klausel as vermieden werden Verwenden Sie so oft wie möglich den Operator != oder a8093152e673feb7aba1828c43532094, andernfalls gibt die Engine die Verwendung des Index auf und führt einen vollständigen Tabellenscan durch.

• Sie sollten versuchen, die Verwendung von „or“ in der where-Klausel zum Verbinden von Bedingungen zu vermeiden. Sie können die Verwendung von „union“ anstelle von „in“ und „not in“ in Betracht ziehen . Seien Sie auch vorsichtig. Verwenden Sie bei kontinuierlichen Werten nicht „in“, „exists“ anstelle von „in“. Versuchen Sie, Ausdrucksoperationen und Funktionsoperationen für Felder in zu vermeiden where-Klausel

• Über Datentypen

• Versuchen Sie, numerische Felder zu verwenden, die nur numerische Informationen enthalten, versuchen Sie, sie nicht als Zeichentypen zu entwerfen . Dadurch wird die Effizienz der Abfragen und Verbindungen verringert und der Speicheraufwand erhöht.

Verwenden Sie so oft wie möglich varchar/nvarchar anstelle von char/nchar, da der Speicherplatz von Feldern variabler Länge klein ist und bei Abfragen die Sucheffizienz in einem relativ kleinen Feld geringer ist ist offensichtlich etwas höher.

• Es ist am besten, NULL in der Datenbank zu belassen, um die Datenbank so weit wie möglich zu füllen. Für Notizen, Beschreibungen, Kommentare usw Für andere ist es am besten, NULL nicht zu verwenden.

• Verwenden Sie „select * from t“ nirgendwo, ersetzen Sie „*“ durch eine bestimmte Feldliste und geben Sie keine nicht verwendeten Felder zurück.

• Über temporäre Tabellen

• Vermeiden Sie das häufige Erstellen und Löschen temporärer Tabellen, um den Verbrauch von Systemtabellenressourcen zu reduzieren. Für einmalige Ereignisse verwenden Sie am besten Exporttabellen.

Wenn beim Erstellen einer temporären Tabelle die auf einmal eingefügte Datenmenge groß ist, können Sie „Auswählen in“ anstelle von „Tabelle erstellen“ verwenden, um eine große Anzahl von Protokollen zu vermeiden und die Geschwindigkeit zu verbessern ; Wenn die Datenmenge nicht groß ist, sollten Sie zuerst die Tabelle erstellen und dann einfügen, um die Ressourcen der Systemtabelle zu entlasten.

• Wenn temporäre Tabellen verwendet werden und alle temporären Tabellen am Ende explizit gelöscht werden, kürzen Sie zuerst die Tabelle und löschen Sie sie. Dadurch kann eine langfristige Sperrung von Systemtabellen vermieden werden.

• Über Indizes

• Sie sollten zunächst darüber nachdenken, Indizes für die Spalten zu erstellen, die an „wo“ und „Reihenfolge nach“ beteiligt sind.

Wenn Sie ein Indexfeld als Bedingung verwenden und der Index ein zusammengesetzter Index ist, muss das erste Feld im Index als Bedingung verwendet werden, um sicherzustellen, dass das System den Index verwendet. Andernfalls wird der Index nicht verwendet und die Feldreihenfolge sollte nach Möglichkeit mit der Indexreihenfolge übereinstimmen.

• Je mehr Indizes, desto besser. Obwohl der Index die Effizienz der entsprechenden Auswahl verbessern kann, verringert er auch die Effizienz des Einfügens und Aktualisierens, da er während des Einfügens oder Aktualisierens möglicherweise neu erstellt wird .index, es kommt also auf den Einzelfall an. Es ist am besten, nicht mehr als 7 Indizes für eine Tabelle zu haben. Wenn es zu viele sind, sollten Sie überlegen, ob es notwendig ist, Indizes für einige Spalten zu erstellen, die nicht häufig verwendet werden.

Optimierung der Datenbankarchitektur

Von der Unterseite über die Anwendungsschicht bis hin zur Architekturschicht ist jedoch von Architektur ohne Geschäftslogik zu sprechen ein Schurke. Die Datenbankarchitektur hängt auch vom Geschäftssystem ab, und die stabile und flexible Bereitstellung des Geschäftssystems ist der Schlüssel. Die Anweisungen zur Architekturoptimierung lauten:

• Partition und Tisch

• Business-Unterbibliothek

• Haupttrennung von Slave-Synchronisation und Lesen und Schreiben

• Datencache

• Master-Slave-Hot-Standby und HA aktiv-aktiv

• …..

Das Obige ist der Inhalt der MySQL-Leistungsoptimierung. Für weitere verwandte Inhalte achten Sie bitte auf PHP-Chinesisch Website (www.php.cn)!