MySQL queries optimaliseren.

[Onbekend]

Ik heb een MySQL database (versie 5.1.53), geïnstalleerd met Wamp.
Het draait op een pc van 3 maanden oud, met Windows 7 64bit, 4GB ram (2GB vrij), standaard 500GB HD (400GB vrij).
Alle tabellen in deze database is van het type MyISAM en kunnen aardig wat records bevatten, waarvan er eentje er zelfs 30 miljoen heeft.
Elke tabel heeft drie kolommen van het type binary(x), waarbij de eerste 2 gezamenlijk de PK vormen. Er zitten verder geen triggers of andere indexen op de kolommen. Ook geen auto-increment key o.i.d..
Er zijn op de gehele database maximaal 4 queries tegelijkertijd actief. Vaak gebruiken ze elk een andere tabel, maar het komt een enkele keer voor dat ze de zelfde tabel gebruiken.

Uiteindelijk ben ik tot de conclusie gekomen dat de harde schijf de bottleneck is, dus zoek ik naar mogelijkheden om de schrijf/leessnelheid naar de database te vergroten zonder hardware aanpassingen te doen.

De selectquery luidt altijd: "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam LIMIT 0, 1";
Er zit geen WHERE conditie in omdat elk record goed is.
Nadat ik het record heb gebruikt, verwijder ik hem met "DELETE FROM Tabelnaam WHERE Kolom1 = Conditie1 AND Kolom2 = Conditie2".
Tussen de select en delete queries insert ik records in andere tabellen met "INSERT IGNORE INTO Tabelnaam" en dan de kolommen erachteraan.

Ik heb de laatste query geprobeerd te optimaliseren met m.b.v. een transactie te openen en dan eerst een select en vervolgens een gewone insert te doen zonder gebruik te maken van ignore, maar dat leverde geen snelheidswinst op.
Losse query's zijn niet traag o.i.d. Een losse query duurt minder dan 0,001 seconde.

Verder heb ik nog geprobeerd om m.b.v. parameters het e.e.a. te versnellen, maar dat is niet gelukt:
key_buffer_size: Geen merkbaar effect omdat ik waarschijnlijk zonder WHERE een record opvraag.
table_cache: Staat nu op 256, maar ook lijkt dat weinig tot geen verschil te geven t.o. de oude waarde.
tmp_table_size: Heeft ook geen merkbaar effect.

Hebben jullie nog suggesties om bepaalde parameters of zelfs query's aan te passen?

[Janoz]

Ik mis eigenlijk een beetje de probleemstelling. Wat is er precies traag? Je geeft aan dat de queries geen tijd kosten, maar toch is er iets langzaam. Het is mij dus niet helemaal duidelijk wat nu exact je probleem is. Want tussen 'de queries zijn los snel' en 'ik denk dat het aan de snelheid van de HD ligt' zit een heel groot gat.

Hoe groot zijn de binairy velden eigenlijk?

[MuddyMagical]

Deze LIMIT lijkt me wat overbodig: "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam LIMIT 0, 1";

Je geeft namelijk aan dat kolom 1 en 2 samen de PK vormen, dus ga je met "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam"; maar 1 record terug krijgen.
Daarnaast zijn misschien views een optie om te gebruiken, maar dat durf ik niet met zekerheid te zeggen.
* MuddyMagical is geen SQL goeroe...

[Onbekend]

Janoz schreef op dinsdag 24 mei 2011 @ 17:25:
Ik mis eigenlijk een beetje de probleemstelling. Wat is er precies traag? Je geeft aan dat de queries geen tijd kosten, maar toch is er iets langzaam. Het is mij dus niet helemaal duidelijk wat nu exact je probleem is. Want tussen 'de queries zijn los snel' en 'ik denk dat het aan de snelheid van de HD ligt' zit een heel groot gat.

Hoe groot zijn de binairy velden eigenlijk?

De twee key-velden zijn elk binary(16), en het derde veld is binary(8 ).

Ik verwacht zelf dat de toegangstijd van de HD het probleem vormt, maar daar ben ik niet 100% zeker van.
Maar ik weet dus niet wat MySQL allemaal op de achtergrond uitvoert bij het inserten of deleten van een record. Ik neem aan dat hij in ieder geval steeds controleert tegen duplicate keys en "ergens" een index-file bijhoud zodat hij snel met primary key kan zoeken.

MuddyMagical schreef op dinsdag 24 mei 2011 @ 17:26:
Deze LIMIT lijkt me wat overbodig: "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam LIMIT 0, 1";

Je geeft namelijk aan dat kolom 1 en 2 samen de PK vormen, dus ga je met "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam"; maar 1 record terug krijgen.
Daarnaast zijn misschien views een optie om te gebruiken, maar dat durf ik niet met zekerheid te zeggen.
* MuddyMagical is geen SQL goeroe...

