[MYSQL] gebruik van een 'view'

[toost]

Ik heb een aantal vragen die gaan over een view in mysql, heb de documentatie van mysql.com en het forum van mysql door gezocht maar kan niet direct een antwoord vinden op mijn vragen, hopelijk hebben jullie deze kennis al wel

situatie:
Ik ben bezig met een statistieken tool te ontwikkelen (op basis van PHP, AJAX en MYSQL).
Aangezien we praten over 4-5 miljoen records per maand hebben we besloten om de data op basis van maand tabellen op te slaan. Het merendeel van de uit te voeren queries gaan over maand specifieke data dus met de maand tabellen heb je ipv 50 miljoen records nog maar 5 miljoen records te doorzoeken.

Maar nu het volgende: er zullen ook queries uitgevoerd worden op basis van jaar statistieken. We kunnen de records niet groeperen omdat we ook gedetaileerde jaar statistieken willen op vragen. Of we zouden voor elk type query gegroepeerde tabellen moeten maken, maar dit vind ik persoonlijk een beetje onzinning.

Nu zijn we gaan kijken naar het gebruik van views. Met een view zouden we alle maand tabellen kunnen koppelen naar een jaar tabel (view), sowieso is dit makkelijk omdat de uit te voeren queries niet 12 join's bevatten waardoor de queries wat onderhoudbaarder zijn.

Maar over het gebruik van views hebben wij dus een aantal vragen:

is een view een echte tabel die de data appart opslaat, zoja gebeurt dit virtueel (in het geheugen) of daadwerkelijk op de hardeschijf.

of is een view een result van de view query die 'on the fly' wordt gegenereerd?

In dat laatste geval zitten er dan volgens ons weinig meer voordelen aan in bv. snelheid aangezien je dan in feite 2 query's uitvoert.

wat ik dus wil is een op zo snel mogelijke manier de goede data verkrijgen, de vraag is dus alleen wat is hiervoor de snelste manier, is dit uberhaupt wel met gebruik van een 'view' of kan je beter voor elke query een temp. tabel aanmaken dmv union oid.

Ik hoop dat het zo een beetje duidelijk is en dat jullie mij wat meer inzicht kunnen geven in een mogelijke oplossing voor mijn vragen

[Verwijderd]

Waarom heb je niet eerst even je eigen tool gebenchmarkt voordat je besloot de boel op te delen in tabellen per maand?
En betreft een view: als je een grote tabel hebt, kun je heel makkelijk even een view erop maken, en kijken of de data directory van MySQL ineens veel groter is. Dat is niet zo, views zijn gewoon een soort queries op bestaande tabellen. Sommige acties kun je loslaten op een view, waarbij de originele tabellen worden aangepast.

Ik denk dat je de problemen een beetje opzoekt voor je uberhaupt ertegenaan gelopen bent. Als je een bestandje van 10 MB moet opslaan, ga je dan gelijk aan een iSCSI LVM oplossing denken?

[toost]

thnx voor je reactie, en inderdaad ik zoek de problemen op zonder er tegenaan gelopen te zijn, maar dat is juist de bedoeling, ik wil dergelijke problemen graag voorkomen Verder is er ook nog geen tool want ik ben deze nog aan het ontwerpen, maar dit is wel gebaseerd op kennis van de huidige tool. En onze ervaring daarmee is dat queries op een tabel met 40miljoen records niet bepaald snel te noemen zijn.

Maar ik kan opmaken uit je antwoord dat een view dus niet op de hd wordt opgeslagen (en inderdaad had ik zelf ook kunnen zien door het aanmaken van een view). Maar de data in de view zelf wordt deze gegenereerd op het moment dat ik een query op de view los laat of wordt de view gegenereerd op het moment dat er een verandering is aan een van de tabellen waar de view zijn data uit haalt?

[djluc]

Ik zou sowieso stoppen met dat tabellen per maand idee, verschrikkelijk plan. Ik zou kijken of je niet bijvoorbeeld in de nacht een snelle cache tabel kunt maken waarin je de totale per maand opslaat. Dat zijn niet zo'n kostbare berekeneningen meer dan die je bij een opvraag van de stats moet doen.

