MySQL database met document store voor zoekfunctie?

[egonolieux]

Ik heb een MySQL database die vanwege het toepassingsdomein sterk genormaliseerd moet zijn. Het is de bedoeling dat ik binnen een Symfony applicatie (PHP) zoekresultaten uit deze database kan weergeven. Dit werkt ondertussen prima door eenvoudigweg de zoekcriteria op te bouwen met een Doctrine querybuilder (ORM).

Als resultaat krijg ik een array van objecten die aan de criteria voldoen. Het probleem is dat ik in sommige gevallen alle objecten uit de lijst volledig wil visualiseren. Omdat mijn database sterk genormaliseerd is, bevat elk object genest tot 20 relaties, wat voor een gigantische hoop N+1 queries zorgt. Bij 100 resultaten zit ik al snel aan 2000 queries, wat echt niet kan.

Fetch joins zijn mogelijks een oplossing voor dit probleem, maar in mijn geval wordt het aantal joins te groot waardoor de result set oneindig groot wordt (en het hydraten van de objecten oneindig lang duurt /geheugen nodig heeft). Er is echter een andere oplossing die ik momenteel gebruik: https://ocramius.github.i...m-optimization-hydration/. Met deze laatste oplossing kan ik de 20 geneste relaties van de gehele result set in één keer ophalen, waardoor ik dus maar 21 queries in totaal heb.

Allemaal goed en wel, maar ik heb het gevoel dat ik technologie aan het gebruiken ben die niet voor dit doel geschikt is. Ook is deze manier van werken niet echt flexibel. Een document store zoals MongoDB lijkt mij hier een betere oplossing voor. Ik zou dus eerst mijn gehele database kunnen serializen en in MongoDB steken, om deze vervolgens bij het zoeken te raadplegen.

Hierbij wil ik benadrukken dat het zoeken naar de resultaten zelf geen probleem is; er zitten geen text searches tussen dus dit is een ideale toepassing voor SQL (moest iemand denken aan bvb Elasticsearch). Het idee is om met een lijst van verkregen IDs na de zoekopdracht de resultaten op te halen uit MongoDB.

Ik zou eigenlijk gewoon wat feedback willen of dit een goed idee is en hoe anderen met dit probleem omgaan.

En nee, geen ORM gebruiken is geen oplossing, de queries zouden hoe dan ook zonder ORM ook apart uitgevoerd moeten worden

[ Voor 4% gewijzigd door egonolieux op 07-04-2018 05:00 ]

[ACM]

egonolieux schreef op zaterdag 7 april 2018 @ 04:56:
Hierbij wil ik benadrukken dat het zoeken naar de resultaten zelf geen probleem is; er zitten geen text searches tussen dus dit is een ideale toepassing voor SQL (moest iemand denken aan bvb Elasticsearch). Het idee is om met een lijst van verkregen IDs na de zoekopdracht de resultaten op te halen uit MongoDB.

Waarom zou Elasticsearch niet kunnen? Dat je geen tekst-searches doet is niet zo relevant, het is ook erg goed in search voor (willekeurige combinaties van) range- en boolean-criteria.

Als je bovendien ook facetten wilt gebruiken, bijvoorbeeld zoals in de Pricewatch waar je ziet hoeveel resultaten er gaan zijn als je een criterium aanpast, dan is Elasticsearch helemaal een van de betere oplossingen.

Maar met zowel Elastic als Mongo kan je inderdaad je objecten denormaliseren en opslaan in de document store, al dan niet met een serialized blob erbij voor het uiteindelijke object.

Let wel dat Mongo niet automatisch indexering erop toepast, hoewel het sowieso wel vrij snel is, is het mogelijk lastig om de juiste set van indexen te hebben voor al je mogelijke queries. Elastic werkt juist automatisch met indexering, waardoor willekeurige combinaties geen extra werk vereisen om snel te krijgen.