Hoezo is die limit overbodig? Als ik zonder where en limit een selectstatement uitvoer, is hij wel een tijdje bezig om 30 miljoen records op te halen.

En hoe zie jij dat met views? Ik heb geen moeilijke query's en een view zou toch steeds opnieuw opgebouwd moeten worden als ik een record toevoeg of verwijder?omdat er ge

[FragFrog]

MuddyMagical schreef op dinsdag 24 mei 2011 @ 17:26:
Je geeft namelijk aan dat kolom 1 en 2 samen de PK vormen, dus ga je met "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam"; maar 1 record terug krijgen.

hoe kom je daarbij?

En je geeft nog steeds niet aan wat er nu traag is: de queries niet stel je, dus wat dan wel? Access tijd? Probeer dan records in geheugen klaar te zetten; dat kun je met MySQL zelf doen (door regelmatig een INSERT INTO `memoryTable` (SELECT FROM `sourceTable` LIMIT 100) oid te doen) of je kan een queue bijhouden met bijvoorbeeld iets als memcacheQ. 500 keer een enkele row selecteren duurt doorgaans langer dan 1 keer 500 rows selecteren

Afhankelijk van je platform kan het ook nog bevorderlijk zijn om persistente databseverbindingen te gebruiken ipv telkens opnieuw connecten, maar dat vermeld je er helaas niet bij.

[ Voor 11% gewijzigd door FragFrog op 24-05-2011 17:47 ]

[MuddyMagical]

MuddyMagical schreef op dinsdag 24 mei 2011 @ 17:26:
Deze LIMIT lijkt me wat overbodig: "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam LIMIT 0, 1";

Je geeft namelijk aan dat kolom 1 en 2 samen de PK vormen, dus ga je met "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam"; maar 1 record terug krijgen.
Daarnaast zijn misschien views een optie om te gebruiken, maar dat durf ik niet met zekerheid te zeggen.
* MuddyMagical is geen SQL goeroe...

Sorry, niet goed gelezen. Ik zie nu dat elk record goed is dat je krijgt en je geen WHERE gebruikt.
* MuddyMagical houdt zijn mond.

[Gomez12]

Wat probeer je nu precies te doen? Want je query's gaan op zich snel genoeg.
En aangezien je enkel 1 record pakt en dan een insert into en dan schijnbaar het origineel gelijk weer verwijderd gok ik dat je iets van een kopie/bewerkings slag probeert te doen record voor record.

Indien dit klopt dan is mijn advies heel simpel, ga in batches werken en bewaar zoveel mogelijk tussentijds in memory. Momenteel kost de administratie van je verbinding gewoon te veel tijd (30 miljoen keer 0,0001 tijd per administratie is nog steeds heel wat tijd).

Haal 100 records op, voer daar je bewerkingen op uit en gooi het in een array. Als die array 100.000 items bevat dan schakel je de indexen op de eindtabel uit en insert je de boel in 1x hierna bouw je de indexen voor de eindtabel weer op en je gaat verder.

[DexterDee]

Een PK op twee binary velden is verre van optimaal, met name in de opbouw van de binary tree index die MySQL moet maken om later records terug te vinden. Als je zeker weet dat de gegevens in deze twee velden per record uniek zijn, dan zou ik gewoon een `id` autoincrement PK kolom maken en dit id weer gebruiken om het record later weg te gooien.

Hoe dan ook, check met EXPLAIN of de PK index überhaupt gebruikt wordt in je select statement. MySQL is soms nogal kieskeurig met multi field indexes.

Afhankelijk van de bewerking van de data kun je overwegen om de select en de insert te combineren in een INSERT ... SELECT statement.

Als laatste en een aanvullig op Gomez12's tip, inserten van meerdere records kun je met een multi INSERT statement doen of met een DELAYED INSERT om tijd te winnen.

[Gomez12]

DexterDee schreef op dinsdag 24 mei 2011 @ 19:49:
Als laatste en een aanvullig op Gomez12's tip, inserten van meerdere records kun je met een multi INSERT statement doen of met een DELAYED INSERT om tijd te winnen.

DELAYED INSERT gaat je waarschijnlijk enkel maar extra tijd kosten. Het is leuk als je concurrent locking problemen hebt omdat je insert gewoon op de achtergrond gaat zitten wachten, heb je geen locking problemen dan kost het je de extra overhead van een extra thread.

[Voutloos]