[toost]

hmm, ben benieuwd waarom je de maand tabellen een slecht idee vind. Er komen straks veel (+1000) gebruikers op de tool, deze willen allemaal hun data zien, daarom hebben we gekozen voor de maandtabellen. De gebruikers willen geen 10sec. wachten tot ze de juiste data op het scherm hebben en door gebruik van de maandtabellen hoef je dus ipv de 40m maar 4m records door te worstelen.

maar ik ga maar eens een aantal tabellen aanmaken en daarop testen, ben namelijk ook een oplossing tegen gekomen met het gebruik van mrg en union in een create table query. Voor zover dat ik het zag werkt dit het zelfde als waarvoor ik een view zou gaan maken, omdat de data in de view identiek zal zijn aan de data in de maand tabellen.

[Verwijderd]

Dit forum heeft tientallen miljoenen posts. Toch komen topics redelijk vlot op het scherm. Goede indexen zijn belangrijk.

[MoBi]

Ik denk dat het veel belangrijker is om te kijken wat je selecteerd en hoe je query wordt opgebouwd.

En onze ervaring daarmee is dat queries op een tabel met 40miljoen records niet bepaald snel te noemen zijn.

Heb je al eens uitgezocht waarom het zo lang duurt? Ligt dit aan de hardware? Of aan slecht geschreven query's? Mis je indexen? enz. Analyzeren van je waarschijnlijk huidige applicatie kan je veel meer input geven waarom die traag zou zijn.

[leuk_he]

toost schreef op donderdag 27 maart 2008 @ 13:22:
of is een view een result van de view query die 'on the fly' wordt gegenereerd?

Een view is niks meer dan een query. Deze kan dus net zo traag zijn als zelf een query schrijven. Het voordeel zit hem in duidelijker code, en heel soms in kortere parse tijden(iets waar je je meestal niet druk over maakt).

Wat iji wil is een materialized view, een table waarin de resultaten van een view daadwerkelijk zin opgelsagen.(uiteraard pas doen als je hebt nagegeken dat je indexen goed liggen en je table ontwerp nog enigzins klopt.)

[Voutloos]

leuk_he schreef op donderdag 27 maart 2008 @ 15:53:
Wat iji wil is een materialized view,

Ik denk dat hij dat niet wil. Ts kan zich beter inlezen in het optimaliseren van queries, structuur en indexen. En als je een tabel met afgeleide data zou willen, lijkt het me eerder een tabel met totalen per bepaalde tijdseenheid zoals djluc noemt.

[leuk_he]

Voutloos schreef op donderdag 27 maart 2008 @ 16:09:
[...]
lijkt het me eerder een tabel met totalen per bepaalde tijdseenheid zoals djluc noemt.

Materialized view is dus een sjieke manier om dat te zeggen....

[Niemand_Anders]

Een oplossing is misschien gebruik maken van een 'dubbele' tabel. Je hebt gewoon je basis tabel waarin alle records staan en je hebt een query tabel waarin de laatste maand staat. Is de zoekopdracht beperkt tot maximaal een maand terug, dan gebruik je de query tabel, moet je verder gebruikt dan kun je eenvoudig terug vallen op de hoofdtabel.

Overigens wordt ons rapportage systeem slechts eenmaal per uur bijgewerkt (statstieken worden berekend). Bij de meeste overzichten is 'vandaag' niet interessant omdat deze incompleet is en daardoor veel grafieken zal vertekenen.

Overigens maken wij gebruik van een enkele tabel met daarin 712.116.264.371 records. Een gemiddelde query kost 1.91 seconde.

[toost]

dat is een mooie query dan

iedereen bedankt voor de tips ik zal me er eens verder in gaan verdiepen.

[Hydra]

toost schreef op donderdag 27 maart 2008 @ 14:08:
hmm, ben benieuwd waarom je de maand tabellen een slecht idee vind.

Omdat je tabellen opsplitst vanwege performance optimalisaties die je ook prima kun maken d.m.v. goeie indices. Zo'n tabel-per-maand hack (het is niets meer dan een vieze hack en heeft niks met degelijk dataontwerp te maken) gaat problemen opleveren als je bijvoorbeeld simpelweg een count(*) wil doen om het totaal aantal rows op te vragen, laat staan als je statestieken over meerdere maanden nodig hebt.

