PHP mysql, wat is sneller?

[Guillome]

Goedemiddag,

Wat is sneller? Ik moet een database elke nacht up to date houden adhv XML feeds, die soms 100 MB per stuk kunnen zijn.

Wat is dan sneller? Elke nacht de DB legen met DELETE FROM blaat? En dan de XML doorlopen en alles INSERTen.
Of de XML doorlopen en elke rij SELECT doen om te zien of hij al bestaat, zo niet, INSERT, zo wel, skip?

Kon hier niet echt iets over vinden.

[Gonadan]

Geen idee, ik denk TRUCATE icm met INSERT.

TRUNCATE maakt de tabel in een keer leeg, in plaats van alle regels te verwijderen.
Dus dan hoef je alleen nog maar in te schieten.

Tip: zet je indices uit en indexeer je tabellen pas als je alles erin hebt geschoten.

Edit:
Je zegt SELECT en dan INSERT, daarvoor heb je:

MySQL:

INSERT INTO <tabel> SET <vars>
ON DUPLICATE KEY UPDATE <vars>;

[ Voor 23% gewijzigd door Gonadan op 24-04-2008 09:46 . Reden: foutje, en spuit11 blijkt nu :+ ]

[RAJH]

Maak een testcase en probeer het uit . In plaats van een select te doen om te kijken of een row bestaat kun je ook gebruiken maken van on duplicate key.

[whoami]

wat denk je zelf ?

Het een kan je misschien -ik weet niet in hoeverre MySQL kan 'praten' met xml (cfr OPENXML in MSSQL) - een setbased operatie zijn, het andere niet ....

Je kan ook eens gek doen, en het uittesten / benchmarken ...

[Gerco]

Je kan ook REPLACE INTO gebruiken als een alternatief voor DELETE, INSERT. Toch is het waarschijnlijk inderdaad sneller als je eerst alles weggooit en dan weer opnieuw inleest.

Het is misschien ook de moeite waard om die Xml om te zetten naar een CSV bestand en dan met LOAD DATA INFILE in 1 keer in te lezen. Scheelt weer een paar honderd duizend queries.

[dik_voormekaar]

Wat vaak ook sneller werkt bij het importeren van grotere hoeveelheden data is eerst de indexen uitschakelen/verwijderen en na het importeren weer inschakelen/aanmaken.

[Verwijderd]

Als je een paar duizen rijen moet importeren is LOAD DATA INFILE de beste oplossing, ik heb er onlangs nog een snelheidswinst van meer dan 250% mee gehaald ten opzichte van gegroupte INSERT queries.

Dus mij lijkt TRUNCATEN, XML naar CSV omzetten en dan LOAD DATA INFILE de meest performante oplossing

[.oisyn]

Zoals altijd: meten is weten

[SchizoDuckie]

developers die applicaties produceren die 100mb aan xml uitspugen moeten eigenlijk publiekelijk aan de schandpaal genageld worden Kijk voor je eigen safety even op memory efficient xml parsers die niet het hele 100mb aan xml in 1x in je server memory proberen te dumpen maar stukje voor stukje inlezen. Ik denk dat database efficientie de minste van je te verwachten problemen is

100 mb aan xml wát staat daar in godesnaam in?

[ Voor 14% gewijzigd door SchizoDuckie op 24-04-2008 20:54 ]

[ValHallASW]

100MB aan xml is niets, en zelfs een getal waarbij het niet een zo bijster veel uitmaakt of je een hele boom bouwt of dat je een SAX-parser gebruikt... een goede boom zal niet veel meer dan die 100MB aan geheugen trekken.

En grote xml files? enwiki heeft aan page history 2TB... aan bz2'ed xml, dus da's 40TB plain text. Ok, de 2TB-dump is niet te downloaden, maar de 6.4GB-dump eronder (die alleen de huidige tekst bevat) is ook, ehm, groot.

