Database kiezen

[rogierslag]

Beste Tweakers,

Inmiddels heb ik redelijk wat ervaring met PHP en MySQL maar nu loop ik tegen een dataset aan welke groter is dan waarmee ik ooit heb gewerkt.

Het betreft een simpele applicatie welke elke minuut de waarde van 16 sensoren uitleest en opslaat in de database. Dit zijn dus 16*60*24=23040 records welke er per dag bijkomen. Momenteel is dit opgeslagen in een MySQL database met de MyIsam storage engine.
De tabelopbouw is met onderstaand ontzettend simpel

code:

CREATE TABLE IF NOT EXISTS `standen_minuut` (
  `TIMESTAMP` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `LEIDING_ID` tinyint(1) NOT NULL,
  `STAND` float NOT NULL,
  PRIMARY KEY  (`TIMESTAMP`,`LEIDING_ID`),
  KEY `TIMESTAMP` (`TIMESTAMP`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Er worden in de betreffende tabel vooral inserts gedaan en af en toe haalt (onregelmatig, maar minder dan 100x per dag) een PHP script waarden op (simpele query, hoogstens wat optellen ed), goochelt er wat mee en maakt er een grafiek van. De enige join om om LEIDING_ID te koppelen aan een andere tabel met gegevens over die betreffende leiding.

De vraag: kunnen MySQL en MyISAM omgaan met zulke hoeveelheden records zonder al te traag te worden en dergelijke? De data dient enkele jaren bewaard te worden dus uiteindelijk zou ik tegen miljoenen records aanlopen.

Het probleem is niet dat MySQL evt meer dan 2 seconden over een query doet (hoewel dat natuurlijk mooier is om te vermijden) omdat de data niet vaak wordt opgevraagd en al helemaal niet tegelijkertijd door meerdere gebruikers.

[_JGC_]

Sowieso zou ik overstappen van MyISAM naar InnoDB vanwege dataconsistentie. Op zich heeft MySQL geen problemen met veel rijen in een database, wij hebben hier enkele tabellen met 175 miljoen rijen, dat werkt nog prima.

[BM]

8,5M records per jaar, daar zou een beetje database geen problemen mee mogen hebben

Heb wel eens access 'databases' gezien waar 10M records in 1 tabel zaten, en zelfs dat ging redelijk goed

[HuHu]

Stel dat je het 10 jaar bewaart: 23040 * 365 * 10 = 84 miljoen. Dat is geen enkel probleem.

[igmar]

Stap om te beginnen eens over op InnoDB als storage engine. MyISAM heeft table level locking, hetgeen betekend dat elke query je hele tabel locked.

Ik heb een aantal databases > 2 miljoen records, en dat is geen probleem (als je tenminste aandacht besteed aan indexen en je tabelstructuur).

[RobIII]

rogierslag schreef op woensdag 03 maart 2010 @ 10:14:
De vraag: kunnen MySQL en MyISAM omgaan met zulke hoeveelheden records zonder al te traag te worden en dergelijke? De data dient enkele jaren bewaard te worden dus uiteindelijk zou ik tegen miljoenen records aanlopen.

Heb je het zelf al eens doorgerekend want daar klinkt het niet erg naar: Ik kom per jaar al op ~8.5M records. Maar nee, dat is (in principe) geen probleem.
/laat

[ReCreator]

En hier nog een interessant artikel over optimaliseren voor sneller zoeken etc.
http://www.databasejourna...L-Queries-and-Indexes.htm

Weet er zelf verder niet gek veel van

[rogierslag]

oke mooi dat het geen probleem zou zijn. Mijn ervaring stopt meestal bij structuren > 10k records per tabel

De meest gedraaide query heb ik net even opgevraagd. Deze doet met 218.320 records ongeveer 1/3 seconde over deze selectie

code:

SELECT round( sum( stand ) , 2 ) , DATE_FORMAT( timestamp, "%e" ) , DATE_FORMAT( timestamp, "%m" ) , DATE_FORMAT( timestamp, "%Y" ) , DATE_FORMAT( timestamp, "%H" ) , DATE_FORMAT( timestamp, "%i" ) 
FROM standen_minuut
GROUP BY (
TIMESTAMP - SECOND( TIMESTAMP ) 
)
ORDER BY TIMESTAMP DESC

Misschien moet ik er wel even bijvermelden dat het geen ramp is om de database 's nachts een uurtje plat te leggen om backups te trekken. Dat was altijd de reden voor mij om wel eens InnoDB te gebruiken, maar dat speelt hier dus niet echt. Ik ben voornamelijk bang voor de mindere performance van InnoDB in deze omgeving.

Heb in ieder geval net wat info gekregen, ik ga even verder zoeken op innodb vs Mysql

[cariolive23]

Je query is fout, de GROUP BY is onmogelijjk en had dus een foutmelding moeten opleveren. Blijkbaar heb je de server (of connectie) niet correct geconfigureerd.

http://wiki.phpfreakz.nl/Werken_met_MySQL#Configuratie
http://wiki.phpfreakz.nl/Group_by

[KabouterSuper]

Nog twee tips:
1) probeer het eens uit...trek een copietje van je database en dupliceer de data met een factor 10, 100 of 1000. Je komt er dan vlot achter of je problemen krijgt (qua snelheid en opslag).
2) In je query groepeer je op een functie van de kolom timestamp. Dit is niet optimaal, en mischien krijg je in de toekomst problemen met de query snelheid. Mocht je je query willen versnellen, dan zou ik dus de kolom timestamp onder de loep nemen. Wellicht kan je een function-based index op de kolom gooien (ondersteunt mysql dit al?). En anders wil je misschien een duplicaat kolom bijhouden zonder seconde aanduiding (met een index hierop).

