[mysql] database optimaliseren?

[Gromba]

Het probleem
Ik heb een database met statistieken van RC5-72 blockjes, maar hij wordt te groot (nu al 7,5 mb) en m'n server krijgt er steeds meer moeite mee. Maar het is te optimaliseren, maar ik weet nog niet precies hoe.

Meer informatie
De tabel (die 'data' heet) bevat op dit moment bijna 75.000 rijen. Met de belangrijkste informatie; de datum, het ip en het aantal blocks

overzicht


Zoals ik met oranje heb aangegeven, wordt voor elk blockje van het zelfde ip een nieuwe rij aangemaakt. En met zo'n 2000 blockjes per dag loopt dat flink op.

De statistieken werken alleen per dag, en niet per uur/minuut/seconde

Dus alles met de zelfde datum (bijv. 2004-11-12) en het zelfde ip (62.251.17.104) zouden allemaal samengevoegt kunnen worden (en dan bij blocks '4')
Ik heb al zelf veel geprobeerd, maar ik moest steeds de backup raadplegen omdat ik er niet uitkwam.

De vraag
Hoe kan ik dit gemakkelijk optimaliseren?

[gorgi_19]

7.5 MB is niet veel voor een db. IP's kan je opslaan als LONG; hoe staan je indexen verder?

[Verwijderd]

Wat een eer mijn naam \o/

Modbreak:

Als je niets nuttigs hebt toe te voegen, post dan niet

[ Voor 61% gewijzigd door gorgi_19 op 21-11-2004 16:16 ]

[Gromba]

gorgi_19 schreef op zondag 21 november 2004 @ 16:11:
7.5 MB is niet veel voor een db. IP's kan je opslaan als LONG; hoe staan je indexen verder?

7.5mb is niet veel nee, maar het is nog maar van ongeveer 1,5 maanden en ik weet zeker dat we nog wel iets langer doorgaan.
En bij mijlpalen berekenen is hij toch een halve seconde bezig.
plaatje

[BrZ]

Maakt de tijd van een block uit? Zo niet, dan zou ik een date-veld gebruiken in plaats van date-time, en het script wat de blocks in de database zet aanpassen dat hij het blocks verhoogt als de combinatie IP + datum al voorkomt, in plaats van een nieuwe rij invoegen.

[edit]
Bedenk nu pas dat de extra informatie per blok anders is, dus je wilt het denk ik wel apart opslaan. In dat gevallen moet je gewoon een goede index maken, dat scheelt heel erg veel qua tijd. (Zo te zien heb je nu niet eens een primary key?)

[ Voor 32% gewijzigd door BrZ op 21-11-2004 16:34 ]

[Gromba]

BrZ schreef op zondag 21 november 2004 @ 16:32:
Maakt de tijd van een block uit? Zo niet, dan zou ik een date-veld gebruiken in plaats van date-time, en het script wat de blocks in de database zet aanpassen dat hij het blocks verhoogt als de combinatie IP + datum al voorkomt, in plaats van een nieuwe rij invoegen.

Dat valt opzich te doen, maar ik wil de bestande rijen graag dingesen.

[edit]
Bedenk nu pas dat de extra informatie per blok anders is, dus je wilt het denk ik wel apart opslaan. In dat gevallen moet je gewoon een goede index maken, dat scheelt heel erg veel qua tijd. (Zo te zien heb je nu niet eens een primary key?)

De informatie per block is anders, maar per IP is alles het zelfde (op een paar uitzonderingen, maar dat maakt opzich ook niet veel uit)

[Eelke Spaak]

Kan je eens wat queries posten die geregeld worden gedaan? Dan kunnen wij wat indices en een primary key erbij bedenken.

Ik weet niet hoe dat met MySQL zit, maar bij SQL Server zit ook een programmaatje (Profiler) dat index-voorstellen voor je kan doen. Echter, voor een dergelijk simpele database moet het nog wel ui het hoofd te doen zijn lijkt mij zo .

[Gromba]

Eelke Spaak schreef op zondag 21 november 2004 @ 16:46:
Kan je eens wat queries posten die geregeld worden gedaan? Dan kunnen wij wat indices en een primary key erbij bedenken.

