[SQL] Usenet index

[TvdW]

Goedendag Tweakers,

Ik ben de laatste tijd bezig geweest met een script om in te kunnen loggen op NNTP-servers (ook wel bekend als Usenet, nieuwsgroepen dus) en alle artikelen binnen 1 nieuwsgroep (in mijn geval a.b.mp3) in een database te zetten (subject + messageID + date + size).

Het zijn in totaal 13 miljoen records, in een MyISAM database. Op dit moment zijn het al bijna 5 miljoen records, maar het script is nog bezig.

Da's allemaal erg leuk, maar ik realiseer me net dat usenet geen losse bestandjes zijn, maar posts. Elk bestandje kan bestaan uit veel posts. 1 MP3 bestand van 5 MB kan al snel opgesplitst worden in 50 posts, die allemaal bij elkaar gezocht moeten worden voor ze gedownload kunnen worden.

Ik had nu 2 vragen:
1. Op een database met 13 miljoen records, is MyISAM wel de beste keuze?
2. Hoe kan ik het beste een query maken die 1 bestand zoekt? Ik kijk nu even naar de top2000 en stel dat ik "Eagles - Hotel California" zou willen. Ik gebruik nu LIKE "%Eagles%Hotel%California%", maar zijn er betere manieren om dit te doen? Dit duurt namelijk eindeloos lang. 1 minuut is toch wel mijn maximum voor een query als deze.

Overige informatie
http://www.tvdw.eu/data.txt is een output-voorbeeldje van onderstaande query. (Die overigens 30 seconden duurde, maar dat was op 1.2 miljoen records en niet de 5 miljoen die ik inmiddels heb)
SELECT * FROM `index` WHERE index_subject LIKE '%deadmau5%';

Iets later op 4.1 miljoen rows nog eens gebenchmarked :
mysql> SELECT * FROM `index` WHERE index_subject LIKE '%Hotel%California%' LIMIT 30;
20 rows in set (6 min 39.32 sec)

code:

