Detaillierte Erläuterung der Optimierung der MySQL-Konfigurationsdatei my.cnf

MySQL 5.5.13
Parameterbeschreibung:
[Client]
Zeichensatz- server = utf8
port = 3306
socket = /data/mysql/3306/mysql.sock
[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = / data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
# Der Parameter table_cache legt die Anzahl der Tabellencaches fest. Bei jedem Verbindungsaufbau wird mindestens ein Tabellencache geöffnet. #Daher sollte die Größe von table_cache mit der Einstellung von max_connections zusammenhängen. Beispielsweise sollten Sie für 200 # Verbindungen, die parallel ausgeführt werden, über einen Tabellencache von mindestens 200 × N verfügen, wobei N die maximale Anzahl von Tabellen in einem Join ist, für die die Anwendung # Abfragen ausführen kann. Darüber hinaus müssen einige zusätzliche Dateideskriptoren für temporäre Tabellen und Dateien reserviert werden.
# Wenn Mysql auf eine Tabelle zugreift und die Tabelle im Cache geöffnet wurde, kann direkt auf den Cache zugegriffen werden. Wenn # er nicht zwischengespeichert wurde, ist in der Mysql-Tabelle noch Platz buffer , dann wird die Tabelle geöffnet und in den Tabellenpuffer gestellt. Wenn der Tabellencache voll ist, wird die derzeit nicht verwendete Tabelle nach bestimmten Regeln freigegeben oder der Tabellencache wird vorübergehend zur Speicherung erweitert Cache sorgt für einen schnelleren Zugriff auf Tabelleninhalte. Durch das Ausführen von Flush-Tabellen wird der Inhalt des Caches gelöscht. Im Allgemeinen können Sie beurteilen, ob Sie den Wert von table_cache erhöhen müssen, indem Sie sich die Statuswerte von Open_tables # und Opened_tables während der Spitzenlaufzeit der Datenbank ansehen (wobei open_tables die Anzahl der derzeit geöffneten Tabellen ist). Opened_tables ist die Anzahl der geöffneten Tabellen. Das heißt, wenn open_tables nahe bei table_cache liegt und der Wert von Opened_tables allmählich zunimmt, sollten Sie erwägen, die Größe dieses #-Werts zu erhöhen. Wenn Table_locks_waited relativ hoch ist, muss auch table_cache erhöht werden.
open_files_limit = 10240
table_cache = 512
#Nicht dynamische Variablen, der Dienst muss neu gestartet werden
# Geben Sie die Anzahl der möglichen Verbindungen zu MySQL an. Wenn der MySQL-Hauptthread in kurzer Zeit viele Verbindungsanfragen empfängt, wird dieser Parameter wirksam und der Hauptthread verbringt kurze Zeit damit, die Verbindung zu überprüfen und einen neuen Thread zu starten. Der Wert des Parameters back_log gibt an, wie viele Anfragen in kurzer Zeit im Stack gespeichert werden können, bevor MySQL vorübergehend nicht mehr auf neue Anfragen reagiert. Wenn das System in kurzer Zeit viele Verbindungen hat, müssen Sie den Wert dieses Parameters erhöhen, der die Größe der Überwachungswarteschlange für eingehende TCP/IP-Verbindungen angibt. Verschiedene Betriebssysteme haben ihre eigenen Grenzen für diese Warteschlangengröße. Der Versuch, back_log auf einen höheren Wert als den Grenzwert Ihres Betriebssystems festzulegen, hat keine Auswirkung. Der Standardwert ist 50. Für Linux-Systeme wird empfohlen, den Wert auf eine Ganzzahl kleiner als 512 festzulegen.
back_log = 600
#MySQL erlaubt die maximale Anzahl von Verbindungen
max_connections = 5000
#Ja, wie viele Fehlerverbindungen zulässig sind
max_connect_errors = 6000
#Verwenden Sie die MySQL-Option --skip-external-locking, um externe Sperren zu vermeiden. Diese Option ist standardmäßig aktiviert
external-locking = FALSE
# Legen Sie die maximale Paketgröße fest, begrenzen Sie die Größe der vom Server akzeptierten Datenpakete und vermeiden Sie Probleme Bei der Ausführung von überlangen SQL-Anweisungen ist der Standardwert 16 MB. Wenn der MySQL-Client oder MySQL-Server ein Paket empfängt, das größer als max_allowed_packet ist, wird der Fehler „Paket zu groß“ ausgegeben und die Verbindung wird geschlossen. Bei einigen Clients kann es bei der Abfrageausführung zu einem Fehler „Verbindung zum MySQL-Server verloren“ kommen, wenn die Kommunikationspakete zu groß sind. Der Standardwert ist 16M.
#dev-doc: http://www.php.cn/
max_allowed_packet = 32M
# Sort_Buffer_Size ist eine Verbindung Ebenenparameter: Wenn jede Verbindung (Sitzung) diesen Puffer zum ersten Mal verwenden muss, wird der festgelegte Speicher sofort zugewiesen.
#Sort_Buffer_Size ist nicht größer, desto besser Da es sich um einen Parameter auf Verbindungsebene handelt, können zu große Einstellungen + hohe Parallelität die Systemspeicherressourcen erschöpfen. Beispiel: 500 Verbindungen verbrauchen 500*sort_buffer_size(8M)=4G Speicher
#Sort_Buffer_Size wird mmap() anstelle von malloc() für die Speicherzuweisung verwendet Die Effizienz wird reduziert.
#Technische Einführunghttp://www.php.cn/
#dev-doc: http://www.php.cn/
#explain select*from table where order limit; filesort erscheint
#Schlüsseloptimierungsparameter
sort_buffer_size = 8M
#Die für den Zwischentabellen-Assoziationscache verwendete Größe
join_buffer_size = 1M
# Server-Thread-Cache Dieser Wert gibt die Anzahl der im Cache gespeicherten Threads an, die wiederverwendet werden können. Wenn beim Trennen der Verbindung noch Platz im Cache vorhanden ist, wird der Thread des Clients im Cache abgelegt Wird erneut angefordert, wird die Anforderung aus dem Cache gelesen. Wenn der Cache leer ist oder es sich um eine neue Anforderung handelt, wird der Thread neu erstellt. Wenn viele neue Threads vorhanden sind, kann die Systemleistung verbessert werden. Durch Vergleichen der Statusvariablen Connections und Threads_created können Sie die Rolle dieser Variablen erkennen.
thread_cache_size = 300
# Ob der Wert von thread_concurrency richtig eingestellt ist oder nicht Eine große Auswirkung auf die Leistung von MySQL. Bei mehreren CPUs (oder mehreren Kernen) führt eine falsche Einstellung des Werts von thread_concurrency dazu, dass MySQL mehrere CPUs (oder mehrere Kerne) nicht vollständig nutzen kann und nur eine CPU (bzw Kern) können gleichzeitig arbeiten. thread_concurrency sollte auf das Zweifache der Anzahl der CPU-Kerne eingestellt werden. Wenn es beispielsweise eine Dual-Core-CPU gibt, sollte thread_concurrency 4 sein; für 2 Dual-Core-CPUs sollte der Wert von thread_concurrency 8 sein
# Dies ist ein wichtiger Optimierungsparameter
thread_concurrency = 8
# Für Benutzer, die MySQL verwenden, wird jeder mit dieser Variablen vertraut sein. Bei der MyISAM-Engine-Optimierung in den vergangenen Jahren war dieser Parameter ebenfalls ein wichtiger Optimierungsparameter. Mit der Entwicklung hat dieser Parameter jedoch auch einige Probleme aufgedeckt. Der Speicher von Maschinen wird immer größer und der Mensch ist es gewohnt, bisher nützlichen Parametern immer größere Werte zuzuweisen. Auch die Erhöhung dieses Parameters verursachte eine Reihe von Problemen. Lassen Sie uns zunächst das Funktionsprinzip von query_cache_size analysieren: Nachdem eine SELECT-Abfrage in der Datenbank ausgeführt wurde, wird die Anweisung von der Datenbank zwischengespeichert den Client aus dem Cache. Ein wichtiger Punkt hierbei ist, dass die Verwendung von Query_cache durch DB erfordert, dass sich die an der Anweisung beteiligte Tabelle in diesem Zeitraum nicht geändert hat. Wenn sich also die Tabelle ändert, wie werden die Daten in Query_cache verarbeitet? Zuerst müssen Sie alle Anweisungen im Zusammenhang mit Query_cache und der Tabelle ungültig machen und dann Aktualisierungen schreiben. Wenn dann der Query_cache sehr groß ist, die Tabelle viele Abfragestrukturen hat und die Abfrageanweisung langsam ungültig wird, wird eine Aktualisierung oder Einfügung sehr langsam sein. Sie sehen also, warum die Aktualisierung oder Einfügung so langsam ist. Daher ist dieser Parameter in Systemen, in denen die Menge an Datenbankschreibvorgängen oder -aktualisierungen relativ groß ist, nicht für eine zu große Zuweisung geeignet. Darüber hinaus wird empfohlen, diese Funktion in Systemen mit hoher Parallelität und großem Schreibvolumen zu deaktivieren.
#Wichtige Optimierungsparameter (Hauptdatenbank-Hinzufügungen, -Löschungen und -Änderungen – MyISAM)
query_cache_size = 512M
#Geben Sie den Puffer an, der verwendet werden soll kann von einer einzelnen Abfrage verwendet werden Bereichsgröße, der Standardwert ist 1M
query_cache_limit = 2M
#Der Standardwert ist 4 KB, die Einstellung eines großen Werts ist gut für große Datenmengen Abfragen, aber wenn es sich bei Ihren Abfragen um kleine Datenabfragen handelt, kann dies leicht zu Speicherfragmentierung und -verschwendung führen Wenn die Fragmentierungsrate des Abfrage-Cache 20 % überschreitet, können Sie FLUSH QUERY CACHE verwenden, um den Cache zu defragmentieren, oder versuchen, query_cache_min_res_unit zu reduzieren, wenn es sich bei Ihren Abfragen ausschließlich um kleine Datenmengen handelt.
#Query-Cache-Auslastung = (query_cache_size – Qcache_free_memory) / query_cache_size * 100 %
#Wenn die Abfrage-Cache-Auslastung unter 25 % liegt, bedeutet dies, dass query_cache_size zu groß eingestellt ist und entsprechend reduziert werden kann. Wenn die Abfrage-Cache-Auslastung über 80 % und Qcache_lowmem_prunes > 50 liegt, bedeutet dies, dass query_cache_size möglicherweise etwas ist klein, oder es ist zu fragmentiert.
# Abfrage-Cache-Trefferrate = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100 %
query_cache_min_res_unit = 2k
default-storage- engine = MyISAM
#Begrenzt die für jeden Datenbankthread verwendete Stapelgröße. Die Standardeinstellungen sind für die meisten Anwendungen ausreichend.
thread_stack = 192K
# Die verfügbaren Ebenen sind wie folgt:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED – nicht festgeschrieben gelesen 2. READ COMMITTE – festgeschrieben gelesen 3. REPEATABLE READ – wiederholbar gelesen 4 .SERIALIZABLE - SERIAL
transaction_isolation = READ-COMMITTED
# Die Standardgröße von tmp_table_size beträgt 32 MB. Wenn eine temporäre Tabelle diese Größe überschreitet, generiert MySQL einen Fehler der Form „Tabellenname ist voll“. Wenn Sie viele erweiterte GROUP BY-Abfragen durchführen, erhöhen Sie den Wert „tmp_table_size“.
tmp_table_size = 246M
max_heap_table_size = 246M
#Index-Cache-Größe: Sie bestimmt die Geschwindigkeit der Datenbankindexverarbeitung, insbesondere des Index Lesegeschwindigkeit
key_buffer_size = 512M
# MySQL-Lesepuffergröße. Eine Anforderung für einen sequentiellen Scan der Tabelle reserviert einen Lesepuffer und MySql reserviert dafür einen Speicherpuffer. Die Variable read_buffer_size steuert die Größe dieses Puffers. Wenn sequentielle Scan-Anfragen für eine Tabelle sehr häufig sind und Sie das Gefühl haben, dass die häufigen Scans zu langsam ausgeführt werden, können Sie die Leistung verbessern, indem Sie den Wert dieser Variablen und die Größe des Speicherpuffers erhöhen.
read_buffer_size = 4M
# Puffergröße für zufälliges Lesen (Abfragevorgang) von MySQL. Wenn Zeilen in beliebiger Reihenfolge gelesen werden (z. B. in sortierter Reihenfolge), wird ein zufälliger Lesepuffer zugewiesen. Beim Durchführen einer Sortierabfrage scannt MySql zunächst den Puffer, um Festplattensuchen zu vermeiden und die Abfragegeschwindigkeit zu verbessern. Wenn eine große Datenmenge sortiert werden muss, kann dieser Wert entsprechend erhöht werden. MySql weist diesen Pufferspeicher jedoch jeder Kundenverbindung zu. Sie sollten daher versuchen, diesen Wert entsprechend festzulegen, um übermäßigen Speicheraufwand zu vermeiden.
read_rnd_buffer_size = 16M
#Batch-Einfügungsdaten-Cache-Größe, die die Einfügungseffizienz effektiv verbessern kann, der Standardwert ist 8M
bulk_insert_buffer_size = 64M
# Pufferung erforderlich für die Neuordnung, wenn sich MyISAM-Tabellen ändern
myisam_sort_buffer_size = 128M
# Die maximal zulässige temporäre Dateigröße, wenn MySQL den Index neu aufbaut (bei REPAIR, ALTER TABLE oder LOAD DATA INFILE).
# Wenn Die Dateigröße ist größer als dieser Wert, der Index wird über Schlüsselwertpufferung erstellt (langsamer)
myisam_max_sort_file_size = 10G
# Wenn eine Tabelle mehr als hat Ein Index, MyISAM kann sie beheben, indem mehr als ein Thread durch parallele Sortierung verwendet wird.
# Dies ist eine gute Wahl für Benutzer mit mehreren CPUs und viel Speicher.
myisam_repair_threads = 1
# MyISAM-Tabellen, die nicht ordnungsgemäß geschlossen wurden, automatisch überprüfen und reparieren
myisam_recover
interactive_timeout = 120
wait_timeout = 120
innodb_data_home_dir = /data/mysql/3306/data
#Tablespace-Datei wichtige Daten
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#Dieser Parameter wird verwendet, um die Speicherpoolgröße der Datenverzeichnisinformationen und anderer in InnoDB gespeicherter interner Datenstrukturen festzulegen, ähnlich wie Bibliothekscache von Oracle. Dies ist kein zwingender Parameter und kann überschritten werden.
innodb_additional_mem_pool_size = 16M
# Dies ist sehr wichtig für Innodb-Tabellen. Innodb-Tabellen reagieren empfindlicher auf Pufferung als MyISAM-Tabellen. MyISAM kann unter der Standardeinstellung „key_buffer_size“ gut laufen, aber Innodb läuft im Schneckentempo unter der Standardeinstellung „innodb_buffer_pool_size“. Da Innodb sowohl Daten als auch Indizes zwischenspeichert, besteht keine Notwendigkeit, dem Betriebssystem zu viel Speicher zu überlassen. Wenn Sie also nur Innodb verwenden müssen, können Sie es auf 70–80 % des verfügbaren Speichers einstellen. Einige Regeln, die für key_buffer gelten, sind: Wenn Ihr Datenvolumen nicht groß ist und nicht explosionsartig wächst, besteht keine Notwendigkeit, innodb_buffer_pool_size zu groß einzustellen
innodb_buffer_pool_size = 512M
#Die Anzahl der Datei-IO-Threads beträgt im Allgemeinen 4, unter Windows kann sie jedoch größer eingestellt werden.
innodb_file_io_threads = 4
# Die Anzahl der erlaubten Threads im InnoDb-Kern
# Der optimale Wert hängt von der Anwendung ab. Hardware Und die Planungsmethode des Betriebssystems.
# Ein zu hoher Wert kann zu gegenseitigem Thread-Ausschluss-Thrashing führen.
innodb_thread_concurrency = 8
# Wenn dieser Parameter auf 1 gesetzt ist, wird das Protokoll nach dem Commit jeder Transaktion auf die Festplatte geschrieben. Um die Leistung zu gewährleisten, kann er auf 0 oder 2 gesetzt werden, allerdings besteht bei einem Ausfall die Gefahr eines Datenverlusts. Die Einstellung 0 bedeutet, dass das Transaktionsprotokoll in die Protokolldatei geschrieben wird und die Protokolldatei einmal pro Sekunde auf die Festplatte geschrieben wird. Die Einstellung 2 bedeutet, dass das Transaktionsprotokoll beim Festschreiben in das Protokoll geschrieben wird, die Protokolldatei jedoch jeweils einmal auf die Festplatte geleert wird.
innodb_flush_log_at_trx_commit = 2
#Dieser Parameter bestimmt die von einigen Protokolldateien verwendete Speichergröße in M. Ein größerer Puffer kann die Leistung verbessern, aber unerwartete Fehler führen zu Datenverlust.
innodb_log_buffer_size = 16M
# Dieser Parameter bestimmt die Größe der Datenprotokolldatei. In M können größere Einstellungen die Leistung verbessern, erhöhen aber auch die Zeit, die zum Wiederherstellen einer ausgefallenen Datenbank erforderlich ist
innodb_log_file_size = 128M
#Um die Leistung zu verbessern, kann MySQL Protokolldateien in einer Schleife in mehrere Dateien schreiben. Die empfohlene Einstellung ist 3M
innodb_log_files_in_group = 3
# Empfohlene Lektüre http://www.php.cn/
# Buffer_Pool Die Anzahl der Dirty_Pages wirkt sich direkt auf die Abschaltzeit von InnoDB aus. Der Parameter innodb_max_dirty_pages_pct kann das Verhältnis von Dirty_Page in Buffer_Pool direkt steuern, und glücklicherweise kann innodb_max_dirty_pages_pct dynamisch geändert werden. Reduzieren Sie daher vor dem Schließen von InnoDB innodb_max_dirty_pages_pct und erzwingen Sie das Leeren des Datenblocks für einen bestimmten Zeitraum, was die Abschaltzeit von MySQL erheblich verkürzen kann.
innodb_max_dirty_pages_pct = 90
# InnoDB verfügt über einen integrierten Deadlock-Erkennungsmechanismus, der dazu führen kann, dass nicht abgeschlossene Transaktionen zurückgesetzt werden. Wenn Sie jedoch die Sperrtabellen-Anweisung von MyISAM oder eine Transaktions-Engine eines Drittanbieters in Verbindung mit InnoDB verwenden, kann InnoDB Deadlocks nicht erkennen. Um diese Möglichkeit auszuschließen, kann innodb_lock_wait_timeout auf einen ganzzahligen Wert gesetzt werden, der MySQL anweist, wie lange (in Sekunden) warten soll, bevor es anderen Transaktionen erlaubt, Daten zu ändern, die letztendlich einem Transaktions-Rollback unterliegen
innodb_lock_wait_timeout = 120
#Exklusiver Tabellenbereich (geschlossen)
innodb_file_per_table = 0
#mysqld mit –slow-query- log-file= starten /data/mysql/3306/slow.log
slow_query_log
long_query_time = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
#Updates aus der Datenbank konfigurieren Ob eine Binärdatei geschrieben werden soll Wenn diese Slave-Bibliothek die Master-Bibliothek anderer Slave-Bibliotheken sein soll, muss dieser Parameter festgelegt werden, damit die Slave-Bibliothek eine Protokollsynchronisierung durchführen kann. Dieser Parameter muss zusammen mit -logs-binLog-Slave-Updates
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
#STATEMENT,ROW,MIXED
# SQL-Anweisungsbasierte Replikation (SBR), zeilenbasierte Replikation (RBR), Replikation im gemischten Modus (mixed-based replication, MBR). Dementsprechend gibt es drei Formate von Binlog: STATEMENT, ROW und MIXED.
binlog_format = MIXED
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
skip-name-resolve
#master-connect -retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
server-id = 1
[mysqldump]
schnell
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[ mysqlhotcopy]
interactive-timeout

Das Obige ist die detaillierte Erklärung der Optimierung der MySQL-Konfigurationsdatei my.cnf Inhalt, achten Sie bitte auf PHP Chinese Net (www.php.cn)!