Brauche einen Tip zur Beschleunigung der Indexerstellung

Hallo!

Ich benötige mal einen Tip(p), wie ich die Indexerstellung bei MySQL
beschleunigen kann.

Hintergrund:
============
Es geht den Import der Daten der Internet Movie Database mit dem Programm Java
Movie Database (JMDB).
Es gibt bei den ganzen Tabellen einen "Problemfall" und das ist die
movies2actors Tabelle für die Zuordnung von Filmen zu Schauspielern.
Diese besteht aus zwei Spalten, nämlich den IDs der Filme (movieid) und der IDs
der Schauspieler (actorid).
Wie man sich vielleicht vorstellen kann, sind dort ein paar mehr Daten drin.


Mit den IMDb-Daten vom 1. Juni 2007 sind das:
==============================================
1.022.252 Filme --> Tabelle: movies
1.319.032 Schauspieler (davon M=825.979 und F=493.053) --> Tabelle: actors
Die Verlinkungstabelle moves2actors enthält 8.117.322 Einträge.


Nachdem die IMDb seit Anfang 2006 auch einzelne Folgen von Serien als einzelne
"movies" führt, explodiert der Datenbestand (siehe Foto auf http://www.jmdb.de/
-> Nov. 2005 und Feb 2006 und danach)

Die Erstellung der beiden Indexe erfolgt nachdem die Daten in der Tabelle sind
und nicht schon beim INSERT. Da gibt es also schon mal nichts zu verbessern.

Nachdem die Daten in der movies2actors gespeichert sind starte ich einen "CREATE
INDEX..." und der dauert inzwischen leider "ewig" (~42 Min pro Index auf einem
Core2Duo E6600 mit 2x 2,40 GHz).
Die Abfrage der Schauspieler zu einem Film dauert ohne Index ca. 13 Sekunden mit
Index etwa 1 Sekunde (ohne Auswertung der Logdatei).


Hier die CREATE INDEX Befehle:
==============================
CREATE INDEX movieid ON movies2actors (movieid);
CREATE INDEX actorid ON movies2actors (actorid);

Soweit also nichts besonderes.


Die movies2actors ist wie folgt definiert:
==========================================
Feld;Typ;Null
movieid; mediumint(8) unsigned; Nein
actorid; mediumint(8) unsigned; Nein
as_character; VARCHAR(1000); Ja

Das Problem beim Indexing-Verfahren düfte sein, das zahlreiche IDs der Filme und
Schauspieler mehrfach in dieser Tabelle sind und MySQL damit ein Problem bei der
Indizierung hat (Stichwort: Kollisionen).


Ich habe die verschiedenen MySQL-Backends jetzt nicht ausprobiert. Aktuell ist
es ein MySQL v5.0.37 (community-nt) unter Windows XP x64, aber auch unter
anderen Systemen ist es nicht unbedingt besser.
Das MySQL-Backend ist InnoDB, wobei dort schon "innodb_file_per_table=1" in der
my.ini definiert habe. Das ist aber leider auch nicht schneller als die
Verwendung einer grossen "IBDATA1" - was ich zunächst erhofft hatte.

Es scheint ansonsten ja bekannt zu sein, das MySQL ab einer gewissen Grösse bei
der Indexerstellung versagt/wenig performant ist (siehe MySQL-Bugs: #24495, #9544).
Hat jemand einen Tip(p) für mich wie ich die Indexerstellung beschleunigen kann
(MySQL Parameter,...)?

Vielen Dank!

--
MfG
Jürgen Ulbts

(make sure you remove "NoSpamMails" from the eMail address)

Re: Brauche einen Tip zur Beschleunigung der Indexerstellung

"J.Ulbts" wrote:
>
> Nachdem die Daten in der movies2actors gespeichert sind starte ich einen "CREATE
> INDEX..." und der dauert inzwischen leider "ewig" (~42 Min pro Index auf einem
> Core2Duo E6600 mit 2x 2,40 GHz).
> Die Abfrage der Schauspieler zu einem Film dauert ohne Index ca. 13 Sekunden mit
> Index etwa 1 Sekunde (ohne Auswertung der Logdatei).
>
>
> Hier die CREATE INDEX Befehle:
> ==============================
> CREATE INDEX movieid ON movies2actors (movieid);
> CREATE INDEX actorid ON movies2actors (actorid);
>
> Soweit also nichts besonderes.
>
>
> Die movies2actors ist wie folgt definiert:
> ==========================================
> Feld;Typ;Null
> movieid; mediumint(8) unsigned; Nein
> actorid; mediumint(8) unsigned; Nein
> as_character; VARCHAR(1000); Ja
>
> Das Problem beim Indexing-Verfahren düfte sein, das zahlreiche IDs der Filme und
> Schauspieler mehrfach in dieser Tabelle sind und MySQL damit ein Problem bei der
> Indizierung hat (Stichwort: Kollisionen).

Nö. Das ist MySQL vollkommen Wurst.

> Ich habe die verschiedenen MySQL-Backends jetzt nicht ausprobiert. Aktuell ist
> es ein MySQL v5.0.37 (community-nt) unter Windows XP x64, aber auch unter
> anderen Systemen ist es nicht unbedingt besser.
> Das MySQL-Backend ist InnoDB, wobei dort schon "innodb_file_per_table=1" in der
> my.ini definiert habe. Das ist aber leider auch nicht schneller als die
> Verwendung einer grossen "IBDATA1" - was ich zunächst erhofft hatte.

