Database van 180GB importeren

[Tim.k]

Tweakers,

Voor een school project moet ik een data analyse project uitvoeren. Hiervoor willen wij een dataset met data van Steam gebruiken ( https://steam.internet.byu.edu ). De onderzoeksvraag en documentatie is al gemaakt en we zijn er vanuit gegaan dat deze dataset wel op een desktop te draaien was. Dom natuurlijk, dit hadden wij eerst moeten proberen want het importeren van het SQL bestand wil niet lukken. Het is nu te laat om een nieuwe dataset te zoeken en een onderzoeksvraag + documentatie op te stellen. Aanstaande woensdag krijgen we een go of no go.

De database is ongeveer 180GB groot en al een week aan het importeren, de indexes maken duurt tergend lang op mijn computer (i5 7400, 16GB RAM, 4TB 7200RPM schijf). De schijf is de bottleneck en is continu 100% belast.

De vraag is dus, hoe ga ik deze database importeren? Of nog beter, eenmaal geïmporteerd, is er überhaupt een query op te draaien?

Ik heb gezocht op een manier hoe ik de indexes uit het create script kan slopen maar een 100gb+ bestand openen in een editor is geen optie. Een zoektocht naar speciale programma's die grote bestanden aan kunnen loopt ook op niets uit.

Ik heb ook al geprobeerd of MySQL de indexes kan overslaan d.m.v. configuratie maar ook daar kan ik niets zinnigs over vinden.

Rekenkracht vanuit school is helaas niet beschikbaar. Ook cloudservices met "studenten" krediet zijn niet toereikend omdat de kosten ver boven het beschikbare krediet zitten.

Heeft iemand ervaring met het importeren van grote databases of tips hoe ik dit zou kunnen aanpakken?

[j-a-s-p-e-r]

Als je geen index hebt wordt queries draaien lastig (lees: ergggg langzaam). Uiteraard kan je wel de indexes eruit slopen die je niet nodig hebt.
In principe zou je dat kunnen doen met een in-file editor (bijv. grep ofzo) om een stukje te replacen (bijv. ALTER TABLE ADD INDEX blabla replacen door #ALTER TABLE.. om het uit te commenten.

Overigens is je dat wel bruikbaar voordat je de indexes gecreeerd hebt. Kan je wat meer vertellen over de bron (ik heb niet zo'n zin om 17GB te downloaden en uit te pakken, sorry). Is het 1 sql file met statments (kan je checken in linux met `head -n 100 filename.sql` (eerste 100 regels ipv hele file. Of `less` gebruiken en er doorheen wandelen.

Verder is je schijf idd de bottleneck. Je zou wel meerdere schrijven (4 stuks minimaal )in RAID 10 kunnen zetten om zo veel hogere performance te halen. Of een paar tientjes spenderen aan een VPS met SSD (transip, amazon, whatever). Als je dat niet wil dan houdt het een beetje op en kom je op compromis uit helaas.

[ Voor 22% gewijzigd door j-a-s-p-e-r op 14-05-2018 22:16 ]

[ik222]

Willekeurig indexes niet laten aanmaken is in elk geval geen goed plan. Daar wordt zo'n dataset echt onwerkbaar van. Dit kan alleen een optie zijn als je precies weet welke queries je straks gaat draaien en welke indexes je daarvoor niet nodig hebt.

Je hebt gewoon meer disk power nodig als je echt de volledige dataset wilt importeren en gebruiken denk ik. Een SSD waar de DB op past zou echt een enorm verschil maken hierbij.

Zonder SSD moet je kijken of je ook een gedeelte van de dataset kan krijgen en dan op basis daarvan je conclusies trekken.

/Edit: Is de database server wel al geoptimaliseerd voor performance met zo veel data? Dus caches ophogen en optimalisatie van de schrijf cache. Ook dat kan best nog veel verschil maken al denk ik dat je sowieso een SSD nodig gaat hebben.

[ Voor 17% gewijzigd door ik222 op 14-05-2018 22:24 ]

[TommyboyNL]

Koop een SSD, zo duur hoeft dat niet te zijn. Dit is bijvoorbeeld een goede koop: https://www.amazon.de/Cru...ternes-Zoll/dp/B0764WCXCV. Dit zal je 1000 keer meer performance opleveren dan een traditionele HD.
En geloof me, na je onderzoek heb je ook nog érg veel plezier van een SSD.

Andere optie: Neem voor een maand een X8 VPS bij TransIP, de eerste maand is gratish (wel zelf opzeggen!): https://www.transip.nl/vps/

[ Voor 18% gewijzigd door TommyboyNL op 14-05-2018 22:23 ]

[Tim.k]

Kosten wil ik niet maken voor dit schoolproject, een VPS met een dergelijk grote SSD is enorm duur en cloud services zijn nog veel duurder.

@j-a-s-p-e-r Ik heb geprobeerd met sed (beperkte kennis) te werken om indexes er uit te krijgen maar zonder het bestand überhaupt te kunnen bekijken (zelfs met tail / head o.i.d. duurt dit uren) is dit natuurlijk erg lastig c.q. onmogelijk.

Het selectief knippen van de database is om diezelfde reden dan ook erg lastig.

Ik heb de dataset op een SSD geprobeerd te importeren (al was deze te klein) maar een tabel van 16GB heeft 28 uur geduurd. Een ruwe schatting zou zijn dat het dan alsnog minimaal 280 uur zou duren.

[Yeebo]

Als je geen geld wilt uitgeven aan een VPS zou je kunnen kijken naar bijv DigitalOcean, daar heb je soms acties dat je honderd dollar starttegoed krijgt en omdat je per minuut betaald zou dit iets kunnen zijn, zo heb je ook bij Azure een trial van ongeveer 180 dollar.

[Tim.k]

@daupie Hier hebben we naar gekeken maar tot eind juni moeten we onze bevindingen elke week finetunen. Dit is met free credit niet te doen. Nu kun je bij Azure of Google Cloud de instanties natuurlijk stoppen maar je betaald nog steeds enorm veel voor strogage. Bij Google Cloud kun je 230 euro tegoed krijgen, echter zijn we aan storage alleen al 80 euro p/m kwijt en de computing instance is met 3 uur per dag on time ook 60 euro per maand. Een gunstiger trial credit / prijs voor storage / instance hebben wij niet gevonden.

[TommyboyNL]

Tim.k schreef op maandag 14 mei 2018 @ 22:26:
Kosten wil ik niet maken voor dit schoolproject, een VPS met een dergelijk grote SSD is enorm duur en cloud services zijn nog veel duurder.

Hoe lang zou dit project moeten duren? Zoals ik schreef is een VPS bij TransIP de eerste maand gratis. Als zowel jij als je collega(s) dit ombeurten afnemen, heb je al minimaal 2 maanden een voldoende grote VPS.

[Henk007]

Come on zeg, het is een schoolopdracht, neem gewoon de eerste 5% van je dataset en ga daarmee aan de gang. Het is toch geen project waar mensenlevens vanaf hangen.

[Tim.k]

@TommyboyNL Dat is wellicht een optie. Ik weet nog dat eerst bij TransIP de eerste maand voor de helft van de prijs was, dit hebben ze dus veranderd! Bedankt.

[TommyboyNL]

Tim.k schreef op maandag 14 mei 2018 @ 22:36:
@TommyboyNL Dat is wellicht een optie. Ik weet nog dat eerst bij TransIP de eerste maand voor de helft van de prijs was, dit hebben ze dus veranderd! Bedankt.

Je kan ook die VPS alleen gebruiken om je dataset te verkleinen, zodat je hem daarna op een ander platform kan draaien. Allicht het overwegen waard.

[Tim.k]

Henk007 schreef op maandag 14 mei 2018 @ 22:35:
Come on zeg, het is een schoolopdracht, neem gewoon de eerste 5% van je dataset en ga daarmee aan de gang. Het is toch geen project waar mensenlevens vanaf hangen.

Als jij mij kan uitleggen hoe ik van een SQL create script van bijna 230GB schoon aan de haak een kleinere selectie kan maken, graag! Ik kan het helaas niet vinden hoe ik dit zou willen doen, editors kunnen dergelijk grote bestanden niet inladen en door middel van streaming bewerken gaat erg lastig als je de inhoud niet kunt zien en niet weet waar je kunt knippen. Daarbij zijn tabellen afhankelijk van elkaar wat knippen zonder query natuurlijk zo goed als onmogelijk maakt.

TommyboyNL schreef op maandag 14 mei 2018 @ 22:37:
[...]

Je kan ook die VPS alleen gebruiken om je dataset te verkleinen, zodat je hem daarna op een ander platform kan draaien. Allicht het overwegen waard.

Dat is inmiddels het plan.

[ Voor 23% gewijzigd door Tim.k op 14-05-2018 22:40 ]

[pedorus]

Tim.k schreef op maandag 14 mei 2018 @ 22:09:
De vraag is dus, hoe ga ik deze database importeren? Of nog beter, eenmaal geïmporteerd, is er überhaupt een query op te draaien?

Hoe doe je dit nu?

Ben die dataset even aan het binnenhalen, maar de server is een beetje traag. In dat bestand staan vooral INSERTs, als je die weggooit kun je het datamodel bekijken.

gunzip < steam.sql.gz |grep -v "^INSERT INTO" >steamwithoutinserts.sql

En dan zie je dingen die je niet wilt zien zoals:

SQL:

CREATE TABLE `Achievement_Percentages` (
  `appid` int(10) unsigned NOT NULL,
  `Name` varchar(64) NOT NULL,
  `Percentage` float NOT NULL,
  PRIMARY KEY (`appid`,`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Het valt me op dat er nog gebruik wordt gemaakt van MyIsam. Dit wil je vervangen door InnoDB, je wil InnoDB wat tweaken (innodb_flush_log_at_trx_commit en wat andere als in https://www.percona.com/b...imization-basics-updated/ ) en wellicht wil je ook betere/minder keys... Keys kun je eventueel achteraf nog maken. Dus ik zou die InnoDB settings tweaken en daarna iets doen als:

gunzip <steam.sql.gz | grep -v "^  KEY"| sed 's/^) ENGINE=MyISAM /\) ENGINE=InnoDB /'|mysql steam

(Wellicht ook met een grep -v voor de PRIMARY KEYs, afhankelijk van hoe het in die file precies staat. Composite primary keys wordt ik ook niet heel vrolijk van.)

Tim.k schreef op maandag 14 mei 2018 @ 22:38:
Als jij mij kan uitleggen hoe ik van een SQL create script van bijna 230GB schoon aan de haak een kleinere selectie kan maken, graag! I

Eerst het datamodel maken (file hierboven). Dan bijvoorbeeld greppen (zonder -v) op de INSERTs en sed - https://superuser.com/a/396557

[Tim.k]

Top reactie @pedorus!

pedorus schreef op maandag 14 mei 2018 @ 23:41:
[...]

Hoe doe je dit nu?

MySQL command line tool.

pedorus schreef op maandag 14 mei 2018 @ 23:41:

Ben die dataset even aan het binnenhalen, maar de server is een beetje traag. In dat bestand staan vooral INSERTs, als je die weggooit kun je het datamodel bekijken.

gunzip < steam.sql.gz |grep -v "^INSERT INTO" >steamwithoutinserts.sql

En dan zie je dingen die je niet wilt zien zoals:

SQL:

CREATE TABLE `Achievement_Percentages` (
  `appid` int(10) unsigned NOT NULL,
  `Name` varchar(64) NOT NULL,
  `Percentage` float NOT NULL,
  PRIMARY KEY (`appid`,`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Niet eens aan gedacht, zo kan ik natuurlijk makkelijk de structuur zien.

pedorus schreef op maandag 14 mei 2018 @ 23:41:
Het valt me op dat er nog gebruik wordt gemaakt van MyIsam. Dit wil je vervangen door InnoDB, je wil InnoDB wat tweaken (innodb_flush_log_at_trx_commit en wat andere als in https://www.percona.com/b...imization-basics-updated/ ) en wellicht wil je ook betere/minder keys... Keys kun je eventueel achteraf nog maken. Dus ik zou die InnoDB settings tweaken en daarna iets doen als:

Is MyIsam niet juist gunstig voor ons, ik weet niet of het een fabeltje is maar MyIsam is dacht ik sneller met selects dan InnoDB?

Het Percona artikel kende ik al, erg fijn en duidelijk artikel. Al verschillende keren MySQL hiermee getuned.

pedorus schreef op maandag 14 mei 2018 @ 23:41:

gunzip <steam.sql.gz | grep -v "^  KEY"| sed 's/^) ENGINE=MyISAM /\) ENGINE=InnoDB /'|mysql steam

(Wellicht ook met een grep -v voor de PRIMARY KEYs, afhankelijk van hoe het in die file precies staat. Composite primary keys wordt ik ook niet heel vrolijk van.)

Ik ben de database momenteel aan het importeren op een TransIP VPS dus dat laat Ik even voor wat het is maar ik ben wel benieuwd naar dit command. Mijn kennis van grep en sed is nihil maar door middel van de manpages begrijp ik alleen niet waarom de grep er tussen zit. Deze pakt zover ik begrijp alles behalve lijntjes waar KEY in zit, hierdoor moet de sed nog steeds door alle inserts zoeken waar dat niet nodig is of interpreteer ik de grep (-v optie) niet goed?

pedorus schreef op maandag 14 mei 2018 @ 23:41:
[...]

Eerst het datamodel maken (file hierboven). Dan bijvoorbeeld greppen (zonder -v) op de INSERTs en sed - https://superuser.com/a/396557

Dan kan ik ruw dingen er uit knippen maar zal veel data niet meer aan elkaar geknoopt kunnen worden door missende identifiers in andere tabellen.

[Hydra]

Wat zei je docent toen je deze problemen aan 'em voorgelegd hebt?

[jetspiking]

@Hydra

Misschien ook maar is een keer dit topic even bekijken?
En wat zei $bla toen je die vraag stelde?

Je "vraag" heeft TS dus écht helemaal niets aan. TS heeft ook al een oplossing gevonden, en soms wil, of kun je, voor dit soort problemen niet even terecht bij je docent. Daarnaast heeft hij het nu al op een prima manier opgelost

@Verwijderd

Ondanks dat je al een oplossing gevonden hebt is een SSD écht aan te raden, anno 2018 kun je eigenlijk ook niet meer zonder. Snellere opstart tijden en sneller uitvoeren van programma's (zoals je database sch(r)ijf snelheid probleem).

[Hydra]

jetspiking schreef op dinsdag 15 mei 2018 @ 09:35:
@Hydra

Misschien ook maar is een keer dit topic even bekijken?
En wat zei $bla toen je die vraag stelde?

Je "vraag" heeft TS dus écht helemaal niets aan. TS heeft ook al een oplossing gevonden, en soms wil, of kun je, voor dit soort problemen niet even terecht bij je docent. Daarnaast heeft hij het nu al op een prima manier opgelost

Het is ook niet bedoeld als een reactie waar de TS direct wat aan heeft. Het is bedoeld as vraag waarna we met die info dieper in kunnen gaan op het probleem. Het is namelijk volledig onduidelijk wat de verwachtingen zijn. Er wordt hier maar meteen met oplossingen gestrooid zonder te achterhalen of de TS daadwerkelijk het juiste probleem aan het oplossen is.

Voor een schoolopdracht 180GB aan data in moeten laden is niet normaal. Je kunt een dergelijke opdracht prima doen met nog geen tiende van de data. En als je die data al in een VPS krijgt, dan is een query doen op die data nog steeds drama waarschijnlijk.

Dus nee, z'n probleem is alles behalve opgelost. Vandaar dat ik eerst doorvraag.

[jetspiking]

@Hydra

TS heeft gezegd dat wisselen nu niet meer mogelijk is, in gesprek gaan met de docent zou eventueel kunnen, maar los van het feit dat je daar waarschijnlijk niets mee opschiet zal je er ook geen extra punten mee gaan scoren.

Indien het lukt om een database in te laden van wat meer GB's en daarop juist de opdracht verricht wel.

En natuurlijk had je ook kunnen vragen of hij dit al overlegd heeft met zijn docent, in plaats van een vraag als deze te stellen...

[Falcon]

Microsoft heeft vastwel met Azure een goeie oplossing voor je, die ook niks/weinig hoeft te gaan kosten.

https://azure.microsoft.c...0ruwQ5EAAYASAAEgK4d_D_BwE

[Paksoy]

Ik heb 3 jaar geleden voor mijn afstudeerstage gratis toegang gekregen tot de supercomputing cluster van SURFSara. Dit waren destijds 84 supercomputers connected, voor een totaal van 4,7TB RAM, en schijfruimte was ver in de Petabytes.

Aangezien dit ook studiegerelateerd is voor jou, en misschien iets wetenschappelijks wilt aantonen, dan zou je een aanvraag kunnen indienen: https://www.surf.nl/en/se...-data-services/index.html . (Mijn toepassing was medisch gericht).

[MarkH NL]

Je kan de set eventueel opsplitsen en per tabel importeren, door te splitten maak je de bestanden ook kleiner waardoor bewerken met een editor ook moet lukken.

https://github.com/kedarvj/mysqldumpsplitter

of

https://github.com/vekexasia/mysqldumpsplit

Succes!

[Hydra]

jetspiking schreef op dinsdag 15 mei 2018 @ 09:52:
@Hydra

TS heeft gezegd dat wisselen nu niet meer mogelijk is, in gesprek gaan met de docent zou eventueel kunnen, maar los van het feit dat je daar waarschijnlijk niets mee opschiet zal je er ook geen extra punten mee gaan scoren.

Nee hoor. Een simpele "hey Kees-Jan; we zijn bezig met het importeren van de database maar het blijkt dat deze 180GB is en het importeren hiervan een week gaat duren. Is het de bedoeling dat we het allemaal importeren of is een subset ook goed?" gaat je echt geen punten kosten. Misschien heeft die docent zich uberhaupt niet gerealiseerd hoe groot die DB is.

Hoe dan ook is dat ding een week laten stampen zonder even met je docent te gaan praten een enorm slecht idee. Je docent is je eerste aanspreekpunt als je er met je groep niet uit gaat komen.

Maargoed. Als jij hier anders over denkt; prima. Maar dit is dus de reden dat ik eerst een vraag stel. Zouden meer mensen moeten doen voor dat ze domweg 'oplossingen' gaan strooien

[q-enf0rcer.1]

Hydra schreef op dinsdag 15 mei 2018 @ 10:50:
[...]


Nee hoor. Een simpele "hey Kees-Jan; we zijn bezig met het importeren van de database maar het blijkt dat deze 180GB is en het importeren hiervan een week gaat duren. Is het de bedoeling dat we het allemaal importeren of is een subset ook goed?" gaat je echt geen punten kosten. Misschien heeft die docent zich uberhaupt niet gerealiseerd hoe groot die DB is.

Hoe dan ook is dat ding een week laten stampen zonder even met je docent te gaan praten een enorm slecht idee. Je docent is je eerste aanspreekpunt als je er met je groep niet uit gaat komen.

Maargoed. Als jij hier anders over denkt; prima. Maar dit is dus de reden dat ik eerst een vraag stel. Zouden meer mensen moeten doen voor dat ze domweg 'oplossingen' gaan strooien

Ben het helemaal met jou eens, gewoon hersenloos die PC een week laten draaien zonder het probleem eerst te bespreken is een bizarre keuze. Het is niet eens duidelijk of de boel überhaupt wel gaat werken na die week. Je bent hier zo een kostbare week kwijt als het niet goed uitpakt! Durf je stem te gebruiken!

[Cloud]

Hydra schreef op dinsdag 15 mei 2018 @ 10:50:
[...]

Misschien heeft die docent zich uberhaupt niet gerealiseerd hoe groot die DB is.

[...]

Het zijn de studenten die zich niet realiseerden wat de grootte van deze dataset met zich meebracht. Zoals TS al aangaf, ze hebben dit zelf uitgezocht en de onderzoeksvraag en alles daarop gebaseerd. De docent zal daar verder niet zoveel mee kunnen veranderen.

Neemt niet weg dat je altijd even kan gaan praten met je docent maar ik vind de zoektocht naar oplossingen voor de huidige situatie helemaal niet zo gek. Switchen van onderzoeksvraag is immers al te laat, daar is al naar gekeken. Ze hebben zelf deze 'misser' begaan, als ze het zelf ook op kunnen lossen is dat alleen maar mooi. En ook leuk voor een kopje 'uitdagingen' in het uiteindelijke verslag

Als ze de dataset gewoon kunnen splitsen (en dus kunnen gebruiken) met de tips van @pedorus en @MarkH NL dan zou dat toch de voorkeur hebben lijkt me.

[Hydra]

Cloud schreef op dinsdag 15 mei 2018 @ 10:59:
Neemt niet weg dat je altijd even kan gaan praten met je docent maar ik vind de zoektocht naar oplossingen voor de huidige situatie helemaal niet zo gek.

Prima. Maar dan juist is het een goed idee om even een stap terug te nemen. Sommigen in dit topic doen dat ook (door voor te stellen een deel van de data in te laden). Anderen focussen zich blind op de vraag van de TS en het geven van specifiek een oplossing daarvoor. Dit kunnen nemen van dat stapje terug en je afvragen of je wel het juiste probleem aan het oplossen bent is een enorm waardevolle skill voor developers en een van de voornaamste punten die iemand 'senior' maakt in mijn ogen. Vandaar dat ik erover doordram

[Tim.k]

Hydra schreef op dinsdag 15 mei 2018 @ 08:22:
Wat zei je docent toen je deze problemen aan 'em voorgelegd hebt?

"Heb je dit ook al met je docent besproken, wellicht dat die je kan helpen?". Fixed it, met dergelijke zinnen in plaats van "wat zei" wordt Tweakers een stuk vriendelijker

Giftige eerste reactie op elke topic

Maar goed, ja, dat heb ik direct gedaan toen wij op dit probleem stuiten. Hij gaf aan dat er vanuit school geen rekenkracht beschikbaar was, Cloud services waar studenten krediet krijgen wellicht een optie was. Database verkleinen, al wist hij zelf niet hoe dat zou moeten gebeuren aangezien tabellen afhankelijk van elkaar zijn was ook nog een optie. Daarna is een projectlid ook nog naar de hoofd ICT'er gegaan, die kwam echter ook niet verder dan wat de docent al zei.

We hebben ook nog besproken of we een andere dataset konden bestuderen. Dit was echter in de korte tijd vrij riskant aangezien we geen feedback meer kunnen krijgen en woensdag aanstaande een go of no go ontvangen.

Falcon schreef op dinsdag 15 mei 2018 @ 09:55:
Microsoft heeft vastwel met Azure een goeie oplossing voor je, die ook niks/weinig hoeft te gaan kosten.

https://azure.microsoft.c...0ruwQ5EAAYASAAEgK4d_D_BwE

Dat gratis tegoed is binnen 1,5 week op met een degelijke server die de dataset zou kunnen draaien. Enkele berichten hierboven heb ik al aangegeven dat cloud services niet haalbaar zijn.

Paksoy schreef op dinsdag 15 mei 2018 @ 10:18:
Ik heb 3 jaar geleden voor mijn afstudeerstage gratis toegang gekregen tot de supercomputing cluster van SURFSara. Dit waren destijds 84 supercomputers connected, voor een totaal van 4,7TB RAM, en schijfruimte was ver in de Petabytes.

Aangezien dit ook studiegerelateerd is voor jou, en misschien iets wetenschappelijks wilt aantonen, dan zou je een aanvraag kunnen indienen: https://www.surf.nl/en/se...-data-services/index.html . (Mijn toepassing was medisch gericht).

Dit had ik ook al gevonden maar Ik denk dat een simpele schoolopdracht hier niet voor kwalificeert. Daarnaast duurt de aanvraag procedure 2 tot 6 weken.

MarkH NL schreef op dinsdag 15 mei 2018 @ 10:25:
Je kan de set eventueel opsplitsen en per tabel importeren, door te splitten maak je de bestanden ook kleiner waardoor bewerken met een editor ook moet lukken.

https://github.com/kedarvj/mysqldumpsplitter

of

https://github.com/vekexasia/mysqldumpsplit

Succes!

Ik heb de bovenste tool al geprobeerd, doet zijn werk maar per tabel importeren zorgt nog steeds voor enorme wachttijden bij het maken van indexes en met bewerken blijf ik met het probleem zitten dat ik records ga verwijderen waar een andere tabel afhankelijk van is.

[MarkH NL]

Ik heb de bovenste tool al geprobeerd, doet zijn werk maar per tabel importeren zorgt nog steeds voor enorme wachttijden bij het maken van indexes en met bewerken blijf ik met het probleem zitten dat ik records ga verwijderen waar een andere tabel afhankelijk van is.

Eventueel keys uitzetten tijdens de import? Als je losse bestanden hebt kan je dit proberen:

https://support.tigertech.net/mysql-large-inserts

zelf niet veel ervaring mee, maar is mogelijk de moeite waard.

[Orion84]

Tim.k schreef op dinsdag 15 mei 2018 @ 11:10:
[...]
Dat gratis tegoed is binnen 1,5 week op met een degelijke server die de dataset zou kunnen draaien. Enkele berichten hierboven heb ik al aangegeven dat cloud services niet haalbaar zijn.

1,5 week is wellicht wel genoeg om de DB te laden en vervolgens een subset van de data te exporteren om lokaal mee verder te werken?

[Tim.k]

@MarkH NL Mooi artikel, ik ben nu al aan het importeren naar een TransIP VPS en dat gaat dusver goed dus dat laat ik even zo. Maar mocht dat toch niet goed gaan ga ik dat zeker toepassen. Ik vraag me echter af of dit niet het probleem verschuiven is want zodra je de keys zou inschakelen moet hij alsnog indexen opbouwen wat juist zo tergend lang duurt?

Orion84 schreef op dinsdag 15 mei 2018 @ 11:19:
[...]

1,5 week is wellicht wel genoeg om de DB te laden en vervolgens een subset van de data te exporteren om lokaal mee verder te werken?

Dat moet inderdaad wel lukken. Ik kijk eerst even aan hoe het met de TransIP VPS afloopt, het idee is nu om daar een subset op te maken.

[ Voor 35% gewijzigd door Tim.k op 15-05-2018 11:21 ]

[Hydra]

Tim.k schreef op dinsdag 15 mei 2018 @ 11:10:
"Heb je dit ook al met je docent besproken, wellicht dat die je kan helpen?". Fixed it, met dergelijke zinnen in plaats van "wat zei" wordt Tweakers een stuk vriendelijker

Of je steekt hand in eigen boezem. Ik stel die vraag niet voor niets.

Maar goed, ja, dat heb ik direct gedaan toen wij op dit probleem stuiten. Hij gaf aan dat er vanuit school geen rekenkracht beschikbaar was, Cloud services waar studenten krediet krijgen wellicht een optie was. Database verkleinen, al wist hij zelf niet hoe dat zou moeten gebeuren aangezien tabellen afhankelijk van elkaar zijn was ook nog een optie. Daarna is een projectlid ook nog naar de hoofd ICT'er gegaan, die kwam echter ook niet verder dan wat de docent al zei.

We hebben ook nog besproken of we een andere dataset konden bestuderen. Dit was echter in de korte tijd vrij riskant aangezien we geen feedback meer kunnen krijgen en woensdag aanstaande een go of no go ontvangen.

Maar waarom meld je dit niet in je OP? Dat is enorm relevante informatie. Vergeet niet dat jij hier met een hulpvraag komt en dat alles wat jij al gedaan en uitgezocht hebt voor de mensen aan wie je die vraag stelt enorm belangrijk is. De database verkleinen is veruit de simpelste optie, door bijvoorbeeld 10% van de data te samplen met een simpel scriptje. Daar zijn veel voorbeelden van te vinden en iets waar je hier vast wel wat hulp mee kan krijgen (is voor een ervaren developer niet moeilijk te regelen). Hoe dan ook is dat ding een week laten stampen een slecht idee; je had na een uur al zo iets moeten hebben dat dit niet gaat werken. Als het inserten zo lang duurt, gaat 't queryen waarschijnlijk ook problematischer zijn.

Ik vind je topic over "zeikreacties" nogal triest. Goeie vragen stellen is een skill die je kan trainen. Wees blij dat iemand je laat weten dat je een slechte vraag stelt, daar leer je meer van dan van een paar mensen die toch maar een antwoord fabriceren.

Anyway; wat ik je aanraad is een simpel script te maken dat de data filtert. Lees gewoon uit elke insert / row / whatever de steam ID. Doe een modulo test om bijvoorbeeld 1% over te houden. En schrijf die regels dan weg naar een nieuwe file. Dan hou je 1.8GB over en dat gaat, hoewel het nog erg veel is voor een relationele DB, een stuk beter te managen zijn. Als je wil kan ik je daar wel mee helpen.

[ Voor 8% gewijzigd door Hydra op 15-05-2018 11:28 ]

[MarkH NL]

Tim.k schreef op dinsdag 15 mei 2018 @ 11:19:
@MarkH NL Mooi artikel, ik ben nu al aan het importeren naar een TransIP VPS en dat gaat dusver goed dus dat laat ik even zo. Maar mocht dat toch niet goed gaan ga ik dat zeker toepassen. Ik vraag me echter af of dit niet het probleem verschuiven is want zodra je de keys zou inschakelen moet hij alsnog indexen opbouwen wat juist zo tergend lang duurt?


[...]


Dat moet inderdaad wel lukken. Ik kijk eerst even aan hoe het met de TransIP VPS afloopt, het idee is nu om daar een subset op te maken.

Mocht het niet lukken wil ik vanavond wel een poging doen om er iets van te maken, laat maar even weten.

[NMe]

Hydra schreef op dinsdag 15 mei 2018 @ 11:20:
Of je steekt hand in eigen boezem. Ik stel die vraag niet voor niets.

Alle informatie die relevant is staat in de TS. Er staat dat er gekozen is voor een erg grote dataset en dat ze die zelf hebben uitgezocht. Er staat in dat het te laat is om nog te switchen van dataset. Er staat wat hij al geprobeerd heeft en waar en waarom het mis ging. Ik wil er best in meegaan dat sommige mensen geen inzet tonen en de vraag zoals jij die stelde daar niet per se fout is, maar hier vind ik het overbodig en overmatig confronterend.

Kunnen we nu weer ontopic?

[Tim.k]

Hydra schreef op dinsdag 15 mei 2018 @ 11:20:
[...]


Of je steekt hand in eigen boezem. Ik stel die vraag niet voor niets.


[...]


Maar waarom meld je dit niet in je OP? Dat is enorm relevante informatie. Vergeet niet dat jij hier met een hulpvraag komt en dat alles wat jij al gedaan en uitgezocht hebt voor de mensen aan wie je die vraag stelt enorm belangrijk is. De database verkleinen is veruit de simpelste optie, door bijvoorbeeld 10% van de data te samplen met een simpel scriptje. Daar zijn veel voorbeelden van te vinden en iets waar je hier vast wel wat hulp mee kan krijgen (is voor een ervaren developer niet moeilijk te regelen). Hoe dan ook is dat ding een week laten stampen een slecht idee; je had na een uur al zo iets moeten hebben dat dit niet gaat werken. Als het inserten zo lang duurt, gaat 't queryen waarschijnlijk ook problematischer zijn.

Ik vind je topic over "zeikreacties" nogal triest. Goeie vragen stellen is een skill die je kan trainen. Wees blij dat iemand je laat weten dat je een slechte vraag stelt, daar leer je meer van dan van een paar mensen die toch maar een antwoord fabriceren.

Je stelt die vraag zeker niet voor niets, het kan echter prima op een andere toon. Dat jij het een triest topic vind, prima, veel mensen delen echter mijn frustratie blijkbaar. Zegt wellicht toch iets over hoe GoT er op dit moment aan toe is.

Ik had het inderdaad kunnen vermelden, ik had alles kunnen vermelden wat ik geprobeerd had. Dan hadden we twee A4'tjes vol aan informatie. Ik heb gekozen om dit niet uitgebreid te vermelden omdat ik onderin al aangaf dat rekenkracht vanuit school niet beschikbaar was wat hopelijk aanduiden dat ik al contact gehad had met een docent (blijkbaar niet).

Verder ben ik erg benieuwd hoe ik de database kan verkleinen. Iedereen in dit topic schreeuwt het maar niemand kan mij dan ook maar een zetje in de rug geven. @pedorus is de enige die waardevolle informatie heeft gegeven over hoe ik dit voor elkaar zou kunnen krijgen. Echter is bij zijn idee ook het probleem dat data die afhankelijk van elkaar is verloren gaat.

Dus, graag, help mij. Daar maak ik dit topic immers voor aan. Alleen zeggen dat het voor een ervaren developer prima te doen is heb ik niet veel aan. Ervaren developers, rijk mij wat handvaten aan

[Hydra]

NMe schreef op dinsdag 15 mei 2018 @ 11:29:
Kunnen we nu weer ontopic?

Ja hoor. Ik heb ook aangeboden te helpen met de oplossing. Dus laten we vooral daar op focussen.

Tim.k schreef op dinsdag 15 mei 2018 @ 11:34:
Ervaren developers, rijk mij wat handvaten aan

Met alle plezier:

Hydra schreef op dinsdag 15 mei 2018 @ 11:20:
Anyway; wat ik je aanraad is een simpel script te maken dat de data filtert. Lees gewoon uit elke insert / row / whatever de steam ID. Doe een modulo test om bijvoorbeeld 1% over te houden. En schrijf die regels dan weg naar een nieuwe file. Dan hou je 1.8GB over en dat gaat, hoewel het nog erg veel is voor een relationele DB, een stuk beter te managen zijn. Als je wil kan ik je daar wel mee helpen.

[ Voor 60% gewijzigd door Hydra op 15-05-2018 11:39 ]

[Tim.k]

Dat is wel erg theoretisch en zeer makkelijk te bedenken. Uitvoeren is wat anders, hoe ga ik die al die ID's storen? Binnen no-time zit het RAM vol, het uitlezen en parsen zal ook een uitdaging zijn want regexes er op los laten gaat waarschijnlijk nog langer duren dan het importeren zelf (alternatief op regexes wellicht?), zo kan ik nog 10 andere struikelblokken bedenken. Ik denk niet dat het een kwestie van "even" een script schrijven is.

Wellicht dat het wel zo simpel is, ik heb echter geen ervaring met het bewerken en / of parsen van dergelijk grote bestanden en hoe ik dit efficiënt wil doen. Zou je wat meer informatie kunnen geven zoals welke taal of welke functies / methodieken wellicht van pas kunnen komen.

Ik heb al verschillende zoektermen op Google los gelaten maar er is maar bijster weinig informatie te vinden over het parsen / bewerken van grote bestanden in programmeertaal x

[ Voor 11% gewijzigd door Tim.k op 15-05-2018 11:58 ]

[Falcon]

Tim.k schreef op dinsdag 15 mei 2018 @ 11:57:
...Ik denk niet dat het een kwestie van "even" een script schrijven is.

Wellicht dat het wel zo simpel is, ik heb echter geen ervaring met het bewerken en / of parsen van dergelijk grote bestanden en hoe ik dit efficiënt wil doen..

Dit geeft precies aan wat je terug leest in jouw post. Doe meer met wat je hier aan geboden krijgt en acteer hierop i.p.v. er tegen in te gaan.

[mddd]

Even gekeken op https://steam.internet.byu.edu/. Het zijn maar een paar tabellen met een duidelijke structuur. Enkele tabellen (lijst van apps, lijst van achievements) zullen waarschijnlijk wel te overzien zijn qua grootte, en zijn de 'basis data' voor de langere lijsten. Dus importeer deze tabellen in hun geheel.

De koppel-tabellen en usage tabellen (usage info en links tussen users) zullen de grootste zijn. Daarvan zou je dus een subset kunnen nemen zoals anderen al voorstelden. Dus bijvoorbeeld alleen naar elke 10.000e user kijken. Je moet dan via de tabelstructuren kijken op welke velden je wilt selecteren. In de meeste tabellen denk ik het veld 'steamid' en eventueel 'steama' of 'steamb' als het gaat om links met andere users.

De manier om de selectie te doen is, zoals ook al aangegeven, waarschijnlijk dat je slim om moet gaan met regex'en en op die manier die juiste inserts eruit pikken. Ik heb de dataset niet gedownload, maar als er 1 insert per rij is dan is dat natuurlijk het makkelijkst. Maar anders is het ook best te doen.

Verklein op deze manier de dataset naar een handelbare grootte en test je pricipes van wat je wilt analyseren. Werkt dit, dan kun je altijd de boel nog eens runnen met een grotere set uit het totaal (bv elke 100e in plaats van elke 10.000e user).

Edit:
Wat @Hydra zegt dus.
Je moet de file verkleinen nog voordat je je iets gaat importeren. Lezen, regex matchen, de matches wegschrijven, dit kan allemaal behoorlijk snel.

[ Voor 10% gewijzigd door mddd op 15-05-2018 12:57 ]

[Tim.k]

Falcon schreef op dinsdag 15 mei 2018 @ 12:13:
[...]


Dit geeft precies aan wat je terug leest in jouw post. Doe meer met wat je hier aan geboden krijgt en acteer hierop i.p.v. er tegen in te gaan.

Dat is juist wat ik graag wil doen, ik vraag dan ook om talen / methodieken die ik zou kunnen gebruiken naast de gegeven theorie. Er is al verschillende keren aangegeven dat ik de dataset moet inkorten, dit wil ik graag doen, maar ik zie verschillende obstakels waar ik alleen met “knip de dataset wat kleiner” niet uit kom dus lichten ik die uit in mijn vorige post.

[Hydra]

Tim.k schreef op dinsdag 15 mei 2018 @ 11:57:
Dat is wel erg theoretisch en zeer makkelijk te bedenken. Uitvoeren is wat anders, hoe ga ik die al die ID's storen?

Da's helemaal niet wat ik zeg. Je hoeft niks op te slaan; je filtert gewoon een deel van de input naar een output veld. Afhankelijk van de modulo die je neemt filter je meer of minder data.

Je reactie over dat er weinig te vinden is geeft precies het probleem aan. Er is genoeg te vinden op google hoe je in welke taal dan ook een file regel voor regel inleest en de regels die voldoen aan een bepaald criterium weer wegschrijft. Als je meer informatie geeft over de opbouw van de file of files (ik ga geen 17GB downloaden, dat begrijp je hopelijk) krijg je ook betere antwoorden. Als je alleen maar klaagt en meldt dat je op google niks kan vinden hebben mensen ook weinig zin je te helpen. Effort in = effort out.

Tim.k schreef op dinsdag 15 mei 2018 @ 12:53:
Dat is juist wat ik graag wil doen, ik vraag dan ook om talen / methodieken die ik zou kunnen gebruiken naast de gegeven theorie.

Als je vragen hebt over het "hoe" dan moet je deze stellen. Dit is in elke taal te doen en compleet triviaal. En er zijn meerdere mensen die aangeven je willen te helpen. Maar we gaan niet zomaar je huiswerk voor je doen.

[ Voor 19% gewijzigd door Hydra op 15-05-2018 13:05 ]

[veltnet]

Het importbestand parsen is geen probleem. Je kunt er met fopen() en fread() doorheen lopen. Als je een kleine buffergrote neemt (bijvoorbeeld 1024 bytes) dan zal je geheugen zeker niet vollopen.

De id's kun je ook wegschrijven naar een file met fopen en fwrite.
Of je kunt de velden direct naar een elasticsearch instantie wegschrijven (dat gaat wat sneller dan mysql)

[nescafe]

pedorus schreef op maandag 14 mei 2018 @ 23:41:
Eerst het datamodel maken (file hierboven). Dan bijvoorbeeld greppen (zonder -v) op de INSERTs en sed - https://superuser.com/a/396557

Hydra schreef op dinsdag 15 mei 2018 @ 11:20:
Lees gewoon uit elke insert / row / whatever de steam ID. Doe een modulo test om bijvoorbeeld 1% over te houden. En schrijf die regels dan weg naar een nieuwe file.

Het probleem bij deze aanpak is dat de inserts zijn gecombineerd op dezelfde regel en daardoor een 'modulo test' op regelniveau niet werkbaar is.

SQL:

INSERT INTO `Achievement_Percentages` VALUES (50,'ach0',0.0910381),(50,'ach1',0.0883079),(50,'ach2',0.0884962),(50,'grp_ach0',0.0107325),(50,'grp_ach1',0.0106384),(60,'FinishedGame',0.061
7539),(60,'FoundEasterEgg',0.0616356),(60,'Invincible',0.00692069)

Bijzonderheden daargelaten (geen ,(..)-reeksen als waarden) moet je zo een eind kunnen komen:

Bash:

gunzip < steam.sql.gz \
  | perl -ne 'if (@m = $_ =~ /^(INSERT INTO `.+` VALUES) (\(.+?\))/) {
                print "$1 $2\n";
                print "@m[0] $_\n" for $_ =~ /,(\(.+?\))/g;
              }'

Deze regel herhaalt de prefix (INSERT INTO `Tabel` VALUES) voor iedere herhaling van ,(...). De $1$2 print de eerste waarde aangezien dat de enige value (...) is die niet voorafgegaan wordt door een komma.

SQL:

INSERT INTO `Achievement_Percentages` VALUES (50,'ach0',0.0910381)
INSERT INTO `Achievement_Percentages` VALUES (50,'ach1',0.0883079)
INSERT INTO `Achievement_Percentages` VALUES (50,'ach2',0.0884962)
INSERT INTO `Achievement_Percentages` VALUES (50,'grp_ach0',0.0107325)
INSERT INTO `Achievement_Percentages` VALUES (50,'grp_ach1',0.0106384)
INSERT INTO `Achievement_Percentages` VALUES (60,'FinishedGame',0.0617539)
INSERT INTO `Achievement_Percentages` VALUES (60,'FoundEasterEgg',0.0616356)

De aanpak van pedorus kun je combineren met bovenstaande perl-regel. Voor de missende foreign keys kun je INSERT IGNORE INTO gebruiken (find/replace met sed). Afhankelijk van wat er overblijft door de hierdoor overgeslagen records kun je dan de sample rate groter maken.

Door het pipen van de commando's zal het geheugengebruik beperkt blijven

[ Voor 1% gewijzigd door nescafe op 15-05-2018 14:33 . Reden: $1$2 => "$1 $2\n" ]

[Sakesushi]

Indien je nog wat zoekt.
Mag je mijn stof vretende dedicated server in Berlijn wel gebruiken.
Ding doet geen moer dus stuur maar een PM mocht je interesse hebben.

[DirkZzZ]

Even los van je vraag over het importeren van de database en puur kijkend naar het doel dat jij voor ogen hebt, de website die jij aanhaalt laat ook zien waar ze de data vandaan hebben getrokken en een groot deel komt direct van Steam middels hun api.

Is het voor de vraag die jij beantwoord wil zien misschien al genoeg door gewoon zelf een kleine set direct uit de api van Steam te halen? Op die manier heb je in ieder geval iets om op terug te vallen mocht het importeren niet lukken.

Eventueel aangevuld met een paar specifieke tabellen uit de set die je hebt gedownload die data bevat die niet uit de api afkomstig is?

Aanmelden kan hier; https://steamcommunity.com/dev?l=dutch

[laurens0619]

Kun je niet tabel voor tabel importere en halverwege de import killen? Dan heb je per tabel een gedeelte data

http://www.joycebabu.com/...-from-mysql-dumpfile.html

En tweak je mysql zoals foreign keys uitschakelen

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0

Overige tips:

Here are some things you can do to speed up restore, given that you have the backup file and can't change the type of file it is:

Disable foreign key checks: SET FOREIGN_KEY_CHECKS=0 (remember to re-enable afterwards). Disable unique checks too: SET UNIQUE_CHECKS=0
Make sure your key_buffer_size is set as large as possible if you use MyISAM tables. The default is 8MB, and the max is 4GB. I'd try 1GB.

These first tips come from a post by Baron Schwartz: http://lists.mysql.com/mysql/206866
Make sure your innodb_buffer_pool_size is set as large as possible if you use InnoDB tables. The default is 8MB, and the max is 4GB. I'd try 1GB.
Set innodb_flush_log_at_trx_commit = 2 during the restore if you use InnoDB tables.
@Mark B adds a good suggestion below to disable keys during a restore. This is how you do it:

ALTER TABLE <table-name> DISABLE KEYS;
...run your restore...
ALTER TABLE <table-name> ENABLE KEYS;

[ Voor 84% gewijzigd door laurens0619 op 15-05-2018 20:56 ]

[Tim.k]

@Hydra Op hoe je een file inleest kan je natuurlijk veel vinden. Efficiënt een groot file of een regex op veel data optimaliseren dan weer een stuk minder. Een teamgenoot heeft reeds een script geschreven, ik heb de code niet gezien, maar dat ging enorm traag. Wellicht slechte code maar van daar dat ik vraag om methodieken / talen waar ik zoiets het best in kan doen, niet of jullie mijn huiswerk voor mij kunnen maken. Ik verwacht echt niet een voorgekauwd script, een manier om regexes (of alternatief) efficiënter te laten lopen, dat is waar ik naar op zoek ben om dit te bewerk stellen. Wat @veltnet bijvoorbeeld aangeeft, dat zijn handige tips, met de buffer knoeien om optimale throughput te halen.

@nescafe Bedankt, dit kan ik wellicht combineren met het antwoord van @mddd en @pedorus

@Sakesushi Bedankt voor het aanbod, de VPS op TransIP is nog even aan het doorpruttelen (op de helft). Mocht dat op niets uitlopen dan zal ik een PM sturen.

@DirkZzZ Daar heb ik naar gekeken, de games die iemand heeft en vrienden zijn belangrijk voor ons onderzoek. Er is geen mogelijkheid om alle gebruikers op te halen van Steam en als ik dat van iemands team pak krijg ik niet echt een globaal beeld omdat vrienden van vrienden redelijk beperkt zullen blijven van Nederland > Europa. Daarnaast is het API call limit vrij beperkt.

@laurens0619 Niet eens aan gedacht maar ook daar blijf ik met het probleem zitten dat keys van tabellen afhankelijk van elkaar zijn helaas.

[laurens0619]

Tim.k schreef op dinsdag 15 mei 2018 @ 21:16:
@Hydra Op hoe je een file inleest kan je natuurlijk veel vinden. Efficiënt een groot file of een regex op veel data optimaliseren dan weer een stuk minder. Een teamgenoot heeft reeds een script geschreven, ik heb de code niet gezien, maar dat ging enorm traag. Wellicht slechte code maar van daar dat ik vraag om methodieken / talen waar ik zoiets het best in kan doen, niet of jullie mijn huiswerk voor mij kunnen maken. Ik verwacht echt niet een voorgekauwd script, een manier om regexes (of alternatief) efficiënter te laten lopen, dat is waar ik naar op zoek ben om dit te bewerk stellen. Wat @veltnet bijvoorbeeld aangeeft, dat zijn handige tips, met de buffer knoeien om optimale throughput te halen.

@nescafe Bedankt, dit kan ik wellicht combineren met het antwoord van @mddd en @pedorus

@Sakesushi Bedankt voor het aanbod, de VPS op TransIP is nog even aan het doorpruttelen (op de helft). Mocht dat op niets uitlopen dan zal ik een PM sturen.

@DirkZzZ Daar heb ik naar gekeken, de games die iemand heeft en vrienden zijn belangrijk voor ons onderzoek. Er is geen mogelijkheid om alle gebruikers op te halen van Steam en als ik dat van iemands team pak krijg ik niet echt een globaal beeld omdat vrienden van vrienden redelijk beperkt zullen blijven van Nederland > Europa. Daarnaast is het API call limit vrij beperkt.

@laurens0619 Niet eens aan gedacht maar ook daar blijf ik met het probleem zitten dat keys van tabellen afhankelijk van elkaar zijn helaas.

Is er geen onderscheid tussen een “data” tabel en een “referentietabel”? Zo ja, importeer de referentietabellen volledig en de data gedeeltelijk

[Frikikip]

Het is een tijdje geleden dat ik met MySQL heb gewerkt, maar wat ik meestal deed met het restoren van grote datasets was de table creëren zonder primary key, foreign keys en indexes.

Vervolgens insert je alle data en restore je de keys en indexes met een alter table.

Probleem nu is dat alle indexes bijgewerkt worden per insert en dat is nogal een intensief proces wat alleen maar langer duurt met een grotere dataset.

Ik heb echter nooit 180 GB hoeven te importeren Maar wellicht is het een poging waard.

[Frikikip]

En nog een tip, zorg dat je dump en database op fysiek gescheiden disks staan. Je database bij voorkeur op een SSD.

[ThomasG]

Frikikip schreef op dinsdag 15 mei 2018 @ 21:54:
En nog een tip, zorg dat je dump en database op fysiek gescheiden disks staan. Je database bij voorkeur op een SSD.

Een andere tip is om niet de gegzipte dump niet uit te pakken, maar te streamen vanaf een named pipe. Scheelt aanzienlijk aan schijfruimte, en een hoop I/O.

[burne]

Tim.k schreef op maandag 14 mei 2018 @ 22:09:
De database is ongeveer 180GB groot en al een week aan het importeren, de indexes maken duurt tergend lang op mijn computer (i5 7400, 16GB RAM, 4TB 7200RPM schijf). De schijf is de bottleneck en is continu 100% belast.

De vraag is dus, hoe ga ik deze database importeren? Of nog beter, eenmaal geïmporteerd, is er überhaupt een query op te draaien?

Ik dump, kopieer en importeer ieder weekend een database van een goeie 575 gigabyte. Het idee is dat functioneel beheer een schaduw-omgeving heeft waarin ze problemen uit productie kunnen onderzoeken en naspelen. Het importeren duurt een uur of 13. 'Mijn' data is bizar complex. 400 tabellen, sommigen met 10 miljoen records, met hele complexe foreign key relaties en constraints.

Het voornaamste verschil is, denk ik, dat ik 8 disks heb, die in raid 10 zitten. (dus: mirrors en stripes) 8 keer zoveel IOPS, en vooral: met een beetje mazzel vier reads en vier writes tegelijk.

Voor je huidig probleem zou ik gaan voor ofwel een dikke VM bij transip bij elkaar klikken of een goedkope 500G SSD kopen en je /var/lib/mysql/databasenaam op die SSD zetten. Als je I/O (lezen van de dump en schrijven naar de database) over twee aparte devices kunt verdelen scheelt dat al enorm.

Indices zijn uit te zetten (ALTER TABLE `table_name` DISABLE KEYS;) maar dat is beperkt bruikbaar. Een dump begint meestal met een 'DROP TABLE IF EXISTS `table_name`' en dan is je DISABLE KEYS ook weer weg.

[Thralas]

Frikikip schreef op dinsdag 15 mei 2018 @ 21:43:
Het is een tijdje geleden dat ik met MySQL heb gewerkt, maar wat ik meestal deed met het restoren van grote datasets was de table creëren zonder primary key, foreign keys en indexes.

Ik ken alleen Postgres, daar is zo'n import met keys onwerkbaar. Even de MySQL manual erbij pakken:

quote: https://dev.mysql.com/doc.../insert-optimization.html

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

En deze is ook absoluut relevant voor grote datasets:

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements. See Section 13.2.7, “LOAD DATA INFILE Syntax”.

Helaas heb je daar weinig aan in dit geval.

burne schreef op dinsdag 15 mei 2018 @ 22:18:
Het voornaamste verschil is, denk ik, dat ik 8 disks heb, die in raid 10 zitten. (dus: mirrors en stripes) 8 keer zoveel IOPS, en vooral: met een beetje mazzel vier reads en vier writes tegelijk.

Ik denk primair de manier waarop je data importeert en secundair het gebruikte DBMS. Oracle of MSSQL zijn vast een stuk beter ingericht op databases van formaat.

Indices zijn uit te zetten (ALTER TABLE `table_name` DISABLE KEYS;) maar dat is beperkt bruikbaar. Een dump begint meestal met een 'DROP TABLE IF EXISTS `table_name`' en dan is je DISABLE KEYS ook weer weg.

Ik zou deze SQL dump sowieso processen, al was het maar om hem op te splitsen. Zo'n databestand (160 GB uncompressed) als single file MyISAM dump publiceren is bijna crimineel te noemen.

Echter, DISABLE KEYS zit al om de data gewrapped:

code:

LOCK TABLES `Games_Publishers_Old` WRITE;
/*!40000 ALTER TABLE `Games_Publishers_Old` DISABLE KEYS */;
...
/*!40000 ALTER TABLE `Games_Publishers_Old` ENABLE KEYS */;
UNLOCK TABLES;

Misschien is het nuttig om de keys gewoon geheel te droppen en ze enkel toe te voegen waar nodig.

En vooral de MySQL-handleiding mbt. performance tuning goed lezen als je echt geen SSD (why?!) kunt vinden waar dit op past. Seeks zijn immers vrij duur op spinnend roest.

[alwinuzz]

Tim.k schreef op dinsdag 15 mei 2018 @ 21:16:
@DirkZzZ Daar heb ik naar gekeken, de games die iemand heeft en vrienden zijn belangrijk voor ons onderzoek. Er is geen mogelijkheid om alle gebruikers op te halen van Steam en als ik dat van iemands team pak krijg ik niet echt een globaal beeld omdat vrienden van vrienden redelijk beperkt zullen blijven van Nederland > Europa. Daarnaast is het API call limit vrij beperkt.

Als we nou een stap terug nemen. Je vraagt hoe de dataset is te verkleinen. Is dat niet afhankelijk van welke data je nodig hebt voor je onderzoek?
In de TS zeg je dat de onderzoeksvraag en documentatie al hebt. Wat is je onderzoeksvraag? Welke data van steam heb je nodig om je onderzoeksvraag te beantwoorden? en in welke unit is dat te hakken?

Bijv voor een x aantal games wil je iets onderzoeken. Waarbij x eerst alle games was, maar dat gaat m niet worden. Dus nu is x=100 of zo.
Dus je hebt x rijen uit de 'game-tabel' nodig, samen met alle bijbehorende info van de game, incl koppelingen zoals users die dat spel hebben gespeeld. Maar alle overige users kan je weggooien. En dan ook de achievements voor die overige users, etc.

Nádat je weet welke data niet nodig is, kan je vervolgens kijken hoe je die data kan 'overslaan'.

[pedorus]

Wat ga je er mee doen is wel een belangrijke vraag inderdaad

burne schreef op dinsdag 15 mei 2018 @ 22:18:
[...]
Het voornaamste verschil is, denk ik, dat ik 8 disks heb, die in raid 10 zitten. (dus: mirrors en stripes) 8 keer zoveel IOPS, en vooral: met een beetje mazzel vier reads en vier writes tegelijk.

Het voornaamste verschil is denk ik de juiste tool voor de job en sequentieel kopiëren. Zo'n backup als deze doen we ook elke dag, en die preparen we om te restoren, maar dan niet met mysqldump.. Nu heb je een dump in willekeurige onbruikbare volgorde, en dat is erg lastig restoren.

Indices zijn uit te zetten (ALTER TABLE `table_name` DISABLE KEYS;) maar dat is beperkt bruikbaar. Een dump begint meestal met een 'DROP TABLE IF EXISTS `table_name`' en dan is je DISABLE KEYS ook weer weg.

Mysqldump heeft dit al gedaan, nut is enkel beperkt denk ik omdat de datavolgorde niet op primary key is.

Tim.k schreef op dinsdag 15 mei 2018 @ 00:32:
Is MyIsam niet juist gunstig voor ons, ik weet niet of het een fabeltje is maar MyIsam is dacht ik sneller met selects dan InnoDB?

Geen idee, volgens mij is MyIsam deprecated en wordt niet meer ontwikkeld..

Mijn kennis van grep en sed is nihil maar door middel van de manpages begrijp ik alleen niet waarom de grep er tussen zit. Deze pakt zover ik begrijp alles behalve lijntjes waar KEY in zit, hierdoor moet de sed nog steeds door alle inserts zoeken waar dat niet nodig is of interpreteer ik de grep (-v optie) niet goed?

Het idee is met grep de KEYs voorkomen (werkte enkel niet goed vanwege probleem met komma's). sed doorzoekt inderdaad ook gewoon alle regels enkel kan dat zeer efficiënt. In totaal ligt het probleem toch bij disk en niet bij cpu.

Dan kan ik ruw dingen er uit knippen maar zal veel data niet meer aan elkaar geknoopt kunnen worden door missende identifiers in andere tabellen.

Hoe je dit precies wil doen hangt er van af hoe je het wil gebruiken. De niet al te grote tabellen zijn wel te importeren, en dan lukken vele joins al:

time gunzip <steam.sql.gz |  grep -v '^  KEY'|sed -E 's/^(  PRIMARY KEY.*),$/\1/'|sed 's/^) ENGINE=MyISAM /) ENGINE=InnoDB /'|grep -v -E '^INSERT INTO `(Friends|Games_[12]|Games_Daily|Player_Summar)'|mysql steam

Draaitijd <30 min op SATA disk(2x3G raid 1)/Ubuntu 14.04/Mysql 5.6 server die ook nog wat andere dingen deed met settings als volgt:

[mysqld]
innodb_doublewrite = Off
innodb_buffer_pool_size=10000000000
innodb_flush_log_at_trx_commit=0

Dan kom je op een punt waarbij je bijvoorbeeld het datamodel wat kan tweaken zodat het importeren hopelijk iets beter gaat en je makkelijk 1% kan querien:

SQL:

alter table Friends partition by hash(steamid_a div 10) partitions 100;
alter table Games_1 partition by hash(steamid div 10) partitions 100;
alter table Games_2 partition by hash(steamid div 10) partitions 100;
alter table Games_Daily partition by hash(steamid div 10) partitions 100;
alter table Player_Summaries partition by hash(steamid div 10) partitions 100;
alter table Groups partition by hash(steamid div 10) partitions 100;

Ik kreeg het idee dat er opvallend veel 0en in de IDs zitten op het einde, vandaar de div 10. En dan de laatste stap (dit is gewoon een 100% import, maar je zou hier wat kunnen gaan tweaken, of bepaalde tabellen weg kunnen laten):

time gunzip <steam.sql.gz |grep -E '^INSERT INTO `(Friends|Games_[12]|Games_Daily|Player_Summar|Groups)' | mysql steam

(Ik vind het wel een mooie dataset om wat mee te testen zonder dat er productiegegevens in staan, mij benieuwen of dit toch kan op SATA. NB: https://www.percona.com/b.../dont-spin-data-use-ssds/ )

[burne]

pedorus schreef op woensdag 16 mei 2018 @ 00:00:
Het voornaamste verschil is denk ik de juiste tool voor de job en sequentieel kopiëren. Zo'n backup als deze doen we ook elke dag, en die preparen we om te restoren, maar dan niet met mysqldump.. Nu heb je een dump in willekeurige onbruikbare volgorde, en dat is erg lastig restoren.

Volgens mij vertelde ik TS dat ik meer dan drie keer zoveel data wekelijks kopieer en inlees in in ieder geval minder dan de helft van de tijd die hij er mee bezig was toen 'ie om hulp vroeg.

Dan kun je mij gaan vertellen dat dat niet klopt en dat ik het fout doe, maar ik doe dat al een jaar of zeven zonder problemen, dus ik betwijfel of dat fout of zelfs maar 'lastig' is.

Ik kan je uit jarenlange ervaring vertellen dat mysqldump begint met A en eindigt met Z, en tabellen dumpt en restored op volgorde van hun primary key. En dus niet in een 'willekeurige onbruikbare volgorde'.

Wat probeer je nou toe te voegen met je reactie? Ik begrijp niet waarom je zegt wat je zegt. Het is fout, het klopt niet en het is onjuist.

[pedorus]

burne schreef op woensdag 16 mei 2018 @ 00:19:
Dan kun je mij gaan vertellen dat dat niet klopt en dat ik het fout doe, maar ik doe dat al een jaar of zeven zonder problemen, dus ik betwijfel of dat fout of zelfs maar 'lastig' is.

Als het goed werkt zou ik niet weten waarom je het fout doet? Ik denk dat je me verkeerd hebt begrepen.

Ik kan je uit jarenlange ervaring vertellen dat mysqldump begint met A en eindigt met Z, en tabellen dumpt en restored op volgorde van hun primary key. En dus niet in een 'willekeurige onbruikbare volgorde'.

Zal iets van MyIsam zijn. --order-by-primary is niet standaard en zeker niet gebruikt bij de dump in kwestie omdat de data niet op volgorde staat.

Wat probeer je nou toe te voegen met je reactie? Ik begrijp niet waarom je zegt wat je zegt.

Ik probeer slechts te verklaren waarom deze dump meer dan een week duurt om te importeren, terwijl het jou veel minder dan een dag kost. Kennelijk backup en restore je die 575 G daadwerkelijk met mysqldump? Meestal is boven de 200G iets als innobackupex of rsync nodig om dat snel genoeg te doen, maar het hangt nogal af van de exacte setup natuurlijk. Die tools komen pas in zicht als mysqldump niet goed werkt zoals hier bij een restore die meer dan een week duurt.

[Hydra]

Tim.k schreef op dinsdag 15 mei 2018 @ 21:16:
@Hydra Op hoe je een file inleest kan je natuurlijk veel vinden. Efficiënt een groot file of een regex op veel data optimaliseren dan weer een stuk minder. Een teamgenoot heeft reeds een script geschreven, ik heb de code niet gezien, maar dat ging enorm traag. Wellicht slechte code maar van daar dat ik vraag om methodieken / talen waar ik zoiets het best in kan doen, niet of jullie mijn huiswerk voor mij kunnen maken. Ik verwacht echt niet een voorgekauwd script, een manier om regexes (of alternatief) efficiënter te laten lopen, dat is waar ik naar op zoek ben om dit te bewerk stellen. Wat @veltnet bijvoorbeeld aangeeft, dat zijn handige tips, met de buffer knoeien om optimale throughput te halen.

Ik heb letterlijk aangeboden je er mee te helpen. Maar joh; laat maar. Als je serieus denkt dat vogelen met buffers een betere suggestie is dan wat ik je gegeven heb; leef je lekker uit.

DirkZzZ schreef op dinsdag 15 mei 2018 @ 20:10:
Is het voor de vraag die jij beantwoord wil zien misschien al genoeg door gewoon zelf een kleine set direct uit de api van Steam te halen? Op die manier heb je in ieder geval iets om op terug te vallen mocht het importeren niet lukken.

Simpelweg de data filteren om een kleinere set te maken is al een grote uitdaging voor ze. Een API implementeren is wel even een stukje complexer.

Komt nog eens bij dat de API rate limited is. Dat gaan ze voorlopig niet afkrijgen.

[laurens0619]

Hydra schreef op woensdag 16 mei 2018 @ 08:19:
[...]


Ik heb letterlijk aangeboden je er mee te helpen. Maar joh; laat maar. Als je serieus denkt dat vogelen met buffers een betere suggestie is dan wat ik je gegeven heb; leef je lekker uit.


[...]


Simpelweg de data filteren om een kleinere set te maken is al een grote uitdaging voor ze. Een API implementeren is wel even een stukje complexer.

Komt nog eens bij dat de API rate limited is. Dat gaan ze voorlopig niet afkrijgen.

Als je een relationele database hebt, hoe zie je het verkleinen dan voor je? Je kunt niet per tabel modulo x pakken want dan kunnen de referenties stuk gaan.

Imho moet het prima lukleb deze db te importere met mysql tuning/indexes etc. Lijkt mij juist een mooie uitdaging

[Hydra]

laurens0619 schreef op woensdag 16 mei 2018 @ 11:25:
Als je een relationele database hebt, hoe zie je het verkleinen dan voor je? Je kunt niet per tabel modulo x pakken want dan kunnen de referenties stuk gaan.

Zoals ik al aangaf; modulo op de user ID. Niet op het row nummer ofzo.

[Tim.k]

Bedankt voor de reacties, erg leerzaam. @pedorus, @Thralas top reacties!

De database is inmiddels geïmporteerd op de TransIP VPS, nog geen kans gehad om er queries op uit te voeren. Ik ga de database vanmiddag even uitkammen en her en der wat tuning toepassen met de gegeven reacties en kijken of de database werkbaar is. Anders ga ik eens het dump file bewerken met alle gegeven reacties en nogmaals importeren.

Edit:
De database is inmiddels gereduceerd naar 20GB en queries zijn prima uit te voeren. Tevens een go gekregen voor het project. Nogmaals bedankt iedereen.

[ Voor 16% gewijzigd door Tim.k op 18-05-2018 21:41 ]

[curkey]

Ik weet dat dit topic een beetje oud is inmiddels, maar is het met dit soort datasets niet de moeite waard te kijken naar column-based engines?

Die zijn vele malen sneller voor analyitische taken, aggregate functies gaan van minuten terug naar seconden, met zulke datasets.

https://www.quora.com/Are...ar-column-store-databases

[Tim.k]

curkey schreef op donderdag 5 juli 2018 @ 14:23:
Ik weet dat dit topic een beetje oud is inmiddels, maar is het met dit soort datasets niet de moeite waard te kijken naar column-based engines?

Die zijn vele malen sneller voor analyitische taken, aggregate functies gaan van minuten terug naar seconden, met zulke datasets.

https://www.quora.com/Are...ar-column-store-databases

Dat was zeker de moeite waard geweest en ik kende MemSQL al die in dat Quora topic genoemd wordt en hier heb ik ook even naar gekeken. Het knelpunt was echter het importeren en uitselecteren van data. Het importeren was het grootste probleem wat MemSQL niet had opgelost. Het uitselecteren was met MemSQL een stuk makkelijker geweest maar, de data om zetten had helaas weer enorm veel tijd gekost.

Bovendien door gebrek aan kennis (het was immers een "zoek het maar uit" research achtig vak zonder enige kennis van het onderwerp) moesten we settelen met kant en klare tools zoals RapidMiner omdat de learning curve te groot was voor de kleine tijd die we beschikbaar hadden. MemSQL + Spark had mij een hele interessante combinatie geleken.

[curkey]

Het is idd jammer dat het zo kort dag was om dingen te regelen, want een SSD had je enorm kunnen helpen met de import inderdaad. Dat in combinatie met bv een free academic use instance van Exasol https://www.exasol.com/en/download/ was best leuk geweest denk ik. Die gaat tot 1TB raw data.
Hopelijk heb je eens meer tijd beschikbaar om naar dit soort dingen te kijken, want dat is best een leuke area om in te grasduinen.

[martindeman]

Tim.k schreef op woensdag 16 mei 2018 @ 12:13:
Bedankt voor de reacties, erg leerzaam. @pedorus, @Thralas top reacties!

De database is inmiddels geïmporteerd op de TransIP VPS, nog geen kans gehad om er queries op uit te voeren. Ik ga de database vanmiddag even uitkammen en her en der wat tuning toepassen met de gegeven reacties en kijken of de database werkbaar is. Anders ga ik eens het dump file bewerken met alle gegeven reacties en nogmaals importeren.

Edit:
De database is inmiddels gereduceerd naar 20GB en queries zijn prima uit te voeren. Tevens een go gekregen voor het project. Nogmaals bedankt iedereen.

Tim hoe verloopt je project inmiddels? Ik heb je een PM gestuurd, misschien kan ik nog wat regelen voor je

[RobIII]

martindeman schreef op vrijdag 6 juli 2018 @ 15:10:
Tim hoe verloopt je project inmiddels? Ik heb je een PM gestuurd, misschien kan ik nog wat regelen voor je

Euh, ja, en dat is nou uitdrukkelijk niet de bedoeling. Je bedoeling is vast goed maar dit valt gewoon onder spam danwel werving en mochten beide niet het geval zijn dan valt 't wel onder "mail me" is ongewenst. Gelieve dit voortaan gewoon achterwege te laten.