Onbekend schreef op dinsdag 24 mei 2011 @ 17:42:
Ik verwacht zelf dat de toegangstijd van de HD het probleem vormt, maar daar ben ik niet 100% zeker van.
Maar ik weet dus niet wat MySQL allemaal op de achtergrond uitvoert bij het inserten of deleten van een record. Ik neem aan dat hij in ieder geval steeds controleert tegen duplicate keys en "ergens" een index-file bijhoud zodat hij snel met primary key kan zoeken.

Nu roep je weer iets willekeurigs. Is het echt traag? Heb je een hoge load? Klapt af en toe de server uit elkaar? Zeg nou eens waarom je het wilt verbeteren...

(wellicht is er wat te verbeteren, maar op GoT verzanden dit soort topics praktisch zonder uitzondering in 50 posts micro-optimalisaties zonder dat iemand uberhaupt heeft gemeten wat het probleem was)

[Onbekend]

FragFrog schreef op dinsdag 24 mei 2011 @ 17:45:
En je geeft nog steeds niet aan wat er nu traag is: de queries niet stel je, dus wat dan wel? Access tijd? Probeer dan records in geheugen klaar te zetten; dat kun je met MySQL zelf doen (door regelmatig een INSERT INTO `memoryTable` (SELECT FROM `sourceTable` LIMIT 100) oid te doen) of je kan een queue bijhouden met bijvoorbeeld iets als memcacheQ. 500 keer een enkele row selecteren duurt doorgaans langer dan 1 keer 500 rows selecteren

Dat is inderdaad een goed idee. Ik deed dit 1 voor 1 omdat mijn programma daar eerst niet mee overweg kon. Nu lukt dat denk ik wel, maar kost wel wat tijd om aan te passen. Maar dat heb ik er wel graag voor over.

Afhankelijk van je platform kan het ook nog bevorderlijk zijn om persistente databseverbindingen te gebruiken ipv telkens opnieuw connecten, maar dat vermeld je er helaas niet bij.

De databaseconnectie blijft wel de gehele tijd geopend, dat had ik toen al gevonden.

Gomez12 schreef op dinsdag 24 mei 2011 @ 19:29:
Haal 100 records op, voer daar je bewerkingen op uit en gooi het in een array. Als die array 100.000 items bevat dan schakel je de indexen op de eindtabel uit en insert je de boel in 1x hierna bouw je de indexen voor de eindtabel weer op en je gaat verder.

Voor het inserten van nieuwe waarden zal ik eerst verzamelen en dan in 1 keer op de database afvuren i.p.v. steeds weer eentje.

DexterDee schreef op dinsdag 24 mei 2011 @ 19:49:
Een PK op twee binary velden is verre van optimaal, met name in de opbouw van de binary tree index die MySQL moet maken om later records terug te vinden. Als je zeker weet dat de gegevens in deze twee velden per record uniek zijn, dan zou ik gewoon een `id` autoincrement PK kolom maken en dit id weer gebruiken om het record later weg te gooien.

Stel dat ik een aparte ID-kolom maak met een autoincrement. Hoe voorkom ik dan dat er twee records met dezelfde binary velden in die tabel voorkomen?

Hoe dan ook, check met EXPLAIN of de PK index überhaupt gebruikt wordt in je select statement. MySQL is soms nogal kieskeurig met multi field indexes.

De explain had ik eerder niet goed kunnen gebruiken omdat het meteen een stuk trager werd, en zo niet gemakkelijk de trage omstandigheden vinden. Maar ik zal het nog eens proberen.
Die twee binary kolommen zou ik ook samen kunnen voegen tot 1 kolom. Indien dat een leuke snelheidswinst oplevert is dat het ombouwen van het programma wel waard.

Voutloos schreef op dinsdag 24 mei 2011 @ 20:00:
[...]
Nu roep je weer iets willekeurigs. Is het echt traag? Heb je een hoge load? Klapt af en toe de server uit elkaar? Zeg nou eens waarom je het wilt verbeteren...

Het is op mijn eigen computer. Geen aparte server, geen negatieve invloeden van netwerkverbindingen, firewalls e.d.. Verder heb ik geen bijzondere programma's draaien die de load ineens omhoog haalt.
En waarom ik het wil verbeteren? Ik heb een tellertje in dat programma zitten, met hoeveel records hij per 5 seconden verwerkt. En hoe langer ik dat programma aan heb staan, des te trager wordt hij. (Als ik het programma opnieuw opstart blijft hij traag, dus het ligt niet aan ongebruikt geheugen e.d.)
Eerst dacht ik aan de hoeveelheden data, op internet had ik gelezen dat zoveel miljoen records geen probleem voor MySQL moest zijn.

(wellicht is er wat te verbeteren, maar op GoT verzanden dit soort topics praktisch zonder uitzondering in 50 posts micro-optimalisaties zonder dat iemand uberhaupt heeft gemeten wat het probleem was)

