Heute werden wir immer wieder über mySQL sprechen. Lassen Sie uns die Optimierung verstehen und über viele Serverparameter sprechen.
Lass uns anfangen.
Start
Den Server lassen wir auf dem CentOS sein . Optimiert die Methode zum Bearbeiten der Konfiguration der my.cnf .
Das Einstellen einiger Parameter kann die
Leistung der Serverdatenbank mehrmals verbessern !
Lassen Sie uns zunächst entscheiden, was wir im Allgemeinen optimieren - das heißt, wie viele Tabellen auf welcher Engine wir haben, welche Hardware wir haben und unter welchen Parametern wir das Ganze anpassen werden.
Dafür nehmen wir htop (als schönes und intuitives Werkzeug):
yum install htop
Ableiten von htop :
htop
Wir bekommen so etwas:
Schreiben Sie sich in die my.cnf :
# 3 , 4
Lassen Sie uns nun die Anzahl der Tabellen und ihre Typen herausfinden.
Dafür nehmen wir den MySQL-Tuner :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Lass uns laufen:
perl mysqltuner.pl
Fazit ungefähr:
Schreiben wir uns in my.cnf :
# 64M myisam, 770M innoDB
Eine typische Konfiguration wird normalerweise wie folgt empfohlen:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 144 <a
href="https://ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1
%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql"
target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a>
= 0 slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
Lassen Sie uns nun herausfinden, was wir hier optimieren werden, warum, wie und warum (insbesondere warum diese Parameter nicht ausreichen.
Optimierung und Konfiguration
Zunächst können Sie zum Ende der Ausgabe des MySQL-Tuners scrollen und sehen, was empfohlen wird. In unserem Fall sieht es ungefähr so aus:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
Wir werden uns nicht auf eine sinnlose Substitution einlassen und die Parameter des MySQL durchgehen , die für uns in erster Linie von Interesse sein könnten. Was ist was:
das Überspringen der externen Verriegelung - entfernt die äußere Verriegelung, die schneller ist;
-name-the überspringt die RESOLVE , - ermöglicht es MySQL , die Antwort auf die Anforderung einer DNS- Überprüfung der Clientverbindung zum Server MySQL zu vermeiden .
Daher verwendet der Server MySQL nur
IP- URLs anstelle von Hostnamen, die etwas, aber schneller sind.
binlog_cache _ size, - Die Größe des Caches zum Speichern von Änderungen im Binärprotokoll. Legt die Größe nur für den Transaktionscache fest. Do 100M - wird nicht mehr benötigt.
innodb_stats_on_metadata = 0 (OFF) , - um mit
INFORMATION_SCHEMA, SHOW TABLE STATUS oder SHOW INDEX zu beschleunigen , deaktivieren Sie die Aktualisierungsstatistik für Funktionen wie
Abfrage _cache_size = 128M und query_sache_type
= 1 , - Anforderungscaches. 1 - im Prinzip aktiviert, 128M Limit. Es wird nicht
empfohlen, über 256 MB zu liegen , da dies zu einer Blockierung führen kann.
Da wir mehr als InnoDB- Tabellen haben, verschwindet das Cache-Bett .
Wenn die Version MySQL 5.6 query_cache_size deaktiviert ist und die Version 8.0
standardmäßig gelöscht ist, werden alle Tabellen und Indizes in einer einzigen Datei gespeichert. Daher verwenden wir innodb_file_per_table = 1.
Der Wert innodb_open_files und table_open_cache - es wird empfohlen, beide Optionen in 4096 oder 4096 festzulegen 8192 . A berechnet im Allgemeinen als die Anzahl der Tabellen in allen Basen multipliziert mit 2 , ungefähr.
Bei der Arbeit mit InnoDB ist der wichtigste Parameter innodb_buffer_pool_size . Es basiert auf dem Prinzip "Je mehr, desto besser". Es wird empfohlen, bis zu 70-80% des Arbeitsspeichers des Servers zuzuweisen .
innodb_log_file_size - Beeinflusst die Schreibgeschwindigkeit, legt die Größe des Operationsprotokolls fest (Operationen werden zuerst in das Protokoll geschrieben und dann auf die Daten auf der Festplatte angewendet). Je größer dieses Protokoll ist, desto schneller arbeiten die Datensätze (da mehr davon in der Protokolldatei enthalten sind). Es gibt immer zwei Dateien und ihre Größe ist gleich. Der Parameterwert legt die Größe einer Datei fest.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
Eine große Größe der Installation innodb_log_file_size kann zu einer Leistungssteigerung führen, aber zugleich wird Erholzeit zu erhöhen, wählen Sie aus 256M bis 1G .
innodb_log _ buffer_size - Größe des Transaktionspuffers. Es wird generell empfohlen, sich nicht zu bewerben, wenn BLOB und TEXT large nicht verwendet werden.
innodb_flush _ Methode , - definiert die Logik zum Löschen von Daten auf die Festplatte. In modernen Systemen mit RAID- und Backup-Sites können Sie zwischen ODSYNC wählen und ODIRECT , - der erste Parameter ist schneller, der zweite sicherer.
_ size bed key_buffer - Puffer für die Arbeit mit Schlüsseln und Indizes und sort_buffer - Puffer zum Sortieren. Wenn Sie keine MyISAM- Tabellen verwenden, wird empfohlen, key_buffer_size zum Speichern von temporären
Tabellenindizes auf 32 MB festzulegen .
Der Parameter thread_cache _ size gibt die Anzahl der Threads (Threads) an, die den Cache verlassen, wenn ein Client die Verbindung trennt. Bei einer neuen Verbindung wird der Thread nicht erstellt, sondern aus dem Cache entnommen, wodurch Ressourcen unter hoher Last gespart werden.
innodb_flush_log_attrx_commit , - kann den Durchsatz von Datensätzen auf das Hundertfache der Basis erhöhen. Es bestimmt, ob MySQL jeden Vorgang auf die Festplatte (in eine Protokolldatei) speichert.
innodb_flush_log_at_trx_commit = 1 wird für Fälle verwendet, in
denen die Datenaufbewahrung - Priorität Nummer eins ist.
innodb_flush_log_at_trx_commit = 2 für die Fälle, in denen ein kleiner Datenverlust nicht kritisch ist. Es gibt auch 0 (Null) - die produktivste, aber unsicherste Option.
max_connections - Wenn der Fehler "Zu viele Verbindungen" angezeigt wird ,Diese Option sollte erhöht werden. Die Optimierung hat also keinen großen Vorteil.
Die Anzahl der Eingabe- / Ausgabedateien in InnoDB-Streams spezifizierte Optionen innodb_read_io_threads , innodbwrite_io_threads , dieser Parameter wird normalerweise auf 4 oder 8 gesetzt , das in der SSD 16 eingestellte Fast-ROM. Bedeutung innodb_thread_concurrency Legen Sie die Anzahl der Kerne fest * 2 .
Die Konfiguration ist wie folgt:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
skip-name-resolve
binlog_cache_size = 100M
thread_cache_size = 32
innodb_stats_on_metadata = OFF
query_cache_limit = 1M
query_cache_size = 0 query_cache_type = 1
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256
innodb_log_buffer_size = 6M
innodb_additional_mem_pool_size = 16M
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 6
innodb_file_per_table = 1
key_buffer_size = 32M
tmp_table_size = 64M
max_connections = 350
sort_buffer_size = 16M read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 8M
thread_stack = 1M
binlog_cache_size = 8M
tmp_table_size = 128M
table_open_cache = 2048
[mysqldump] quick
quote-names
max_allowed_packet = 16M
Und schließlich können Sie die Empfehlungen des Tuners sehen und ihnen folgen.
Fazit
Hier hat sich eine so interessante Konfiguration herausgestellt. Wenn Sie Schwierigkeiten haben, sollten Sie zunächst einen mySQL- Rechner verwenden, der Ihnen die wichtigsten Parameter anzeigt und es Ihnen ermöglicht, nicht über den verfügbaren Speicher hinauszugehen - schließlich hängt alles davon ab:
Vielen Dank für Ihre Aufmerksamkeit. Beteiligen Sie sich an der Diskussion.