[MySQL] Database wordt heel snel langzamer

[MBV]

Ik heb hier op mijn stage een website waarop klanten meetwaarden kunnen bekijken. Hij zit nog niet in de productiefase, maar zit daar wel dicht tegenaan. Elke dag wordt daar door klanten een flinke berg data op gezet: van de afgelopen 6 maanden testen heb ik 2 miljoen rijen met registraties. Die bestaan uit 2 integers en een double.
Het probleem is als volgt: als er nieuwe data bij is gezet, is de database ontzettend traag. Elk grafiekje waarvoor gegevens uit die tabel moeten worden gehaald, doet er langer over om te genereren dan mozilla goed vindt. Nadat ik optimize table heb gedaan, is het probleem echter helemaal weg, en laden de grafieken binnen 5 seconden.
Hoe kan dat komen? En is optimize table na elke serie insert-query's een structurele oplossing ?

Een minder direct toepasbare oplossing zou zijn om een andere database te gebruiken. Vanuit de hosting provider is het volgens mij ook mogelijk om postgre te gebruiken. Zou dat een performanceverbetering geven?

[Glimi]

Gebruik je wel indices of niet?
Zie oa http://www.mysql.com/doc/en/MySQL_indexes.html

[ Voor 48% gewijzigd door Glimi op 01-04-2004 13:44 ]

[Verwijderd]

Ja database indexeren is een wijs idee met zo een groot aantal records

ALTER TABLE table ADD INDEX "blaat" (id,blaat,blaat)

bovenstaande is niet helemaal zeker maar errug in de buurt


ow sorry.. overbodige post .. glimi had het eigenlijk al neergezet

[ Voor 19% gewijzigd door Verwijderd op 01-04-2004 17:06 ]

[THIJZEL]

ja iig indices gebruiken en misschien is het een idd om optimize table elke nacht als een soort cronjobje te draaien?

[MBV]

optimize table wordt al elke nacht als chron-job gedraaid, inclusief gegevens ouder dan X weken weggooien. Een index is er al.
Tabel:

code:

RegIndex  (Int 11, PK,  FK naar klant enzo)
TimeStamp (Int 15, PK, Unix timestamp met tijd waarop gezocht kan worden. 
                       Naam heb ik niet verzonnen, gaat nog aangepast worden)
Value     (double, wordt niet op gezocht)

Index is PRIMARY constraint op RegIndex en TimeStamp. Dat zou toch voldoende moeten zijn?

[ Voor 5% gewijzigd door MBV op 02-04-2004 08:23 . Reden: tabel in [code] gezet ]

[whoami]

Hoe ziet je query er uit ?
Als je een samengestelde query hebt op regindex, timestamp, en je zoekt op timestamp, dan wordt je index niet gebruikt.
Doe eens een explain plan ofzo.

[Janoz]

Een primary constraint op een dubbel veld? Het lijkt erop dat er dan alleen een index is op de combinatie. Ik vind het iig erg vreemd om een primary key op 2 velden te hebben.

[PrisonerOfPain]

Uit m'n hoofd is een unix timestamp 10 tekens lang, geen 15

[MBV]

Sorry.
Ik bepaal eerst met een andere query de RegIndex. Dat blijkt vaak sneller te zijn dan een gecombineerde query. Dat kost nooit veel tijd. Dan komt de klapper:

code:

<?php
$q = "SELECT TimeStamp, Value ".
     "FROM Registrations ".
     "WHERE webID_REGINDEX = '".$regid."' ".
     "  AND TimeStamp >= $beginDatum ".
     "  AND TimeStamp < $eindDatum ".
     "ORDER BY TimeStamp";
$fp = Mysql_Query($q2) or die("foutje: ".MySQL_error());
//doe iets
?>

En ik bedoelde het idd dat het een index is op de combinatie.

structuur exporteren in phpmyadmin levert het volgende op:

code:

CREATE TABLE `Registrations` (
  `webID_REGINDEX` int(11) NOT NULL default '0',
  `TimeStamp` int(15) NOT NULL default '0',
  `Value` double NOT NULL default '0',
  PRIMARY KEY  (`webID_REGINDEX`,`TimeStamp`)
) TYPE=MyISAM;

[ Voor 36% gewijzigd door MBV op 02-04-2004 08:48 ]

[chem]