Er is trouwens voor beide een Doctrine-module beschikbaar, maar ik heb geen ervaring met Doctrine, en al helemaal niet met die modules

[Gropah]

Ik ben er zelf niet heel bekend mee, maar is het ook niet een idee om te kijken naar een graph db achtig iets? Afaik zijn die databases er op gemaakt om makkelijk en snel joins te doe.

[HollowGamer]

Je hoeft niet en-en toe te passen. Je gebruikt de MySQL database voor het opslaan/bijwerken van je entries en synced deze naar ES (het geen waarin je wilt zoeken/wilt laat presenteren).

Zo gebruikte ik het inderdaad voor search, en zodra iemand de entry bekijk, val je weer terug op je database. Maar je kunt dit zo gek maken als je zelf wilt.

Heb enkel ervaring hierin met Laravel, maar verwacht dat er ook packages bestaan voor Symfony.

[ Voor 23% gewijzigd door HollowGamer op 07-04-2018 11:15 ]

[Bigs]

Je zult de data voor zoekdoeleinden gedenormaliseerd op moeten slaan. Dat kan prima in een extra MySQL tabel die je dmv triggers bijhoudt (of in Symfony, wat mensen zonder diepgaande database kennis meestal lijken te doen). Maar dan zou ik ook zeker een zoekmachine als Elasticsearch of Solr overwegen. Die zijn immers ontworpen voor allerlei vormen van zoeken, niet alleen full text search.

[HollowGamer]

Gropah schreef op zaterdag 7 april 2018 @ 11:12:
Ik ben er zelf niet heel bekend mee, maar is het ook niet een idee om te kijken naar een graph db achtig iets? Afaik zijn die databases er op gemaakt om makkelijk en snel joins te doe.

Bedoel je dat graph databases gemaakt zijn voor joins? Wat dat zijn ze juist niet. Het is de bedoeling dat je die gebruikt om juist het hele 'document' in op te slaan.

Of heb ik het verkeerd?

[Gropah]

HollowGamer schreef op zaterdag 7 april 2018 @ 11:18:
[...]

Bedoel je dat graph databases gemaakt zijn voor joins? Wat dat zijn ze juist niet. Het is de bedoeling dat je die gebruikt om juist het hele 'document' in op te slaan.

Of heb ik het verkeerd?

Wat ik lees van bijvoorbeeld de documentatie van Neo4j is dat het juist makkelijk is om data te combineren, maar wederom, ik heb geen ervaring met dit soort databases dus het kan zijn dat ik iets over het hoofd zie of dat het deze specifieke implementatie is die daar beter mee om gaat.

[TheBorg]

Ik kan je vraag niet helemaal volgen omdat ik me geen voorstelling kan maken wat je aan het ontwikkelen bent maar ik sluit me aan bij @ACM. In de eenvoudigste vorm doe je het zonder MongoDB of Elasticsearch. Het is helemaal niet raar om een database die helemaal stuk genormaliseerd is in een cache (table/database) te stoppen waar je wel efficient search queries op uittgevoerd kunnen voeren.

[DJMaze]

egonolieux schreef op zaterdag 7 april 2018 @ 04:56:
Omdat mijn database sterk genormaliseerd is, bevat elk object genest tot 20 relaties, wat voor een gigantische hoop N+1 queries zorgt. Bij 100 resultaten zit ik al snel aan 2000 queries, wat echt niet kan.

Als je met MySQL moet werken is dit probleem erg makkelijk op te lossen, maar dan moet je wel afstappen van ORM.

[HollowGamer]

DJMaze schreef op zaterdag 7 april 2018 @ 11:46:
[...]

Als je met MySQL moet werken is dit probleem erg makkelijk op te lossen, maar dan moet je wel afstappen van ORM (Doctrine).

Doctrine zou toch zo slim moeten zijn om een join te maken en deze zo best mogelijk in te zetten?
Maar inderdaad is wel zelf schrijven van een query misschien wel de makkelijkste en beste weg.

