[VB6/MySQL] grote db importeren gaat traag

[Verwijderd]

Hallo allemaal,

Voor testdoeleinden wil ik een "grote" database(+/- 1.9 miljoen records) importeren via een programma wat ik zelf geschreven heb in vb6. De sql file is +/- 200mb. Als ik m'n programma de sql file laat verwerken zonder dat ik de insert statements echt doorstuur naar de server heeft hij in 50 seconden alle 1.9mil records verwerkt ( dus alle query's stuk voor stuk uit de sql gesplitstmaar niet verstuurd naar de server ). Dit is een prima acceptabele snelheid. Maar wanneer ik ze dus werkelijk op de database probeer uit te voeren gaat dit "enorm" traag. Ik stuur elke query dmv de mysql ODBC driver en het execute commando naar de server.

Ik dacht eerst dat het aan mijn programma lag dus daarom heb ik het geprobeerd om het zonder het werkelijk door te sturen te verwerken en dat ging behoorlijk snel. Dus daarom heb ik als test verschillende methodes geprobeerd om het te importeren, dus via de mysql prompt de sql file in te laden, dit werkt wel maar gaat ook enorm traag, bigdump geprobeerd maar die houd er na enkele 1000-en statements mee op ook al staat de sql file lokaal zodat je niet met post limit zit, navicat werkt wel en gaat al wel sneller(1800 queries per minuut) als de rest maar is nogsteeds niet zoals ik het verwacht.

In mijn programma word na elke gesplitste query de query verstuurt omdat de odbc 3.51 niet multiple query's ondersteund. Schijnbaar kan de laatste versie van de odbc driver ( v5.nogwat ) dit wel maar deze is nog in beta versie dus ik weet niet of het verstandig is om deze nu al te gebruiken.

Nogwel een grappige toevoeging is dat wanneer ik een CSV file waar precies hetzelfde instaat als in de sql file via navicat erheen probeer te sturen hij ineens wel 90.000 records per minuut kan verzenden naar de server terwijl een sql(kant en klare commando's) file via navicat in vergelijking enorm traag is.

Dus heeft iemand dus nog een idee waarom het zo traag gaat en wat ik er aan kan doen? Ligt het aan de manier van versturen ( enkele query per keer ) of ligt het aan de driver(ODBC 3.51) die gebruikt word om het te versturen of misschien heel iets anders?

[N3oC]

Wordt na elk sql commando de connectie weer op nieuw opgebouwd, dit kan een hoop tijd kosten ipv 1 verbinding die alles insert!

[Verwijderd]

de connectie word niet opnieuw opgebouwd, maar ik heb denk ik al wel een oplossing( moet hem nu nog inbouwen), dit dmv niet iedere keer weer een nieuwe query te executen maar door 1 insert query te gebruiken en meerdere rijen met gegevens mee te nemen in de query zo haalde ik 10K records in +/-0.2 sec. Als het ingebouwd is en het werkt naar behoren laat ik het nog even weten.

[Gomez12]

Ach tja, gewoon grotere query's bouwen. Je kan voor zover ik weet best 1000 inserts achter elkaaar concatten ( zolang je ze maar scheid met een ; ). Dan begin je een transactie voor je query, beeindig je transactie na je query en als je query failed dan roolback je hem weer.

Ben je alleen nog maar afhankelijk van de max string lengte van VB ( en de mysql-driver ).
Nog enkele tips als je dit soort acties wilt doen.
Gooi voordat je begint alle indexen ervanaf,
lock je tables waarop je gaat inserten,
als het eenmalig / periodiek is zorg er dan voor dat mysql al zijn buffers geleegd heeft en desnoods met andere instellingen opgestart is.
Zorg ervoor dat mysql genoeg geheugen vrij heeft ( is niet leuk om 1 bestand van 3 Gb in te moeten lezen op een mysql machine met 4Gb waarbij mysql gaat swappen )
Als je grotere inserts maakt, houd dan ook even een pause tussen de inserts zodat mysql eerst alles netjes kan verwerken ipv dat je de volgende opdracht er gelijk achteraan knalt en daarna gelijk de volgende. ( hoeft maar een paar ms te zijn, maar werkt wel een stuk beter imho )
Insert zoveel mogelijk data zonder berekeningen in mysql ( oftewel eerst mass-inserten, mass updaten kan daarna stap 2 zijn en daarna als stap 3 weer je indexen opbouwen )

[Verwijderd]

Nou, ik heb het zelf nog niet eens geprobeerd maar schijnbaar ondersteund die ODBC driver geen meerdere queries gesplitst dmv ; Dat las ik namelijk ergens.
Ik doe het nu via hetvolgende statement, werkt prima opzich ( met deze haalde ik 10K records in 1 lange query )

code:

INSERT INTO x (a,b)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

De database die ik nu gebruik is gewoon een uitdaging voor om met een zelfbouw programma supersnel data te kunnen importeren.

Van de week moet ik nogwel een forum van +/- 80 a 90mb importeren dus daarom test ik nu met een database vol onzin. Locken hoeft toch alleen wanneer je wilt dat niemand anders acties op de database gaat uitvoeren terwijl jij bezig bent? Dat is sowieso niet aan de orde omdat de machine van het net los is gekoppeld. Maar wat bedoel je precies nog met het legen van de buffer? Kan vreemd overkomen misschien maar als de database niks aan het doen is dan is z'n buffer toch ook leeg?

[user109731]

Verwijderd schreef op maandag 11 juni 2007 @ 22:04:
Locken hoeft toch alleen wanneer je wilt dat niemand anders acties op de database gaat uitvoeren terwijl jij bezig bent? Dat is sowieso niet aan de orde omdat de machine van het net los is gekoppeld.

Het is sneller omdat MySQL anders bepaalde caches flusht na elke query. Door de table te locken word dit slechts na de unlock gedaan

Maar wat bedoel je precies nog met het legen van de buffer? Kan vreemd overkomen misschien maar als de database niks aan het doen is dan is z'n buffer toch ook leeg?

Denk meer aan buffer als in: cache. De database kan bijvoorbeeld een veelgebruikte tabel in zn buffer hebben staan

[Verwijderd]

Dat verklaart het een en ander ja. Moet ik trouwens de tables stuk voor stuk locken of kan je de hele db in 1x zo locken dmv: flush tables with read lock of heeft dat niet hetzelfde effect?