[djluc]

Als het te traag wordt kan je nog overwegen om alle overbodige zooi uit je query te halen zodat die zo plat mogelijk is. Gezien de beperkte grootte van je resultaatset kan dat wellicht schelen. (Checken met explain) Het meest invloedrijk zullen de indices worden, controleer dus goed of deze gebruikt worden.

Of joinen noodzakelijk is vraag ik me ook af, als het niet hoeft in dit geval niet doen. Als er bijvoorbeeld 16 leidingen zijn kan je deze gewoon met 1 simpele platte query ophalen en vervolgens gebruiken vanuit een array als je de meetdata gaat presenteren. Als je gescheiden databaseserver gebruikt dan haal je zo de meeste last van de database.

[_JGC_]

cariolive23 schreef op woensdag 03 maart 2010 @ 10:30:
Je query is fout, de GROUP BY is onmogelijjk en had dus een foutmelding moeten opleveren. Blijkbaar heb je de server (of connectie) niet correct geconfigureerd.

http://wiki.phpfreakz.nl/Werken_met_MySQL#Configuratie
http://wiki.phpfreakz.nl/Group_by

Ik weet niet in hoeverre MySQL group by ondersteunt op berekende waardes, maar als ie dat kan, is er niks mis met die query. Het nadeel van die query is dat ie ontzettend traag is omdat voor elke query een full table scan nodig is. Bij een kleine tabel is dat zo klaar, bij een tabel met tig rijen is dat wel even bezig.

Wat betreft InnoDB vs MyISAM: MyISAM is zonder tuning een stuk sneller dan InnoDB. Als je de InnoDB opties een beetje fatsoenlijk instelt heb je meer voordelen van InnoDB dan nadelen.

[Voutloos]

_JGC_ schreef op woensdag 03 maart 2010 @ 11:38:
[...]

Ik weet niet in hoeverre MySQL group by ondersteunt op berekende waardes, maar als ie dat kan, is er niks mis met die query.

Behalve dan dat er zaken worden geselecteerd welke niet in de group by staan of het resultaat van een aggregate function zijn. Aka de nr1 mysql group by wtf.

[T-MOB]

Als je gaat Joinen op leidingid zou ik daar in elk geval een index opleggen. De losse index op timestamp kun je nog verwijderen. MySQL kan voor die kolom de Primary Key gebruiken. Verder lijkt me de de tip van KabouterSuper wel een goede: maak een kolom met de timestamp zonder de secondes om je group by te versnellen.

[Sebazzz]