Mijn voorkeur zou zijn om te kijken naar ES, dan heb je geen gedoe en een index opbouwen moet te doen zijn.

[DJMaze]

HollowGamer schreef op zaterdag 7 april 2018 @ 11:48:
Doctrine zou toch zo slim moeten zijn om een join te maken en deze zo best mogelijk in te zetten?

Ja zou kunnen, maar ik praat niet over joins

[edit]
MySQL kan unbuffered queries.
MySQLi kent de mysqli_result class en heeft de Iterator interface.
Daarnaast is er ook nog zoiets als GROUP_CONCAT().
En dan zijn er nog legio mogelijkheden.

Op dit moment gebruikt hij gewoon te veel zvals

[ Voor 35% gewijzigd door DJMaze op 07-04-2018 12:02 ]

[egonolieux]

DJMaze schreef op zaterdag 7 april 2018 @ 11:46:
[...]

Als je met MySQL moet werken is dit probleem erg makkelijk op te lossen, maar dan moet je wel afstappen van ORM (Doctrine).

Met Doctrine is dat ook op te lossen. Het is niet omdat een ORM gebruikt wordt dat je ineens geen SQL meer kan schrijven. De hoofdzakelijke reden dat ik Doctrine gebruik, is om mezelf een hele boel werk te besparen en het wiel niet heruit te moeten vinden bij het mappen van mijn database naar domain entities. Dat wil daarom niet zeggen dat Doctrine alles voor me doet. Sterker nog, je kan binnen de meeste ORMs inclusief Doctrine zelfs native queries uitvoeren en dus direct van databasespecifieke features gebruik maken.

Door gewoon native SQL te gebruiken blijft het probleem hetzelfde trouwens. Het gaat hem er om dat ik telkens een hele hoop extra queries moet schrijven/uitvoeren om de entity uit de database te halen in zijn volledige vorm wegens een zeer genormaliseerd datamodel. Als ik een documentstore opbouw van deze entities, moet ik niet telkens een arbitrair extra aantal queries uitvoeren voor de properties die ik nodig heb.

TheBorg schreef op zaterdag 7 april 2018 @ 11:33:
Ik kan je vraag niet helemaal volgen omdat ik me geen voorstelling kan maken wat je aan het ontwikkelen bent maar ik sluit me aan bij @ACM. In de eenvoudigste vorm doe je het zonder MongoDB of Elasticsearch. Het is helemaal niet raar om een database die helemaal stuk genormaliseerd is in een cache (table/database) te stoppen waar je wel efficient search queries op uittgevoerd kunnen voeren.

Heb je het niet over de post van @Bigs? Moest ik voor deze manier van werken gaan, wat doe je dan met een-op-meer en meer-op-meer relaties? Een tiental nullable foreign key kolommen maken voor elk en hopen dat er nooit meer dan 10 nodig zijn? Mijn voorkeur lijkt dan toch eerder naar Elasticsearch te gaan. Dat lijkt me veel flexibeler en schaalbaarder voor moest ik in de toekomst nog extra zoekfuncties willen toevoegen of een REST API.

Zoals @ACM reeds voorstelde, is er inderdaad reeds een Symfony bundle voorzien voor Elasticsearch: https://github.com/FriendsOfSymfony/FOSElasticaBundle. Ik moet het nog eens in detail bekijken en er mee experimenteren, maar ik denk als volgt te werk te gaan:

1. De benodigde delen uit mijn database serializen naar JSON.
2. Deze JSON in Elasticsearch steken.
3. Na elke verandering binnen een entity, de Elasticsearch database updaten.
4. De benodigde repositories ombouwen zodat ze de entities ophalen uit Elasticsearch ipv MySQL.

Bij punt 4 zou ik niet focussen op zoeken alleen; ik zou gewoon alle read/find operaties (of het nu één of meer entities zijn) via Elasticsearch laten lopen.

[NMe]