ik gok dat de order-by een file-sort+tem table vereist, zeker icm met de range-search.
doe eens een explain geven?

[Verwijderd]

waar zitten indices op? als je ergens op groupt of ordert moeten daar indices opstaan en ook als je er een statement op loslaat, dus die timestamp

[Janoz]

Je gecombineerde sleutel gaat je geen enkele winst opleveren bij die query. Nergens wordt iets met het gecombineerde veld gedaan. Wat zijn exact de betekenis en de eigenschappen van de regindex en timestamp velden (welke uniek zijn ed)? Waarschijnlijk zal het al een heleboel helpen waneer je elk veld een losse key zou geven.

[whoami]

Janoz, hij doet toch een query op RegIndex en op die timestamp. Normaal gezien zou deze index dus kunnen gebruikt worden.
Echter, regindex is een numeriek veld, en je zet quotes rond je filtercriteria voor die regindex. De meeste DBMS'en die ik ken, gaan dan ook geen gebruik maken van die index.

[MBV]

Ik kan nu even niet via SSH bij mysql zelf iets doen, om firewall-technische redenen maar ik zal hier de output van mysql neerzetten. eerst nogmaals de query:

code:

EXPLAIN SELECT TimeStamp, Value
FROM Registrations
WHERE webID_REGINDEX = 12 
  AND TimeStamp >= 1041375600 
  AND TimeStamp <  1041418800
ORDER BY TimeStamp

Het resultaat van PHPMyAdmin:
Table: Registrations
Type: Range
Possible_keys: PRIMARY
Key: PRIMARY
key_len: 8
Ref: NULL
Rows: 1
Extra: where used

Ik wist niet eens dat Explain bestond, laat staan wat het doet. Misschien kunnen jullie hier iets aan zien?

oh ja, over de lengte van een timestamp: dat kan toch een volledige integer zijn, dus als je nu zegt dat hij 10 lang mag zijn dan heb je in 20** een probleem

@Janoz: Ik denk dat die structuur prima is. REGINDEX bepaalt de relatie naar een kanaal van een apparaat van een klant, TimeStamp bepaalt de tijd waarop die registratie plaatsvond, en de combinatie is uniek. Hoe wilde je een andere index maken? Die opzoektabel zou dan even groot worden als deze tabel, denk ik.

[ Voor 31% gewijzigd door MBV op 02-04-2004 09:07 . Reden: @janoz toegevoegd ]

[Janoz]

Op die manier, dan is inderdaad je primary goed. Dat de opzoek tabelen even groot zouden kunnen worden klopt ook, maar het zoeken door die tabellen gaat veel sneller. Waneer er nu een veld met een bepaalde index gezocht moet worden wordt de hele tabel doorgescand. Waneer je een index toevoegd kan de database hier in logaritmische tijd door zoeken wat het verwerken aanzienlijk versnelt. Probeer het eens zou ik zo zeggen.

@whoami: Ik vermoed dat de key alleen op de combinatie wordt gelegd, waardoor de losse velden er geen voordeel van ondervinden.

[ Voor 14% gewijzigd door Janoz op 02-04-2004 09:17 ]

[ggvw]

Btw, op mysql.com staat een stukje geschreven over het optimaliseren van queries.
Nuttig om een keer gezien te hebben.

Hier staat ook nog een interessant artikel: http://www.databasejourna...mysql/article.php/1382791

[whoami]

Janoz schreef op 02 april 2004 @ 09:15:

@whoami: Ik vermoed dat de key alleen op de combinatie wordt gelegd, waardoor de losse velden er geen voordeel van ondervinden.

Idd, (behalve dan als je een query doet op het eerste veld van de samengestelde query).
echter, in zijn voorbeeld filtert z'n query op beide velden, en dan is een samengestelde index meestal beter dan 2 indexen op losse velden.

[MBV]

Denken jullie dat ik iets kan doen om te voorkomen dat ik continue optimize table moet doen, of niet? Ik vind het geen elegante oplossing, maar ik kan er geen oplossing voor verzinnen. Kennelijk kan MySQL gewoon slecht tegen deze situatie.

De andere vraag uit de Topic Start: kan ik met een andere DBMS of Tabeltype een verbetering verwachten qua performance?

