Referenz zu MySQL-Optimierungsparametern!

Vorwort

Wenn es um die Optimierung des täglichen MySQL-Betriebs und der MySQL-Wartung geht, darf die MySQL-Konfigurationsdatei my.cnf nicht ignoriert werden. Die Standardparameter von MySQL können die Anforderungen unseres täglichen Online-Geschäfts nicht erfüllen, daher ist die Optimierung der Parameter ebenfalls ein unverzichtbares Bindeglied. Ich möchte nicht auflisten, wie viele Elemente es in der my.cnf-Konfiguration gibt und welche Bedeutung jedes Element hat. Diese finden Sie in der offiziellen Dokumentation. Im Folgenden werden nur einige Parameter beschrieben, auf die bei der täglichen Arbeit geachtet werden sollte.

Einige Parameter werden unten erklärt. Abhängig von Ihrer Auslastung oder Hardware können natürlich auch andere Einstellungen eine Rolle spielen: In Situationen mit langsamem Speicher und schneller Festplatte, hoher Parallelität und schreibintensiven Arbeitslasten benötigen Sie eine spezielle Abstimmung. Das Ziel hier besteht jedoch darin, dass Sie schnell eine robuste MySQL-Konfiguration erhalten, ohne zu viel Zeit damit zu verbringen, einige unbedeutende MySQL-Einstellungen anzupassen oder die Dokumentation zu lesen, um herauszufinden, welche Einstellungen für Sie wichtig sind.

InnoDB-Konfiguration

Ab MySQL Version 5.5 ist InnoDB die Standard-Speicher-Engine und wird viel häufiger verwendet als jede andere Speicher-Engine. Deshalb muss es sorgfältig konfiguriert werden.

innodb_file_per_table

Tabellendaten und Indizes werden in gemeinsam genutzten Tabellenbereichen oder separaten Tabellenbereichen gespeichert. Die Installation unseres Arbeitsszenarios setzt standardmäßig innodb_file_per_table = ON, was auch die Migration separater Tabellenbereiche während der Arbeit erleichtert. In MySQL 5.6 ist der Standardwert dieser Eigenschaft ON.

_flush_log_at_trx_commit

Der Standardwert ist 1, was bedeutet, dass InnoDB ACID-Funktionen vollständig unterstützt. Dieser Wert ist am besten geeignet, wenn Ihr Hauptanliegen die Datensicherheit ist, 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.

Wenn Sie den Wert auf 2 setzen, führt dies zu unzuverlässigem Verhalten. Da festgeschriebene Transaktionen nur einmal pro Sekunde in das Redo-Protokoll geschrieben werden, ist dieser Wert für einige Szenarien akzeptabel. Beispielsweise ist dieser Wert für den Backup-Knoten des Primärknotens akzeptabel. Ein Wert von 0 ist schneller, kann jedoch bei einem Systemabsturz zu Datenverlusten führen: Gilt nur für Backup-Knoten. Wenn man über diesen Parameter spricht, fällt einem sicherlich ein anderes sync_binlog ein.

innodb_flush_method

Diese Konfiguration bestimmt, wie Daten und Protokolle auf die Festplatte geschrieben werden. Insgesamt gibt es drei Methoden. Wir verwenden standardmäßig O_DIRECT. O_DIRECT-Modus: Der Schreibvorgang der Datendatei erfolgt direkt vom MySQL-Innodb-Puffer auf die Festplatte, ohne den Betriebssystempuffer zu durchlaufen. Der eigentliche Abschluss erfolgt im Flush-Schritt, und das Protokoll muss noch den Betriebssystempuffer durchlaufen.

innodb_log_buffer_size

Diese Konfiguration bestimmt den Cache, der für Transaktionen zugewiesen wird, die noch nicht ausgeführt wurden. Der Standardwert (1 MB) ist im Allgemeinen ausreichend. Wenn Ihre Transaktion jedoch große Binärobjekte oder große Textfelder enthält, wird dieser Cache schnell voll und löst zusätzliche E/A-Vorgänge aus. Schauen Sie sich die Statusvariable Innodb_log_waits an. Wenn sie nicht 0 ist, erhöhen Sie innodb_log_buffer_size.

innodb_buffer_pool_size

Dieser Parameter sollte bei Betrieb und Wartung unbedingt beachtet werden. Der Pufferpool ist ein zentraler Parameter von MySQL. Unter normalen Umständen ist dieser Parameter auf 60 % bis 70 % des physischen Speichers eingestellt. (Allerdings handelt es sich bei unseren Instanzen grundsätzlich um gemischte Bereitstellungen mehrerer Instanzen, daher muss dieser Wert basierend auf der Geschäftsskala analysiert werden.)

innodb_log_file_size

Dies ist die Größe des Redo-Logs. Mithilfe der Redo-Protokollierung wird sichergestellt, dass Schreibvorgänge schnell und zuverlässig ablaufen und nach Abstürzen wiederhergestellt werden können. Wenn Sie wissen, dass Ihre Anwendung häufig Daten schreiben muss und Sie MySQL 5.6 verwenden, können Sie es von Anfang an auf 4G einstellen. (Die spezifische Größe muss entsprechend Ihrem eigenen Unternehmen angepasst werden)

