FinalFrag.be

Als eerste artikel als gastschrijver op deze blog, wil ik graag een onderwerp aanhalen dat toch wel belangrijk is bij het beheren van een uitgebreide database, zoals bijvoorbeeld die van Spacetarion, namelijk het in de gaten houden van de bevolking van je database.

Het probleem

Bij het creeëren van je datamodel heb je wellicht rekening gehouden met het optimaal gebruiken van de datatypes die MySQL aanreikt. Een voorbeeld is het verschil tussen de tinyint en de smallint, een tinyint kan maximum waarde 255 bevatten en een smallint maximum 65 535 (wanneer ze worden ingesteld als unsigned). In een optimale database is er goed afgewogen welke datatypes er gebruikt worden voor welk veld. De unieke sleutel van je gebruikerstabel zal waarschijnlijk geen tinyint zijn, tenzij je maar 255 leden verwacht. Maar hoeveel leden verwachten je dan wel? Omdat dit moeilijk in te schatten is, hebben Massimo en ikzelf een tool geschreven die ons toont hoe ver de tabelvelden ‘bevolkt’ zijn. Met de Data Type Usage Profiler (DTUP) kan een volledige database beheerd worden en kunnen de volgende datatypes gecontroleerd worden op hun bevolkingsgraad:

• alle integers (tinyint, smallint, mediumint, int en bigint)
• alle teksten (tinytext, text, mediumtext en longtext)
• varchar (met eigen ingestelde lengte)

In de DTUP zijn er 4 niveaus of zones waarin een datatype zich kan bevinden. Elke zone heeft een kleuraanduiding die terugkomen in de tabelweergave van de databasetabel en in de grafische weergave. De mogelijke zones zijn:

1. > 90% – Rood
2. > 75% – Licht rood
3. > 50% – Oranje
4. < 50% – Groen

Als een datatype zich in de groene of oranje zone bevindt, is er nog geen reden tot het doorvoeren van database aanpassingen. Als echter de het bezettingspercentage > 75% is, dan kan misschien geopteerd worden om een datatype hoger te nemen of de lengte aan te passen. De test op de datatypes uit de reeks van integer gaat kijken naar de hoogste waarde in de tabel. De varchar en text reeks gaat rekening houden met de lengte die het aantal karakters in beslag neemt.

DTUP is gemaakt met het vertrouwde CodeIgniter en Smarty. De pagina die getoond wordt door de controller te bezoeken, bestaat uit 3 delen.

1. Overzicht: geeft het aantal gevallen weer die voorkomen in elke zone op een pie chart;
2. Tabellenlijst: alle tabellen die voorkomen in de gebruikte database;
3. Databasetabellen: bevat een tabel die weergeeft hoeveel waarden er in gebruik zijn en hoeveel er maximum gebruikt mogen worden met als laatste kolom in de tabel de procentuele verhouding tussen aantal gebruikte en maximum te gebruiken. Een bar chart vervolledigt de tabel door op een grafische manier de verhouding nogmaals weer te geven. Zowel de tabel als de grafiek maken gebruik van de kleuren die bij de zones horen.

Waar jullie allemaal op wachten, screenshots

Deze pie chart toont de beknopt overzicht van alle velden uit de database. Het grootste deel in de voorbeeld database is gezond (groen) en enkele velden bevinden zich in de kritische zone. De velden die vooral in die rode zone liggen zijn velden waarvan we op voorhand de maximale lengte kennen, bijvoorbeeld omdat we een MD5 hash gebruiken.

Bovenstaande schermafbeelding geeft duidelijk weer waar dit artikel en DTUP eigenlijk om draait. Alle velden van een bepaalde tabel worden getoond met informatie zoals aantal gebruikt, maximum te gebruiken en de procentuele verhouding. Ook het datatype is weergegeven zodat er van hieruit beslist kan worden welk datatype misschien de opvolger kan worden van het huidige, te kleine, datatype.

Bovenstaand voorbeeld kan een deel zijn van een gebruikerstabel. Zoals al gezegd, wisten we op voorhand dat het wachtwoord 32 tekens lang ging zijn, dus dat rode veld is geen verrassing. Het veld waarin de gebruikersnaam wordt bijgehouden is ook volledig gebruikt. Er heeft zich namelijk een gebruiker geregistreerd met een gebruikersnaam die net 32 karakters is. Nu is de vraag, houden we de beperking op die 32 tekens, of breiden we het veld uit? Dat hangt natuurlijk af van het doel. In ons voorbeeld stellen we dat er van hogerhand beslist is dat een gebruikersnaam nooit langer mag zijn dan 32 tekens.

Dit voorbeeldje ging over een varchar veld. Het is een heel ander verhaal wanneer het gaat om een unieke sleutel die automatisch verhoogd wordt met AUTO_INCREMENT.

In onze instellingen tabel is er gekozen om met een tinyint te werken als unieke sleutel die maximum 255 records toelaat. Zoals te zien is in bovenstaande figuur is ondertussen waarde 240 bereikt. Als er nog meer instellingen bijkomen, is het misschien niet onbelangrijk om op dit moment het datatype tinyint op te krikken naar een smallint die tot waarde 65 535 aankan. Voor deze tabel is het natuurlijk geen probleem als de unieke sleutels op zijn. Voor een gebruikerstabel, waarvan de ID zichzelf verhoogt, kan dit veel kritischer zijn omdat, eenmaal de grens bereikt is, er geen leden meer kunnen registreren en dat wil je natuurlijk niet.

Voila

Ik hoop dat ik jullie een nieuwe kijk heb kunnen geven op de manier waarop Massimo en ikzelf Spacetarion onderhouden.

Dit zelfgeschreven profiler tooltje met behulp van CodeIgniter, Smarty en een MySQL database laat ons toe nauwgezet de bevolking van onze datatypes te monitoren. Deze pagina is toegankelijk vanuit het beheerpaneel van Spacetarion dat opgebouwd is met ExtJS. Binnenkort zullen onze trouwe volgers ook een blik kunnen werpen op de adminkant van Spacetarion. Zo weten jullie des te meer waar wij mee bezig zijn. Met dat paneel en deze DTUP kunnen wij ons uitgebreid systeem opvolgen om het zo stabiel mogelijk te presenteren aan de spelers, hopelijk aan jou dus.

Verbeteringen? Altijd mogelijk…

Mogelijke verbeteringen zijn er altijd, zo zou ik nog kunnen voorzien dat het datatype vanuit de DTUP gewijzigd kan worden. Een tweede verbetering die ik nog zou kunnen doorvoeren, is alle autonummeringvelden als speciaal veld behandelen waarbij een verhouding van > 75% echt kritiek is. In het geval van kritieke autonumeringvelden kan ik via een cronjob mezelf via mail op de hoogte houden en direct ingrijpen. Een stap verder is dat het systeem zichzelf kan verbeteren door automatisch zijn datatype te verhogen en me via mail op de hoogte stellen dat deze actie ondernomen werd.

De DTUP staat nog maar in z’n kinderschoenen en kan dus nog verder uitgebreid worden. Misschien heb jij wel een suggestie die DTUP kan omvormen tot een mooi product dat voor iedereen te downloaden zal zijn in de toekomst? Laat het ons zeker weten!

Delen

Facebook      Twitter     TinyUrl: http://tinyurl.com/kvtaez