Nov 10, 2021 at 07:27:48 (UTC)

1708

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

Please also mark the comments that contributed to the solution of the article

Content-Key: 1489299469

Url: https://administrator.de/contentid/1489299469

Printed on: May 9, 2024 at 04:05 o'clock

9 Comments

Latest comment

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

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

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

iotop -o

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).

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=16

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):

slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 5
log_slow_verbosity     = query_plan,explain
log-queries-not-using-indexes

In deinem Fall evtl. noch die tmp_table_size anheben (wenn noch Speicher hinzugefügt wird oder frei ist) z.B.

tmp_table_size = 4GB

Anfangen 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.

Sorry, wie kommst Du denn da drauf? Quellen?
Damit würden x Prozent aller DBs nicht performant laufen, kaum vorstellbar.

In der MariaDB kannst du die IO mit innodb_io_capacity kontrollieren.

innodb_io_capacity=200

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).

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.

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.

Tolle Info. Und wie genau hilft das jetzt dem TO mit MySQL?

Zitat von @sabines:

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.

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

German Question Databases Development

Hotly discussed

Wireguard with systemd and nftablesLinuxero - 9 Comments