innodb_support_xa

innodb_support_xa kann die zweistufige XA-Transaktionseinreichung von InnoDB umstellen. Standardmäßig unterstützt innodb_support_xa=true die zweistufige XA-Transaktionsübermittlung. Da die zweistufige XA-Transaktionsübermittlung zu redundanten Flush- und anderen Vorgängen führt, beträgt die Auswirkung auf die Leistung 10 %. Um die Leistung zu verbessern, setzen einige Datenbankadministratoren daher innodb_support_xa=false. In diesem Fall werden Redolog und Binlog nicht synchronisiert und es kann Situationen geben, in denen Transaktionen in der Hauptdatenbank übermittelt, aber nicht im Binlog aufgezeichnet werden. Dies kann auch zum Verlust von Transaktionsdaten führen.

innodb_additional_mem_pool_size

Dieser Parameter wird zum Speichern von Datenfeldinformationen und anderen internen Datenstrukturen verwendet. Je mehr Tabellen vorhanden sind, desto mehr Speicher muss hier allokiert werden. Wenn InnoDB in diesem Pool nicht mehr genügend Speicher hat, beginnt InnoDB, Speicher vom Betriebssystem zuzuweisen und schreibt Warnmeldungen in das MySQL-Fehlerprotokoll. Der Standardwert ist 8 MB. Die allgemeine Einstellung beträgt 16 MB.

max_connections

Die Standardanzahl der Verbindungen im MySQL-Server ist relativ gering, normalerweise etwa 100. Es ist am besten, den Maximalwert größer einzustellen. Wenn Sie ihn auf 500 bis 1000 einstellen, belegt im Allgemeinen jeder Link eine bestimmte Menge an Speicher. Je größer der Parameter, desto besser. Manche Leute erhöhen die Größe dieses Parameters, wenn sie auf zu viele Verbindungen stoßen. Wenn jedoch ein Problem mit dem Geschäftsvolumen oder der Programmlogik vorliegt oder die SQL nicht gut geschrieben ist, hilft auch eine Erhöhung dieses Parameters nicht Es ist nur eine Frage der Zeit, bis ein Fehler erneut auftritt. Die Verwendung von Verbindungspooling in Ihrer Anwendung oder Prozesspooling in MySQL kann zur Lösung dieses Problems beitragen.

• Speicherzuweisung auf Seesionsebene

max_threads(当前活跃连接数)* (

read_buffer_size– 顺序读缓冲，提高顺序读效率

+read_rnd_buffer_size– 随机读缓冲，提高随机读效率

+sort_buffer_size– 排序缓冲，提高排序效率

+join_buffer_size– 表连接缓冲，提高表连接效率

+binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率ß

+tmp_table_size– 内存临时表，提高临时表存储效率

+thread_stack– 线程堆栈，暂时寄存SQL语句/存储过程

+thread_cache_size– 线程缓存，降低多次反复打开线程开销

+net_buffer_length– 线程持连接缓冲以及读取结果缓冲

+bulk_insert_buffer_size– MyISAM表批量写入数据缓冲

)

• Speicherzuweisung auf globaler Ebene

global buffer(全局内存分配总和) =

innodb_buffer_pool_size

— InnoDB高速缓冲，行数据、索引缓冲，以及事务锁、自适应哈希等

+ innodb_additional_mem_pool_size

— InnoDB数据字典额外内存，缓存所有表数据字典

+innodb_log_buffer_size

— InnoDB REDO日志缓冲，提高REDO日志写入效率

+key_buffer_size

— MyISAM表索引高速缓冲，提高MyISAM表索引读写效率

+query_cache_size

–查询高速缓存，缓存查询结果，提高反复查询返回效率+table_cahce — 表空间文件描述符缓存，提高数据表打开效率

+table_definition_cache

–表定义文件描述符缓存，提高数据表打开效率

Das ultimative Ziel der Parameteroptimierung besteht darin, MySQL durch eine angemessene Steuerung der Speicherzuweisung eine bessere Nutzung der Ressourcen zu ermöglichen. Es wird empfohlen, die Speicherzuweisung in angemessener Weise zu reduzieren.

Server-ID

Stellen Sie sicher, dass die Server-ID beim Kopieren des Schemas unterschiedlich ist. Normalerweise ist die Master-ID kleiner als die Slave-ID.

log_bin

Wenn Sie möchten, dass der Datenbankserver als Backup-Knoten für den Master-Knoten fungiert, ist die Aktivierung des Binärprotokolls ein Muss. Vergessen Sie in diesem Fall nicht, server_id auf einen eindeutigen Wert festzulegen. Selbst bei nur einem Server ist dies (Aktivieren der Binärprotokollierung) nützlich, wenn Sie eine Datenwiederherstellung zu einem bestimmten Zeitpunkt durchführen möchten: Stellen Sie Ihre Daten von Ihrem letzten Backup wieder her (vollständige Sicherung) und übernehmen Sie die Änderungen im Binärprotokoll (inkrementelle Sicherung). .