Es gibt keinen Grund zu der Annahme, ein separater Tablespace pro
Tabelle würde InnoDB irgendwie beschleunigen.

> Hat jemand einen Tip(p) für mich wie ich die Indexerstellung beschleunigen kann
> (MySQL Parameter,...)?

Für diese Art Daten dürfte MyISAM schneller sein. Ansonsten ist das
Erzeugen eines Index auf einer großen Tabelle gerne mal I/O-bound.
Andererseits sind 8 Mio rows mit zwei INTs gerade mal 64MB. Wenn du
also die MySQL-Buffer hochdrehst (für InnoDB: innodb_buffer_pool_size)
sollte das relativ flink gehen. Für MyISAM sind es myisam_sort_buffer,
key_buffer und Co. Lies halt mal das Handbuch. Dazu ist es ja da.


XL

Re: Brauche einen Tip zur Beschleunigung der Indexerstellung

Hallo Alex!

Axel Schwenke schrieb:
>> Das Problem beim Indexing-Verfahren düfte sein, das zahlreiche IDs der Filme und
>> Schauspieler mehrfach in dieser Tabelle sind und MySQL damit ein Problem bei der
>> Indizierung hat (Stichwort: Kollisionen).
> Nö. Das ist MySQL vollkommen Wurst.

InnoDB benutzt einen BTree für den Index, ok das sollte log
Bei Hashes hätte man das Kollisionsproblem da diese bei gleichen IDs ja
prinzipbedingt auftreten.

>> Das MySQL-Backend ist InnoDB, wobei dort schon "innodb_file_per_table=1" in der
>> my.ini definiert habe. Das ist aber leider auch nicht schneller als die
>> Verwendung einer grossen "IBDATA1" - was ich zunächst erhofft hatte.
>
> Es gibt keinen Grund zu der Annahme, ein separater Tablespace pro
> Tabelle würde InnoDB irgendwie beschleunigen.

Ich war davon ausgegangen das es leichter ist einen Index in einer Datei von am
Ende 750 MB Datei zu speichern als einer ~2,x GB grossen Datei.
Ich habe gerade noch einmal beide Indexe entfernt und die movies2actors.ibd
liegt jetzt bei 425 MB (demnach 2x Index = 325 MB).


>> Hat jemand einen Tip(p) für mich wie ich die Indexerstellung beschleunigen kann
>> (MySQL Parameter,...)?
>
> Für diese Art Daten dürfte MyISAM schneller sein. Ansonsten ist das
> Erzeugen eines Index auf einer großen Tabelle gerne mal I/O-bound.
> Andererseits sind 8 Mio rows mit zwei INTs gerade mal 64MB. Wenn du
> also die MySQL-Buffer hochdrehst (für InnoDB: innodb_buffer_pool_size)
> sollte das relativ flink gehen. Für MyISAM sind es myisam_sort_buffer,
> key_buffer und Co. Lies halt mal das Handbuch. Dazu ist es ja da.

Danke das hat ordentlich was gebracht (habe es erst einmal auf 200M gesetzt -
RAM ist genug vorhanden). Der erste Index war jetzt nach 7 Minuten und 28
Sekunden angelegt, der zweite nach 9 Minuten und 12 Sekunden.
Damit kann man wohl leben.

Vielen Dank!

--
MfG
Jürgen Ulbts

(make sure you remove "NoSpamMails" from the eMail address)

Re: Brauche einen Tip zur Beschleunigung der Indexerstellung

"J.Ulbts" wrote:
> Axel Schwenke schrieb:

>> Es gibt keinen Grund zu der Annahme, ein separater Tablespace pro
>> Tabelle würde InnoDB irgendwie beschleunigen.
>
> Ich war davon ausgegangen das es leichter ist einen Index in einer Datei von am
> Ende 750 MB Datei zu speichern als einer ~2,x GB grossen Datei.
> Ich habe gerade noch einmal beide Indexe entfernt und die movies2actors.ibd
> liegt jetzt bei 425 MB (demnach 2x Index = 325 MB).

InnoDB hält seine Daten in Pages a 16KB. Aktive Pages werden in den
buffer_pool geladen. Ob die Pages nun aus einem gemeinsamen Tablespace
oder aus einem dedizierten pro Tabelle kommen, ist nebensächlich.

>> Für diese Art Daten dürfte MyISAM schneller sein. Ansonsten ist das
>> Erzeugen eines Index auf einer großen Tabelle gerne mal I/O-bound.
>> Andererseits sind 8 Mio rows mit zwei INTs gerade mal 64MB. Wenn du
>> also die MySQL-Buffer hochdrehst (für InnoDB: innodb_buffer_pool_size)
>> sollte das relativ flink gehen. Für MyISAM sind es myisam_sort_buffer,
>> key_buffer und Co. Lies halt mal das Handbuch. Dazu ist es ja da.
>
> Danke das hat ordentlich was gebracht (habe es erst einmal auf 200M gesetzt -
> RAM ist genug vorhanden). Der erste Index war jetzt nach 7 Minuten und 28
> Sekunden angelegt, der zweite nach 9 Minuten und 12 Sekunden.
> Damit kann man wohl leben.

Nochmal besser sollte das werden, wenn man der Tabelle einen PK
(movie_id, actor_id) verpaßt und außerdem einen Index auf (actor_id).
Ich nehme dafür an, daß mehr Queries mit WHERE movie_id = ... kommen.
Ansonsten anders herum.


XL