Analyse van koppelvlakken op basis van MonetDB

Sinds het beschikbaar stellen van KV1 (dienstregeling), KV6 (punctualiteit en voertuigposities), KV15 (halteberichten) en KV17 (afwijking op het operationeel proces) door Connexxion aan Calendar42 zijn wij actieve gebruikers geweest van MonetDB om koppelvlakken te analyseren en te verwerken. Dat werkte niet altijd direct zoals we graag wilden, in vijf jaar hebben we de grenzen van menig systeem verkend en vaak ook verlegd. Voorafgaande aan een uitgebreide handleiding deze blog.

Een van de grenzen waar een beginnende KV6 of KV78turbo gebruiker tegen aan loopt is dat huis-tuin-en-keuken databanksoftware vaak niet snel genoeg is om een inkomende datastroom te verwerken en er tegelijkertijd vanaf een andere verbinding vragen over te kunnen beantwoorden. De verwerking vertraagt en er ontstaat een wachtrij die er uiteindelijk voor zorgt dat reisinformatie die wordt getoond minuten ouder is dat de laatst verstuurde gegevens, maar die op dat moment nog niet verwerkt zijn. Verschillende oplossingen zijn binnen handbereik; in plaats van per bericht de databank bij te werken kunnen berichten van meer voertuigen in een keer worden verwerkt.

Ook kan het concept van relationele database overboord worden gegooid. Vaak is alleen de laatste waarde interessant en wordt een koppelvlak gefilterd op een gekozen sleutel om een specifieke doorsnede te maken. In het geval van een OVradar is de primaire sleutel de samengestelde waarde van vervoerdercode en voertuignummer. Wanneer een applicatie voor bushaltes wordt gemaakt is die primaire sleutel de samengestelde waarde van vervoerdercode en haltenummer. De omvang van het maximum aantal sleutels is daarmee vooraf bekend en dat maakt het geheugenbeheer van een actuele reisinformatie server veel eenvoudiger.

Toch is de vraag: Kunnen jullie ook alle reisinformatie doorzoekbaar maken? meermalen gesteld, door hogescholen, universiteiten, overheden, vervoerders en adviesbureaus. Laten we daarom in prioriteit aangeven wat hiervoor van belang is. Als eerste we willen hoe dan ook alle informatie opslaan, een redundante opslag van NDOV koppelvlakken is dan ook een noodzaak. We behandelen onszelf niet anders dan andere afnemers, en sluiten dan ook aan op de pijp die wordt beheerd door ACC ICT, ze noemen zichzelf tegenwoordig specialist in continuïteit en dat is juist hier nodig. Met meerdere ontvangstpunten en samenwerking met meerdere NDOV-afnemers kunnen we alle reisinformatie opslaan die vervoerders uitsturen, ook als we zelf een steekje laten vallen.

Die opslag wordt in eerste instantie gerealiseerd door koppelvlakken van XML-structuur om te zetten naar CSV. Dat ging goed totdat een vervoerder corrupte XML stuurde, daarom wordt corrupte XML in een afzonderlijk bestand opgeslagen. Voor een normale toepassing zou op middernacht een nieuw bestand beginnen een handige methode zijn, echter in het openbaar vervoer loopt de operationele dag van 4 uur ’s ochtends tot ruim 24 uur later. We kunnen ook de structuur van de bestanden inzetten om data partities te maken. De primaire sleutel in KV6 bevat onder andere de vervoerdercode en operationele dag. Het is dus eenvoudig om data naar vervoerder op te splitsen, immers: er bestaat tijdens de latere verwerking geen relatie tussen verschillende vervoerdercodes. Wij hebben er voor gekozen om data weg te schrijven naar een uniek bestand per operationele dag, en later pas doorsnedes te maken per vervoerder. Dat gezegd hebbende, hebben we nog wel een dingetje met open bestanden op te lossen.

