9
MariaDB - Alter Index
Hallo Zusammen,
ich soll einen neuen Index auf einer recht großen Tabelle ( ca. 200 Millionen Einträge) erzeugen und dabei hatte ich zunächst auf einem baugleichen System einen Test angestrebt und einen schnöden ALTER Befehl abgesetzt "ALTER TABLE `relations` ADD INDEX `test` (`falseporitive`, `source`, `due_date`, `type`, `user`) USING BTREE;" . Dies führte nun dazu, da das System extrem langsam wurde. Innerhalb einer halben Stunde hatte die DB die Tabelle in eine tmp-Tabelle kopiert und dann mit der Indizierung begonnen. Nach 2 Stunden war der Stand immer noch bei 0%.
Wir setzen derzeit noch MariaDB 10.0 auf einem Ubuntu 16.04 ein. Das System ist eine VMware VM mit einem Intel Xeon Silver 4110 mit 8 Kernen, 32GB RAM und einem SSD Storage.
Hat jemand eine Idee wie ich das Ganze beschleunigen kann und wielange soetwas ungefähr dauern wird?
Gruß,
Roadmax
ich soll einen neuen Index auf einer recht großen Tabelle ( ca. 200 Millionen Einträge) erzeugen und dabei hatte ich zunächst auf einem baugleichen System einen Test angestrebt und einen schnöden ALTER Befehl abgesetzt "ALTER TABLE `relations` ADD INDEX `test` (`falseporitive`, `source`, `due_date`, `type`, `user`) USING BTREE;" . Dies führte nun dazu, da das System extrem langsam wurde. Innerhalb einer halben Stunde hatte die DB die Tabelle in eine tmp-Tabelle kopiert und dann mit der Indizierung begonnen. Nach 2 Stunden war der Stand immer noch bei 0%.
Wir setzen derzeit noch MariaDB 10.0 auf einem Ubuntu 16.04 ein. Das System ist eine VMware VM mit einem Intel Xeon Silver 4110 mit 8 Kernen, 32GB RAM und einem SSD Storage.
Hat jemand eine Idee wie ich das Ganze beschleunigen kann und wielange soetwas ungefähr dauern wird?
Gruß,
Roadmax
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 1489299469
Url: https://administrator.de/contentid/1489299469
Ausgedruckt am: 23.11.2024 um 15:11 Uhr
9 Kommentare
Neuester Kommentar
Moin, es könnte sein, dass MySQL hier wirklich nicht besonders performant arbeitet, mit so großen Tabellen habe ich allerdings keine Erfahrungen.
Eventuell hilft Dir das hier weiter:
https://github.com/soundcloud/lhm
Gruss
Eventuell hilft Dir das hier weiter:
https://github.com/soundcloud/lhm
Gruss
Moin,
das hängt von den Daten selber ab.
Wenn es wirklich 200 Millionen unterschiedliche Werte sind und diese auch noch vollständig unsortiert sind kann das durchaus richtig lange dauern.
Passt die DB vollständig in den RAM?
Wenn nicht dauert es auch nochmal deutlich länger.
Alternativ ist es vermutlich schneller die Daten in eine neue Tabelle, wo die indizes schon enthalten sind, zu kopieren.
Auch hier gilt: Mehr RAM ist wichtiger als CPU.
Stefan
das hängt von den Daten selber ab.
Wenn es wirklich 200 Millionen unterschiedliche Werte sind und diese auch noch vollständig unsortiert sind kann das durchaus richtig lange dauern.
Passt die DB vollständig in den RAM?
Wenn nicht dauert es auch nochmal deutlich länger.
Alternativ ist es vermutlich schneller die Daten in eine neue Tabelle, wo die indizes schon enthalten sind, zu kopieren.
Auch hier gilt: Mehr RAM ist wichtiger als CPU.
Stefan
Hi,
uhh, eine MariaDB (oder MySQL) unter einer Virtualisierung ist immer schwierig und man verliert sehr viel Performance. In der Regel sollten Datenbanken nicht in einer Virtualisierung laufen. Die beste Performance bekommt man mit einem SSD-Raid 10 (ohne VM). Das nur mal nebenbei zur Info.
Auch sind 32GB RAM für den DB mit 200 Mio Einträgen - ähh - nicht genug (ich empfehle min 64 GB RAM). Wie groß ist denn Eure DB (GB)?
Es sollte auch sonst kein Dienst mehr auf der VM laufen. Ich empfehle auf die neueste MariaDB 10.6 zu aktualisieren, da diese deutlich schneller ist.
Wenn man keine andere Wahl hat solltest du folgendes als erstes berücksichtigen: IO-Last.
VMs sind da sehr empfindlich. Schau dir mal das Tool iotop an. Mit
sieht du, was deine DB mit deinem Filesystem so macht. Der %IO sollte da nicht permanent über 50-60% sein. Denn dann hast du schon verloren bei 200 Mio Daten. Ich gehe jetzt einfach mal davon aus, dass es sich um eine InnoDB Tabelle handelt.
In der MariaDB kannst du die IO mit innodb_io_capacity kontrollieren. Hier wird die IO-Handhabung ein wenig beschrieben (gilt auch für MariaDB).
"200" ist bei uns ein Wert für eine MariaDB mit 50 Mio Einträgen. Das hängt aber natürlich von der VM ab, wieviel IO ihr zugeordnet wurde (auch das mal prüfen).
Was bei uns auch gut funktioniert sind folgende Einstellungen (VM mit 24 Kernen, 120 GB RAM):
Auch solltest du erst mal checken, wie gut deine DB generell läuft (wenn in der Produktion-DB, dann nur kurz starten und es dann wieder ausschalten):
In deinem Fall evtl. noch die tmp_table_size anheben (wenn noch Speicher hinzugefügt wird oder frei ist) z.B.
Anfangen würde ich aber mit der Prüfung der IO-Last, dort liegt in der Regel das Problem
Viel Erfolg
max
uhh, eine MariaDB (oder MySQL) unter einer Virtualisierung ist immer schwierig und man verliert sehr viel Performance. In der Regel sollten Datenbanken nicht in einer Virtualisierung laufen. Die beste Performance bekommt man mit einem SSD-Raid 10 (ohne VM). Das nur mal nebenbei zur Info.
Auch sind 32GB RAM für den DB mit 200 Mio Einträgen - ähh - nicht genug (ich empfehle min 64 GB RAM). Wie groß ist denn Eure DB (GB)?
Es sollte auch sonst kein Dienst mehr auf der VM laufen. Ich empfehle auf die neueste MariaDB 10.6 zu aktualisieren, da diese deutlich schneller ist.
Wenn man keine andere Wahl hat solltest du folgendes als erstes berücksichtigen: IO-Last.
VMs sind da sehr empfindlich. Schau dir mal das Tool iotop an. Mit
iotop -osieht du, was deine DB mit deinem Filesystem so macht. Der %IO sollte da nicht permanent über 50-60% sein. Denn dann hast du schon verloren bei 200 Mio Daten. Ich gehe jetzt einfach mal davon aus, dass es sich um eine InnoDB Tabelle handelt.
In der MariaDB kannst du die IO mit innodb_io_capacity kontrollieren. Hier wird die IO-Handhabung ein wenig beschrieben (gilt auch für MariaDB).
innodb_io_capacity=200"200" ist bei uns ein Wert für eine MariaDB mit 50 Mio Einträgen. Das hängt aber natürlich von der VM ab, wieviel IO ihr zugeordnet wurde (auch das mal prüfen).
Was bei uns auch gut funktioniert sind folgende Einstellungen (VM mit 24 Kernen, 120 GB RAM):
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_use_native_aio=1
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_read_io_threads=16
innodb_write_io_threads=16Auch solltest du erst mal checken, wie gut deine DB generell läuft (wenn in der Produktion-DB, dann nur kurz starten und es dann wieder ausschalten):
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 5
log_slow_verbosity = query_plan,explain
log-queries-not-using-indexesIn deinem Fall evtl. noch die tmp_table_size anheben (wenn noch Speicher hinzugefügt wird oder frei ist) z.B.
tmp_table_size = 4GBAnfangen würde ich aber mit der Prüfung der IO-Last, dort liegt in der Regel das Problem
Viel Erfolg
max
Zitat von @max:
Hi,
uhh, eine MariaDB (oder MySQL) unter einer Virtualisierung ist immer schwierig und man verliert sehr viel Performance. In der Regel sollten Datenbanken nicht in einer Virtualisierung laufen.
Hi,
uhh, eine MariaDB (oder MySQL) unter einer Virtualisierung ist immer schwierig und man verliert sehr viel Performance. In der Regel sollten Datenbanken nicht in einer Virtualisierung laufen.
Sorry, wie kommst Du denn da drauf? Quellen?
Damit würden x Prozent aller DBs nicht performant laufen, kaum vorstellbar.
Das geht nur wenn als Subsystem auch InnoDB bei der Installation gewählt wurde, unter Linux ist das nicht der default.
Hi @Sabine,
wir betreiben seit über 20 Jahren mehrere MySQL und MariaDB Datenbanken. Das sind Erfahrungswerte, aus vielen Tests und Versuchen. Wir haben ein ganzen RZ dafür mit vielen unterschiedlichen Konfigurationen (mit und ohne VMs, alles Linux/Unix).
Genau so ist es auch. Aber das wird meist mit mehr CPU, RAM oder IO Zuteilung ausgeglichen. Aber "schnell" laufen in einer VM weder MySQL noch MariaDB. Sie laufen, mehr aber nicht.
VMs haben Speicherpools (NAS oder ähnliches) die sich meist noch viele User teilen. Die IO kann in diesen Pools niemals so schnell wie eine direkte Hardware-Anbindung sein und hängt stark von Zuteilung, Konfiguration, Netzwerk und Last ab. Zu viele Parameter, um wirklich schnell zu sein
Bei NoSQL Datenbanken ist das noch viel schlimmer. Da liegt der Performance-Verlust in einer VM bei mehr als 40-50%, da die meisten NoSQLs auf RocksDB basieren (und das ist auf SSDs optimiert). Datenbanken wie Redis, die komplett im Speicher (RAM) laufen, funktionieren in VMs am Besten.
Stimmt, bis zur MariaDB Version 10.1 wurde der InnoDB-Fork XtraDB als Standard-Datenbank-Engine benutzt, ab 10.2 kommt dann InnoDB als Speichersubsystem zu Einsatz. Die Einstellungen oben sollten aber auch unter der XtraDB von 10.0 funktionieren.
Seit der 10er Version war unter Linux InnoDB (welcher Form auch immer) das Standard Speichersubsystem. Bei 200 Mio Daten gehe ich jetzt mal davon aus, dass er InnoDB benutzt.
@Roadmax erhelle uns.
wir betreiben seit über 20 Jahren mehrere MySQL und MariaDB Datenbanken. Das sind Erfahrungswerte, aus vielen Tests und Versuchen. Wir haben ein ganzen RZ dafür mit vielen unterschiedlichen Konfigurationen (mit und ohne VMs, alles Linux/Unix).
Damit würden x Prozent aller DBs nicht performant laufen, kaum vorstellbar.
Genau so ist es auch. Aber das wird meist mit mehr CPU, RAM oder IO Zuteilung ausgeglichen. Aber "schnell" laufen in einer VM weder MySQL noch MariaDB. Sie laufen, mehr aber nicht.
VMs haben Speicherpools (NAS oder ähnliches) die sich meist noch viele User teilen. Die IO kann in diesen Pools niemals so schnell wie eine direkte Hardware-Anbindung sein und hängt stark von Zuteilung, Konfiguration, Netzwerk und Last ab. Zu viele Parameter, um wirklich schnell zu sein
Bei NoSQL Datenbanken ist das noch viel schlimmer. Da liegt der Performance-Verlust in einer VM bei mehr als 40-50%, da die meisten NoSQLs auf RocksDB basieren (und das ist auf SSDs optimiert). Datenbanken wie Redis, die komplett im Speicher (RAM) laufen, funktionieren in VMs am Besten.
Das geht nur wenn als Subsystem auch InnoDB bei der Installation gewählt wurde, unter Linux ist das nicht der default.
Stimmt, bis zur MariaDB Version 10.1 wurde der InnoDB-Fork XtraDB als Standard-Datenbank-Engine benutzt, ab 10.2 kommt dann InnoDB als Speichersubsystem zu Einsatz. Die Einstellungen oben sollten aber auch unter der XtraDB von 10.0 funktionieren.
Seit der 10er Version war unter Linux InnoDB (welcher Form auch immer) das Standard Speichersubsystem. Bei 200 Mio Daten gehe ich jetzt mal davon aus, dass er InnoDB benutzt.
@Roadmax erhelle uns.
200 Millionen Rows ist nicht wirklich viel - für moderne Datenbanken.
PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
edb=# create table roadmax(val numeric);
CREATE TABLE
edb=*# insert into roadmax select random() from generate_series(1, 200000000) s;
INSERT 0 200000000
edb=*# \timing
Timing is on.
edb=*# commit;
COMMIT
Time: 6,885 ms
edb=# create index CONCURRENTLY idx_val on roadmax (val);
CREATE INDEX
Time: 453802,655 ms (07:33,803)
edb=# PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
Zitat von @akretschmer:
PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
Tolle Info. Und wie genau hilft das jetzt dem TO mit MySQL?
Zitat von @sabines:
Tolle Info. Und wie genau hilft das jetzt dem TO mit MySQL?
Zitat von @akretschmer:
PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
PostgreSQL, 8GB RAM. create index CONCURRENTLY, damit die Tabelle während der Indexerstellung nicht exclusive gesperrt wird. Kann MySQL wohl (auch) nicht.
Tolle Info. Und wie genau hilft das jetzt dem TO mit MySQL?
Nun, der TO könnte eine Migration in Erwägung ziehen.
@akretschmer
ja, die PostgreSQL ist deutlich schneller und besser als eine MySQL oder MariaDB. Ich bezweifle aber, dass er mal eben eine Migration durchführen will
Der Autor hat uns leider noch nicht verraten welches Speichersubsystem zum Einsatz kommt (InnoDB?) und wie große eigentlich sein Index ist. Nur die Anzahl der Datensätze wissen wir. Da er oben zum Test "BTREE" schreibt, kann der Index schon etwas größer sein (z.B für LIKE). Außerdem besteht er wohl aus mehreren Felden.
Ich denke weiterhin das es ein Problem mit dem IO und der VM ist. Wenn die IO voll ausgeschöpft ist, wird das System mehr als nur langsam.
Ich denke ohne eine detaillierte Aussage des Autors kommen wir nicht weiter, raten macht keinen Sinn.
max
ja, die PostgreSQL ist deutlich schneller und besser als eine MySQL oder MariaDB. Ich bezweifle aber, dass er mal eben eine Migration durchführen will
Der Autor hat uns leider noch nicht verraten welches Speichersubsystem zum Einsatz kommt (InnoDB?) und wie große eigentlich sein Index ist. Nur die Anzahl der Datensätze wissen wir. Da er oben zum Test "BTREE" schreibt, kann der Index schon etwas größer sein (z.B für LIKE). Außerdem besteht er wohl aus mehreren Felden.
Ich denke weiterhin das es ein Problem mit dem IO und der VM ist. Wenn die IO voll ausgeschöpft ist, wird das System mehr als nur langsam.
Ich denke ohne eine detaillierte Aussage des Autors kommen wir nicht weiter, raten macht keinen Sinn.
max
