Wie kann man MySQL für die Wiederherstellung von MySQL-Dumps anpassen?

7224
Alex R

Meine kleine 5-GB-Datenbank, die über mysqldump 5 Minuten zum Speichern benötigt, die Wiederherstellung dauert 9 Stunden. Glücklicherweise habe ich dies während eines Testlaufs herausgefunden, nicht aus einer tatsächlichen Notfallsituation.

Was sind die besten Parameter, um dies zu beschleunigen?

Ich habe die folgenden Einstellungen auf meinem Server mit 2 GB RAM versucht:

innodb_buffer_pool_size=512M innodb_additional_mem_pool_size=50M innodb_file_per_table innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 innodb_log_file_size=1G innodb_log_buffer_size=1G

Das Seltsame ist, dass selbst mit diesen Einstellungen die Einstellung topzeigt, dass mysqld nur einen Bruchteil des zugewiesenen Speichers verwendet:

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4421 mysql 20 0 247m 76m 5992 S 91 3.7 4:09.33 mysqld
4

3 Antworten auf die Frage

4
RolandoMySQLDBA

Sie sollten diese Zahlen vor dem mysqldump berechnen können .

Bezüglich der Einstellungen, die Sie in der Frage angegeben haben,

innodb_log_file_size=1G

Diese Einstellung ist zu groß !!!

Der innodb_log_file_sizesoll 25% von seininnodb_buffer_pool_size

innodb_log_file_size = 128M

Wenn Sie dies festgelegt haben /etc/my.cnf, müssen Sie folgende Schritte ausführen, um die InnoDB-Protokolldateien zu ändern:

  1. service mysql stop
  2. rm -f /var/lib/mysql/ib_logfile[01]
  3. service mysql start

Wie für die andere Einstellung

innodb_log_buffer_size=1G

Sie möchten nie eine Tonne Daten hier zwischenspeichern, bevor Sie sie an die InnoDB-Protokolldateien senden, insbesondere bei mysqldump-reloads oder umfangreichen Transaktions-COMMITs. Die sollte eine Größenordnung kleiner sein.

innodb_log_buffer_size=32M

Übrigens Sie sollten die binäre Protokollierung vor dem erneuten Laden deaktivieren. Andernfalls landen alle Daten in Ihren Binärprotokollen. Bitte führen Sie einen der folgenden Schritte aus:

  1. Machen Sie dies -> SET SQL_LOG_BIN=0;die erste Zeile der mysqldump-Datei.
  2. Führen Sie in der MySQL-Befehlszeile den Befehl SET SQL_LOG_BIN=0;Ausführen aussource < mysqldumpfile >
  3. Kommentieren Sie log-bin aus /etc/my.cnfund starten Sie MySQL 5.1 neu, laden Sie die mysqldump-Datei, dekommentieren Sie log-bin und starten Sie MySQL neu.

UPDATE 24.07.2011 20:30 Uhr

Wenn Sie über eine mysqldump-Datei verfügen /root/MyData.sql, können Sie die Befehle immer noch so ausführen

SET SQL_LOG_BIN=0; source /root/MyData.sql

Dies fällt unter Option 2.

