Onderwerp: Trends in SQL

Lastig om een onderwerp te kiezen die de lading dekt, maar goed, hopelijk is dit het.

Wij hebben een simpele database waarin al het netwerk verkeer wordt geplaatst. De tool die dit doet verwerkt elke 5 minuten data en stopt deze per poort/switch in de database. Je kunt nagaan dat deze dus relatief snel groot wordt. Ook het verwerken per maand zal snel zorgen voor een grote load op de database.

Nu wil ik dit graag verkleinen. Het is de bedoeling om trends te maken over specifieke perioden. Nu is de vraag eigenlijk hoe je dit het beste kunt doen. Zijn hier bijvoorbeeld al classes voor beschikbaar? Zoeken in Google geeft snel de verkeerde resultaten, maar misschien zoek ik een ander woord dan 'trend'.

Uhm, waarom in een database? Dat kun je veel beter met een netwerk monitoring tool doen (Cacti, Zabbix, noem maar op).

Ik denk dat het woord wat je zoekt 'correlations' is. Dus dan krijg je bijvoorbeeld een correlatie tussen hoeveelheid traffic en tijd.

Je kunt uiteraard een hoop statistiek op je data loslaten maar dan moet je wel weten wat je precies wilt.

Een andere mogelijkheid is om het tijdsinterval waarop je meet te vergroten. Bijvoorbeeld niet iedere minuut een meting doen maar iedere 5 of 10 minuten.

Oorspronkelijk geplaatst door kjkoster

Uhm, waarom in een database? Dat kun je veel beter met een netwerk monitoring tool doen (Cacti, Zabbix, noem maar op).

Zij slaan hun data net zo goed op in een database. Dat maakt dus niets uit.

In dit geval heb ik het liever in eigen hand. Het gaat me niet om de grafieken, maar om het verbruik. Leuk om te zien of er iets piekt, maar aan de hand van een set data over een maand moet er een administratieve actie ondernomen worden (dat is waarschijnlijk te maken in Zabbix, maar ik hou dit liever gescheiden).

Uiteindelijk wil ik gewoon daadwerkelijk verbruik zien. Zowel voor netwerk als stroom verbruik. Maandelijks moeten deze gegevens verwerkt worden in de administratie en bij bepaalde tresholds zal er ofwel automatisch ofwel handmatig gekeken moeten worden naar de vervolgacties; dit vervolg laat ik dan ook even in het midden omdat het daar niet om draait nu.

We draaien overigens gewoon MRTG die het in SQL plaatst. Misschien dat hier nettere oplossingen voor zijn. In dat geval is er natuurlijk ruimte om te switchen. Al zie ik meer op tegen een leercurve wat betreft storage van bestaande systemen dan dat deze zelf gedeeltelijk ontwikkelt worden. Vraag blijft namelijk vaak hoe databases zijn opgezet en juist daar ontbreekt vaak de documentatie.

Je hebt je dag, week, maand, jaar interval. Ga gewoon stoeien met het gemiddeld aantal punten dat je per graph nodig hebt. Voor de week heb je echt niet alle punten nodig die je met de dag interval behaald hebt. Lijkt me dan ook dat je deze gewoon zou kunnen middelen en 1/7 deel van je dagdata max nodig hebt.

Ik denk dat het als er geen uitgewerkte tool is je moet gaan kijken hoeveel je eruit kan halen zodat het beeld nog steeds betrouwbaar blijft. De dag graph heeft 288 opslag punten om een mooie curve te genereren. Ik kan me voorstellen dat de week graph geen 2016 opslag punten nodig heeft om een soort gelijke lijn te kunnen trekken maar dit ook met 288 punten die samen blokjes gemiddelde vertegenwoordigen van je 2016 totale opslag punten.

^^kan het niet duidelijker omschrijven

// mocht je hier niet uit komen, ik heb ergens nog een php stukje code liggen wat uit de rrd files in strings de dag verbruik kan sommen, week, maand & jaar. Zowel mbits, 95% en gb's . Maar pin me er niet op vast. Zal goed moeten gaan graven, weet dat het er ergens nog moet zijn

Als je geen wayback log wilt behouden zou je in plaats van die INSERT ook gewoon een gemiddelde of cumulatief op kunnen slaan, toch?

