Smart Index Creator: hoe wij stiekem je database sneller maken!

Regelmatig vertellen we je over onze ontwikkelingen via e-mail of blogartikelen, maar niet alles komt ter sprake. Ik denk dat dit vaak komt, doordat wij het vanzelfsprekend vinden ons volledig in te zetten voor een zo goed mogelijke klantbeleving. Het is onze tweede natuur, het zit in ons DNA. Vandaar dat het me een leuk idee lijkt om eens wat te vertellen over een tool die al jaren bij ons op de achtergrond draait, we weinig omkijken naar hebben, maar heel veel moois voor je website en database brengt: de ‘Smart Index Creator’!

Smart Index Creator: hoe wij stiekem je database sneller maken!

Oorsprong

Deze tool is ongeveer drie jaar geleden ontstaan, nadat er een aantal servers overbelast waren. Na enkele seconden naar de ‘top’ te kijken, zagen we al vrij snel wat het probleem was: MySQL liep nogal te stampen, er waren bijna 10 CPU-cores in gebruik!

Smart Index Creator: MySQL-belasting

Vervolgens zijn we binnen MySQL gaan kijken, om zo de query’s die hier verantwoordelijk voor waren op te sporen. Het bleken een handvol query’s te zijn, die ‘full tables scans’ aan het uitvoeren waren (straks meer over ‘full table scans’).

Tabellen van 800 MB waren geen uitzondering. Nu hoeft een grote tabel niet direct een prestatieprobleem op te leveren, maar wanneer het opvragen van data hieruit niet efficiënt gebeurt, dan kan het snel mis gaan. Zeker wanneer je gastenboek door vervelende bots op het internet met vunzige teksten is ‘volgespamd’.

Wij, als webhoster, zitten met dit soort situaties vaak in een spagaat. De klant is verantwoordelijk voor zijn applicatie, maar gebruikt in 99 van de 100 gevallen een standaard CMS. De klant heeft geen of weinig kennis van de interne werking van de software, wat ook volkomen logisch is. Ik heb ook een auto, maar ben geen automonteur.

Voor de ontwikkelaar van de applicatie geldt eigenlijk hetzelfde. Hij programmeert een prachtige applicatie, maar ziet waarschijnlijk zelden de diversiteit waarin het gebruikt wordt, zoals wij dat zien. Daarnaast is er een enorme wildgroei aan verschillende plugins, waar je als ontwikkelaar moeilijk rekening mee kunt houden.

Het voorbeeld waarbij ik dacht, ‘verdikkeme – hier moeten we echt wat mee’, was ‘Phoca Guestbook’. Een pracht van een applicatie, maar dat vinden spammers ook. Wanneer je gastenboek niet up-to-date of veilig is, dan wordt deze binnen no-time voorzien van allerlei niet al te poëtische teksten. Vaak staan de berichten overigens niet openbaar, omdat ze eerst goedgekeurd moeten worden, maar het levert wel onwenselijke belasting op voor je website.

Beter voor de klant, beter voor ons

Wij geloven er heilig in dat alles wat wij in onze klanten investeren, uiteindelijk ook goed voor ons is. De ‘Smart Index Creator’ is hier een goed voorbeeld van. We verhogen hiermee de gebruikerservaring, vaak zonder dat je het doorhebt. Een aantal voordelen:

  • je hoeft zelf de lastige probleemopsporing niet te doen;
  • je website wordt sneller;
  • jij bent daardoor blijer;
  • het scheelt ons waardevolle CPU-cycles in I/O-operaties;
  • waarschijnlijk hebben we zelfs al heel veel bomen gered door de stroombesparing;
  • volgens mij hoef ik het nut van dit soort projectjes niet verder uit te leggen 🙂

Wat is een database-index?

Simpel gezegd: een database-index is een bestand binnen de databaseserver, die ervoor zorgt dat je op een efficiënte manier data kunt opvragen. Het voorkomt dat bij bepaalde query’s er ‘full tables scans’ uitgevoerd worden. Dit type scan is een behoorlijk inefficiënte manier om data op te zoeken. Je kunt het vergelijken met de index van een boek. Stel, je wilt graag weten waar een bepaald woord in het boek wordt gebruikt. Hiervoor pak je de index van het boek erbij, zodat je op die wijze weet op welke pagina je het woord kunt vinden.

Binnen een databaseserver gebeurt ongeveer hetzelfde. Het verschil is dat je hiervoor niet expliciet de index hoeft te raadplegen: de software snapt wanneer dit wel of niet moet gebeuren. Wanneer er geen index aanwezig is die past bij je query, dan vindt er een ‘full table scan’ plaats. Je kunt deze operatie vergelijken met het van voor naar achter doorlezen van een boek, alleen om een bepaald woord te vinden. Liever gebruik je, veel efficiënter, de index.

Database-indexes gaan een stuk verder dan de simpele index die je in veel boeken tegenkomt. Om dit te illustreren, nemen we als voorbeeld een online schoenenwinkel, een keer wat anders dan een autobedrijf. Deze schoenenwinkel heeft een grote uitverkoop, want één van de inkopers heeft een foutje gemaakt door alle schoenen in maat 43 in te kopen. Ze willen snel van deze schoenen af, dus ze besluiten een marketingcampagne op te zetten. Dit bedrijf heeft een behoorlijk grote klantendatabase, inclusief schoenmaat. Wat toevallig! Ze kunnen nu specifiek de groep mensen ‘targetten’ die schoenmaat 43 hebben.

Om de data voor de campagne te verzamelen, gebruiken ze waarschijnlijk de onderstaande query. Om de snelheid van de uitvoer van de query te laten zien, gebruik ik in deze voorbeelden echter een ‘COUNT(*)’.