Einige SQL-Dateien können schwer zu bearbeiten sein und "SET SQL_LOG_BIN = 0;" Stattdessen: echo "SET SQL_LOG_BIN = 0;" | gzip> sql_log_bin.sql.gz; Führen Sie dann Folgendes aus: zcat sql_log_bin.sql.gz backup.sql.gz | Mysql Andy Lee Robinson vor 13 Jahren 0
Ich möchte nur sicherstellen, dass Sie --opt mit dem Dienstprogramm mysqldump verwenden. Dies ist eine große Zeitersparnis beim Nachladen. ohne es erstellt mysqldump die sql-Datei Zeile für Zeile. --opt tut: Fügt Sperren hinzu, löscht und erstellt alle aktivierten Erstellungsoptionen, deaktiviert die Schlüssel vorübergehend für ein schnelles Neuladen und schaltet sie am Ende wieder ein, um die Indizes nach dem Schreiben wiederherzustellen. Es verkettet mehrere Zeilen in Einfügungen, um die Dateigröße und die Parsing-Zeit jeder Anweisung (um bis zu 70%) zu reduzieren, und ermöglicht es --quick, die Belastung des Dumping-DBs während der Ausführung zu reduzieren. reduziert die Ladezeit um 80% ppostma1 vor 9 Jahren 0
@ ppostma1 --opt ist standardmäßig aktiviert. In der Dokumentation heißt es schon: http://dev.mysql.com/doc/refman/5.6/de/mysqldump.html#option_mysqldump_opt RolandoMySQLDBA vor 9 Jahren 0
Ich habe 4 verschiedene Linux-Varianten, auf denen Online-Server laufen, und einige MySQL-Paketmanager von distro deaktivieren sie! Es kann ein Unterschied zwischen 12 Stunden für das Neuladen einer WP-Datenbank und 8 Minuten sein, wenn sie aktiviert ist. Ich finde, die Erklärung des Managers lautet "Verfügbarkeit inkrementeller Backups". Mein bestes Verständnis ist, wenn der Server routinemäßig gesichert wird, er könnte standardmäßig deaktiviert sein. Dann muss der Benutzer die Option --opt manuell hinzufügen, um anzugeben, dass dies kein inkrementeller Sicherungsversuch ist. ppostma1 vor 9 Jahren 0
@ ppostma1 Oh das ist beängstigend !!! Die Dokumentation sagt, dass sie standardmäßig aktiviert ist. Meine Vermutung ist, dass die Distributionen, die "--opt" nicht haben, aus der Quelle zusammengestellt worden sein müssen und die Option vergessen wurde. Ich werde diese Ausgabe definitiv in meiner Hosentasche für zukünftige Begegnungen wie diese behalten. Danke dir. RolandoMySQLDBA vor 9 Jahren 1
0
Denis Kokorin

Ich befand mich in einer ähnlichen Situation, da ein Dump-Restore zu lange dauerte. In meinem Fall konnte ich die Wiederherstellung 3-10 mal beschleunigen, indem die Erstellung des MySQL-Indexes verschoben wurde.

Kurz gesagt, der Index wird bei jedem Insert neu berechnet. Es ist also besser, den Index für die gesamte Tabelle auf einmal zu berechnen.

Hier ein Beispiel, wie MySQL Daten exportiert:

CREATE TABLE `SOME_TABLE` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SOME_COLUMN` varchar(255) NOT NULL, `OTHER_COLUMN` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`), KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`) );  INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`) VALUES (...), (...), ... ;

Wenn Sie Millionen von Zeilen in einer Tabelle mit Indizes haben, werden diese wesentlich länger wiederhergestellt, als wenn Indizes nach dem Einfügen angewendet würden.

Sie erhalten eine höhere Importgeschwindigkeit, wenn Sie nach dem Einfügen nur die Anweisungen zur Indexerstellung verschieben:

CREATE TABLE `SOME_TABLE` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SOME_COLUMN` varchar(255) NOT NULL, `OTHER_COLUMN` varchar(255) NOT NULL, PRIMARY KEY (`id`) );  INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`) VALUES (...), (...), ... ;  ALTER TABLE `SOME_TABLE` ADD UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`), ADD KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`);

Überprüfen Sie mein Repository auf ein einfaches Python-Skript, das die obige Umwandlung ausführt.

Dies ist nur eine Antwort, die in einen Kommentar eingeschlossen ist. Außerdem ist Ihr Python-Skript zwar hilfreich, aber wenn jemand Python oder ein Skript nicht verwenden möchte? Ist die Antwort dann nicht einfach "Stellen Sie keine Indizes ein, bis alle Daten eingefügt wurden?" JakeGould vor 6 Jahren 3
Dies ist eine großartige Antwort, die nutzlos (vage und nicht verbindlich) wäre, wenn sie nicht mit einem Link zu einer Referenzimplementierung verbunden wäre. Alex R vor 6 Jahren 0
-1

Ihr Problem ist E / A, nicht Speicher. Wenn Sie ein Profil für den Datenträger erstellen, werden Sie feststellen, dass er beim Lesen und Schreiben wie verrückt ausfällt, insbesondere wenn er sich auf demselben Datenträger befindet wie das Backup.

Ich würde die Datei auf ein anderes physisches Laufwerk verschieben und sehen, ob das hilft.

Verwandte Probleme