Hierboven is de explain genoemd, en die zal ik nog eens proberen te gebruiken om de oorzaak te vinden.

[RobIII]

Onbekend schreef op dinsdag 24 mei 2011 @ 21:42:
Stel dat ik een aparte ID-kolom maak met een autoincrement. Hoe voorkom ik dan dat er twee records met dezelfde binary velden in die tabel voorkomen?

Maak een unique constraint
Seriously; als je dat soort (toch wel redelijk basic) zaken niet kent, waarom ben je dan wel bezig met optimaliseren op zo'n niveau?

Edit: Wait... whut

Onbekend schreef op dinsdag 24 mei 2011 @ 17:42:
De twee key-velden zijn elk binary(16), en het derde veld is binary(8 ).

Ik neem aan dat die unique constraint er al is dan Wat is je probleem dan met een extra ID kolom?

[ Voor 31% gewijzigd door RobIII op 24-05-2011 21:52 ]

[The Eagle]

RobIII schreef op dinsdag 24 mei 2011 @ 21:43:
[...]

Maak een unique constraint
Seriously; als je dat soort (toch wel redelijk basic) zaken niet kent, waarom ben je dan wel bezig met optimaliseren op zo'n niveau?

Edit: Wait... whut

[...]


Ik neem aan dat die unique constraint er al is dan Wat is je probleem dan met een extra ID kolom?

Ik weet niet hoe MySQL zoiets oplost, maar als ik in Oracle een tabel definieer waarbij ik de keys opgeef, maakt Oracle op de key velden automatisch een unique index aan. Dus OF je hebt geen keys opgegeven en gaat er vanuit dat ze door de data al unique zijn, of een autoincrement is sowieso overbodig.

Makkelijk te testen overigens: maak maar eens een enkele unique index aan op de twee velden die jij key noemt. Als dat lukt heb je idd door je data je key velden per ongeluk unique. Lukt het niet, dan heb je dubbelingen en klopt je data dus niet.

Mocht het nou zo zij dat er wel een unique index op die velden ligt, dan zou ik eens kijken of ik de cachingmogelijkheden een eind op kon schroeven, evenals het bijwerken van de data. Ik weet niet hoe MySQL het doet, maar Oracle schrijft standaard eerst naar zijn buffer, en dan pas (en niet eens specifiek op gezette tijden) naar disk .De buffer is bij Oracle namelijk een essentieel onderdeel van het DBMS

[Voutloos]

Onbekend schreef op dinsdag 24 mei 2011 @ 21:42:
En hoe langer ik dat programma aan heb staan, des te trager wordt hij. (Als ik het programma opnieuw opstart blijft hij traag, dus het ligt niet aan ongebruikt geheugen e.d.)