(nlwiki is 'slechts' 6GB bz2'ed... nog een hoop )

[SchizoDuckie]

ValHallASW schreef op donderdag 24 april 2008 @ 21:35:
100MB aan xml is niets, en zelfs een getal waarbij het niet een zo bijster veel uitmaakt of je een hele boom bouwt of dat je een SAX-parser gebruikt... een goede boom zal niet veel meer dan die 100MB aan geheugen trekken.

En grote xml files? enwiki heeft aan page history 2TB... aan bz2'ed xml, dus da's 40TB plain text. Ok, de 2TB-dump is niet te downloaden, maar de 6.4GB-dump eronder (die alleen de huidige tekst bevat) is ook, ehm, groot.

(nlwiki is 'slechts' 6GB bz2'ed... nog een hoop )

Da's leuk en aardig allemaal als je een dedicated webserer tot je beschikking hebt, maar ik blijf van mening dat XML noooit bedoeld is om 100mb groot te worden (weet je hoeveel a4'tjes aan tekst dat zijn? en de gemiddelde PHP installatie krijgt normaal gesproken 8MB geheugen toegewezen op een virtual host. Knappe jongen die daar 100mb data in in weet te lezen.

[eamelink]

Waarom verwacht je dat iemand die dagelijks XML files van 100 MB krijgt iets aan het doen is op een virtual hostingaccountje?

[Verwijderd]

En dan nog, wanneer de XML redelijk plat is (en XML representaties van een tabel zijn dat) kun je met een SAX parser prima 100MB verwerken met 8MB geheugenruimte.

Bij een DOM parser moet je eerst de hele XML laden voordat 'ie 't kan verwerken, maar bij SAX kun je node voor node parsen en verwerken terwijl de stream binnenkomt. Klein nadeeltje is wel dat je misschien al een aantal flink records in je database gezet hebt en dan blijkt dat de XML niet valid is, maar dat is met transacties op te lossen.

[Varienaja]

Ik zou doen: verzamel de sleutels uit de XML, verzamel de sleutels uit je DB. Deze twee lijstjes ga je beide sorteren. Dan loop je ze beide van boven naar beneden door, en vergelijkt de verschillen. Soms ontbreekt een getalletje in de lijst van je DB --> Record invoegen. Soms ontbreekt een getalletje in de lijst van de XML --> record uit de DB wegmikken.

En klaar ben je al, met de minst mogelijke (nouja vrij weinig in elk geval) I/O.

[Verwijderd]

Variejana, je houdt dan geen rekening met het feit dat bestaande records misschien ook gewijzigd kunnen zijn.
Maar met 'record wegmikken' heb je wel een punt, daar hebben de REPLACE INTO mensen hierboven geen rekening mee gehouden.

Ik zou in dit geval gaan voor een truncate en daarna alle records opnieuw inserten, en dan gewoon door zelf de XML data te parsen. LOAD DATA INFILE is opzich wel sneller, maar als je feed XML is moet je die toch nog zelf omzetten naar CSV. En of je zelf alle inserts doet of die inserts overlaat aan LOAD DATA INFILE? Geen idee of 't de moeite waard is van die extra conversieslag, maar zoals .oisyn al ze, meten is weten...

[.oisyn]

Verwijderd schreef op donderdag 24 april 2008 @ 22:34:
En dan nog, wanneer de XML redelijk plat is (en XML representaties van een tabel zijn dat) kun je met een SAX parser prima 100MB verwerken met 8MB geheugenruimte.

Bij een DOM parser moet je eerst de hele XML laden voordat 'ie 't kan verwerken, maar bij SAX kun je node voor node parsen en verwerken terwijl de stream binnenkomt. Klein nadeeltje is wel dat je misschien al een aantal flink records in je database gezet hebt en dan blijkt dat de XML niet valid is, maar dat is met transacties op te lossen.

Transacties gebruiken om te bailen als de XML niet valide lijkt? Dat lijkt me een beetje overkill. Valideer 'm dan eerst, kun je er daarna zeker van zijn dat je data volledig in de db te zetten is. Dan hog je de db server ook niet meteen.

[Verwijderd]

Kan je niet de nieuwe en de oude xml "diff"en zodat je alleen een verschil xml overhoud en die inspoelt?

[Verwijderd]

.oisyn schreef op donderdag 24 april 2008 @ 23:18:
Transacties gebruiken om te bailen als de XML niet valide lijkt? Dat lijkt me een beetje overkill. Valideer 'm dan eerst, kun je er daarna zeker van zijn dat je data volledig in de db te zetten is. Dan hog je de db server ook niet meteen.

.oisyn, met een SAX parser kun je de XML niet van tevoren valideren. Bij het verwerken van de nodes heb je 'm namelijk nog niet helemaal binnen. Dat is aan de ene kant het voordeel van SAX (je kunt direct de binnenkomende gegevens verwerken), en aan de andere kant z'n nadeel (als er wat mis is heb je al dingen verwerkt).
En ja, wanneer er dan iets mis gaat is een transactie de beste manier om je DB niet om zeep te helpen.

[.oisyn]

Je kunt 'm wél van tevoren valideren, en daarna nóg een keer over de file heen te gaan om 'm in te voeren. En als je de XML van een andere source dan van disk (zoals via het netwerk) binnenkrijgt, dan kun je 'm eerst opslaan (wat dan sowieso wel handig is)

[ Voor 42% gewijzigd door .oisyn op 25-04-2008 00:30 ]

[Niemand_Anders]

Je kunt de XML via SAX toch omzetten naar een CSV bestand? En mocht op een gegeven moment blijken dat de XML niet geldig is, dan verwijder je gewoon het import csv bestand.

Dergelijke bestanden inlezen als DOM tree is niet verstandig, want nu is het bestand misschien 100MB, maar over 18 maanden kan dat natuurlijk gemakkelijk zijn gegroeid naar 250MB.