Met dagelijkse KV6 en KV17 bestanden ontbreekt een ding om alles aan elkaar te koppelen: een dagelijkse dienstregeling. De verwerking van de verschillende KV1 bestanden per vervoerder kost op zichzelf veel moeite door verschillende versies van de standaard en verschillende geldigheden per vervoerder. We willen er zeker van zijn dat we de laatst geldige dienstregeling van een vervoerder gebruiken. OVapi levert ons iedere dag een uitgeschreven gedenormaliseerde dienstregeling, met alle passeertijden langs alle haltes en stations van Nederland, we noemen dit koppelvlak voor het gemak: TT (Timetable).

Losse bestanden op schijf zijn niet eenvoudig te combineren, daarvoor is een databank nodig. Eens per dag laden we het complete TT bestand als nieuwe tabel. Als naam gebruiken daarvoor de vervoerdercode, het type en de operationele dag, bijvoorbeeld: HTM_TT_20160606. Daarnaast laden we de bestanden die gegeneerd zijn uit KV6 en KV17 gedurende de dag een aantal maal als HTM_KV6_20160606 en HTM_KV17_20160606. In de periode van 00:00 – 08:00 worden twee updates gedaan de operationele dag van gisteren en de nieuwe operationele dag.

Op dit moment hebben we ruim twee jaar data beschikbaar om actief te kunnen bevragen. Een bevraging kan een rijtijdanalyse zijn, maar ook alle vertragingen op een bepaalde halte. Op het eerste oog lijkt onze horizontale partitie, per vervoerder per dag, onhandig om om de hele databank in een keer door te zoeken. Een andere methode maakt slechts 1 tabel per type, waarin alle gegevens worden aangevuld. Als gedachte experiment: stel dat we een combinatie maken tussen tabel TT en KV6, dan wordt deze toepast op de primaire sleutel van KV6 waar vervoerdercode en operationele dag onderdeel van zijn. De volledige tabel moet worden ingelezen terwijl we vooraf weten dat slechts kleine gedeeltes zullen koppelen, een slimme databank zal waarschijnlijk een hash-index maken voor de operatie wordt uitgevoerd. In het geval van onze partities zijn die hash-indices 1/n-dagen kleiner en daarmee sneller te combineren.

Om toch in de volledige databank te doorzoeken kan in de databank programmeertaal SQL gebruik gemaakt worden van een doorsnede, oftewel VIEW. Er is een doorsnede te maken die de verschillende tabellen weer combineert door het UNION ALL commando. Het eerste nadeel is dat een zoek argument op de doorsnede er voor zorgt dat de volledige view moet worden gemaakt, slimme databank optimalisaties zullen er waarschijnlijk voor zorgen dat de argumenten op de verschillende gecombineerde tabellen worden uitgevoerd en het resultaat wordt samengevoegd. Een tweede nadeel van deze methode is dat per dag de VIEW definitie moet worden verwijderd om de nieuwe tabellen te koppelen. Om praktische redenen kan het handig zijn om het databankschema niet te veranderen, bijvoorbeeld wanneer andere tabellen er een afhankelijkheid op hebben.

MonetDB biedt een interessant alternatief voor een klassieke gecombineerde tabel: de MERGE TABLE. Deze gecombineerde tabel heeft een transparante administratie. Tabellen kunnen worden toegevoegd en verwijderd, zonder dat het schema zelf wijzigt. In de afgelopen tijd hebben wij erg veel geëxperimenteerd met deze functionaliteit. Eerlijkheid gebiedt te zeggen dat er al heel wat water door het Rijn-Schiekanaal heeft gestroomd voordat alle eigenaardigheden zijn onderkend, beschouw dit niet als waarschuwing, maar als bevestiging dat het anno juni 2016 prima werkt en alleen nog beter kan worden. Voorgaande beschreef het tweede nadeel, hoe zit het met het eerste nadeel?

TL;DR Sinds vandaag hebben wij MonetDB zo ver dat het alleen de tabellen bekijkt die daadwerkelijk informatie bevat waar we om vragen. Praktisch; stel we hebben twee jaar aan data in onze gecombineerde tabel, en we zoeken op 2 juni dan zal alleen de onderliggende tabel van HTM_KV6_20160602 worden bekeken. Hoe weet MonetDB dan dat het alleen daar moet kijken? Onze tabellen zullen na het aanmaken niet meer veranderen, we kunnen de tabel dan ook als alleen-lezen markeren. Daarna laten we MonetDB de tabel analyseren, vervolgens wordt de geanalyseerde tabel aan de gecombineerde tabel toegevoegd. Op basis van de analyse vooraf kan de MonetDB beslissen dat de tabel binnen of buiten de selectie valt.