Smart Index Creator: een voorbeeld van een trage query

Zoals je ziet: het kost het best wat tijd om door de tabel met 5.750.005 klanten te spitten. Dit moet efficiënter kunnen. We voegen een ‘index’ op de schoenmaat-kolom toe:

Smart Index Creator: het toevoegen van een index

Hey, de query gaat nu ineens 40 keer zo snel!

Smart Index Creator: een voorbeeld van een snelle query, na het toevoegen van een index

Wanneer gebruik je liever wel of geen index?

Willekeurig indexes op kolommen aanmaken, is vaak niet zo’n slim plan. Het kan namelijk ook een negatieve impact op de performance hebben. Maak ze dus selectief aan, alléén voor query’s die regelmatig worden uitgevoerd. Daarnaast is het belangrijk dat je de juiste kolomtypes instelt. Voor ronde getallen neem je dus ‘INT’, voor datums ‘DATE’ en voor niet al te lange teksten ‘VARCHAR’. Stel, waar mogelijk, ook een maximale lengte in voor de kolom. Hierdoor kan de databaseserver beter bepalen waar en hoe hij data opslaat en opvraagt.

Voordat je een index aanmaakt, wil je eerst weten hoe lang hij nodig heeft zonder de optimalisatie. Voer de query uit met de ‘SQL_NO_CACHE’ en ‘COUNT(*)’ statements, zoals te zien in de screenshots hierboven. Hiermee voorkom je dat de resultaten uit de ‘query cache’ van MySQL/MariaDB komen en dat PhpMyAdmin ‘sneaky’ een ‘LIMIT’ aan je query toevoegd. Nadat je de index hebt aangemaakt, voer je hem nogmaals uit. Wanneer hij niet minimaal twee keer zo snel is, zou je jezelf af kunnen vragen of een index hier wel op zijn plek is. Gelukkig kun je hem dan gemakkelijk weer verwijderen.

Naast het vergelijken van de snelheid waarmee een query wordt uitgevoerd, raad ik je ook aan om te kijken naar de grootte van de index op schijf, ten opzichte van de volledige tabel. Hij moet significant kleiner zijn, anders is het waarschijnlijk alleen maar verspilling van schijfruimte; het kan zelfs een negatieve invloed op de performance hebben.

Smart Index Creator: de grootte van de index

Welke indexes maken wij voor jou?

Eén keer per week scannen we alle databases op al onze servers. Daarbij controleren we of er indexes bestaan, die opgenomen zijn in de definitieset binnen onze ‘Smart Index Creator’. Wanneer dit niet het geval is, dan maken we deze automatisch voor je aan. Je kunt deze indexes herkennen aan de naam, die beginnen met ‘anta_’. Naast deze scan optimaliseren we, elke woensdag in de vroege ochtend, ook alle databases met de ‘OPTIMIZE-statement’. Hiermee worden indexes opnieuw aangemaakt, waardoor alles weer op volle snelheid werkt, wanneer er bijvoorbeeld in tussentijd veel data is gewijzigd.

De indexes die we aanmaken, zijn volledig transparant voor je website. Hiermee bedoel ik dat er geen query’s aangepast hoeven te worden. Het enige effect wat deze aanpassing heeft, is dat bepaalde query’s die vroeger traag waren ineens ontzettend snel zijn.

Voegen we overal indexes aan toe? Nee, absoluut niet. We doen dit alleen op locaties waarbij bewezen is dat dit een positief effect heeft. Ook doen we het alleen voor applicaties die op grote schaal worden gebruikt. Heb je dus je eigen applicatie geschreven, dan is de kans klein dat we hiervoor een definitie opnemen in onze tool.

Een voorbeeld van een optimalisatie die we een tijdje geleden hebben toegevoegd, is de ‘wp_options’-tabel binnen een WordPress-installatie. De afgelopen jaren is er een enorme wildgroei ontstaan in de hoeveelheid WordPress-plugins, met als gevolg dat deze tabel vaak duizenden regels bevat. Hier is de layout van een WordPress-database oorspronkelijk niet op ontworpen. Dit, terwijl er voor een goed functionerende website alleen de regels nodig zijn waarbij ‘autoload’ op ‘1’ staat. Deze kolom kan twee waardes bevatten: 0 of 1. Hier maken we automatisch een index voor aan. Dit zorgt er in veel gevallen voor dat de tijd die nodig is om een SELECT ‘option_name’ FROM ‘wp_options’ WHERE ‘autoload’ = 1′ uit te voeren van 4 naar 0.01 seconden gaat.

Tot slot

Misschien heb je wel eens gemerkt dat een website van de ene op de andere dag een heel stuk sneller is geworden. Dit zou goed kunnen komen door de Smart Index Creator. We krijgen regelmatig klanten die van een collega-hoster naar ons verhuizen en stomverbaasd zijn over de nieuwe snelheid van de website. Deze tool, samen met een heel hoop andere optimalisaties, draagt hieraan bij. We geloven sterk in simpliciteit, we doen ons best om jou zo min mogelijk te belasten met zaken waar je liever niet mee bezig bent.

Wil je aan de slag, maar heb je nog geen webhostingpakket? Neem dan gerust een kijkje in onze virtuele winkel en breng je website onder bij de beste webhoster van Nederland! Mocht je twijfelen over pakketkeuze, dan kun je natuurlijk altijd contact met ons opnemen. We helpen je graag verder!

P.S. Wil je op de hoogte blijven van alle artikelen, updates, tips en trucs die verschijnen op ons blog? Dat kan! Rechts bovenin via RSS, e-mail, het liken op Facebook, het +1’en op Google+ of het volgen op Twitter.

Deel Tweet +1 Deel