De meest gedane (is dat een woord?) query is:
Select claimed,SUM(blocks) AS blocks FROM data where date > '20041121020000' and date < 20041121235959 GROUP BY claimed ORDER BY blocks DESC
'De stats van vandaag'

[Eelke Spaak]

In ieder geval een index op date leggen, en een DESC index op blocks.

[gorgi_19]

Eelke Spaak schreef op zondag 21 november 2004 @ 17:08:
In ieder geval een index op date leggen, en een DESC index op blocks.

Ook een DESC index op date Hoewel ik me afvraag of het niet beter is om de boel een keer per 5 minuten te berekenen oid en dan te cachen

[ Voor 21% gewijzigd door gorgi_19 op 21-11-2004 17:16 ]

[Gromba]

gorgi_19 schreef op zondag 21 november 2004 @ 17:15:
[...]

Ook een DESC index op date Hoewel ik me afvraag of het niet beter is om de boel een keer per 5 minuten te berekenen oid en dan te cachen

Hey ja, goed idee eigelijk. Briljant eigelijk!
Geen idee hoe ik dat ga doen, maar dat is wel de oplossing!

[Vold]

Gromba schreef op zondag 21 november 2004 @ 17:22:
[...]

Hey ja, goed idee eigelijk. Briljant eigelijk!
Geen idee hoe ik dat ga doen, maar dat is wel de oplossing!

Ik ben nu ook bezig met wat database gezeur enzo, en ga ervan uit dat deze database van mij heeel groot wordt, en dan wil ik ook vanaf het begin al gaan cachen.. Maar hoe doe je dat het best ? Ik dacht aan het uitlezen van de gegevens , die schrijven in een txt bestand in het gewenste formaat, en dat dan weer include op de benodigde plaatsen.

Of is dat te omslachtig ?

[gorgi_19]

Vold schreef op zondag 21 november 2004 @ 17:41:
[...]


Ik ben nu ook bezig met wat database gezeur enzo, en ga ervan uit dat deze database van mij heeel groot wordt, en dan wil ik ook vanaf het begin al gaan cachen.. Maar hoe doe je dat het best ? Ik dacht aan het uitlezen van de gegevens , die schrijven in een txt bestand in het gewenste formaat, en dat dan weer include op de benodigde plaatsen.

Of is dat te omslachtig ?

Wat vind je groot? En je kan zut ook in application variabelen cachen; bestanden aanmaken. Hangt er een beetje vanaf wat je precies wilt

[ Voor 6% gewijzigd door gorgi_19 op 21-11-2004 17:48 ]

[dvvelzen]

gezien je datetime in de query zou ik van date een date maken ipv een datetime (als het mogelijk is zelfs een smalldate)


ip, als dit veel dezelfde gevens oplevered kan je hem altijd opslaan in een losse tabel waar bij je als fk / pk een kleiner datatype gebruikt (maar dan moet je de inhoud enzo wel goed monitoren, als je dat niet blijft doen is het handiger dit niet te wijzigen)

email is wel heel redundant zou ik een losse tabel van maken met een identity. (nullable voor fk, of default naar een default email id)

version gebruik een zo klein mogelijk int tiny of small (1 / 2 byte) en link deze naar een aparte tabel

denk bij elk van de overige blocks / OS / cpu / core / claimed goed aan de maximum waarde en pas de kolommen waar nodig aan naar een kleiner data type...

cpu > 255 bv lijkt me niet echt logisch dus tinyint is mooi zat


indexen:
iig een op date gezien je gegeven query als je van plan bent er meerdere specifieke te maken dan wordt het date/claimed/blocks.

verder zal je denk ik een ip/date ook wel nodig hebben...

kijk wel uit dat je niet voor alles een index gaat aanmaken 1 of 2 complete covering indexes op een belangrijke/grote tabel kan maar als je er veel meer doet worden je indexen vele male groter dan je data (kost dus ook meer ruimte en tijd om een record te DML-en)


have fun,
Dennis

[Verwijderd]

Overigens is het qua rekentijd verstandiger om INT te gebruiken ipv TinyInt. Een INT neemt wel meer ruimte in, maar heeft voor de CPU maar een kloktik nodig voor een berekening en een TinyInt heeft er twee nodig. Dat zijn wel van die miereneukdingetjes maargoed

[dvvelzen]

Ach daar hebben we op onze Sun Fire E20K Server's weinig omkijken naar