CREATE TABLE `index` (
  `index_id` int(11) NOT NULL,
  `index_group` varchar(30) NOT NULL,
  `index_subject` varchar(200) NOT NULL,
  `index_date` varchar(35) NOT NULL,
  `index_bytes` int(11) NOT NULL,
  `index_xref` blob NOT NULL,
  `index_messageID` varchar(100) NOT NULL,
  PRIMARY KEY  (`index_id`),
  UNIQUE KEY `index_messageID` (`index_messageID`),
  KEY `index_subject` (`index_subject`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Tom

[Kuhlie]

Ik denk dat je het beste van de index_subject een Wikipedia: Inverted index kunt maken: "Stores a list of occurrences of each atomic search criterion". Op die manier kan je zeer snel zoeken. Koste wel meer dan de ene tabel die je nu hebt, en je moet die index zelf maken, dat gaat je database waarschijnlijk niet voor je doen.

[mjax]

Je hebt geen (full-text) index op het veld index_subject, dus MySQL moet elke row inspecteren of deze voldoet aan de WHERE clause. Oplossing zou dus zijn het toevoegen van een full text index op index_subject en gebruik te maken van MATCH() in je WHERE clause. Alternatief is het gebruik van een normale index op dat veld dat dan een LIKE 'xyz%' actie uit te voeren (dus zonder % aan het begin).

[donquix]

Kun je niet beter proberen om in je "import" script de diverse bestanden te laten herkennen. Deze zet je dan vervolgens in twee tabellen, eentje met de diverse bestanden en eentje met de bijbehorende posts.

[BCC]

Of een database gebruiken die geschikt is voor veel data, zoals bijvoorbeeld een key value store. Kijk bijvoorbeeld eens naar MongoDB of CouchDB. Daarnaast is het natuurlijk een stuk cooler

[ Voor 38% gewijzigd door BCC op 28-12-2009 20:15 ]

[ACM]

Doorgaans is MySQL niet de geprefereerde keus voor het doorzoeken van grote tekstsystemen. Je bent waarschijnlijk beter af met een systeem gebaseerd op Sphinx, Lucene (of Solr), Xapian of een ander gespecialiseerde full text indexsysteem.
De metadata voor posts kan je dan waarschijnlijk wel aardig in je MySQL of alternatieve database opslaan.

Daarnaast kan je waarschijnlijk het best tijdens het importeren al zoveel mogelijk proberen de boel bij elkaar te zoeken. Als het goed is zitten er markeringen in de nttp-berichten om aan te geven bij welke groep berichten ze horen.

[TvdW]

@Kuhlie:
Lijkt me niet echt een oplossing, omdat het geen woorden zijn, maar een bestandsindex. hier komen de raarste dingen in voor dus ook zo'n inverted index wordt gigantisch.

@mjax:
Goed plan, ik ga er zeker even naar kijken.

@donquix:
Dat is ook zeker een goed plan. Ik ga kijken of ik deze met die van mjax kan combineren.

@BCC:
Helaas niet echt een optie, omdat MySQL op dit moment mijn enige optie is ivm connectie met PHP. Mongo/Couch zijn ook niet ondersteund door de PDO driver.

@ACM:
Helaas is MySQL de enige optie ivm de PDO driver in PHP.

[ Voor 6% gewijzigd door TvdW op 28-12-2009 20:42 ]

[BCC]

TvdW schreef op maandag 28 december 2009 @ 20:39:

@BCC:
Helaas niet echt een optie, omdat MySQL op dit moment mijn enige optie is ivm connectie met PHP. Mongo/Couch zijn ook niet ondersteund door de PDO driver.

Nog niet nee, dat komt omdat PDO ook nogal geent is op de structuur van retlationele databases. Maar de implementatie is niet echt rocketscience:

http://php.net/manual/en/class.mongodb.php
http://wiki.apache.org/couchdb/Getting_started_with_PHP
http://sebastian-bergmann...s-Sleep-on-the-Couch.html

De suggesties van ACM zijn trouwens ook erg cool. Ik geloof meer in "the right tool for the job" dan ik heb/ken alleen dit, dus het moet hiermee

[ Voor 10% gewijzigd door BCC op 28-12-2009 20:50 ]

[ACM]

TvdW schreef op maandag 28 december 2009 @ 20:39:
@Kuhlie:
Lijkt me niet echt een oplossing, omdat het geen woorden zijn, maar een bestandsindex. hier komen de raarste dingen in voor dus ook zo'n inverted index wordt gigantisch.

Wat is gigantisch? Alle postings los proberen te doorzoeken ga je ook niet binnen een redelijke tijd kunnen doen lijkt me?

@BCC:
Helaas niet echt een optie, omdat MySQL op dit moment mijn enige optie is ivm connectie met PHP. Mongo/Couch zijn ook niet ondersteund door de PDO driver.

@ACM:
Helaas is MySQL de enige optie ivm de PDO driver in PHP.

En waarom moet je per se via PDO verbinden? Om de specifieke zoekopdrachten sterk te versnellen kan het serieus veel tijd schelen om je niet geforceerd op een specifieke database-communicatie vast te bijten.

Overigens is er iig een MySQL-wrapper voor Sphinx, waardoor je in principe gewoon via je MySQL-database kan communiceren met die index.

[TvdW]

Ik ga zeker even kijken naar Sphinx. En ik geef toe dat ik mij iets te veel focus op PDO, wat eigenlijk niet hoeft, en ik nu dus ook niet meer zal doen.

[TvdW]

1. Ik heb de database eventjes opnieuw ingedeeld (en dus ook geleegd). Geen nutteloze xref kolommen meer, ik heb nu alleen de post ID, message ID, subject en group (a.b.mp3, maar dat wordt mogelijk meer). subject heeft geen index meer, dit laat ik via Sphinx lopen
2. Voordat ik de database leegde, heb ik eventjes gebenchmarked met Sphinx. Het indexeren van mijn 5 miljoen rijen duurde ongeveer 2 minuten, het zoeken duurde minder dan een honderdste van een seconde. Ik moet nog even kijken hoe de PHP implementatie werkt, maar het lijkt te kunnen en het lijkt niet zo complex. ('t is een hobby projectje, ik heb geen zin om allemaal nieuwe dingen te moeten gaan leren.)
3. Het script is inmiddels weer opnieuw alle rijen aan het invoeren in de database, en op de een of andere manier gaat dat veel sneller dan de vorige keer. Zal wel zijn omdat er geen index meer is op subject.

Bedankt, allemaal! (En vooral ACM)

Tom

[marco_balk]

TvdW schreef op maandag 28 december 2009 @ 22:43:
3. Het script is inmiddels weer opnieuw alle rijen aan het invoeren in de database, en op de een of andere manier gaat dat veel sneller dan de vorige keer. Zal wel zijn omdat er geen index meer is op subject.

Dat weet ik wel zeker! Bij iedere INSERT zal er ook data toegevoegd worden aan de index. Bij sommige systemen loont het dan ook om _voor_ een grote INSERT eerst de index te verwijderen en deze na de INSERT weer aan te maken.

[TvdW]

marco_balk schreef op maandag 28 december 2009 @ 22:55:
[...]

Dat weet ik wel zeker! Bij iedere INSERT zal er ook data toegevoegd worden aan de index. Bij sommige systemen loont het dan ook om _voor_ een grote INSERT eerst de index te verwijderen en deze na de INSERT weer aan te maken.

MyISAM staat 't gelukkig toe dat ik eerst de tabellen lock, en daarna weer unlock. Zodra je ze unlockt worden de indexen pas gemaakt. Dit gaat per 25.000 rijen tegelijk, dus zo'n verschil is dat niet.

[ACM]

TvdW schreef op maandag 28 december 2009 @ 22:43:
1. Ik heb de database eventjes opnieuw ingedeeld (en dus ook geleegd). Geen nutteloze xref kolommen meer, ik heb nu alleen de post ID, message ID, subject en group (a.b.mp3, maar dat wordt mogelijk meer). subject heeft geen index meer, dit laat ik via Sphinx lopen

Je kan eventueel veel meer ook via Sphinx laten lopen, bijvoorbeeld de groep (wellicht met een 'boolean' term) ook nog en eventuele andere metadata, zodat je daar op kan filteren en sorteren. Of dat tov je nieuwe situatie nog de moeite loont is natuurlijk maar de vraag

('t is een hobby projectje, ik heb geen zin om allemaal nieuwe dingen te moeten gaan leren.)

Dat zijn juist de projectjes waarbij ik nieuwe dingen uitprobeer

[cariolive23]

TvdW schreef op maandag 28 december 2009 @ 22:58:
[...]

MyISAM staat 't gelukkig toe dat ik eerst de tabellen lock, en daarna weer unlock. Zodra je ze unlockt worden de indexen pas gemaakt. Dit gaat per 25.000 rijen tegelijk, dus zo'n verschil is dat niet.

Sinds wanneer is een table lock een voordeel??? Performance draai je hiermee de nek om, de tabel is gedurende de lock volkomen onbereikbaar voor de rest van de gebruikers. Het noodzakelijke gebruik van table locks is juist de reden waarom MyISAM zuigt voor toepassingen met meerdere gebruikers.

[Woy]

Over het hele MySql/database gebeuren heb ik eigenlijk weinig toe te voegen.

Maar over het usenet gedeelte: Het lijkt me dat messages die bij hetzelfde bestand horen, ook een naamgeving hebben die overeenkomt. Kun je dan niet bij het inlezen van alle messages al bestandsnamen uitfilteren? Zoals je al zegt kan een file van 5 mb al uit 50 messages bestaan. Op die manier kun je al makkelijker zoeken. Verder kun je als je de bestandsnaam hebt natuurlijk ook al wat pre-processing doen waardoor je later makkelijker kunt zoeken.

[TvdW]

cariolive23 schreef op dinsdag 29 december 2009 @ 09:25:
[...]

Sinds wanneer is een table lock een voordeel??? Performance draai je hiermee de nek om, de tabel is gedurende de lock volkomen onbereikbaar voor de rest van de gebruikers. Het noodzakelijke gebruik van table locks is juist de reden waarom MyISAM zuigt voor toepassingen met meerdere gebruikers.

Nee hoor, de tabel is gewoon toegankelijk voor READ. Geen idee hoe dat kan, maar zo werkt het wel.

@Woy:
Dan krijg ik straks in een 2e tabel alle message IDs staan. Want die message IDs zijn belangrijk voor het downloaden. Dan heb ik straks 2 tabellen die veel complexer worden dan wat ik nu heb, lijkt me...

[ Voor 16% gewijzigd door TvdW op 29-12-2009 10:39 ]

[Woy]

TvdW schreef op dinsdag 29 december 2009 @ 10:38:
[...]

Nee hoor, de tabel is gewoon toegankelijk voor READ. Geen idee hoe dat kan, maar zo werkt het wel.

@Woy:
Dan krijg ik straks in een 2e tabel alle message IDs staan. Want die message IDs zijn belangrijk voor het downloaden. Dan heb ik straks 2 tabellen die veel complexer worden dan wat ik nu heb, lijkt me...

Die message-id's heb je inderdaad nog steeds nodig, maar met de juiste index, kun je die er dus zo bij zoeken. Het probleem wat je hebt is toch vooral het zoeken in de bestanden?

[TvdW]

Woy schreef op dinsdag 29 december 2009 @ 10:42:
[...]

Die message-id's heb je inderdaad nog steeds nodig, maar met de juiste index, kun je die er dus zo bij zoeken. Het probleem wat je hebt is toch vooral het zoeken in de bestanden?

Nou, eigenlijk is dat probleem inmiddels opgelost

[Woy]

TvdW schreef op dinsdag 29 december 2009 @ 11:09:
[...]

Nou, eigenlijk is dat probleem inmiddels opgelost

Ah dat had ik even gemist.

[donquix]

Nog even over het pre-processen;
Stukje van binsearch.info:

The yEnc specification strongly recommends that you use the following format:
[Comment1] "filename" yEnc (partnum/numparts) [size] [Comment2]

Daar kun je dus lekker op regular-expression-en.

[Gomez12]

TvdW schreef op dinsdag 29 december 2009 @ 10:38:
[...]

Nee hoor, de tabel is gewoon toegankelijk voor READ. Geen idee hoe dat kan, maar zo werkt het wel.

Dan zal ik je lock opmerking wel weer niet goed begrijpen, maar afaik is het heel simpel :
- Of elke insert update je indexen zodat deze ook tijdens READS nog te gebruiken zijn
- Of elke READ tijdens een lock loopt buiten de indexen om en je krijgt een locking festijn van hier tot tokio omdat elke READ giga-lang gaat duren

@Woy:
Dan krijg ik straks in een 2e tabel alle message IDs staan. Want die message IDs zijn belangrijk voor het downloaden. Dan heb ik straks 2 tabellen die veel complexer worden dan wat ik nu heb, lijkt me...

Yep, dat heet normaliseren... Het is ietwat complexer maar doet wonderen voor de snelheid als je veel dubbele data hebt

[TvdW]

Gomez12 schreef op dinsdag 29 december 2009 @ 18:37:
[...]

Dan zal ik je lock opmerking wel weer niet goed begrijpen, maar afaik is het heel simpel :
- Of elke insert update je indexen zodat deze ook tijdens READS nog te gebruiken zijn
- Of elke READ tijdens een lock loopt buiten de indexen om en je krijgt een locking festijn van hier tot tokio omdat elke READ giga-lang gaat duren

Geen idee hoe 't werkt, maar 't werkt. Trouwens, het is niet voor een live website dus opzich maakt 't niet zo uit, want zo vaak zullen read en write niet conflicteren.

Gomez12 schreef op dinsdag 29 december 2009 @ 18:37:
[...]

Yep, dat heet normaliseren... Het is ietwat complexer maar doet wonderen voor de snelheid als je veel dubbele data hebt

Och. Als het voor een zoekterm maar 0.001 seconde duurt, heb ik al een wonder gevonden

[cariolive23]

TvdW schreef op dinsdag 29 december 2009 @ 18:54:
Geen idee hoe 't werkt, maar 't werkt. Trouwens, het is niet voor een live website dus opzich maakt 't niet zo uit, want zo vaak zullen read en write niet conflicteren.

Klinkt alsof je met slechts één concurrent user aan het testen bent, dan is het logisch dat het werkt. Je lockt tenslotte niet voor jezelf, dan zou je niets meer met de data kunnen. Ga maar eens met een tiental concurrent users testen, dan ziet het plaatje er waarschijnlijk heel anders uit...

[TvdW]

cariolive23 schreef op dinsdag 29 december 2009 @ 20:41:
[...]

Klinkt alsof je met slechts één concurrent user aan het testen bent, dan is het logisch dat het werkt. Je lockt tenslotte niet voor jezelf, dan zou je niets meer met de data kunnen. Ga maar eens met een tiental concurrent users testen, dan ziet het plaatje er waarschijnlijk heel anders uit...

ik heb 1 backend die de rows in de database zet, en 1 user die mogelijk eens gebruik maakt van de database. meer users zullen er nooit zijn

[TvdW]

Ik heb nu nog 1 probleempje :

PHP:

foreach ($result['matches'] as $i => $item) {
    $list[] = $i;
}
$rows = $pdo -> query('SELECT * FROM `index` WHERE index_id IN ('.implode(',', $list).')') -> fetchAll(PDO :: FETCH_ASSOC);

Het lijkt me dat dit een probleem op gaat leveren. In $result['matches'] zitten alle results van Sphinx (IDs dus) die ik daarna volledig maak door ze uit de database te halen. Kan dit beter?

[marco_balk]

Waar het in ieder geval beter kan, is bij de "SELECT *".
"SELECT *" is af te raden. De database moet dan zelf nog nadenken over wat die '*' precies moet worden.
Schrijf je SELECT helemaal uit, zo krijg je altijd precies wat je nodig hebt en hoeft de database niet zelf uit te vogelen wat er opgehaald moet worden.

Daar zit je eerste performance winst.

[GlowMouse]

marco_balk schreef op dinsdag 29 december 2009 @ 22:32:
Waar het in ieder geval beter kan, is bij de "SELECT *".
"SELECT *" is af te raden. De database moet dan zelf nog nadenken over wat die '*' precies moet worden.
Schrijf je SELECT helemaal uit, zo krijg je altijd precies wat je nodig hebt en hoeft de database niet zelf uit te vogelen wat er opgehaald moet worden.

Daar zit je eerste performance winst.

De winst zit in de traffic/geheugengebruik.

Je kunt alle velden uit index in Sphinx stoppen, als Sphinx dat ondersteunt. En anders zie ik geen dingen die sneller kunnen anders dan bovenstaande.

[TvdW]

marco_balk schreef op dinsdag 29 december 2009 @ 22:32:
Waar het in ieder geval beter kan, is bij de "SELECT *".
"SELECT *" is af te raden. De database moet dan zelf nog nadenken over wat die '*' precies moet worden.
Schrijf je SELECT helemaal uit, zo krijg je altijd precies wat je nodig hebt en hoeft de database niet zelf uit te vogelen wat er opgehaald moet worden.

Daar zit je eerste performance winst.

done

GlowMouse schreef op dinsdag 29 december 2009 @ 22:34:
[...]

Je kunt alle velden uit index in Sphinx stoppen, als Sphinx dat ondersteunt. En anders zie ik geen dingen die sneller kunnen anders dan bovenstaande.

Ik ken Sphinx natuurlijk nog niet 100%, maar ik denk niet dat Sphinx dit ondersteunt.

Opzich is het ook geen gigantisch probleem, het uitvoeren van eerdergenoemde query duurt slechts een halve seconde, en 't is op een backend-script.

Tom

[PolarBear]

marco_balk schreef op dinsdag 29 december 2009 @ 22:32:
Waar het in ieder geval beter kan, is bij de "SELECT *".
"SELECT *" is af te raden. De database moet dan zelf nog nadenken over wat die '*' precies moet worden.
Schrijf je SELECT helemaal uit, zo krijg je altijd precies wat je nodig hebt en hoeft de database niet zelf uit te vogelen wat er opgehaald moet worden.

Daar zit je eerste performance winst.

Ik denk dat een database zo gevonden heeft welke kolommen er in een tabel bevinden. Daar zit echt minimale performance winst. Dat is ook niet de reden om het niet te doen.