@janoz: Ik kan me er iets bij voorstellen, heb ooit het vak 'datastructuren en algoritmen' gehad Ik dacht dat je bedoelde dat ik een aparte tabel moest maken om op te zoeken, en dat leek mij niet bepaald practisch. Een index is natuurlijk iets anders
en over die quotes: het komt af en toe voor dat een van de velden leeg is (door een zwaar bagger ontwerp van het PHP gedeelte, waar ik niet voor verantwoordelijk ben). Dan geeft MySQL een error als je daar geen quotes hebt, en kennelijk kan hij het prima vertalen naar INT.

[ Voor 19% gewijzigd door MBV op 02-04-2004 09:31 ]

[Bosmonster]

Volgens mij kan het sowieso geen kwaad om de optimizen als er een grote hoeveelheid data is toegevoegd. Je zegt dat ze zelf iedere dag grote hoeveelheden data toevoegen, misschien is het een idee om daar geautomatiseerd een optimize achter te plakken.

Neemt niet weg dat je ook de indices goed moet hebben natuurlijk

[ Voor 13% gewijzigd door Bosmonster op 02-04-2004 09:34 ]

[cavey]

@Bosmonster:

MBV schreef op 02 april 2004 @ 08:21:
optimize table wordt al elke nacht als chron-job gedraaid, inclusief gegevens ouder dan X weken weggooien. Een index is er al.

....

[Bosmonster]

Ow Dan snap ik het probleem niet zo. Optimize is juist bedoeld om je tabel te 'defragmenteren'. Der is dus niks vies aan om dat regelmatig te draaien als je een grote tabel hebt waar veel data in verschuift.

[MBV]

Ok. Ik heb gisteren (direct toen het probleem zich voordeed) na het invoegen van de grote hoeveelheid data een optimize geplakt (sorry Bosmonster, was je al voor )
Kan ik nu tegen mijn baas zeggen dat dit een structurele oplossing is?

en voor de derde keer: kan een andere DBMS of TABELTYPE helpen qua performance bij deze database?

[ Voor 2% gewijzigd door MBV op 02-04-2004 10:42 . Reden: iets een beetje geaccentueerd :) ]

[whoami]

Als je veel gegevens inlaadt in je tabel, kan het zijn dat je statistieken niet meer up to date zijn, waarbij het execution plan op basis van verouderde statistieken gemaakt wordt.
Vandaar dat die query opnieuw zoveel sneller is na een optimize table waarschijnlijk (als ik ervanuit ga dat optimize table hetzelfde is als update statistics oid).

[MBV]

wat is execution plan?

[chem]

execution plan is het plan wat de dbms gaat volgen om je query uit te voeren.

explain geeft je enig inzicht hierin, maar andere dbms'en hebben veel mooiere tools ervoor, maar goed.

[MBV]

MBV schreef op 01 april 2004 @ 13:40:
....
Een minder direct toepasbare oplossing zou zijn om een andere database te gebruiken. Vanuit de hosting provider is het volgens mij ook mogelijk om postgre te gebruiken. Zou dat een performanceverbetering geven?

MBV schreef op 01 april 2004 @ 13:40:
....
De andere vraag uit de Topic Start: kan ik met een andere DBMS of Tabeltype een verbetering verwachten qua performance?
...

MBV schreef op 02 april 2004 @ 10:23:
....
Kan ik nu tegen mijn baas zeggen dat dit een structurele oplossing is?

en voor de derde keer: kan een andere DBMS of TABELTYPE helpen qua performance bij deze database?

Zou iemand deze 2 vragen kunnen beantwoorden? Zoals je ziet heb ik ze al een paar keer gesteld Maakt niet uit, ik heb ook wel over gedeeltes van antwoorden heen gelezen

edit:
dat komt netjes op de volgende pagina, lijkt haast een nieuwe TS

[ Voor 4% gewijzigd door MBV op 02-04-2004 11:09 ]

[chem]

niet per se. Je zou kunnen kijken of je table fixed-length is (en daar anders voor zorgen).
Daarbij kan je een index proberen op [webID_REGINDEX, Timestamp, Value]

[MBV]

hij is fixed-length (zitten geen varchar en dat soort dingen in). Een index op Value lijkt mij zinloos: ik hoef nooit te zoeken op waarde, alleen op datum en dat soort dingen.

Maar een andere DBMS zal weinig uitmaken, begrijp ik dus.