Je zou natuurlijk ook een wayback log van 24h kunnen maken en automatisch elke 5 minuten de entry van 24*3600 seconden ouder dan $nu weggooien, met uitzondering van elke 7e logregel (om op die 288 punten van Mikey te blijven). Scheelt 85.71% aan regels in je tabel aan het einde van de maand, maar dan kan je toch nog steeds één etmaal op de 5 minuten nauwkeurig terugkijken (kan ook handig zijn )

Als je huidige data wél wilt behouden dan is volgens mij de makkelijkste oplossing om een cronjob de data om te laten zetten naar een andere tabel en daarin de gemiddelden, totalen en/of cumulatieven per etmaal op te slaan.

de kleinste hoeveelheid data is natuurlijk te bereiken door alles om te zetten naar een formule (a la excel plot fit). Het enige probleem is dan natuurlijk dat je pieken de formula stevig verstoren.

Een andere optie is om de interval te vergroten, en simpelweg beter te bekijken welke data je exact opslaat. (verschil tussen ip, hostnaam, user, proces etc en alleen user (door vaste toewijzign) en hoeveelheid data)

Wat je eigenlijk wil doen is aggregeren door middel van een soort batch jobje die iedere x uur onderhoud uitvoert op je database. Zo werken Enterprise Monitoring tools zoals IBM Tivoli Monitoring ook in hun datawarehouse. Iedere x uur wordt van oude data gemiddelden berekend en opgeslagen. Hiermee worden vervolgens de grafieken en rapporten gevuld.

Oorspronkelijk geplaatst door Sander-

Wat je eigenlijk wil doen is aggregeren door middel van een soort batch jobje die iedere x uur onderhoud uitvoert op je database. Zo werken Enterprise Monitoring tools zoals IBM Tivoli Monitoring ook in hun datawarehouse. Iedere x uur wordt van oude data gemiddelden berekend en opgeslagen. Hiermee worden vervolgens de grafieken en rapporten gevuld.

Weet niet over wat voor database we het hier hebben, maar ipv. batch jobje kan je ook gewoon een stored procedure aanroepen via de scheduler, dit kan oa. in mysql.

Zo werkt Zabbix ook, na bepaalde tijd wordt alle data verzameld en weggeschreven in de trends tabel.

Goede indexing is het codewoord (en daarbij dus genoeg geheugen in je database server).

Edit: Even iets meer uitleg:
Multi-column-indexes waar velden inzitten waar je op WHERE'ed en op GROUPED. En dan letten op de goede volgorde. Want let er op (iig in MySQL): Per tabel kan er maar ÉÉN index gebruikt worden per query.

Oorspronkelijk geplaatst door VisualWeb

Want let er op (iig in MySQL): Per tabel kan er maar ÉÉN index gebruikt worden per query.

Dat is al een tijdje niet meer voor sommige queries. Ook kan je door goeie subselects te gebruiken vaak de snelheid verhogen (en meerdere keys gebruiken)

Goed, dan blijkt toch weer dat mijn programmeer kennis niet van een al te hoog niveau is en dat ik misschien weer eens teveel wil voor wat ik zelf kan maken. Hoe interessant ik het ook vind, indexing en stored procedures gaan boven mijn pet :-)

Voor nu opgelost met een klein script wat elke dag alle data verwerkt. Bleek achteraf relatief simpel omdat ik al wat classes had gemaakt die totalen kon aanmaken. Het verwerken was dus een makkie.

Het gaat uiteindelijk niet om hele grote sets data. Ik gok momenteel op een kleine 45000 records die dagelijks worden bijgevoegd. In ieder geval bedankt, brengt me in ieder geval weer op wat nieuwe inzichten en misschien dat ik toch eens dieper in sommige zaken moet gaan duiken :-)

Een database is minder efficiënt voor write-once read-once storage. Als je iedere dag maar records blijft toevoegen dan wordt 'ie snel erg groot en als je er dan niet regelmatig in zoekt is dat niet de meest goedkope opslag.

Je kunt na het verwerken na de relevante periode (gebaseerd op bijvoorbeeld de facturatie termijn?) de getallen uit de database halen en als tekst per datum in een gzip bestandje wegschrijven, kleiner kan haast niet en tegenwoordig kan dat met circa twee regels programma.

Kijk even na hoe en RRD file ineen steekt, daar slaan bvb zabbix en cacti hun data in op.

Om trends bij te houden ga je oude data moeten samenvoegen en gemiddelden bij houden. Zo ga je data die een maand oud is bvb per uur samenvoegen, ipv per 5 minuten. Nog oudere data ga je per 6 uur samenvoegen, ... etc etc...