Ik word er een beetje moe van:
Wát wordt nou trager? Wees (&#%@^%#@ eens specifieker. Pas dan weet je wat je bottleneck is en wat je precies aan verbetering (of verslechting !) boekt bij je wijzigingen.

Je blijft maar als een kip zonder kop dingen roepen. Ondertussen roepen anderen willekeurige trivia over connecties en andere merkjes RDBMSen, wat op zich goedbedoeld is en nut kan hebben, maar je kan nu ()*&@@% niet bewijzen dat het nut heeft of je gericht laten helpen. En trust me, ik kom heel ver met trivia opsommen.

* Voutloos vindt dat got de ik-wil-iets-optimaliseren-meer-weet-niet-wat-en-meet-het-lekker-niet-topics iets strenger zou mogen behandelen.

[ Voor 27% gewijzigd door Voutloos op 24-05-2011 23:08 ]

[CyBeR]

Onbekend schreef op dinsdag 24 mei 2011 @ 17:14:
De selectquery luidt altijd: "SELECT Kolom1, Kolom2, Kolom3 FROM Tabelnaam LIMIT 0, 1";
Er zit geen WHERE conditie in omdat elk record goed is.
Nadat ik het record heb gebruikt, verwijder ik hem met "DELETE FROM Tabelnaam WHERE Kolom1 = Conditie1 AND Kolom2 = Conditie2".
Tussen de select en delete queries insert ik records in andere tabellen met "INSERT IGNORE INTO Tabelnaam" en dan de kolommen erachteraan.

Die tabellen, daar wordt ook uit gelezen? En alles is MyISAM? There's your problem. MyISAM lockt de hele tabel bij een update. Op zo'n moment kun je er ook niet uit lezen. Daar merk je niet zoveel van als je een losse query draait, maar wel als je parallel meerdere dingen aan het doen bent.

Ik heb de laatste query geprobeerd te optimaliseren met m.b.v. een transactie te openen en dan eerst een select en vervolgens een gewone insert te doen zonder gebruik te maken van ignore, maar dat leverde geen snelheidswinst op.
Losse query's zijn niet traag o.i.d. Een losse query duurt minder dan 0,001 seconde.

Transacties bij MyISAM doen niets.

Hebben jullie nog suggesties om bepaalde parameters of zelfs query's aan te passen?

Hou om te beginnen op met MyISAM gebruiken.

[ Voor 6% gewijzigd door CyBeR op 24-05-2011 23:11 ]

[Anoniem: 42323]

Het probleem is inderdaad MyISAM, niet het locken is het probleem maar als je database groot word krijg je overflow wat steeds meer disk I/O's tot gevolg heeft en dat maakt het langzaam. Misschien moet je eens kijken naar B+TREE. B+TREE neemt meer schijfruimte in beslag maar word niet trager naarmate het groter word.

Verder kan je de query optimizer erbij pakken maar dan zul je je er wel even in moeten verdiepen hoe het werkt.

[ Voor 15% gewijzigd door Anoniem: 42323 op 24-05-2011 23:23 ]

[GlowMouse]

CyBeR schreef op dinsdag 24 mei 2011 @ 23:10:
[...]


Die tabellen, daar wordt ook uit gelezen? En alles is MyISAM? There's your problem. MyISAM lockt de hele tabel bij een update. Op zo'n moment kun je er ook niet uit lezen. Daar merk je niet zoveel van als je een losse query draait, maar wel als je parallel meerdere dingen aan het doen bent.

Als locking het probleem was, had TS dat allang opgemerkt.

Anoniem: 42323 schreef op dinsdag 24 mei 2011 @ 23:18:
Het probleem is inderdaad MyISAM, niet het locken is het probleem maar als je database groot word krijg je overflow wat steeds meer disk I/O's tot gevolg heeft

Overflow van wat?

Ik mis heel erg veel informatie, er staat niet eens bij wat nou precies traag is en wat de definitie van traag is hier.

[ Voor 7% gewijzigd door GlowMouse op 25-05-2011 09:11 ]

[Anoniem: 42323]

GlowMouse schreef op woensdag 25 mei 2011 @ 09:10:


[...]

Overflow van wat?

Een korte uitleg over myISAM: myISAM maakt data pages met ieder 3 records, iedere data page kan worden bereikt met de index via de hoogste key. De records worden sequentueel opgeslagen, dus als je iets toevoegd maar de data page zit vol maak je een overloopvoorziening, oftewel een overflow met een pointer van de data page naar de overflow. Zo kan de database veel groter worden maar je krijgt ook steeds meer disk I/O's wat het langzamer maakt.

Valt mij altijd weer op dat veel informatica'rs zich nooit verdiept hebben in opslagstructuren, de meeste kiezen maar iets zonder te weten waar ze voor kiezen met als gevolg dat databases problemen gaan geven als ze gaan groeien.

[ Voor 17% gewijzigd door Anoniem: 42323 op 25-05-2011 11:31 ]

[Anoniem: 35775]

Ik snap ook niet echt waarom je gebruik maakt van binary velden als keys ... dit blijven volgens mij gewoon string en worden deze bitwise vergeleken, wat dus stukken trager is dan een simpele int.

Mij lijkt het ophalen van 100 (peanuts) rijen zelfs op een machine met de capaciteiten van een tosti-ijzer geen probleem.

---- edit ----

ik mis ook enigszins de grootte van de tabel in rows, wat voor data er in zit en dat soort achtergrond info ... altijd wel handig

[ Voor 18% gewijzigd door Anoniem: 35775 op 25-05-2011 15:03 ]

[RobIII]

Anoniem: 35775 schreef op woensdag 25 mei 2011 @ 14:59:
dit blijven volgens mij gewoon string

Een string is gewoon een byte-array En een veelvoud van een x-aantal bytes is een long/integer/dword/whatever-you-name-it Zolang de bytes in een enkel register passen is er weinig tot niets aan de hand; bij 16 bytes (zoals hier het geval is) kan 't wat trickier worden maar je uitspraak is dus wat zwart/wit

[ Voor 25% gewijzigd door RobIII op 25-05-2011 15:04 ]

[Anoniem: 35775]

RobIII schreef op woensdag 25 mei 2011 @ 15:03:
[...]

Een string is gewoon een byte-array En een veelvoud van een x-aantal bytes is een long/integer/dword/whatever-you-name-it

Ging mij meer om de bitwise vergelijking ...

[RobIII]

Anoniem: 35775 schreef op woensdag 25 mei 2011 @ 15:04:
Ging mij meer om de bitwise vergelijking ...

Wat versta je onder een bitwise vergelijking? Want byte_a met byte_b vergelijken kost je evenveel als char_a met char_b vergelijken. Ze zijn immers hetzelfde (even aangenomen dat we van een 8 bits byte en een 8 bits char uitgaan) voor de CPU. En zoals ik zei is een string een array van bytes. Een string-compare gaat dan, hooguit, byte-wise vergelijkingen doen, niet bitwise

[Anoniem: 35775]

@RobIII ... ik las ergens dat intern mysql bitwise-and of memcmp gebruikt om varchars/varbinary te vergelijken.

Na het hele verhaal nogmaals gelezen te hebben denk ik toch dat het datamodel de bottleneck is, tabellen die 30 miljoen records bevatten is toch enigszins ongebruikelijk... kan zijn dat ik het fout heb natuurlijk maar ik weet niet wat de hele bedoeling van het systeem is.

[RobIII]

Anoniem: 35775 schreef op woensdag 25 mei 2011 @ 15:20:
tabellen die 30 miljoen records bevatten is toch enigszins ongebruikelijk

Voor een willekeurig blogje misschien, in serieuze applicaties is 30 miljoen records peanuts.

[Anoniem: 35775]

@RobIII ...

Ik werk dagelijks met een 'redelijk' serieuze applicatie (bestel / ticket / voorraad systeem voor een ISP) ... waar totaal wel 30 miljoen records inzitten maar wel verspreid over flink wat tabellen.

Ik snap ook wel dat echt grote systemen bij overheden, banken of verzekeraars dit makkelijk toppen ... maar deze draaien dan meestal ook op iets betere machines dan een desktop onder WAMP ... vandaar dat ik 30 miljoen ook wat fors vind.

[Anoniem: 42323]

Aan de kennis van de TS te zien denk ik dat die 30 miljoen records eerder onkunde is.

[Anoniem: 35775]

@Spetsnaz : beetje een bold statement .. zonder enige context is het moeilijk bepalen of die 30 miljoen nodig zijn of niet.

[Anoniem: 42323]

Zou kunnen maar dan hebben ze duidelijk de verkeerde persoon op het project gezet.

[Wolfboy]

Of 30M wel of niet normaal is voor de TS z'n applicatie kunnen we onmogelijk bepalen zonder verdere info. Daarover verder speculeren lijkt me dan ook niet zinnig.

Maar 30M rijen is imho toch ook weer niet heel vreemd, voor webapps komt het misschien niet snel voor aangezien je dan al snel "trage" queries gaat krijgen. Maar het is zeker niet volledig ongewoon.


Het verschilt natuurlijk nogal of 30M rijen in een tabel staat met alleen wat integers, of dat het grote rijen zijn met tekstkolommen die meerdere gigabytes in beslag nemen. Ik heb hier bijvoorbeeld een tabel met 40M rijen die maar zo'n 3GB aan ruimte inneemt (inclusief index). Dat is ook voor een eenvoudige wamp installatie nog prima behapbaar.

[cariolive23]

Onbekend schreef op dinsdag 24 mei 2011 @ 21:42:
De explain had ik eerder niet goed kunnen gebruiken omdat het meteen een stuk trager werd, en zo niet gemakkelijk de trage omstandigheden vinden.

EXPLAIN laat het queryplan zien wat de database opstelt voor jouw query, dat is alles wat EXPLAIN doet. En omdat voor iedere query een queryplan moet worden opgesteld, zal dit absoluut zeker weten echt niet langzamer zijn dan het volledig uitvoeren van de query, onmogelijk. Geen idee wat je doet, maar het klinkt alsof je iets fout doet.

Laat dus eens zien wat EXPLAIN roept over jouw queries, dan hebben we het ergens over.

Even een afslagje maken van het datamodel is ook wel handig, dan hoeven we er niet naar te raden of proberen jouw verhaal te interpreteren: een (denk) foutje is snel gemaakt.

[Onbekend]

Even algemene info. Het is een testprojectje van mij, waar ik graag dingen uit wil leren. Het is dus niet zo dat "het bedrijf een verkeerd persoon erop heeft gezet"!
Over de datastructuur heb ik zeker nagedacht, en de binary's leken mij het efficiëntste. Ik gebruik bij de Binary(16) ook de volle 16 bytes en heb geen records met 15 bytes of lager in die kolom.

De keuze op MyISAM is ook bewust gemaakt volgens deze pagina: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html .


Ik heb de explain uitgevoerd op een "trage" tabel met 1,6 miljoen records. (De 3 bestanden van die tabel zijn samen nu 104MB groot.)
Dit is het resultaat van EXPLAIN SELECT Kolom1, Kolom2, Kolom3 FROM TableName LIMIT 0, 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	TableName	ALL	NULL	NULL	NULL	NULL	1655447

Ik maak hieruit op dat het type ALL de vertragende factor is.

[ValHallASW]

Het is al een aantal maal gevraagd, maar wat is er nu precies traag? Die SELECT? In je TS zeg je namelijk dat de query max 0.001s duurt. De DELETE? de INSERT IGNORE? Wat zijn de andere queries die tijdens die trage query lopen?

[Gomez12]

Onbekend schreef op woensdag 25 mei 2011 @ 18:20:
Ik heb de explain uitgevoerd op een "trage" tabel met 1,6 miljoen records. (De 3 bestanden van die tabel zijn samen nu 104MB groot.)
Dit is het resultaat van EXPLAIN SELECT Kolom1, Kolom2, Kolom3 FROM TableName LIMIT 0, 1

Lees eerst eens wat explain doet, de hele suggestie voor explain is al vrij kansloos bij dit soort query's.

Deze query ga je niet significant sneller krijgen dan hij nu is. Je vraagt namelijk enkel de tabel te openen en het 1e record wat hij vindt (want geen order by oid, oftewel echt het 1e record wat hij tegenkomt) terug te geven.
Een andere storage engine kan het wellicht sneller ophalen, maar dat is totaal niet relevant.

De tijd zit hem niet in je query maar voor zover ik het inschat puur in je administratie ( je gooit er waarschijnlijk 1,6 miljoen query's tegenaan dat is simpelweg inefficient want er moet ook 1,6 miljoen keer de administratie bijgewerkt worden)

Een query heeft gewoon een overhead, en als je query zo extreem simpel is als dit heb je grote kans dat je overhead duurder is dan je query, normaliter merk je hier niets van omdat de overhead + query nog steeds een tijd van niks is. Maar als je 1,6 (of 30) miljoen keer niks doet houd je uiteindelijk toch een redelijke tijd over.

[Voutloos]

Met hem ^. Je query is niet noemenswaardig complexer dan een SELECT 1+1; dus aan 1 enkele, losstaande executie van die query zal het niet liggen.

[Anoniem: 35775]

Het trage zal hem ook wel zitten in de bulk van heel veel simpele queries achter elkaar uitvoeren,per rij eem bewerking doen en tegelijkertijd inserts op dezelfde tabel te doen (locking).

Gomez12 heeft ook zeker een een punt mbt die overhead, daarom zou het misschien beter zijn om 100 records per keer te querien en te verwerken. Ook het verwerken zou je nog op kunnen splitsen in losse processen ... aangezien PHP niet altijd even goed blijft draaien in een continue loop.

@Onbekend 'Even algemene info' ... heeeeeel algemeen inderdaad waar ik niet echt uit kan halen wat je nu eigenlijk aan het doen bent / wil doen.

[GlowMouse]

Anoniem: 42323 schreef op woensdag 25 mei 2011 @ 11:21:
[...]


Een korte uitleg over myISAM: myISAM maakt data pages met ieder 3 records, iedere data page kan worden bereikt met de index via de hoogste key. De records worden sequentueel opgeslagen, dus als je iets toevoegd maar de data page zit vol maak je een overloopvoorziening, oftewel een overflow met een pointer van de data page naar de overflow. Zo kan de database veel groter worden maar je krijgt ook steeds meer disk I/O's wat het langzamer maakt.

Overlopen van pages gaat bij een kleine database precies even snel als bij een grote database. Los daarvan gebruikt MyISAM helemaal geen pages (bron).

[Gomez12]

Anoniem: 35775 schreef op woensdag 25 mei 2011 @ 20:33:
Ook het verwerken zou je nog op kunnen splitsen in losse processen ... aangezien PHP niet altijd even goed blijft draaien in een continue loop.

Definieer continue loop eens?

Ik sta er echt van te kijken hoe men aankijkt tegen 30 miljoen records. Dat hoort voor een db een peuleschil te zijn en xxx miljoen handelingen zijn voor php ook een peuleschil.

Imho moet een php continue zwaarbelast zeker een week kunnen draaien mits niet al te grote fouten. En tja, 30 miljoen handelingen moet php er toch echt wel binnen een kwartier uit kunnen poepen.

Waar de TS last van heeft is gewoon geen ervaring / totaal onkundige opzet.

De normale opzet is gewoon zoveel mogelijk records naar php pompen en dan php de handelingen zoveel mogelijk in-memory laten afhandelen en dan het totale resultaat retour sturen naar de dbase.
Dan heb je in volgorde :
- 1x een dbase die alle interne optimalisaties kan benutten om de records op te halen
- 1x de overdrachtskosten tussen mysql en php
- 1x een php die de handelingen uitvoert
- 1x de overdrachtskosten tussen php en mysql
- 1x een dbase die interne optimalisaties kan benutten om de records weg te schrijven
Loop je hiermee tegen problemen aan, dan is de goedkoopste optie om er gewoon extra memory bij te plaatsen zodat het nog steeds kan.

De geoptimaliseerde opzet is gewoon zoveel mogelijk records (als er inclusief handelingen en totaalresultaat in het geheugen passen) naar php pompen en dan php de handelingen in-memory laten afhandelen en dan het batch resultaat retour sturen naar de dbase.

Wat TS lijkt te doen is :
- 1,6Mx een dbase die geen interne optimalisaties kan benutten om 1 record op te halen
- 1,6Mx de overdrachtskosten tussen mysql en php
- 1,6Mx een php die de handelingen uitvoert
- 1,6Mx de overdrachtskosten tussen php en mysql
- 1,6Mx een dbase die geen optimalisaties kan benutten om 1 record weg te schrijven

GlowMouse schreef op woensdag 25 mei 2011 @ 20:50:
[...]

Overlopen van pages gaat bij een kleine database precies even snel als bij een grote database. Los daarvan gebruikt MyISAM helemaal geen pages (bron).

Sowieso is dat niveau totale micro-optimalisaties die totaal niet van toepassing zijn zolang TS 1,6M query's uitvoert om 1,6M records op te halen.

[The Eagle]

Antwoord van ^^^ even in een notedop: je wilt je DBMS zoveel mogelijk selecties en sorteringen af laten handelen en je app zo weinig mogelijk laten doen. Een DBMS is gemaakt voor dat soort dingen, een applicatie(server) niet. QED wat mij betreft

Kortom: als ik jou was zou ik de developer eens schoppen. Ben je zelf de developer, doe jezelf dan een lol en loop met je kop tegen een muur op, want dit is dan duidelijk niet jouw cup of tea. Nofi

[Onbekend]

Ik zie dat jullie het over PHP hebben, maar daar doe ik helemaal niets mee. Ik gebruik een programma wat ik heb geschreven in Delphi om de database te vullen.

Nu lees ik 200 records per keer, en de wegschrijfqueries bewaar ik nu in een array. Als hij met elk record klaar is, voer ik al die queries achter elkaar uit i.p.v. steeds een enkele. I.c.m. de twee lees/schrijf-threads in dat programma lijkt dat al een interessante verbetering te zijn.

Misschien moet ik intern wel aparte connecties opzetten, waarbij ik de transacties per type opsplits. Maar dat ga ik morgen testen.

[Gomez12]

Onbekend schreef op woensdag 25 mei 2011 @ 23:48:
Ik zie dat jullie het over PHP hebben, maar daar doe ik helemaal niets mee. Ik gebruik een programma wat ik heb geschreven in Delphi om de database te vullen.

Replace in alle bovenstaande berichten php door delphi en het principe blijft gelijk

Nu lees ik 200 records per keer, en de wegschrijfqueries bewaar ik nu in een array. Als hij met elk record klaar is, voer ik al die queries achter elkaar uit i.p.v. steeds een enkele.

Begrijp ik nu goed dat je nog steeds 200 verwijderquery's uitvoert als je 200 records weg wilt hebben?
Tja, dan hoop ik maar dat je ze wel in een transactie douwt zodat de db niet na elke query de indexen gaat bijwerken, maar waarom niet gewoon 1 query die 200 records weggooit?

I.c.m. de twee lees/schrijf-threads in dat programma lijkt dat al een interessante verbetering te zijn.

Kappen met die threads-meuk, dat is geneuzel in de marge die zeer waarschijnlijk ook nog eens gigantische locking veroorzaakt als ik zie hoe de rest van je aanpak is. (iets met table-locks etc. waar je geen last van hebt als je het normaal aanpakt, maarja dan heb je ook geen 2 threads nodig)

Misschien moet ik intern wel aparte connecties opzetten, waarbij ik de transacties per type opsplits. Maar dat ga ik morgen testen.

When will the hurting stop, fix nou eerst eens je omgang met een dbase. Daarna kan je je altijd nog eens rustig in gaan zitten lezen of meerdere connecties voor of tegen je gaan werken (hint: het gaat niet voor je werken)

Je aanpak moet zijn :
- Laat je db een zo groot mogelijke batch klaarzetten (ipv 200 gewoon eens op 200.000 knallen)
- Laat je prog die zo groot mogelijke batch verwerken
- Laat je db die zo groot mogelijke batch in 1x weer afhandelen

[Anoniem: 35775]

@Onbekend alle optimalisatie enzo terzijde ...

Ben ik eigenlijk nog steeds benieuwd naar wat je hele applicatie nu doet of wat het doel is ?!