DJMaze schreef op zaterdag 7 april 2018 @ 11:49:
[...]

Ja zou kunnen, maar ik praat niet over joins

[edit]
MySQL kan unbuffered queries.
MySQLi kent de mysqli_result class en heeft de Iterator interface.
Daarnaast is er ook nog zoiets als GROUP_CONCAT().
En dan zijn er nog legio mogelijkheden.

Op dit moment gebruikt hij gewoon te veel zvals

Doctrine heeft ook gewoon een DBAL-laag dus van Doctrine afstappen hoeft niet eens voor exotische query's. Niet dat dat überhaupt nodig is in dit geval, als je verstandig omgaat met lazy en eager fetching.

egonolieux schreef op zondag 8 april 2018 @ 00:35:
[...]

4. De benodigde repositories ombouwen zodat ze de entities ophalen uit Elasticsearch ipv MySQL.

Bij punt 4 zou ik niet focussen op zoeken alleen; ik zou gewoon alle read/find operaties (of het nu één of meer entities zijn) via Elasticsearch laten lopen.

Dan moet je wel de hele entities en alle velden die je nodig hebt uit andere entities ook in ElasticSearch zetten. Dan zit je al heel snel je complete database te repliceren. Het lijkt me logischer om alleen de velden waarop je wil kunnen filteren/zoeken erin te zetten en gewoon te hydraten vanuit Doctrine, maar dan moet je zoals gezegd wel op een slimme manier omgaan met lazy fetching wanneer je database zo uitgebreid verbonden is als je hier omschrijft.

[ Voor 70% gewijzigd door NMe op 08-04-2018 12:44 ]

[DJMaze]

@NMe een post eerder had ik het over afstappen van ORM, niet DBAL.
De verwarring ontstond omdat ik Doctrine tussen haakjes had gezet, heb die weg gehaald.

egonolieux schreef op zondag 8 april 2018 @ 00:35:
Door gewoon native SQL te gebruiken blijft het probleem hetzelfde trouwens. Het gaat hem er om dat ik telkens een hele hoop extra queries moet schrijven/uitvoeren om de entity uit de database te halen in zijn volledige vorm wegens een zeer genormaliseerd datamodel. Als ik een documentstore opbouw van deze entities, moet ik niet telkens een arbitrair extra aantal queries uitvoeren voor de properties die ik nodig heb.

egonolieux schreef op zaterdag 7 april 2018 @ 04:56:
Fetch joins zijn mogelijks een oplossing voor dit probleem, maar in mijn geval wordt het aantal joins te groot waardoor de result set oneindig groot wordt (en het hydraten van de objecten oneindig lang duurt /geheugen nodig heeft).

Als je mijn post goed leest verdwijnt je geheugen probleem deels als sneeuw voor de zon.
Als je vervolgens zelf nog alle records in een array() gaat stoppen, dan niet natuurlijk.

code:

// Geen geheugen probleem
result = query("SELECT * FROM table"); // 1.000.000 records 80MB
while (record = result->fetch_row()) {
    echo record;
}
result->free();

// Geheugen probleem
result = query("SELECT * FROM table"); // 1.000.000 records 80MB
resultset = array();
while (record = result->fetch_row()) {
    resultset[] = record;
}
result->free();

Ik host veel websites. De PHP memory limit staat expres op 32MB.
Als iemand aan die limiet komt, moet de ontwikkelaar zijn code aanpassen.
Lukt het hem niet, dan kijk ik er naar. Lukt het echt niet, dan gaat de limiet omhoog voor die ene website.

[ Voor 7% gewijzigd door DJMaze op 08-04-2018 12:56 ]

[egonolieux]