Binärprotokolle werden nach der Erstellung dauerhaft gespeichert. Wenn Ihnen also nicht der Speicherplatz ausgeht, 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.

skip_name_resolve

Wenn der Client eine Verbindung zum Datenbankserver herstellt, führt der Server die Auflösung des Hostnamens durch. Wenn 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. Die einzige Einschränkung besteht darin, dass in GRANT-Anweisungen später nur IP-Adressen verwendet werden können. Daher ist beim Hinzufügen dieser Einstellung zu einem vorhandenen System Vorsicht geboten.

sync_binlog

Der Standardwert von sync_binlog ist 0. Wie der Mechanismus des Betriebssystems zum Aktualisieren anderer Dateien synchronisiert MySQL nicht mit der Festplatte, sondern verlässt sich darauf, dass das Betriebssystem das Binärprotokoll aktualisiert.

Wenn sync_binlog =N (N>0), verwendet MySQL die Funktion fdatasync(), um sein geschriebenes Binärprotokoll jedes Mal mit der Festplatte zu synchronisieren, wenn es das Binärprotokoll N-mal schreibt. Am sichersten ist es, wenn innodb_flush_log_at_trx_commit und sync_binlog beide 1 sind. Im Falle eines Absturzes des mysqld-Dienstes oder des Server-Hosts kann das Binärprotokoll höchstens eine Anweisung oder Transaktion verlieren. Da Sie Ihren Kuchen jedoch nicht gleichzeitig essen können, führt dies zu häufigen E/A-Vorgängen, daher ist dieser Modus auch der langsamste Weg. Aus geschäftlichen Gründen und wenn der geschäftliche Druck dies zulässt, ist die Standardkonfiguration die Doppel-1-Konfiguration.

log_slave_update

Wenn die Kaskadenarchitektur im Unternehmen verwendet werden muss, muss der Parameter log_slave_update = 1 aktiviert werden, da die dritte Ebene sonst möglicherweise das von der ersten Ebene generierte Binlog nicht empfangen kann und daher keine Datensynchronisierung durchgeführt werden kann.

tmpdir

Wenn die temporäre Speichertabelle das Limit überschreitet, konvertiert MySQL sie automatisch in eine festplattenbasierte MyISAM-Tabelle und speichert sie im angegebenen tmpdir-Verzeichnis. Konfigurieren Sie tmpdir daher so weit wie möglich auf einem Speichergerät mit guter Leistung und Geschwindigkeit.

Langsame Protokollierung

slow_query_log = 1 #Slow-Log öffnen

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0,5 #Legen Sie fest, wie viele Sekunden eine Abfrage in das langsame Protokoll eingetragen wird

Andere Fragen

Der Einfluss von SSD auf Parameter

Mit der Entwicklung von Wissenschaft und Technologie haben immer mehr Speichergeräte begonnen, von herkömmlichen mechanischen Komponenten auf permanente Speicher aus elektronischen Komponenten umzusteigen, und die Preise werden für Unternehmen immer akzeptabler. Nachdem die Geschwindigkeit der Speicherkomponenten verbessert wurde, scheint es verschwenderisch zu sein, die DB-Konfiguration herkömmlicher mechanischer Komponenten zu verwenden. Daher muss die MySQL-Konfiguration an verschiedene Speichertechnologien angepasst werden. Beispielsweise müssen innodb_io_capacity erhöht, Protokolldateien und Redo erstellt werden sollte auf der mechanischen Festplatte platziert werden, und das Rückgängigmachen sollte auf der mechanischen Festplatte platziert werden. Für SSD sind für atomares Schreiben kein doppelter Schreibpuffer, keine InnoDB-Komprimierung, mehrere Instanzen einer einzelnen Maschine + cgroup usw. erforderlich. Analysieren Sie die E/A-Situation und passen Sie innodb_io_capacity und innodb_max_dirty_pages_pct dynamisch an. Versuchen Sie, innodb_adaptive_flushing anzupassen, um den Effekt zu sehen.

Thread-Pool-Einstellungen

Wir haben noch keine Optimierung für innodb_write_io_threads und innodb_read_io_threads vorgenommen, aber ich glaube, dass durch die Anpassung auf 8 oder 16 die System-E/A-Leistung besser sein wird. Außerdem müssen Sie auf die folgenden Punkte achten: Jede Anpassung muss auf Datenunterstützung und einer gründlichen Analyse basieren, sonst ist diese Art der Optimierung sehr sinnvoll und kann wirklich einen Mehrwert bringen und so gut wie möglich verstehen, warum wir uns auf diese Weise anpassen müssen.

CPU-bezogen

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• table_definition_cache=2000

IO-bezogen

• Innodb_flush_method Es wird empfohlen, O_DIRECT zu verwenden
• Innodb_io_capacity ist auf die maximale IOPS eingestellt, die von der Festplatte unterstützt wird
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• Wenn das Vorlesen von Innodb auf einem Haupt- oder Einzelindexsystem basiert, wird empfohlen, das Vorlesen zu deaktivieren
• Innodb_random_read_ahead = aus

Das obige ist der detaillierte Inhalt vonReferenz zu MySQL-Optimierungsparametern!. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!