Gemiddelde snelheid (warm) na 3x draaien
totaal aantal regels analyzed view merge table
punctdep filter operatingday en lineplanningnumber 83.715.212 nee 719ms 673ms
punctdep filter operatingday en lineplanningnumber 83.715.212 ja 220ms 11ms
kv6 filter operatingday en lineplanningnumber 548.825.316 ja 267ms 12ms
Machine
CPU 8x Intel(R) Xeon(R) CPU E3-1245 v5 @ 3.50GHz
RAM 64GB
Besturingssysteem Gentoo Linux
Kernel Linux 4.6.1-gentoo
Opslag 2x SAMSUNG MZ7LN512; SoftRAID 10-far2
Bestandssysteem F2FS (DBfarm), EXT4 (programmacode)


DROP TABLE kv6;
CREATE MERGE TABLE "sys"."kv6" (
"receive" TIMESTAMP,
"message" TIMESTAMP,
"vehicle" TIMESTAMP,
"messagetype" VARCHAR(10),
"operatingday" DATE,
"dataownercode" VARCHAR(10),
"lineplanningnumber" VARCHAR(10),
"journeynumber" INTEGER,
"reinforcementnumber" SMALLINT,
"userstopcode" VARCHAR(10),
"passagesequencenumber" SMALLINT,
"distancesincelastuserstop" INTEGER,
"punctuality" INTEGER,
"rd_x" INTEGER,
"rd_y" INTEGER,
"blockcode" INTEGER,
"vehiclenumber" INTEGER,
"wheelchairaccessible" VARCHAR(5),
"source" VARCHAR(10),
"numberofcoaches" SMALLINT,
"trip_hash" BIGINT
);

ALTER TABLE kv6_20160605 SET READ ONLY;
ALTER TABLE kv6_20160604 SET READ ONLY;
ANALYZE sys.kv6_20160605;
ANALYZE sys.kv6_20160604;
ALTER TABLE kv6 ADD TABLE kv6_20160605;
ALTER TABLE kv6 ADD TABLE kv6_20160604;

Advertenties
Dit bericht werd geplaatst in Bigdata. Bookmark de permalink .

4 reacties op Analyse van koppelvlakken op basis van MonetDB

  1. ikud zegt:

    We tried to use ANALYZE command, but didn’t notice increase in speed.

    Also about «8x Intel(R) Xeon(R) CPU E3-1245 v5 @ 3.50GHz» – does it mean that your server has 8 CPUs? Does it help?
    Our server has 4 cores and 8 threads. We set monetdb nthreads=8 to increase speed, but see that only one core works during any query.

    • ndovloket zegt:

      The speed comes from the merge table pre-selection. But as a link in the article already suggests there is still something wrong to fit everyday use. Why the performance increase is so significant in this specific example is because a specific partition is touched, hence only one table out of 800 tables is scanned. I was already informed that the issue will be looked into, this will result in the general solution. TL;DR there is great potential when the data is split into partitions, but you can only observe it now when one partition is selected in the where statement of your query.

      • ikud zegt:

        Thanks for reply!
        We’ve been using monetdb about 1 year and everything was good, but now we’re trying to scale our results and don’t know how to do it with monetdb.
        The query processing speed decreases rapidly with an increase in the number of rows. Monetdb always use only one thread even for queries like “select count(*) from table”.
        Is it bug?

      • ndovloket zegt:

        I don’t think it is a bug. If you do a a single table selection, without where, it should still be a single column of the table to be touched. Now a single processor can process GigaBytes per second, but typically there is an I/O bottleneck. Hence it isn’t faster to process with multiple processors since the data can’t arrive in the memory in time.

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit / Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit / Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit / Bijwerken )

Google+ photo

Je reageert onder je Google+ account. Log uit / Bijwerken )

Verbinden met %s