Als na het aanleggen van indices performance nog een probleem is, kun je altijd veelgebruikte queries cachen.

[toost]

Vrees alleen dat cached query's niet gaan werken. De bedoeling is dat er 'live' data op het scherm komt en geen cached. En wat betreft de maandtabellen zie ik je punt, het is idd een hack.

[onkl]

toost schreef op vrijdag 28 maart 2008 @ 15:50:
Vrees alleen dat cached query's niet gaan werken. De bedoeling is dat er 'live' data op het scherm komt en geen cached. En wat betreft de maandtabellen zie ik je punt, het is idd een hack.

Maar de data van twee maanden terug veranderd toch niet meer? Dan kan je die toch statisch opslaan en dynamisch "aanvullen" met de meest recente data?

[toost]

Je hebt helemaal gelijk, het kan wel eens voorkomen dat oude data door hercalculatie veranderd wordt, maar dit zal niet vaak voor komen, cached query's staat nu ook op het onderzoeks lijstje

[joppybt]

Niemand_Anders schreef op donderdag 27 maart 2008 @ 17:07:
Overigens maken wij gebruik van een enkele tabel met daarin 712.116.264.371 records. Een gemiddelde query kost 1.91 seconde.

offtopic:
Dit maakt me nieuwsgierig: waarvan wil iemand in vredesnaam 712 miljard records bijhouden?
Bij 10 bytes per record (toch niet raar als je ook indexen hebt) zit je al op 7 terabyte aan data. Hoeveel schijfruimte hangt er onder?
Dan praten we nog niet eens over backups.

[Niemand_Anders]

joppybt schreef op vrijdag 28 maart 2008 @ 21:12:
[...]

offtopic:
Dit maakt me nieuwsgierig: waarvan wil iemand in vredesnaam 712 miljard records bijhouden?
Bij 10 bytes per record (toch niet raar als je ook indexen hebt) zit je al op 7 terabyte aan data. Hoeveel schijfruimte hangt er onder?
Dan praten we nog niet eens over backups.

In ons geval betreft niet niet statistieken, maar financiële transacties van de afgelopen 15 jaar van een aantal grote verzekering maatschappijen. Denk bijvoorbeeld aan de inkoop en verkoop orders van aandelen, koersen per aandeel per 2 uur, afgesloten verzekering polissen, etc. Maar de gegevens worden ook gebruikt om te bepalen of een verkoper nog wel genoeg polissen verkoopt. De marketing afdelingen zullen voornamelijk koers informatie willen en wat cijfers over een bepaalde periode.

Waarom we alles zo precies bijhouden? Je kunt eigenlijk niet anders, het is namelijk niet aan ons om de gegevens de groeperen (bijvoorbeeld per uur), want dan verlies je details. Onze klanten kunnen statistieken opvragen op basis van elke denkbare criteria.

Onze database is in totaal 156TB groot en backups worden uiteraard niet op een tapeje gezet. Elk uur wordt er een backup gemaakt naar een backup machine bij een andere hosting provider gekopieerd.

Overigens is de SAN opgebouwd uit honderden scsi harddisks in een raid 50 opstelling. Ons harddisk rack heeft momenteel ruimte voor 600TB, waarvan zo'n 400Tb in gebruik is. De logschipping bestanden bevatten eigenlijk alleen maar toevoegingen. Een gemiddeld logschipping bestand is zo'n 800mb groot. De logschipping bestanden worden eigenlijk alleen maar gemaakt in het geval een terrorist het de hosting provider (Spaanse Kubus, Rotterdam) opblaast. In dit gebouw zitten ook veel telecom bedrijven (Vodafone, KPN, Versatel) dus niet helemaal ondenkbaar.