cariolive23 schreef op woensdag 03 maart 2010 @ 10:30:
Je query is fout, de GROUP BY is onmogelijjk en had dus een foutmelding moeten opleveren. Blijkbaar heb je de server (of connectie) niet correct geconfigureerd.

De query is inderdaad fout, maar MySQL zal dat nooit toegeven. Zulke query voeren bij MySQL vrijwel altijd uit.

[rogierslag]

Ik heb nu de MySQL insert code wat aangepast zodat bij de insert al direct de now()-SECOND(now()) wordt gedaan.

Nu is de query

code:

SELECT 
sum(stand),DATE_FORMAT(timestamp,"%e"), DATE_FORMAT(timestamp,"%m"),DATE_FORMAT(timestamp,"%Y") 
FROM standen
 GROUP BY TIMESTAMP 
ORDER BY TIMESTAMP DESC;

Deze lijkt mij wel correct maar is niet sneller dan de vorige query. Enige manier hoe die te versnellen is? Er staan al indexen op timestamp en leiding_id. Primary key kan niet want elke keer komen 16 timestamps overeen. Komt dit door de Group By clausule oid?

[FireDrunk]

Hoe vaak word de query nou letterlijk uitgevoerd? 1 keer per dag? Wat maakt het dan uit hoe lang die query duurt. Of hij nou 3 of 5 seconden duurt is dan toch niet meer interessant?

Ik zou mijn energie steken in het backuppen en consistent houden van de data in plaats van de performance...

[kwaakvaak_v2]

ik zou ook eens kijken naar de archive storage engine in mySQL. Is niet zo heel snel met het ophalen van data, maar als het voor puur opslag van logger gegevens is, is het zeker een goed alternatief imho.


Bedenk mij zojuist iets,

Opsplitsen in twee tabellen met twee storage engine's, logger data zelf in archive, en de sum query die je doet in een losse tabel in myisam/innodb. Dat kun je eventueel via een cron of iets dergelijks doen.

Het resultaat van een join is met enige regelmaat hetzelfde te noemen, dus waarom voor oude data telkens opnieuw uitrekenen als je het ook op kan slaan? Schijfruimte is relatief goedkoop

[ Voor 51% gewijzigd door kwaakvaak_v2 op 04-03-2010 10:54 . Reden: extra optie bedacht ]

[KabouterSuper]

Draaitijden zijn vaak niet-lineair (tov grootte database). Dus als je met een kleine tabel geen problemen hebt, zegt dat niet dat je met een grote tabel ook geen problemen hebt. Slimme groeperingen met goede indexen zorgen dat je kunt opschalen. De winst die je zo kunt halen is enorm (heb wel eens queries van 24 uur gezien die na de aanpassing in 8 minuten draaiden)

[_JGC_]

Met een beetje servertuning kan je ook heel ver komen. Vooral voor InnoDB staan de standaardwaarden extreem laag. Ik heb in het verleden ooit een component voor een CMS geschreven die voornamelijk mysql_query en mysql_fetch_object deed volgens de profiler. Dit kwam tijdens tests op een getunede server niet aan het licht, maar toen het live ging op de server van een klant zonder enige vorm van tuning was het na 1000 rows al bingo. Pageloads van 5 seconden is niet leuk.

[GlowMouse]

Je key TIMESTAMP is een prefix van een andere index, dus die kun je weglaten.

InnoDB versus MyISAM maakt hier qua performance niets uit, daarom zou ik kiezen voor InnoDB ivm consistentie.

Wil je die query echt snel krijgen, dan pak je voor timestamp een signed integer veld en laat je de applicatie de kolom vullen met als waarde 0-timestamp, afgerond op hele minuten. Je kunt dan GROUP BY TIMESTAMP
ORDER BY TIMESTAMP ASC gebruiken. De DATE_FORMAT wordt dan DATE_FORMAT(FROM_UNIXTIME(-timestamp),"...").
Een alternatief is om de query aan te passen naar
SELECT stand,timestamp
FROM standen
ORDER BY TIMESTAMP DESC
LIMIT 10000 /* je wilt toch niet meer weten dan een paar dagen/weken */
en de berekeningen verder in je applicatie te verrichten.

[ Voor 8% gewijzigd door GlowMouse op 05-03-2010 13:20 ]