NMe schreef op zondag 8 april 2018 @ 12:40:
Dan moet je wel de hele entities en alle velden die je nodig hebt uit andere entities ook in ElasticSearch zetten. Dan zit je al heel snel je complete database te repliceren. Het lijkt me logischer om alleen de velden waarop je wil kunnen filteren/zoeken erin te zetten en gewoon te hydraten vanuit Doctrine, maar dan moet je zoals gezegd wel op een slimme manier omgaan met lazy fetching wanneer je database zo uitgebreid verbonden is als je hier omschrijft.

Daar heb ik op zich geen probleem mee. In de meeste gevallen naast zoeken, heb ik meer dan de helft van de properties van de entity nodig. Dat zijn zo een 10-20 extra queries met lazy loading. Ik denk dat telkens het gehele object uit Elasticsearch halen performanter is.

DJMaze schreef op zondag 8 april 2018 @ 12:49:
Als je mijn post goed leest verdwijnt je geheugen probleem deels als sneeuw voor de zon.
Als je vervolgens zelf nog alle records in een array() gaat stoppen, dan niet natuurlijk.

code:

// Geen geheugen probleem
result = query("SELECT * FROM table"); // 1.000.000 records 80MB
while (record = result->fetch_row()) {
    echo record;
}
result->free();

// Geheugen probleem
result = query("SELECT * FROM table"); // 1.000.000 records 80MB
resultset = array();
while (record = result->fetch_row()) {
    resultset[] = record;
}
result->free();

Ik host veel websites. De PHP memory limit staat expres op 32MB.
Als iemand aan die limiet komt, moet de ontwikkelaar zijn code aanpassen.
Lukt het hem niet, dan kijk ik er naar. Lukt het echt niet, dan gaat de limiet omhoog voor die ene website.

Ik heb misschien maar max 20000 records in de tabel in kwestie. Als ik alle relaties van de tabel in kwestie mee wil ophalen in dezelfde query, zit ik aan gigantisch aantal records (bij de 20 joins met meer op meer relaties). Ik zou dit in PHP zoals je voorstelt inderdaad "on the fly" kunnen verwerken, maar ik denk dat dit een typisch geval is waarbij het veel performanter is meerdere queries uit te voeren.

[NMe]

DJMaze schreef op zondag 8 april 2018 @ 12:49:
Ik host veel websites. De PHP memory limit staat expres op 32MB.

Dat is inderdaad leuk voor een websiteje van een paar pagina's. Een webapplicatie van enige grootte heeft al heel snel veel meer nodig. Je gaat met 32MB al nat als je een geüpload plaatje wil resizen.

egonolieux schreef op maandag 9 april 2018 @ 22:10:
[...]

Daar heb ik op zich geen probleem mee. In de meeste gevallen naast zoeken, heb ik meer dan de helft van de properties van de entity nodig.

Waarom heb je die enorme hoeveelheid data nodig in hetzelfde venster waar je zoekresultaten of lijstjes toont? En met 20 joins die blijkbaar allemaal relevant zijn: weet je zeker dat je niet te ver doorgeslagen bent met normaliseren?

Dat zijn zo een 10-20 extra queries met lazy loading. Ik denk dat telkens het gehele object uit Elasticsearch halen performanter is.

Performance check je door te testen, niet alleen door te denken. Je zou de eerste niet zijn die prematuur performanceproblemen probeert op te lossen die niet eens daadwerkelijk optreden.

[momania]

Mocht je beide (document store en relationele DB) willen combineren in 1 system, kijk dan eens naar Postgres.
Datatype jsonb doet wat je wilt, is zoekbaar, te indexen, kan je updaten middels simpele triggers in je DB, etc.
Er is zelfs ondersteuning voor basis-full text search mocht je dat later toch willen of nodig hebben. Niet zo geavanceerd als wat je met ES kan bereiken, maar vaak genoeg.

[DJMaze]

DJMaze schreef op zondag 8 april 2018 @ 12:49:
Ik host veel websites. De PHP memory limit staat expres op 32MB.

NMe schreef op maandag 9 april 2018 @ 22:17:
Dat is inderdaad leuk voor een websiteje van een paar pagina's. Een webapplicatie van enige grootte heeft al heel snel veel meer nodig. Je gaat met 32MB al nat als je een geüpload plaatje wil resizen.