Binnenkort (binnen twee jaar) gaan we wel de database aanpassen en dat komt omdat steeds meer database aan elkaar gekoppeld gaan worden. De normale database users kunnen namelijk geen data verwijderen of aanpassen, ze hebben alleen lees en schrijf rechten. Verwijderen van data gebeurt alleen op verzoek en daarvoor is een DBA account nodig. Overigens verwijderen wij data niet direct, maar verplaatsen wij deze 30 dagen naar een 'deletion' database zodat mochten enkele randdebielen erachter komen dat hun verzoek verkeerd was, wij toch nog de data kunnen herstellen. Echter na 30 dagen geld weg = weg en krijg je nooit meer terug.

hopelijk is de beweegreden van de hoeveelheid data wat duidelijker worden. Echter mag ik helaas niet in de echt details treden.

[MMUilwijk]

Niemand_Anders schreef op zaterdag 29 maart 2008 @ 17:07:
[...]

In ons geval betreft niet niet statistieken, maar financiële transacties van de afgelopen 15 jaar van een aantal grote verzekering maatschappijen. Denk bijvoorbeeld aan de inkoop en verkoop orders van aandelen, koersen per aandeel per 2 uur, afgesloten verzekering polissen, etc. Maar de gegevens worden ook gebruikt om te bepalen of een verkoper nog wel genoeg polissen verkoopt. De marketing afdelingen zullen voornamelijk koers informatie willen en wat cijfers over een bepaalde periode.

Waarom we alles zo precies bijhouden? Je kunt eigenlijk niet anders, het is namelijk niet aan ons om de gegevens de groeperen (bijvoorbeeld per uur), want dan verlies je details. Onze klanten kunnen statistieken opvragen op basis van elke denkbare criteria.

Onze database is in totaal 156TB groot en backups worden uiteraard niet op een tapeje gezet. Elk uur wordt er een backup gemaakt naar een backup machine bij een andere hosting provider gekopieerd.

Overigens is de SAN opgebouwd uit honderden scsi harddisks in een raid 50 opstelling. Ons harddisk rack heeft momenteel ruimte voor 600TB, waarvan zo'n 400Tb in gebruik is. De logschipping bestanden bevatten eigenlijk alleen maar toevoegingen. Een gemiddeld logschipping bestand is zo'n 800mb groot. De logschipping bestanden worden eigenlijk alleen maar gemaakt in het geval een terrorist het de hosting provider (Spaanse Kubus, Rotterdam) opblaast. In dit gebouw zitten ook veel telecom bedrijven (Vodafone, KPN, Versatel) dus niet helemaal ondenkbaar.

Binnenkort (binnen twee jaar) gaan we wel de database aanpassen en dat komt omdat steeds meer database aan elkaar gekoppeld gaan worden. De normale database users kunnen namelijk geen data verwijderen of aanpassen, ze hebben alleen lees en schrijf rechten. Verwijderen van data gebeurt alleen op verzoek en daarvoor is een DBA account nodig. Overigens verwijderen wij data niet direct, maar verplaatsen wij deze 30 dagen naar een 'deletion' database zodat mochten enkele randdebielen erachter komen dat hun verzoek verkeerd was, wij toch nog de data kunnen herstellen. Echter na 30 dagen geld weg = weg en krijg je nooit meer terug.

hopelijk is de beweegreden van de hoeveelheid data wat duidelijker worden. Echter mag ik helaas niet in de echt details treden.

Je bent bekend met fenomeen Business Intelligence? Bovenstaande casus is naar mijn smaak perfect geschikt voor een data warehouse concept. Daarbij kun je gebruik maken van datamodeleringstechnieken die per definitie geschikt zijn voor zulke bevragingen. Weinig joins, eventuele snapshots, aggregatietabellen.

Ik zou zelf nooit views gebruiken bij deze grootte. Views zijn traag, zelfs indexed views. Ik ken de performance van MySQL niet, maar een "serieus" DBMS als SQL Server 2005 vindt indexed views in deze order van grootte niet fijn. Ik heb wel eens een clickstream applicatie moeten ontsluiten waarbij we ook te maken hebben gehad met een aantal honderd miljoen rijen. Met behulp van een goed datamodel, goede indexen zijn dan prima en snel rapportages te genereren.

Kortom, ga eens kijken naar BI, en laat de views iig voor wat ze zijn. Dan liever echte fysieke tabellen die je dagelijks / maandelijks update door je tool (en ook daarvoor kun je eventueel gebruik maken van BI tools).