MySQL-performance beim hinzufügen einer Spalte zu einer großen Tabelle

Ich habe MySQL 5.5.37 mit InnoDB lokal installiert mit apt-get auf Ubuntu 13.10. Mein Rechner i7-3770 + 32Gb Speicher + SSD Festplatte auf meinen desktop. Für eine Tabelle "mytable", die enthält nur 1,5 Millionen Datensätze der folgenden DDL-Abfrage dauert mehr als 20 min (!):

ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

Gibt es eine Möglichkeit, es zu verbessern?
Ich überprüfte

show processlist;

war und es zeigt, dass es das kopieren von meinem Tisch aus irgendeinem Grund.
Es ist verstörend unbequem. Gibt es eine Möglichkeit zu deaktivieren diese Kopie?
Gibt es andere Möglichkeiten um die Leistung zu verbessern, das hinzufügen einer Spalte zu einem großen Tisch?

Anderes, als dass meine DB ist relativ klein mit nur 1,3 Gb dump-Größe. Daher sollte es (theoretisch) passen 100% in den Speicher.

Gibt es Einstellungen, die helfen können?
Würde die migration zu Precona nichts ändern für mich?

Hinzufügen: ich habe

innodb_buffer_pool_size = 134217728
Obwohl es in den Hauptspeicher paßt, es zu schreiben, hat alles wieder auf die Festplatte um Sie permanent zu machen.
mögliche Duplikate von "optimieren" mySql-für eine schnellere alter table add column
Sie reden MyISAM vs InnoDB für mich

InformationsquelleAutor Artem | 2014-07-09

  1. 11

    Gibt es andere Möglichkeiten um die Leistung zu verbessern, das hinzufügen einer Spalte zu einem großen Tisch?

    Kurze Antwort: Nein. Sie können die ENUM-und SET-Werte werden sofort, und Sie können fügen Sie sekundäre Indizes während der Verriegelung nur schreibt, sondern verändern die Struktur einer Tabelle erfordert immer eine Tabelle kopieren.

    Lange Antwort: dein wirkliches problem ist nicht wirklich performance, aber die lock-Zeit. Es spielt keine Rolle, wenn es langsam ist, ist es nur wichtig, dass andere clients können nicht führen Sie Abfragen durch, bis Ihr ALTER TABLE ist fertig. Es gibt einige Optionen in diesem Fall:

    1. Können Sie die pt-online-schema-change, von Percona toolkit. Sichern Sie Ihre Daten zuerst! Das ist die einfachste Lösung, aber funktioniert möglicherweise nicht in allen Fällen.

    2. Wenn Sie nicht mit foreign keys und es ist langsam, weil Sie haben eine Menge von Indizes, es könnte schneller sein, für Sie zu erstellen Sie eine Kopie der Tabelle mit den änderungen, die Sie benötigen, aber keine sekundären Indizes, fülle es mit den Daten, und erstellen Sie alle Indizes mit einer einzigen alter table am Ende.

    3. Wenn es einfach ist für Sie Replikate erstellen, wie wenn Sie hosted in Amazon RDS können Sie erstellen eine master-master-Replikat, führen Sie die Anweisung alter table gibt, lasst es wieder in sync, und switch-Instanzen nach fertig.

    UPDATE

    Als die anderen genannten, MySQL 8.0 INNODB-Unterstützung für instant-Spalte hinzu. Es ist nicht eine Magische Lösung, es hat Einschränkungen und Nebenwirkungen -- es kann nur die Letzte Spalte, die Tabelle muss nicht einen full-text-index, etc-aber sollte in vielen Fällen helfen.

    Können Sie angeben, explizite ALGORITHM=INSTANT LOCK=NONE Parameter, und, wenn eine sofortige schema-änderung ist nicht möglich, MySQL zu einem Fehler, anstatt zurückzufallen, um INPLACE oder COPY. Beispiel:

    ALTER TABLE mytable
ADD COLUMN mycolumn varchar(36) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

    https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

    "Sie können mit der [pt-online-schema-change][1], von Percona toolkit. Sichern Sie Ihre Daten zuerst! Das ist die einfachste Lösung, aber funktioniert möglicherweise nicht in allen Fällen." -- dies ist eine sehr gute Beratung

    InformationsquelleAutor Pedro Werneck

  2. 4

    MariaDb 10.3, MySQL 8.0 und wahrscheinlich auch andere MySQL-Varianten zu Folgen, haben eine "Sofortige" ADD COLUMN" - Funktion, wodurch die meisten Spalten (es gibt ein paar Einschränkungen, siehe docs) können Hinzugefügt werden, sofort mit keine Tabelle neu erstellen.

    MySQL-8 unterstützt jetzt, da der 8. Mai 2018

    InformationsquelleAutor ColinM

  3. 2

    Was Sie über Online-DDL?

    http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html

    Vielleicht würden Sie verwenden, TokuDB statt:

    http://www.tokutek.com/products/tokudb-for-mysql/

    Cooles feature, aber ich fürchte, es wird alter table noch langsamer

    InformationsquelleAutor Jackson Cunha Cassimiro

  4. 1

    Gibt es keine Möglichkeit zu vermeiden, kopieren Sie die Tabelle beim hinzufügen oder entfernen von Spalten, weil die änderungen in der Struktur. Sie können hinzufügen oder entfernen Sie sekundäre Indizes, ohne eine Tabelle zu kopieren.

    Ihre Tabelle Daten nicht in Speicher befinden. Die Indizes können sich im Speicher.

    1,5 Millionen Datensätzen ist nicht eine Menge von Zeilen und 20 Minuten scheint ziemlich lange, aber vielleicht sind deine Zeilen sind groß und Sie haben viele Indizes.

    Während die Tabelle kopiert wird, können Sie immer noch wählen Sie die Zeilen aus der Tabelle. Allerdings, wenn Sie versuchen zu tun, keine updates, Sie wird blockiert, bis die ALTER abgeschlossen ist.

    InformationsquelleAutor Marcus Adams

Schreibe einen Kommentar