Hmmm nooit problemen mee, en ik verwerk ook zip bestanden van 1+GiB waar XML in zit.
De IOPS zijn erg intensief, het geheugen niet.

Alles draait er om hoe je de beste prestaties uit de machine haalt. Soms is dat via het geheugen, soms niet.
Soms is PHP handig, soms niet.

Het is goed dat egonolieux dit uitzoekt en de vraag stelt. Het getuigt van inzicht in processen en kijken hoe je het beste deze kan vormgeven.
Kanttekening is wel tijd en eventueel budget, zolang dat geen probleem is kan je heel veel bereiken.
Anders is inderdaad er meer geheugen tegenaan gooien de snelste en goedkoopste oplossing.

Zo had ik ooit het volgende gebruikt omdat dat het snelste was:

code:

product_ids = SELECT GROUP_CONCAT(product_id) FROM products WHERE.....;

variant_ids = SELECT GROUP_CONCAT(variant_id) FROM products_variants WHERE product_id IN (product_ids);

variants_buffer = SELECT * FROM products_variants WHERE variant_id IN (variant_ids);

Alle variants stonden nu gebuffered in het geheugen.
En dan...

code:

while (object = products->fetch_object('productClass')) {
    object->getVariants(variants_buffer);
}

Al met al is alles mogelijk op verschillende manieren. Soms is de één beter soms de ander.
ORM heeft zo echt wel zijn waarde.

Maar goed, het voorbeeld struikt vast met alle design principes van veel mensen.

[ Voor 25% gewijzigd door DJMaze op 09-04-2018 23:47 ]

[egonolieux]

NMe schreef op maandag 9 april 2018 @ 22:17:
Waarom heb je die enorme hoeveelheid data nodig in hetzelfde venster waar je zoekresultaten of lijstjes toont? En met 20 joins die blijkbaar allemaal relevant zijn: weet je zeker dat je niet te ver doorgeslagen bent met normaliseren?

Ik had het hier over het weergeven van één enkele entity. Omdat ik meerdere views heb van deze entity die elk een andere hoeveelheid data weergeven, dacht ik dat het misschien handig was dit ook te koppelen aan Elasticsearch i.p.v telkens een bijkomstig verschillend aantal queries/joins uit te voeren. Ik doe dit i.p.v. lazy loading om N+1 situaties te vermijden bij een aantal geneste veel-op-veel relaties; anders zit ik makkelijk aan 50+ queries terwijl het met 15 ook kan.

Je zou misschien kunnen denken dat ik te ver gegaan ben met normaliseren, maar ik heb enkel genormaliseerd in functie van data integriteit, hooguit in 3NF. Het is nu eenmaal zo dat de hoofdtabel een stuk of 10 veel-op-veel relaties heeft met op zich ook weer een aantal veel-op-veel relaties.

Momenteel is er geen performanceprobleem, maar als ik Elasticsearch implementeer voor zoekfunctionaliteit (en een REST API, waarvoor ik wel alle data tegelijk moet weergeven), dacht ik mijn code mogelijks te vereenvoudigen door gewoonweg altijd de volledige entity op te halen uit Elastic ipv het geknoei met eager fetching te forceren met extra joins/queries.

egonolieux schreef op maandag 9 april 2018 @ 22:10:
Ik denk dat telkens het gehele object uit Elasticsearch halen performanter is.

NMe schreef op maandag 9 april 2018 @ 22:17:
Performance check je door te testen, niet alleen door te denken. Je zou de eerste niet zijn die prematuur performanceproblemen probeert op te lossen die niet eens daadwerkelijk optreden.

Anders gezegd: ik vermoed dat Elasticsearch wel eens performanter zou kunnen zijn dan wat ik nu doe. Maar dit ga ik inderdaad eerst moeten testen.