Sql-optimalisatie

Eén grote versus veel kleine queries

Inleiding

In de afgelopen periode hebben we bij Tweakers diverse belangrijke stukken code herschreven. Eerder dit jaar is ons vernieuwde reactiesysteem geïntroduceerd, en in april en mei zijn we bezig geweest om het karmasysteem te herschrijven.

Daarbij is niet alleen de functionaliteit aangepakt, ook de prestaties van de componenten zijn opnieuw bekeken. Bij beide systemen zijn de prestaties bij het gebruik van de database dominant voor de totale benodigde tijd. Een goede vorm om de data op te slaan, met de juiste indices, en het efficiënt uitlezen van de gegevens zijn dus erg belangrijk. Voor beide voorbeelden gebruiken we onze centrale MySQL-database.

Op internet zijn allerlei uitspraken over de snelheid van sql-databases te vinden. In de afgelopen jaren zijn er bijvoorbeeld allerlei NoSQL-databases verschenen en vaak worden dan een of meer negatieve aspecten genoemd die met een dergelijke nieuwe database beter gaan. Vaak wordt gesteld dat het voldoen aan alle vier de componenten van acid de prestaties en schaalbaarheid in de weg zit. Daarbij worden dan vooral consistency en isolation zwakker uitgewerkt in die systemen.

Ook de complexe queries die met sql mogelijk zijn, worden genoemd; die mogelijkheid zou slecht zijn voor de prestaties en bovendien helemaal niet nodig zijn. Het kunnen join-en van tabellen en de mogelijkheden voor subqueries zijn de vaste voorbeelden daarbij. Het gaat dan soms zelfs zover dat in één adem geadviseerd wordt om die zaken ook te vermijden als je wél sql blijft gebruiken. Het zou dan eveneens slecht zijn voor de prestaties en lastig te verenigen zijn met sharding-functionaliteit. Het advies is dan om de queries zo simpel mogelijk te houden en liefst alleen via de primary key in een tabel te zoeken. Complexe handelingen, zoals het combineren van data, kunnen tenslotte ook in de applicatielaag gedaan worden.

Als je een object relational mapper gebruikt, zoals Doctrine, dan wordt dat advies effectief standaard opgevolgd. Bij een orm moet er meestal zelfs moeite gedaan worden om van dat model af te wijken. Een join gebruiken betekent vaak extra code toevoegen.

Hoewel het altijd verstandig is om code eenvoudig te houden, is het wel zonde als daardoor grote prestatiewinsten blijven liggen. De adviezen over sharding van data zijn tenslotte alleen relevant als dat echt gebruikt wordt. Als er geen sharding wordt gebruikt, is het juist handig om data uit diverse queries in de database al te koppelen. Dat kan flink wat overhead schelen; denk aan de losse round-trips van query en data, rechtenchecks en allerlei aanverwante handelingen die dan in de database minder vaak gedaan hoeven te worden.

Er is daarbij natuurlijk wel verschil tussen de diverse platforms. Op sommige platforms, waaronder veel soorten applicaties in java-applicatieservers, kunnen orm's gegevens in het geheugen van de applicatie cachen. In zulke gevallen worden al round-trips bespaard, maar dat valt of staat dan met de effectieve hitratio van de cache en de kwaliteit van de synchronisatie met de database. De hier gegeven adviezen gelden vooral voor toepassingen waarbij dergelijke caches niet praktisch zijn, zoals meestal bij php het geval is.

Wat ons betreft valt de database van Tweakers met 219GB trouwens niet in de orde van grootte om over te stappen op sharding of andere technieken om verscheidene servers tegelijk te gebruiken. Overigens hebben we er wel op diverse manieren voor gezorgd dat de MySQL-database niet voor alle gegevens gebruikt hoeft te worden. We hebben er bijvoorbeeld voor gekozen om bepaalde stukken data in MongoDB op te slaan of in memcached te cachen en veel van de informatie wordt via onze Java-omgeving opgevraagd.

In dit artikel beschrijven we twee toepassingen waarbij de databaseprestaties voor ons belangrijk waren. Daarbij laten we stapsgewijs een aantal optimalisaties zien die significante verbeteringen in de prestaties gaven. Bedenk wel dat dit artikel niet gaat over het plaatsen van de juiste indices of het optimisaliseren van de instellingen van een database. Dit is een parallelle taak waarbij bekeken wordt of de database wel optimaal ingezet wordt, maar een goed geoptimaliseerde tabelstructuur en database zijn uiteraard ook belangrijk voor goede prestaties.

Queries voor de reacties

Volgens sommige bezoekers zijn de reacties onder artikelen vaak nog interessanter dan het artikel zelf. Daarom hebben we veel moeite gedaan om de reacties efficiënt op te slaan en uit de database te kunnen halen, zodat we direct al reacties bij een artikel kunnen tonen. Bovendien leidt snelle verwerking tot betere schaalbaarheid en ruimte om bijvoorbeeld verschillende sorteringsopties en paginagroottes aan te bieden.

Doordat de reacties in boomvorm worden gepresenteerd, is het niet triviaal om te bepalen wat de beste manier van opslaan in een relationele database is. Bij de eenvoudigste opslagvariant, ook wel adjacency list genoemd, worden reacties domweg met een parent id opgeslagen en recursief uitgelezen om de child nodes op te halen. Daarnaast zijn er complexere modellen, zoals de nested set. Dat laatste model is vooral nuttig als er relatief veel gelezen wordt en daarbij complete deelbomen - de volledige boom is ook een deelboom - worden opgehaald. Het nadeel van nested sets is dat ze behoorlijk complexe logica nodig hebben en bovendien bij elke nieuwe node gemiddeld de helft van alle nodes moeten aanpassen.

Omdat deelbomen voor ons niet relevant zijn en de insert performance juist wel, hebben we het simpel opgelost. We slaan bij elke reactie niet alleen de id van de reactie erboven op, maar bovendien die van het artikel waarbij de reactie is geplaatst. De tabel ziet er in beginsel als volgt uit:

Kolom	Type
id	int not null primary key
parentId	int null default null
articleTypeId	int not null
articleId	int not null
userId	int not null
insertTime	datetime not null default current_timestamp
content	text not null

Het articleTypeId is bij ons nodig omdat we een centrale opslag van de reacties willen, maar geen gecombineerde nummering voor nieuws, reviews en andere artikelen.

Met deze structuur kunnen we met één select statement alle reacties op een artikel ophalen. De reacties worden daarna in de php-code in een boomstructuur geplaatst, in de goede volgorde gezet en vervolgens gepagineerd getoond. Dat leidt ertoe dat we standaard 'te veel' reacties ophalen, maar het heeft verder tot gevolg dat we veel makkelijker kunnen sorteren en pagineren. De sortering op reacties kijkt bijvoorbeeld niet alleen naar de score van een specifieke reactie, maar ook naar de scores van alle subreacties.

Hieronder beschrijven we een standaardimplementatie voor een dergelijk reactiesysteem met daarna een aantal optimalisaties voor het benaderen van de database. Daarbij gaan we ervan uit dat de orm voor elk gerelateerd object, zoals de gebruiker die een reactie schreef, opnieuw de database moet benaderen, tenzij anders vermeld. Daarbij laten we overigens alle implementatiedetails weg uit de code, het gaat hier puur om de basis van het systeem en welke queries er in de database worden gedaan. Sterker nog, de voorbeeldcode is niet bedoeld om perfect aan allerlei codestandaarden te voldoen of om zelfs maar te werken, hij dient louter als illustratie.

Voor de bespreking van prestaties gaan we steeds uit van een nieuwsbericht met 153 reacties. Dat is op Tweakers een heel gebruikelijk aantal en dus lang niet het populairste artikel. De sinterklaasacties van de afgelopen drie jaar hadden met respectievelijk 4.464, 4.041 en 5.299 veel meer reacties. Daarom nemen we ook die laatste mee om een reëel worst case scenario te kunnen geven.

Standaardimplementatie met orm

We beginnen met een eenvoudige standaardimplementatie. Voor elke reactie moet de orm de gebruiker en de eventuele subreacties los ophalen. Daarnaast willen we natuurlijk kunnen tonen of een reactie al is beoordeeld. Daarom moet ook het eventueel gegeven oordeel per reactie worden opgehaald.

function displayArticleComments(Article $article, User $currentUser)
{
// SELECT * FROM comments
   // WHERE articleId = ? AND articleTypeId = ? AND parentId = 0
   $comments = $article->getComments();

   displayCommentList($comments, $currentUser);
}

function displayCommentList($comments, User $currentUser)
{
foreach($comments as $comment)
{
// SELECT * FROM users WHERE id = ?
      $user = $comment->getUser();

      // SELECT * FROM ratings WHERE commentId = ? AND userId = ?
      $givenRating = $comment->getRating($currentUser);

      displayCommentBody($user, $comment, $givenRating);

      // SELECT * FROM comments WHERE parentId = ?
      $subComments = $comment->getSubComments();

      // Recursion
      if( ! empty($subComments))
displayCommentList($subComments, $currentUser);
   }
}

De vraagtekens in bovenstaande queries zijn de standaardnotatie voor prepared statements. Op die plek moet nog de waarde van een variabele worden ingevoegd. Of er daadwerkelijk prepared statements worden gebruikt is voor de prestaties overigens niet heel relevant.

Bovenstaande implementatie is lekker simpel. De queries zijn kort en voldoen perfect aan de eerder genoemde adviezen om ze join-vrij te houden. De prestaties ervan zijn echter niet zo goed; er zijn naast de initiële query nog drie queries nodig voor elke reactie. Dat komt neer op 460 queries in totaal en met het artikel met 5.299 reacties zelfs op 15.898 queries!

Optimalisatie 1: geen recursie

Zoals eerder gezegd bouwt de reactiecode van Tweakers zelf de boomstructuur op en is ook de bij reacties op diepere niveaus bekend bij welk artikel ze zijn geplaatst. Het is daarom niet nodig om de reacties recursief uit de database te halen: dat scheelt een query voor iedere getoonde reactie. Het totaal komt dan op 307 queries voor het normale voorbeeld en een nog altijd forse 10.599 bij het grote voorbeeld. De implementatie slaan we over, die is bijna hetzelfde als de code van de volgende optimalisatie. Overal waar een select list staat, kan een * gedacht worden

Optimalisatie 2: alleen selecteren wat nodig is

De 'select * from' in de bovenstaande twee varianten heeft tot gevolg dat er onnodig data wordt opgehaald. Zo is vooral bij de gebruikers veel te besparen. Alleen de nickname, het abonnementstype en de avatar zijn nodig, terwijl er veel meer kolommen in onze gebruikerstabel zitten. De code ziet er ongeveer zo uit:

function displayArticleComments(Article $article, User $currentUser)
{
// SELECT id, userId, aggregatedScore, insertTime,
   //  lastUpdateTime, parentid
   // FROM comments WHERE articleId = ? AND articleTypeId = ?
   // Note: getComments returns a tree of comments with full nesting
   $comments = $article->getComments();

   displayCommentList($comments, $currentUser);
}

function displayCommentList($comments, User $currentUser)
{
foreach($comments as $comment)
{
// SELECT nickname, showSubscriptionBadge, subscriptionId, avatar
      // FROM users WHERE id = ?
      $user = $comment->getUser();

      // SELECT score FROM ratings WHERE commentId = ? AND userId = ?
      $givenRating = $comment->getRating($currentUser);

      // No query here
      $subComments = $comment->getSubComments();

      displayCommentBody($user, $comment, $givenRating);

      // Recursion
      if( ! empty($subComments))
displayCommentList($subComments, $currentUser);
   }
}

De winst bij deze optimalisatie is overigens beperkt. Er wordt weliswaar minder data van de database naar de php-code verstuurd, maar het totale aantal queries is gelijk gebleven aan dat van de vorige optimalisatie.

Batchqueries voor de reacties

Het is ook mogelijk om fors minder queries uit te voeren. In de onderstaande voorbeelden gaan we ervan uit dat de orm ook een bulk-modus heeft en later zelfs aan de hand van joins verscheidene objecten tegelijk kan maken. Daardoor kan dan een grote hoeveelheid gebruikers en beoordelingen aan de hand van een lijst van id's opgehaald worden uit de database.

Optimalisatie 3: bulk load

Als alle reacties, gebruikers en gegeven beoordelingen in bulk worden opgehaald zijn er slechts drie queries nodig. Sql kent daarvoor de in-operator. Daarvoor is dan natuurlijk wel vereist dat de reacties twee keer doorlopen worden, eerst om alle relevante id's te verzamelen en later nog een keer voor de weergave van de boom. De code ziet er ongeveer zo uit:

function displayArticleComments(Article $article, User $currentUser)
{
// SELECT id, userId, aggregatedScore, insertTime,
   //  lastUpdateTime, parentid
   // FROM comments
   // WHERE
   //  articleId = ? AND articleTypeId = ?

   // Note: getComments returns a tree of comments with full nesting
   $comments = $article->getComments();

   // SELECT id, nickname, showSubscriptionBadge, subscriptionId, avatar
   // FROM users WHERE id IN(?, ?, ...)
   $userList = $article->getUsers($comments);

   // SELECT commentId, score, insertTime FROM ratings
   // WHERE commentId IN(?, ?, ...) AND userId = ?
   $ratings = $article->getRatings($comments, $currentUser);

   storeCommentData($comments, $userList, $ratings);
   displayCommentList($comments, $currentUser);
}

function displayCommentList($comments, User $currentUser)
{
foreach($comments as $comment)
{
// No query here
      $user = $comment->getUser();
      // No query here
      $givenRating = $comment->getRating($currentUser);
      // No query here
      $subComments = $comment->getSubComments();
      displayCommentBody($user, $comment, $givenRating);

      // Recursion
      if( ! empty($subComments))
displayCommentList($subComments, $currentUser);
   }
}

Bovenstaande code levert een forse besparing in de hoeveelheid queries op. Er zijn er nog slechts drie in totaal, ongeacht hoeveel reacties er zijn. Het is bovendien nog steeds in de geest van de originele serie tips: geen joins gebruiken en bij voorkeur alleen via de primary key uitlezen.

Optimalisatie 4: joins gebruiken

Wat als we toch joins gebruiken? Het totale aantal queries kan dan terug naar één. Vergeleken met de vorige variant zal de database niet eens zo veel minder werk hebben, maar in de php-code zelf wordt ook nog de extra loop bespaard en die twee round-trips naar de database minder zijn eveneens mooi meegenomen.

De code wordt uiteindelijk zoiets:

function displayArticleComments(Article $article, User $currentUser)
{
// SELECT c.id, c.userId, c.aggregatedScore, c.insertTime,
   //    c.lastUpdateTime, c.parentid,
   //    u.nickname, u.showSubscriptionBadge, u.subscriptionId, u.avatar,
   //    r.score, r.insertTime as ratingTime
   // FROM comments c
   //    JOIN users u ON u.id = c.userId
   //    LEFT JOIN ratings r ON r.commentId = c.id AND r.userId = ?
   // WHERE
   //    articleId = ? AND articleTypeId = ?

   // Note: getComments returns a tree of comments with full nesting
   // Note: getComments also returns user-objects and ratings for the current user
   $comments = $article->getComments();

   displayCommentList($comments, $currentUser);
}

function displayCommentList($comments, User $currentUser)
{
foreach($comments as $comment)
{
// No query here
      $user = $comment->getUser();
      // No query here
      $givenRating = $comment->getRating($currentUser);
      // No query here
      $subComments = $comment->getSubComments();
      displayCommentBody($user, $comment, $givenRating);

      // Recursion
      if( ! empty($subComments))
displayCommentList($subComments, $currentUser);
   }
}

Resultaten

De vraag is nu natuurlijk welke van deze vijf varianten het snelst is. Maakt het echt wat uit of er veel of weinig queries worden gedaan? En die join, helpt die ook nog?

Om dat te bepalen hebben we de tijd gemeten die ieder van de vijf varianten nodig heeft in onze testomgeving. Daar gebruiken we onze voormalige master-databaseserver met een volledige kopie van de productiedata. Hoewel die server ondertussen ruim vijf jaar oud is, zijn de prestaties nog behoorlijk vergelijkbaar met die van onze actuele productiedatabase. Belangrijk daarbij is dat (ook) deze server, net als in productie, ruim voldoende geheugen heeft om alle reacties in het ram-geheugen te houden en vrijwel dezelfde configuratie heeft voor Linux en MySQL.

Zoals hierboven te zien is, zijn de laatste twee varianten significant sneller. Bij het artikel met 153 reacties is de bulk load met join zelfs ruim zeventig keer zo snel. Bij de variant met 5.299 reacties is het verschil iets kleiner. Met bijna 49 keer zo snel is dat echter nog steeds zeer significant. Daarbij gaat die grote variant van een voor de gebruiker onacceptabele wachttijd van 11 seconden naar een heel aardige 0,2 seconde.

Het blijft natuurlijk wel belangrijk om dit soort aanpassingen goed bij de beoogde toepassing te laten passen. Als er inderdaad sharding wordt gebruikt of de tabellen nog veel groter zijn, dan kan de bulk load, zeker met joins, wel degelijk minder efficiënt blijken te zijn. Wellicht is dan een variant op de drie losse bulk loads handiger.

Het advies om joins te vermijden en de queries zo simpel mogelijk te houden is echter in ons voorbeeld geen goed advies. Het kost niet alleen in de php-laag meer tijd, ook de database heeft het daardoor uiteindelijk drukker.

Karma: eenvoudige berekening

Zoals in de inleiding van dit artikel is genoemd hebben we ook aan ons karmasysteem gewerkt. Een belangrijke wijziging is dat we de hoeveelheid karma per onderwerp - tag, categorie, merk, serie of product - wilden kunnen bepalen. De code was echter al behoorlijk oud en de opslag was niet geschikt voor dit scenario. Daarom hebben we het moment aangegrepen om de code te herschrijven en de criteria voor de te verdienen karma te actualiseren. Zo zijn diverse berekeningen vereenvoudigd en geharmoniseerd, en er zijn zelfs een paar bugs in de berekeningen gefikst

Doordat we ook het verdiende karma per onderwerp uit het verleden wilden weten, moesten we al het karma met terugwerkende kracht opnieuw berekenen. In de ruim vijftien jaar dat Tweakers bestaat, hebben gebruikers er echter tientallen miljoenen bijdragen op geplaatst. Dat is normaal gesproken natuurlijk erg tof, maar het vereist bij dit soort berekeningen wel extra aandacht. Met een verkeerde aanpak kan het zomaar verschillende dagen of weken duren voordat een berekening klaar is.

Aangezien we nog niet wisten of de vernieuwde criteria voor de karmaberekeningen goede uitkomsten zouden geven, was het extra belangrijk om de berekening efficiënt te maken. Het is tenslotte niet fijn als je er na een paar dagen rekenen achterkomt dat er een aanpassing nodig is en je opnieuw moet wachten…

In deze en de volgende pagina's beschrijven we hoe we het voor elkaar hebben gekregen dat al die data in slechts twee à drie uur kan worden doorgerekend. Ook hier was weer de kern: verminder het aantal queries en zorg ervoor dat de database zo weinig mogelijk data naar de client stuurt.

Let op: de voorbeelden hieronder en op de volgende pagina's zijn bedoeld als illustratie. Ze geven ongeveer aan hoe een en ander algoritmisch werkt, maar tonen niet exact welke scores in ons karmasysteem worden gegeven. De uitkomsten uit de berekeningen van dit artikel zullen dan ook verschillen van de uitkomsten die we in de productieomgeving geven

Berekeningen voor ratings

Het eerste voorbeeld is eenvoudig, dat gaat bijvoorbeeld over de beoordelingen van gebruikers van productreviews en/of reacties. Voor iedere rating wordt in deze voorbeeldimplementatie domweg één punt gegeven. De originele implementatie aanpassen zodat er altijd één per moderatie zou worden gegeven, zou zoiets hebben opgeleverd:

function storeRatingKarma()
{
$result = $mysqli->query("SELECT id, userId, time FROM ratings");
   while($row = $result->fetch_assoc())
{
// Store 1 karma per rating
      $mysqli->query("INSERT INTO per_item_karma
                  (itemType, itemId, userId, time, karma)
                  VALUES(15, ?, ?, ?, 1)"
      );
   }
}

Erg efficiënt is het niet en dat is ook te zien in de benchmarks.

Optimalisaties 1 en 2: transacties en prepared statements

De originele code gebruikte geen prepared statements voor het gebruikte insert-statement en werkte ook niet in een transactie. Wel of geen transactie is op zich niet zo belangrijk voor de prestaties, maar dat autocommit dan ook uitgeschakeld wordt wel. Beide worden vaak genoemd als manier om dit soort operaties te versnellen. In werkelijkheid haalden beide veranderingen weinig uit voor de looptijd van de code. Of dat een speciale oorzaak heeft, hebben we verder niet onderzocht.

Optimalisatie 3: inserts groeperen

MySQL biedt de mogelijkheid om insert-statements te groeperen door verschillende rijen tegelijk te specificeren. Een dergelijke query ziet er dan zo uit:

INSERT INTO per_item_karma
(itemType, itemId, userId, time, karma)
VALUES
 (15, 5, 141, 11412121, 1),
...,
 (15, 6, 631, 11412122, 1);

Er is op zich geen limiet aan het aantal rijen dat zo kan worden gecombineerd, zolang de query maar binnen een maximale grootte blijft. Andere databases kennen soortgelijke mogelijkheden; PostgreSQL heeft bijvoorbeeld naast de bovenstaande insert-mogelijkheid ook nog het copy-statement. In php-code hebben we dit getest met honderd en duizend rijen gecombineerd tot één insert-statement. De code daarvoor is helaas wat complexer en er moet ook rekening worden gehouden met het feit dat er niet altijd een veelvoud van honderd of duizend rijen zal zijn:

function storeRatingKarma(mysqli $mysqli)
{
$result = $mysqli->query("SELECT id, userId, time FROM ratings");

   $rows = array();
   $i = 0;

   while($row = $result->fetch_assoc())
{
$rows[] = "(15, ?, ?, ?, 1)";

      if(++$i % 1000 == 0)
{
// Store 1 karma per rating with 1000 ratings at a time
         $mysqli->query("INSERT INTO per_item_karma
            (itemType, itemId, userId, time, karma)
            VALUES " . implode(",", $rows));
         $rows = array();
      }
}

// Store the last few rows
   if( ! empty($rows))
{
$mysqli->query("INSERT INTO per_item_karma
            (itemType, itemId, userId, time, karma)
            VALUES " . implode(",", $rows));
   }
}

Dit levert uiteraard een reductie van het totale aantal inserts op, met respectievelijk een factor 100 of 1000.

Optimalisatie 4: insert + select gebruiken

Het kan echter met nog minder queries. SQL kent ook de mogelijkheid om een select en een insert direct aan elkaar te koppelen. De query daarvoor is in dit voorbeeld erg eenvoudig:

INSERT INTO per_item_karma
(itemType, itemId, userId, time, karma)
SELECT 15, id, userId, time, 1 
 FROM ratings;

Resultaten van optimalisaties

Om te bekijken wat de diverse varianten deden, hebben we ook deze op de testomgeving uitgevoerd. We hebben het beperkt tot een test met 100.000 en een met 500.000 beoordelingen. In werkelijkheid zijn er bijna 6 miljoen beoordelingen gegeven, maar omdat deze twee resultaten vrijwel lineair opschaalden, hebben we om tijd te besparen die test niet gedaan.

Hier zien we opnieuw een groot verschil tussen de variant met veel losse queries en de gegroepeerde varianten. De beste tijd werd neergezet door de gecombineerde insert+select. Naast de winst in snelheid is ook de eenvoudigere query een welkome bijkomstigheid. Bovendien maakt het voor de insert+select niet uit hoe snel de verbinding tussen de php-code en de database is; de bijbehorende gegevens worden tenslotte niet tussen de php-code en de database heen en weer gestuurd.

Karma: complexere berekening

Op de vorige pagina toonden we een triviale karmaberekening voor de gegeven beoordelingen, maar niet al onze karmaberekeningen zijn zo simpel. Gaat dezelfde truc op voor complexere varianten? Is MySQL bij complexere berekeningen net zo snel als php?

In de praktijk konden we al onze karmaberekeningen omzetten naar sql. Daarbij hielp het dat de berekeningen al vereenvoudigd werden. Voor de reacties werd bijvoorbeeld voorheen een berekening gebruikt waarbij er verschil zat tussen een reactie die door minimal één 'expert moderator' was beoordeeld en een andere reactie. Verder werd een aantal correcties toegepast om te voorkomen dat een +2-reactie uiteindelijk slechter kon uitkomen dan een +1-reactie. Dat soort dingen hebben we uit de berekeningen gegooid - het laatste is nu in het geheel niet meer mogelijk - en de uiteindelijke versie werd daardoor een stuk behapbaarder.

Implementatie in php

De php-implementatie is uiteindelijk ongeveer dit geworden:

function storeCommentKarma()
{
$result = $mysqli->query("SELECT id, userId, time, score, message
                  FROM comments");

   while($row = $result->fetch_assoc())
{
$karma = calculateCommentKarma($row['score'], $row['message']);
      $mysqli->query("INSERT INTO per_item_karma
            (itemType, itemId, userId, time, karma)
            VALUES(3, ?, ?, ?, ?)");
   }
}

function calculateCommentKarma($score, $message)
{
$config = [
-2 => ['base' => -10, 'perChar' => 0.01],
      -1 => ['base' =>  -5, 'perChar' => 0.01],
       0 => ['base' =>   0, 'perChar' => 0.01],
       1 => ['base' =>   5, 'perChar' => 0.02],
       2 => ['base' =>  10, 'perChar' => 0.04],
       3 => ['base' =>  15, 'perChar' => 0.08]
   ];
   
   $messageLength = strlen($message);
   $karma = $config[$score]['base'] 
+ ($messageLength * $config[$score]['perChar']);
   
   return min($karma, 100);
}

Kortom, de te verdienen hoeveelheid karma hangt af van de lengte van de reactie, maar de precieze waardering per lengte hangt weer van de ontvangen score af.

Implementatie in sql

Bovenstaande code kon vrij eenvoudig in sql worden omgezet. Bij sql is het lastig om tussenresultaten te bewaren en is het gebruikelijk om daarom met één statement te werken. Alle handelingen daarin combineren komt de leesbaarheid echter niet altijd ten goede. Eventueel is het mogelijk om deelresultaten te bewaren via temporary tables of om functionaliteit te verhuizen naar stored procedures. Het nadeel van stored procedures is dat daarmee ook programmacode in de database terechtkomt. In dit geval kon de leesbaarheid met praktische formatting, het toevoegen van commentaar en het gebruik van een subselect op orde blijven.

INSERT INTO per_item_karma
(itemType, itemId, userId, time, karma)
-- Outer stage, per score calculation
SELECT
 3,
 id,
 userId,
 time,

 -- Generic maximum for all scores
 LEAST(100,
    -- Per score calculation of karma
    CASE score
WHEN -2 THEN
      -10 + (messageLength * 0.01)
WHEN -1 THEN
       -5 + (messageLength * 0.01)
WHEN 0 THEN
            (messageLength * 0.01)
WHEN 1 THEN
        5 + (messageLength * 0.02)
WHEN 2 THEN
       10 + (messageLength * 0.04)
WHEN 3 THEN
       15 + (messageLength * 0.08)
END
 ) -- End LEAST

FROM
 (
-- Inner stage, basic data preparation
  SELECT
   id,
   userId,
time,
   score,
   CHAR_LENGTH(message) AS messageLength
FROM
   comments
) as commentBase

Resultaten php versus sql

Net als met de eenvoudige berekening van karma wordt hiermee een aanzienlijk aantal queries bespaard. Hoewel de database hier ook het concrete rekenwerk moet verrichten, is het in dit geval alsnog significant sneller.

Het is consistent ongeveer een factor 60 sneller. Doordat in de php-variant alle reacties in het geheugen worden geladen - MySQL stuurt standaard direct alle resultaten voor een select-statement naar de client - is bovendien de php-variant niet erg schaalbaar. Er komt een punt waarop er niet genoeg ram beschikbaar is om het proces verder te laten lopen.

Nog complexer?

Bovenstaande uitwerking is niet de meest complexe karmaberekening die we hebben. Zo wordt bij de berekening voor productreviews ook rekening gehouden met het aantal afbeeldingen en beoordeelde criteria die zijn toegevoegd. Ook dat kan uiteindelijk in één sql-statement worden toegevoegd. Dat hebben we opgelost met het toevoegen van subselects.

  -- Inner stage, basic data preparation
  SELECT
   id,
   userId,
   time,
   score,
   CHAR_LENGTH(message) AS messageLength,
   (SELECT COUNT(*) FROM reviewimages i 
WHERE i.reviewId = r.id) as imageCount
FROM
   productreviews r

Ook hebben we berekeningen waarbij bleek dat MySQL het toch niet zo efficiënt kon als we hadden gehoopt. Dat lag in alle gevallen aan het feit dat de selectie van data zelf al behoorlijk lastig was geworden, bijvoorbeeld doordat er diverse zoekcriteria werden gecombineerd. Bij productreviews wordt bijvoorbeeld de lijst van reviews die zelf zijn bijgewerkt gecombineerd met de reviews die nieuwe beoordelingen door gebruikers hebben gekregen. Uiteindelijk werd dan het combineren van die criteria met de uitgebreidere berekening wat te veel van het goede.

Gelukkig was ook dat vrij eenvoudig op te lossen door het selectieproces los te halen en het resultaat eerst in een temporary table op te slaan. Dat kan bijvoorbeeld met zo'n soort opbouw:

CREATE TEMPORARY TABLE reviewsToProcess
(
PRIMARY KEY (id)
)
SELECT id FROM productreviews
WHERE updateTime > NOW() - INTERVAL 20 DAY
 UNION
 SELECT id FROM reviewsratings
WHERE insertTime > NOW() - INTERVAL 20 DAY;

INSERT INTO per_item_karma
(itemType, itemId, userId, time, karma)
-- Outer stage, per score calculation
SELECT
 ...
FROM
 (
-- Inner stage, basic data preparation
 SELECT
 ...
FROM
 productreviews r
JOIN reviews_to_process p ON r.id = p.id
) as reviewData;

Door de selectie van reviews te verhuizen naar een temporary table kan MySQL zich voor de complexere berekenquery beperken tot het uitlezen van de primary key van de productreviews-tabel. Voor de liefhebbers: de union in de eerste select zorgt ervoor dat er alleen unieke id's worden bewaard in de temporary table. Daardoor komt er gegarandeerd geen duplicate key error uit die query

Conclusie

Na de voorgaande pagina's zijn er diverse conclusies mogelijk. De sql-statements worden met de verdere optimalisaties steeds sneller, maar ook steeds wat complexer.

Dat laatste zal moeten passen bij het team dat de code onderhoudt. Voor ons was dat geen probleem. We, ik in het bijzonder, houden ons vrij veel met databases en de prestaties daarvan bezig. Het optimaliseren van code geeft een zekere voldoening en dat geldt net zo goed voor (groepen van) queries.

Ik vind het bovendien een leuke uitdaging om bij ad-hocverzoeken voor data uit onze database - zoals: "Hoeveel nieuwsberichten publiceren we tussen 8:00 en 10:00u 's ochtends?" - dat met uitsluitend sql-statements op te lossen. Het liefst natuurlijk met één statement dat binnen een paar seconden klaar is. Dat heeft ook een praktisch nut; het scheelt een ad-hoc-(php-)script schrijven, waarbij het leesbaar uitspugen van de gevraagde data lang niet altijd even eenvoudig is. Databasetools als phpmyadmin kunnen resultaten van queries vaak direct als csv- of xls-bestand aanleveren.

Het karma-project was mij door de hoeveelheid data op het lijf geschreven. Tijdens dat project bleek wel dat collega's het wat minder logisch vonden om alles in puur sql te doen. De statements zijn af en toe schermvullend en daardoor minder eenvoudig te begrijpen dan reguliere programmacode. Programmacode heeft (meestal) als voordeel dat het conceptuele model ervan beter bij onze eigen logica past. Bovendien hebben programmeertalen faciliteiten om die logica op te delen. Als dat goed wordt gedaan is concentreren op details net zo makkelijk als het krijgen van een overzicht.

Dat kan helaas niet zo makkelijk met sql. Resultaten kunnen stap voor stap worden opgebouwd met tijdelijke tabellen en via subselects worden gegroepeerd, maar het blijft lastig om daar goed mee om te gaan. Er zijn ook nog valkuilen, zoals het cartesisch product, dat je kunt krijgen met onjuist uitgevoerde joins of het feit dat een group-by-statement tot gevolg heeft dat kolommen hun onderlinge relatie per rij verliezen. Overigens kan het cartesisch product ook nuttig zijn, als er juist wel voor elke combinatie van tabel A en B iets gedaan moet worden.

Desondanks vond ik de prestatiewinst van het gebruiken van puur sql bij het karmasysteem zodanig dat ik die opzet verder heb uitgewerkt. Er waren bij de laatste testrun ruim 50 miljoen items die karma hebben opgeleverd. Daaruit volgden ruim 30 miljoen dagelijkse samenvattingen per gebruiker en 20 miljoen maandelijkse samenvattingen per onderwerp. Ondanks die enorme aantallen kon de sql-code dat in zo'n 2 à 3 uur doorrekenen. En hoewel we nu veel meer informatie uit deze resultaten kunnen halen, zoals wie de meeste karma heeft verzameld bij het onderwerp Software Development, is dit aanzienlijk minder tijd dan de oude code nodig zou hebben om alles opnieuw te berekenen.

Kortom, als de snelheid van het selecteren en/of verwerken van data belangrijk is, volg dan de onderstaande tips, naast het optimaliseren van het datamodel, de queries en de database zelf.

Beperk het aantal statements. Er zijn daarbij uiteraard wel grenzen aan wat zinvol is en/of nog goed past bij de gebruikte code. Het simpelweg in bulk 'preloaden' van data in een orm-cache kan al een zeer effectieve optimalisatie zijn. Verscheidene selects door middel van joins groeperen kan nog extra tijd besparen, maar is misschien lastig te integreren. Het in bulk opslaan van data bespaart meestal veel tijd, in onze voorbeelden scheelt het zelfs een factor 60.

Dit is ook bij updates en deletes mogelijk, hoewel daarbij wel goed opgelet moet worden dat het eindresultaat hetzelfde blijft. Als een record bijgewerkt moet worden, maar toegevoegd als het nog niet bestond, zijn upsert-commando's erg handig, zoals: 'insert .. on duplicate key update' in MySQL.

Mochten de queries langer duren dan gehoopt, dan kan het vaak ook helpen om het werk in delen te splitsen. Door bijvoorbeeld eerst een selectie van data op te slaan in een temporary table kan MySQL vaak weer wel efficiënt werken. Dat proces kan ook herhaald worden met verscheidene temporary tables.

Laat de database vooral doen waar hij goed in is. Complexere queries vereisen weliswaar meer voorbereiding door de database, maar als anders datzelfde werk in de applicatielaag moet worden uitgevoerd kan het toch de nodige overhead besparen. Laat ook data in de database als die verder niet (echt) wordt aangepast in de applicatielaag, maar slechts elders in diezelfde database moet worden opgeslagen. De database kan met een insert+select, update+join/subquery, delete+join/subquery of create-table+select veel efficiënter data kopiëren dan een applicatie dat kan.

Reacties (206)

[Anoniem: 457607]

Er is tegenwoordig bij met name jonge ontwikkelaars een soort aversie aan het ontstaan tegen relationele databases. Dit is komen overwaaien uit Silicon Valley. Daar hebben we internetbedrijven die zo'n enorme schaal hebben dat standaard oplossingen niet meer werken. Het is niet zozeer dat men daar tegen SQL is, men heeft geen andere keuze dan een andere weg te bewandelen. In veel gevallen is NoSQL een scaling hack, en geen principiele keuze.

Maar helaas blijft dat stigma kleven. Data access en logica verplaatsen naar de applicatie laag is een beroerde architectuur. Werkend te krijgen, ja, maar nog steeds beroerd. Joins vermijden is simpelweg bizar, gezien daar de hele kracht van SQL ligt. Data consistency en integrity en transacties op de 2e rang zetten is risicovol en slecht vakmanschap.

Bovenstaande zaken gooi je alleen overboord wanneer je daadwerkelijke dat enorm scaling probleem hebt, maar helaas lijken het bijna standaard keuzes te gaan worden. Ik ben blij dat de schrijver ook dat stukje tegengas geeft.

Voor het opslaan van hierarchische data in MySQL, vraag ik me af of er ook gekeken is naar de lineage techniek:

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

In het kort de uitleg:

- Tijdens het schrijven van een comment bouw je de tree naar boven op en slaat deze plat op in een kolom op het comment zelf. In sommige gevallen zijn 2 writes nodig. Dit lijkt zwaar, maar stelt weinig voor. Het opbouwen van de tree is ook niet zwaar en bestaat uit 1 enkel SQL statement, zonder joins. Naast deze zogenaamde "lineage" vul je ook een diepte kolom op het comment zelf.

- De write is dus erg beperkt en snel, maar het lees voordeel is nog veel groter. Je kunt nu vervolgens in een enkele platte query, zonder joins, de comment uitlezen in zowel de juiste hierarchie als in de juiste volgorde.

In het geval van Tweakers zou dat dus betekenen dat je in PHP al die sorting niet hoeft te doen. Bovenstaande oplossing is de best of both worlds: goedkope writes en super krachtige reads zonder joins die makkelijk schalen.

[Tjeerd]

Aanhakend, soms is onbewust ook niet duidelijk wat voor soort gevolgen een bepaalde oplossing heeft. Laatst zag ik een stuk code voorbij komen van een (jong) ontwikkelaarsteam waarmee wij samen werken en daar werd standaard gewoon een lijst van alle gegevens uit een bepaalde tabel opgehaald en in de code werd vrolijk alle data met Java-8 lambda gefilterd/gesorteerd enzovoort. Momenteel had dat geen prestatieproblemen tot gevolg, maar je kunt er vanuit gaan dat bepaalde tabellen dusdanig hard gaan groeien dat je liever al het werk door de database wil laten doen. Ten eerste is een database daar meer voor geoptimaliseerd en scheelt het onnodig datagepomp.

Verder hebben op mijn werk gemerkt dat er ook nog een hoop te optimaliseren is door bijv. WITH TABLE statements te gebruiken om alvast een soort subdataset voor te bereiden waar je tegen aan kunt querien. Of bijvoorbeeld zoiets als de volgorde van je WHERE criteria dat ook uit kan maken, dus zorgen dat je zo snel mogelijk begint de kleinste/meest relevante rijen op te halen en daarna pas verder te filteren, bijv. eerst zoeken naar je plaats en dan alle straten, ipv alle straten en dan je plaats. Klinkt misschien allemaal heel logisch, maar niet iedereen staat er altijd even goed bij stil en soms merk je in het begin ook helemaal geen snelheidsverschil omdat je dataset nog veel te klein is. Eigenlijk een goede manier om alvast vooruit te kijken zou zijn om een heleboel dummydata aan te maken van bijv. een paar miljoen rijen om te kijken of je queries op de toekomst voorbereid zijn.

SELECT p.naam, s.naam FROM plaats p, straat s WHERE s.plaats_id = p.id AND p.naam='Brielle'; -- Langzamer, want eerst alle straten en plaatsen worden opgehaald en dan pas gefilterd op de plaatsnaam
SELECT p.naam, s.naam FROM plaats p, straat s WHERE p.naam='Brielle' AND s.plaats_id=p.id; -- Sneller, eerst plaats ophalen en dan bijbehorende straten ophalen

Ook zoiets simpels als uit luiheid alle velden maar ophalen uit een tabel door een asterisk te gebruiken kan al heel snel tot onnodig veel datagebruik leiden. Verder maken we gebruik van een ORM-laag zoals Hibernate en ik ben van mening dat je het beste van twee werelden moet gebruiken. Daar waar je 'domme herhalende' dingen kunt laten doen door een raamwerk (CRUD), gewoon doen, dat scheelt je een hoop onderhoud, tijd en herhalend werk. Voor de snelle overzichten die uit verschillende tabellen moeten komen zijn ORM's weer wat gepriegel en is het vaak handiger om gewoon toch maar zelf je eigen queries te schrijven.

Wel goed artikel trouwens om hier gewoon open over te zijn en samen met de gemeenschap kennis te delen. Niet iedereen is zo open om over hun geprogrammeerde site te vertellen, misschien bang voor de kritiek die er kan komen dat het anders moet en of niet goed genoeg is, dus +1 voor die moeite.

[Reactie gewijzigd door Tjeerd op 22 juli 2024 19:33]

[Zerotorescue]

[code]SELECT p.naam, s.naam FROM plaats p, straat s WHERE s.plaats_id = p.id AND p.naam='Brielle'; -- Langzamer, want eerst alle straten en plaatsen worden opgehaald en dan pas gefilterd op de plaatsnaam
SELECT p.naam, s.naam FROM plaats p, straat s WHERE p.naam='Brielle' AND s.plaats_id=p.id; -- Sneller, eerst plaats ophalen en dan bijbehorende straten ophalen[/code]

Deze manier van JOINen wordt volgens mij sowieso afgeraden (vb), ik zou het als volgt doen. Hiermee ben ik ook gelijk expliciet met de JOIN-type.
[code]SELECT p.naam, s.straat
FROM plaats p
INNER JOIN straat s ON(s.plaats_id=p.id)
WHERE p.naam='Brielle'[/code]

Ook zoiets simpels als uit luiheid alle velden maar ophalen uit een tabel door een asterisk te gebruiken kan al heel snel tot onnodig veel datagebruik leiden.

Dit kan met name een probleem worden met JOINs omdat elke JOIN rij die vaker voorkomt meerdere keren wordt herhaalt. Als je (een variatie op) de methode in "Optimalisatie 4" uit het artikel gebruikt maakt dit veel minder uit en zou ik me er pas druk om gaan maken als het een probleem wordt.

Ik ben persoonlijk erg fan van het Yii framework; met een statement als het volgende in de $article->getComments methode (Yii1, weet even niet hoe dit in Yii2 gaat);
[code]$comments = Comment::model()->with('user', 'rating')->findAllByAttributes([
'articleId' => $this->id,
'articleTypeId' => $this->typeId,
]);[/code]
haal je alle comments op met users en ratings met slechts 3 queries. Wil je JOINs gebruiken kan dat eenvoudig aangezet worden met de `together` optie/methode. Met de with() functie geef je hier aan dat alle $comment->user en rating kinderen ge"eager load" moeten worden. Zonder dit worden ze ge"lazy load" zodra je ze wilt gebruiken (dus zodra je "$comment->user" doet). Het fijne van dit systeem vind ik dat je bijna nooit hoeft na te denken over je queries en werkt het standaard mechanisme (lazy load) meestal prima en altijd erg makkelijk.

[Tom-W]

Of dat sneller of trager is hangt ook van je database af. Vele databases zullen zelfs in je eerste voorbeeld zowiezo eerst de filter uitvoeren van p.naam='Brielle' en vervolgens pas de join doen op voorwaarde dat er een index op bestaat, ongeacht de volgorde van je tabellen of condities in de where-clause.

Een Oracle database houdt daarnaast ook nog statistieken bij over hoe dikwijls waarden voorkomen. Zo kan er rekening mee gehouden worden hoeveel records er in straat zitten en hoe uniek de naam 'Brielle' is om af te wegen of het interessanter is om eerst de straat tabel te raadplegen of eerst naar 'Brielle' te zoeken. Stel dat er 100 plaatsen 'Brielle' heten en er maar 1 rij in straat zit, dan zou het toch sneller zijn om eerst de join te maken.

Als performantie belangrijk is, is het altijd belangrijk om te weten hoe je database engine met queries omgaat.

[trogdor]

Daar kan je bij mysql erg kort over zijn: die heeft een byzonder matige optimizer. Als die optimizer slimmer was geweest dan zou de volgorde van condities geen effect hebben, maar dat hebben ze inderdaad wel.
Bij alle 'betere' databases (oracle, postgres, sqlserver) is dat soort gepiel gelukkig niet nodig.
Het gebruik van een voldoende grote test set is daarom inderddad een goed idee, als je interesse hebt in het toekomst bestedig maken van de queries tenminste.

[ACM]

Ik heb het ook nooit in MySQL meegemaakt dat die volgorde uitmaakt. Er wordt toch echt gewoon gekeken naar beschikbare indexen en die gebruikt waar zinvol.
Misschien dat het 'vroeger' uitmaakte, maar niet in mijn herinnering vanaf ergens rond MySQL 4. Het had allerlei problemen en kon veel dingen slechter optimaliseren dan bijvoorbeeld PostgreSQL, maar dit was er niet een van

[Hel8y]

Allereerst, goed artikel!

Om hier even in te haken op de performance. Ik heb zelf ervaren wat het verschil kan zijn bij het selecteren van gegevens op vrijwel alle manieren die hier worden omschreven. Om even te voorkomen dat iedereen nu standaard grote queries met joins gaat schrijven en achteraf toch tegen problemen aanloopt, hier even mijn ervaring ;-) (Ik zeg niet dat 1 bulk query met join altijd slecht is, maar ik heb in de praktijk meegemaakt dat dit wel negatieve gevolgen kan hebben)

In de afgelopen jaren heb ik bij een database van vergelijkbare omvang in de loop der tijd alle vormen gezien die hier worden vermeldt. In eerste instantie werden overzichten geladen vanuit MySQL door alle gerelateerde gegevens op te halen met losse queries. Op een gegevens moment werd dit dusdanig traag dat alles gelijk is omgezet naar bulk loads met joins. Dit heeft een paar maanden goed gewerkt. Na verloop van tijd werd dit toch weer te traag voor normaal gebruik.

Het wordt hier in het artikel ook al aangegeven, er zit een minimaal verschil tussen bulk loads met join en bulk loads met 3 losse queries. Als ik het artikel verder goed lees begrijp ik dat de queries werden uitgevoerd op een server die niet meer in een live omgeving gebruikt wordt. Nu weet ik niet onder welke voorwaarden dit exact is getest, maar het kan een redelijk verschil maken wanneer een MySQL server belast wordt door normaal gebruik en wanneer deze alleen wordt gebruikt om de response tijd van queries op te testen. In de praktijk heb ik gemerkt dat bulk loads met 3 losse queries soms betere resultaten geven, terwijl de bulk load met joins soms juist voor grotere problemen zorgt.

Ik zal uitleggen wat ik in de praktijk heb vernomen en waarom mijn voorkeur eigenlijk altijd uit gaat naar 3 losse bulk queries om de extra gegevens te laden uit MySQL (en heel specifiek uit InnoDB tabellen). Het probleem wat voort vloeit uit 1 bulk query met join heeft te maken met concurrency. Voorbeeld: de query met joins duurt in het ideale geval in totaal 240 ms. Dit betekent dat de rijen in alle 4 tabellen die geselecteerd worden, niet kunnen worden gewijzigd voor 240 ms. Aan de andere kant, wanneer een rij wordt gewijzigd zal deze de rij locken, waardoor de query met joins die uitgevoerd moet worden zal moet wachten totdat de lock verwijderd is. Wanneer er veel concurrency is op de tabellen waaruit de data wordt geselecteerd, zal de query geen 240 ms duren, maar zal dit hoger uitvallen. Dit heeft niet alleen gevolgen voor de de query die de gegevens probeert te selecteren, maar ook voor de andere queries die gegevens proberen te updaten/verwijderen. In een live omgeving duurde een query bij mij in het ergste geval soms 5 - 10 seconden, terwijl dit op de test omgeving netjes 50 - 100 ms was. Nu werd deze tabellen ook wel extreem belast, maar met enige load kun je dit verschil waarschijnlijk al merken.

Neem aan de andere kant de bulk query met 3 losse queries. In totaal worden de losse queries uitgevoerd en duren allemaal 65 ms (je hebt 3x de round trip time, etc.). De 3 losse queries worden uitgevoerd op de tabellen A, B en C. Stel nu dat op één van de rijen een exclusive lock zit in tabel A. Er kunnen in de tussentijd wel gegevens geselecteerd worden uit de tabellen B en C. Uiteindelijk heb je op deze manier een stuk minder last van concurrency.

Niet in alle gevallen zal dit een probleem zijn, maar vanwege de kleine extra tijd die het kost om de 3 extra bulk queries uit te voeren in plaats van 1 query met een join, gaat toch mijn voorkeur uit naar 3 extra bulk queries. Op termijn kan dit een hoop problemen voorkomen wanneer je systeem een sterke groei kent. Als je op een gegeven moment fouten in je applicatie ziet als "lock wait timeout exceeded" ben je al te laat ;-)

[witkop525]

Je hoeft niet per sé haakjes te gebruiken bij de ON clausule toch?

[littlewolvie]

Neen, en je mag ook je volledige query op 1 lijn plaatsen. Maar leesbaarheid kan je een hoop kopzorgen besparen. Net zoals bij andere programmeertalen raad ik dan ook aan om voor SQL en in het geval van Oracle PL/SQL binnen je bedrijf ook Coding Principles af te spreken.

[Niemand_Anders]

In het geval van een inner join kun je de ON selectie uitbreiden met de condities uit de where voor die combinatie. Bij een simpele join query als hierboven weten de meeste SQL engines welke de boel te optimaliseren, maar bij complexere joins merk ik dat database grote verschillen tonen in de optimalisatie van de queries. Tegenwoordig ben ik vooral van de micro ORM frameworks welke mapping van de resultset naar je entity classes verzorgen. Bij de meeste projecten komt het zeer zelden voor dat je daadwerkelijk van database engine overstapt..

Maar ik ben best wel benieuwd wat de performance zou zijn op een andere database engine zoals PostgreSQL of Oracle..

[Anoniem: 457607]

Het probleem met ORM is dat SQL een leaky abstraction is. In een echte abstractie, hoef je niets te weten van de werking van de laag daaronder, maar in SQL is dat niet het geval. SQL is en blijft een skill op zichzelf. ORM kan "blind" gebruikt worden in een kleine setup, en dat kan heel produktief zijn, maar zodra je moet schalen valt het uit elkaar. Een ander bekend probleem is dat je boven je ORM laag zaken op verschillende manieren kunt oplossen, welke functioneel gelijk zijn, maar in werkelijkheid een verschil van 1000 maken in het aantal queries.

Ik ben daarom geen groot fan van ORM. Een mooie "model" laag met goede handgeschreven SQL geniet mijn voorkeur en is wel degelijk onderhoudbaar.

Een ander slecht voorbeeld van NOSQL, gevonden op het werk...

We hebben een eenvoudige UI met een lijstje items, deze worden opgehaald uit MongoDB. Individuele items kunnen in de UI bewerkt worden. Gezien de MongoDB een domme, zeer slecht opgezette name/value store is in dit geval, wordt de complete lijst items als soort van text blob als value opgeslagen. Het resultaat is dat de front-end door de lijst moet zoeken bij een edit, deze in memory aanpast, en vervolgens de complete bewerkte lijst items terugstuurt als een enkele waarde.

Een foutje dus in de front-end, en de hele lijst is corrupt. Data consistency uit het raam. Schaalbaarheid ook uit het raam gezien de enorme kans op write conflicts.

Een van slechtste architecturen die ik ooit heb gezien. Maar ja, het is No-SQL dus cool en beter dan al die ouwbollige mensen.

[SuperDre]

In een echte abstractie, hoef je niets te weten van de werking van de laag daaronder

En dat vind ik tegenwoordig dus een steeds groter probleem worden, abstractie is geweldig natuurlijk, maar het is toch wel heel erg fijn als de developer wel weet wat er werkelijk onderliggend gebeurd. Je hoeft er niet het fijne van te weten, maar een beetje is toch wel handig. Maar helaas zie ik tegenwoordig steeds vaker dit probleem naar boven komen, ze hebben geen idee wat er werkelijk gebeurd en kunnen daardoor ook veel minder goed problemen oplossen als het al niet standaard in het gebruikte framework zit, en over optimalisatie hoeven we het al helemaal nog niet te hebben.. Zeker met porten komen dit soort problemen snel naar boven..

[Anoniem: 457607]

Toch is abstractie de belangrijkste manier van de mensheid om vooruit te komen. Zo zitten we in autos die we niet snappen. Ook zitten we op internet, gebruikmakend van TCP/IP welke maar liefst 4 of 5 abstractielagen boven op elkaar stapelen.

In development, is abstractie vooral een zegen voor produktiviteit, maar inderdaad en helaas, de abstracties zelf zijn niet zo heel goed, waardoor het noodzakelijk is toch vanalles te weten over de lagen daaronder.

[wankel]

@Fledder2000

Toch is abstractie de belangrijkste manier van de mensheid om vooruit te komen. Zo zitten we in autos die we niet snappen.

Met de auto kom je misschien wel vooruit, maar met gebrek aan kennis niet. Ik stap niet in een auto waarvan ik de werking niet snap. Zonder voorhoede die weet hoe de wereld werkt komt de mensheid niet vooruit.

[trogdor]

Een dergelijke byzarre oplossing kan je natuurlijk ook in sql implementeren. Of met textfiles of zo. Ligt niet aan mongo.

[Anoniem: 457607]

Dat heb ik ook nergens zo gesteld. Het ligt aan de inzet van technologie op basis van mode ipv geschiktheid. MongoDB is een prima NoSQL DB. NoSQL is echter geen prima oplossing voor alles.

[trogdor]

Maar er is geen enkele data-opslag methode die je beschermt tegen debiele constructies.

[Veneus]

Maar deze slechte architectuur ligt dus aan de implementatie en niet aan MongoDB zelf.
Hoewel ik het er mee eens ben dat NoSQL niet een antwoord is op alles, heeft het echt wel zijn nut en voordelen. Het is niet slecht omdat het slecht gebruikt wordt

[Anoniem: 457607]

Dat klopt, kort gezegd the right tool for the right job.

[littlewolvie]

Volledig met je eens. NoSQL databases zijn in mijn ogen een aanvulling op relationele databases, maar GEEN vervanging van.

[ACM]

De lineage-variant kende ik niet met die naam, maar wel van principe.

We hebben een aantal features in het reactiesysteem die eigenlijk elke vorm van hiearchisch querien lastig of overbodig maken. En daarmee is elke complexere vorm van de hiearchie opslaan dat ook.

- We tonen bovenaan de lijst hoeveel reacties onder elk niveau-filter beschikbaar zijn (dit kan je natuurlijk denormalizeren indien nodig)
- We tonen op pagina's meerdere complete trees (of subtrees van niveau 0) tegelijk niet slechts 1, maar ook niet allemaal. Maar die trees die we tonen, worden wel helemaal getoond.
- De score-sortering hangt af van de scores van de reacties op diepere niveau's (zie dat maar in sql te krijgen..., hoewel je ook dat zou kunnen denormaliseren)
- De reacties op lagere niveau's dan je score-filter laten we 'ingeklapt' zien, dus ze moeten alsnog opgehaald worden.

En ik vergeet er vast nog een paar

Los daarvan hadden we de adjacency list al en dit systeem werkt vrij aardig.

[Reactie gewijzigd door ACM op 22 juli 2024 19:33]

[Kajel]

Ironisch genoeg gebruiken veel van die "web-scale" Silicon Valley bedrijven ook gewoon relationele databases voor hun primaire data opslag. Twitter gebruikt nog steeds voornamelijk MySQL als primaire data storage. Bij Facebook idem, waarbij ze proberen MySQL te optimaliseren voor de schaal waarin ze opereren, in plaats van te grijpen naar NoSQL technologieën.
Instagram gebruikt voornamelijk PostgreSQL voor data opslag, en daarnaast nog wat Cassandra en Redis.

Kortom, scaling requirements is geen reden om relationele databases links te laten liggen en te grijpen naar NoSQL oplossingen. Wat dan wel? Data access patronen. Hoe je je data schrijft en leest, bepaalt wat de beste manier is om het op te slaan, met welke technologie, maar ook met wel Schema.

[Apache]

Op zich is dit niet zo vreemd en past perfect in het CQRS verhaal. Sla je acties (transactioneel) op in een db, pomp de data (evt asynchroon) ook in iets als elasticsearch en query hem op die manier.

Er zijn ook firma's die er verder in gaan en eventsourcing gebruiken en de events die worden doorgestuurd van de command's die tegen het systeem worden uitgevoerd, bvb plaats nieuwe post op facebook command, integraal opslaan zodat die later gereplayed kunnen worden. Eventueel tov een andere versie van de business logica bvb zonder de vervelende bug.

Er zijn talloze opties. Persoonlijk denk ik dat het hier ook een zeer interessant poc kan zijn om alles in elasticsearch te duwen. Persoonlijk voor statistieken als hoeveel bezoekers tussen 8h en 10h zou ik zeker geen sql queries meer WILLEN draaien. Ik zou van elke view een async message willen sturen naar een rabbit mq die een logstash als consumer heeft die dat in elasticsearch duwt die dan via kibana netjes een semi-realtime fancy dashboard heeft waar de business zelf nog wat quick filtering kan doen en dat ze mij ondertussen rustig laten verder werken aan the next big fancy thing in de applicatie architectuur

[gekkie]

Heeft Postgresql een leuke extensie voor .. "ltree" type kolom
http://www.postgresql.org/docs/current/static/ltree.html

[Reactie gewijzigd door gekkie op 22 juli 2024 19:33]

[Adlermann]

Als mensen die complexe databases bouwen eerst het probleem in relationele algebra opschrijven, dan kom je automatisch al tot de meest optimale query. Je kunt het probleem dan al veel concreter en beperkter opschrijven, wat leidt tot simpelere queries met minder overhead.

Door het zo aan te pakken, focus je echt eerst op de juiste materie, zonder afgeleid te worden door sql syntax e.d. Eerst nadenken voor doen

[Reactie gewijzigd door Adlermann op 22 juli 2024 19:33]

[BadpunK]

Sorry, maar hier ben ik het niet met je eens. Kijk maar eens naar een explain plan van een query. Als deze over geindexeerde en niet-geindexeerde kolommen gaat wil dit namelijk over het algemeen grote verschillen maken. Het gebruik van hints e.d. wil nog wel eens meewerken om het iets te laten performen. Hetzelfde geldt om de statistieken van tabellen met een regelmaat bij te werken wanneer er veel transacties hebben plaats gevonden. Als laatste is namelijk ook de structuur van de database van belang bijvoorbeeld reageerd een (standaard) Oracle database totaal anders dan een Oracle Exadata database. Dan heb ik het nog niet eens over de gebruikte modelleertechnieken zoals 3NF, DataVault of dimensioneel model.

Al met al SQL tweaken en beter te draaien is een vak apart en niet zo simpel zoals jij hierboven aan het schetsen bent...

[TheekAzzaBreek]

Sql tuning is inderdaad een vak apart, en eentje waar ik altijd veel genoegen aan beleef, maar tot mijn spijt onderhand wel een achterhaalde exercitie.

Sowieso beperkt tuning zich tot die gevallen waar de optimizer faalt, en dan is het meestal verstandiger de oorzaak aan te pakken waarom die faalt dan om correcte query's te gaan herschrijven.
Maar tuning experts zijn duur, en bloedsnelle storage wordt snel goedkoper, dus performance problemen met hardware oplossen steeds interessanter. Het aantal gevallen waar sql tuning nog een economisch verstandige propositie is neemt snel af.

[pennywiser]

Waarom eigenlijk niet toch gewoon tunen als er reeds optimale hardware is? Vroeg of laat loop je weer tegen beperkingen aan neme ik aan?

[VolvoGek]

exact...
alles is kapot te maken, maar met een overdreven hoeveelheid hardware is het vermoedelijk wel lang wachten.

[pennywiser]

Maar levert het tunen dan niets meer op op ssd bv.? Ik zou zeggen blijf dat tunen doen, dat levert de ideale config op langere duur op.

[VolvoGek]

Tunen levert veel op en het is mijn vak.

Het is echter ook een economische afweging.
In hoeverre gaat iets een probleem vormen in de komende ...dagen/weken/jaren.
En wat gaat het kosten (aan tijd) om er iets aan te doen.

Een kosten/baten analyse voor performance verbeteringen is heel normaal.

[VolvoGek]

Lekker kort door de bocht.

Natuurlijk kun je veel tunen met ijzer, er zijn echter grenzen.
Ik heb genoeg omgevingen gezien die ook met veel ijzer uiteindelijk niet meer performen. Simpelweg omdat er (onnodig) teveel data geraadpleegd moet worden.
en ja, ik ben een 'dure' tuning expert, maar ik heb al veel grote omgevingen met een paar kleine aanpassingen extreem veel sneller gemaakt. Waarbij ik de klant een flinke hoeveelheid geld heb kunnen besparen vanwege het NIET aanschaffen van duur ijzer en extra licenties. Dat was dan uiteindelijk heel goedkoop.

Het grootste probleem dat ik zie in de praktijk is het gebrek aan kennis omtrent indexen bij developers. De grootste misvattingen kom ik daar tegen.
Daarnaast is het zogenaamde platform onafhankelijk programmeren een ander probleem. Je moet de kracht van de database gebruiken. Ansi-sql is dan wel generiek maar ontneemt je de mogelijkheid om specifieke Oracle, SQLServer, etc functionaliteit te gebruiken.

[OMX2000]

Voordat Efbe langskomt even sense gaat maken van dit alles. Mijn Jan Boeren Fluitjes reactie.

Nonsense! Ja het klopt dat je data technisch kunt komen tot optimalere queries. Maar ik denk dat je sowieso twee verschillende modellen zou kunnen gebruiken voor schrijven en lezen. Een read optimized model hoeft niet in de 100ste normaal vorm te zijn. Het moet geoptimaliseerd zijn voor gemak en snelheid bij het lezen. Dus desnoods zwaar ge-denormaliseerd. Dus het CQRS principe toepassen. En dan zou het zomaar kunnen zijn dat je voor opslag een relationele database gebruikt, en voor het lezen een NoSQL database zou kunnen gebruiken.

[VolvoGek]

In de praktijk lossen we dit op met zgn. data-grids de persistence in een relationele database en het serveren van data vanuit een memory georienteerde omgeving.
Producten als Coherence en Cassandra onder andere

[Whazor]

Hier ben ik het ook niet mee eens. Want de meeste SQL query's zien er hetzelfde uit als RA query's, je kunt ze vaak één op één vertalen. De uitzondering is wanneer specifieke SQL functionaliteit wordt gebruikt, die in RA kan ontbreken. Afhankelijk van de database kan je in SQL ook recursieve query's uitvoeren [1].

Zorgen dat alles in één query is het aller belangrijkst. Want op basis hiervan kan de database alle optimalisaties uitvoeren. De database rekent voor jou uit welke tabellen als eerste opgehaald moeten worden en hoeft dan bepaalde acties niet dubbel uit te voeren. Een andere belangrijke optimalisatie is het gebruiken van indexering.

[1] http://www.postgresql.org/docs/9.4/static/queries-with.html (ctrl+f recursive)

[trogdor]

Met een belangrijke kanttekening: dat is zo voor de 'betere' databases als postgres oracle en sqlserver, maar mysql heeft nogal eens problemen om de juiste keuzes te maken, de optimizer is gewoonweg niet zo snugger. Dan moet je gaan helpen door bijvoorbeeld een temporary table te gebruiken zoals in het artikel.

[Patrick_Wolf]

Daarvoor maak je toch een UML?

[Probook8979]

Erg tof, maar nu vraag ik me af, waarom zou je dit willen veranderen op de achtergrond? Is het beter? Zo ja waarom?

[ACM]

Welk deel?

We wisten wel dat je bijvoorbeeld in totaal 500 karma op 10 januari 2014 had gekregen voor een stel reacties, maar niet voor welke reacties (of hoeveel per reactie) en al helemaal niet bij welke nieuwsberichten en reviews die reacties hoorden. En nog een stap verder; we wisten dus ook niet bij welke onderwerpen (dat zijn de aan de artikelen gekoppelde categorieen, merken, series, etc) dat verdiende karma dan hoorde.

En dat laatste wilden we juist wel weten

Dus we moesten alle oude bijdrages opnieuw doorrekenen voor dat onderscheid. De karma-berekening was een berg oude code die sowieso gerefactored moest worden voor die nieuwe wensen.
Daarmee werd het automatisch opportuun om het zo te herschrijven dat het ook relatief snel een eindresultaat geeft

[Reactie gewijzigd door ACM op 22 juli 2024 19:33]

[BadpunK]

Wat voor soort modelleertechniek is er gebruikt op de database? Daar lees ik helaas weinig over in dit artikel.

[ACM]

Hoe bedoel je dat? Bedoel je wat er aan diagrammetjes is getekend vooraf? Waarom is volgens jou de modelleertechniek relevant voor de uiteindelijke queries (voor zover de structuur niet de query dicteert)?

[BadpunK]

Omdat sommige modellen parallelle uitvoer van queries ondersteunen zonder (dead)locks op de tabellen te zetten. Parallel versus sequentieel is volgens van mij sowieso een nog grotere tijdswinst

Daarbij zag ik ook ergens een subselect voorbij komen wat doorgaans ook een zeer vertragende factor is..

[Reactie gewijzigd door BadpunK op 22 juli 2024 19:33]

[ACM]

Parallel geeft natuurlijk alleen een tijdswinst als de wall-clocktime dan ook daadwerkelijk lager is

Als bijvoorbeeld in dit voorbeeld de losse-query versie van reactie-karma in losse threads zou zijn gestopt en je er dan 10 tegelijk had kunnen laten draaien, was het nog steeds trager geweest... Pas bij 60 tegelijk zou dat gelijk zijn qua tijd aan de enkele-query versie.

En schaalbaarheid is zelden oneindig. In dit geval moet het uiteindelijk allemaal in 1 database in 1 tabel terechtkomen. Er zijn databases (vooral in de nosql wereld) die heel goed overweg kunnen met grote aantallen threads tegelijk, maar dat was hier niet zo nodig en had vooral ook weer een hoop extra werk opgeleverd omdat het nog weer een hele andere aanpak vereiste.

Verder is parallel vaak ook lastiger om (goed) uit te werken.

Hoedanook, dit is allemaal gebouwd met MySQL en die heeft geen ondersteuning om queries om te zetten naar parallelle verwerking. Maar SQL zelf leent zich vrij aardig voor een databaseplatform dat de e.e.a. parallel kan verwerken; je definieert tenslotte (vooral) wat je wil en niet hoe de database dat dan vervolgens voor elkaar moet zien te krijgen

[Reactie gewijzigd door ACM op 22 juli 2024 19:33]

[Interfico]

Wie is die mysterieuze man die jullie vertelt geen joins te gebruiken? Natuurlijk zijn simpele queries mooier als het kan, maar om dat als uitgangspunt te nemen vind ik een beetje gek.

Erg leuk om dit achtergrondartikel te lezen!

[Anoniem: 474132]

Dit is afkomstig uit de big data/NoSQL wereld, waarbij je eigenlijk alleen met key:value paren werkt. Voordeel is dat de database zo homogeen is dat deze makkelijk massively parallel in te zetten is. De overhead die je in je applicatiecode krijgt door het vele 'join' werk, is ook geen probleem want dan plaats je gewoon een cluster servers bij. Kortom: schaalbaarheid.

Voor relatief eenvoudig web databases en enterprise database is het natuurlijk prima volledig de mogelijkheden van SQL in te zetten. Daar is het voor bedoeld: zelf je joins in php programmeren is eigenlijk regressie naar de dagen voordat SQL uitgevonden was!

Overigens kun je tegenwoordig met ANSI SQL constructies als de WITH clause queries gemoduleerd opzetten. Een soort inline views waardoor je geen temporary tabellen meer nodig hebt. Daarnaast is er ook een hiërarchische query constructie in ANSI SQL (CONNECT BY) waarmee je bijvoorbeeld de commenttree in een bulk load op kunt halen. Weet niet of MySQL dat inmiddels al heeft overigens.

[desmond]

Heerlijk dit inhoudsartikel, fantastische reacties ook. Dank daarvoor.

Over de joins: oorzaak en gevolg gaan in deze thread door elkaar heenlopen. BadpunK wees hierboven al op de modelleertechniek. Joins zijn natuurlijk onlosmakelijk verbonden met de gekozen datamodellering. Op dataniveau is het van wezenlijk belang om een gegeven slechts eenmaal, op een locatie op te slaan. Dit garandeert op de lange duur een consistente gegevensverzameling. Dataredundantie moet dus voorkomen worden, en dat doet men door data tot de derde normaalvorm te normaliseren. Dit proces heeft tot gevolg dat er veel meer tabellen ontstaan die op functioneel niveau met elkaar verbonden zijn. Stel je als voorbeeld de adresgegevens van klanten voor. Uitgenormaliseerd zullen dat minstens twee tabellen worden, die uiteraard een JOIN verdienen bij uitlezen.

"zelf je joins in php programmeren is eigenlijk regressie naar de dagen voordat SQL uitgevonden was": Dataverzamelingen behoren goed genormaliseerd te zijn en daar horen JOINS bij. NoSQL-databases bieden daar vooralsnog geen oplossing voor - en al helemaal niet behoorlijk gestandaardiseerd (SQL) uitvraagbaar.

[TheekAzzaBreek]

Dat is modelleringstheorie uit de jaren tachtig. Sindsdien is allang geaccepteerd dat je voor specifieke scenario's (en zeker dit soort read intensieve toepassingen) denormalisatie toepast. Hier heeft de praktijk de theorie wel een beetje ingehaald.

[desmond]

En er is nog steeds niets mis aan die theorie. Natuurlijk kun je denormalisatie toepassen, maar dat gaat ten koste van je write-performance. Punt van de test was dat de performance-penalty van joins behoorlijk overschat werd door de ontwikkelaars.

[TheekAzzaBreek]

Met de theorie is niks mis, alleen met het woordje moet.

Derde normaalvorm gaat in de eerste plaats over data integriteit, niet over performance. Als je extreme performance eisen hebt zit het je juist vaak in de weg, ook voor (extreem) schrijfintensieve toepassingen.

[desmond]

Ik begrijp niet wat dit verder nog toevoegt aan de discussie, maar data-integriteit lijkt mij voor de functie van data-opslag van het hoogste belang. Punt van de test was dat de performance-penalty van joins behoorlijk overschat werd door de ontwikkelaars.

[Bastiaan V]

Toch krijg ik als ik dit soort verhalen lees het gevoel dat er iets mist in de lamp stack. (tussen php en mysql) Doordat php, in de basis een templating framework, niet (snel genoeg) logica kan uitvoeren, waardoor dit verplaatst wordt naar de database laag.

Deze zou zich toch niet bezig moeten houden met het afdwingen van business rules, maar met het (snel) opslaan en terughalen van data? Ik dat licht begrijp ik helemaal waarom er een java middleware is gemaakt voor t.net.

Het blijft mij verbazen hoeveel webapplicaties draaien (en op grote schaal!) zonder bijvoorbeeld iets in memory te kunnen houden (binnen de applicatie, querycache of een cache in je netwerk stack is iets heel anders :-) )

[RobertMe]

Het is inderdaad zo dat PHP trager is dan sommige andere talen (PHP is immers een scripttaal en wordt geinterpreteerd, waar een Java en C# JITen naar native code tijdens uitvoer). Maar het probleem wat in dit artikel wordt aangekaart is dat vaak querien gewoon traag is, onafhankelijk van de taal.

Elke query die je uitvoert kost je immers al een (network) roundtrip naar de database. Vervolgens zal de database elke keer de query moeten parsen (iets wat met prepared statements wel voorkomen wordt) etc. Vervolgens zal de data uit cache of van disk gelezen moeten worden, er zullen nog wat locks geplaatst (en verwijderd) moeten worden (niet alleen bij writes, maar ook bij reads), controleren of er uberhaupt al geen locks bestaan die de huidige actie blokkeren.

Bij data manipulatie (insert, update, delete) is het vervolgens ook nog eens zo dat je weinig aan caches in RAM hebt. De query is pas afgerond en gelukt nadat de data naar de disk is geschreven, en ook dit kost relatief veel tijd (ten opzichte van een read die alles uit het RAM kan halen).

Tel dit allemaal bij elkaar op en je hebt een enorme overhead tussen of je 1000 INSERTs met een enkel record doet of 1 INSERT voor 1000 records doet (bulk insert). Immers zal al het bovenstaande dan ook 1000 vs 1 keer aan tijd kosten.

[Sand0rf]

Erg interessant artikel en leuk om eens in de keuken van Tweakers te kijken.
Jullie gebruiken dus helemaal geen Views voor het 'klaarzetten' van bijvoorbeeld comments?
En geen bigint voor comment id? Had ik wel verwacht met het aantal reacties hier

[ACM]

Geen views, sowieso bieden die geen performance-winst (tenzij je natuurlijk materialized views hebt). Sowieso is me niet helemaal duidelijk op welke wijze je die zou willen gebruiken

Bigint voor comment id? Waarom zouden we? Met een gewone int kunnen we 2 miljard reacties kwijt, we zitten nu na 15 jaar op nog geen 8 miljoen. Zelfs als we er nu ieder jaar 8 miljoen bij krijgen kunnen we dan nog 249 jaar vooruit

[supersnathan94]

Een normale integer in een mysql database kan ruim 4 miljard waarden aan. Dit is in het huidige systeem meer dan zat. Zelfs een signed int is met 2 miljard nog ruim zat. Een big int kan daarentegen 18 triljoen (!!) waarden kwijt. Dat is natuurlijk belachelijk veel en dit zullen we de komende 10.000 jaar nog niet bereiken.

Het grote punt waarom een big int niet wenselijk is (naast dat deze dus behoorlijk overbodig is) is dat deze 8bytes nodig heeft ipv 4. Voor 8 miljoen reacties betekent dit dus 32MB extra omdat we zonodig een ander type nummer moeten gebruiken. Dit is natuurlijk vrij weinig op 200GB aan datasets, maar ga je dit bij alles doen dan kan zoiets dergelijks behoorlijk in de papieren gaan lopen wat betreft performance. Zeker in de oude opzet waarbij het laden van 5000 reacties 11 seconden duurt.

Het gaat hier immers om performance upgrades en niet over toegevoegde schaalbaarheid van de DB wat betreft de mogelijkheid voor triljarden reacties.

[GrooV]

Net zoals Slashdot dus

[s.stok]

Views (met uitzondering van materialized views) zijn meer bedoeld om toegang te geven aan bepaalde gegevens zonder direct de hele tabel beschikbaar te maken voor de gebruiker/role.
Ze zijn meer bedoeld voor abstractie dan snelheid.

Overigens was view ondersteunen in MySQL zwaar beperkt;

Oudere versies (<5.0) ondersteunen geen views dus doet MySQL (bij wijzen van hulp) de view omzetten naar een tabel met alleen de huidige records
En je kon tot 6.0 (of 5.5 kan niet vinden) geen subquries gebruiken in een view.

[fdh]

Als je met afzonderlijke queries werkt in plaats van met joins, hoe ga je dan eigenlijk consistency garanderen?
Tussen jouw 2 (of meerdere) queries kan dan immers al een update op 1 van de 2 tabellen de records gewijzigd zijn.
Door te beperken tot 1 query met een (of meerrdere) joins zorgt de rdbms ervoor dat je data consistent is.

[VolvoGek]

Of je data consistent is hangt wel af van het gebruikte rdbms.

Oracle doet standaard aan read-consistency, (wat volgens sommige developers fout is )
Maar sommige rdbms-en vereisen een explicitie keuze voor een 'read consistent transactie'.

[fdh]

Ja, ik ben zelf een Oracle DBA, maar van andere rdbms-en ken ik weinig.
Je kunt ook bij Oracle inderdaad een "transactie punt" zetten, maar ik ben dit eigenlijk nog nooit in de praktijk tegengekomen.

Het lijkt mij ook heel onlogisch om transactie punten te zetten en dan tabellen afzonderlijk te gaan ophalen en dan joins te gaan uitvoeren in de applicatie code zelf....
Oracle licenties zijn niet echt goedkoop (zeker de enterprise edition niet), dus als je die gebruikt dan moet je die ook ten volle gebruiken en niet proberen het wiel opnieuw uit te vinden.

Daarnaast is database programming eigenlijk een afzonderlijke discipline, net zoals het ontwikkelen van een goeie front-end dat is.
Daarom dat ik een voorkeur heb voor een afzonderlijke laag voor de db acties (in bv pl/sql) die lost staat van de front-end.
Vanuit de front-end roepen de programmeurs dan functies aan zoals save_post of get_post_thread....
Eenvoudiger om secure te werken en meerdere applicaties te hebben die op dezelfde data werken

[pruxor]

Ik zag nergens iets over het gebruikte transactiemechanisme. Misschien zijn die voor de voorbeelden ook iets minder relevant.

[ACM]

In deze praktijkvoorbeelden is het meestal niet zo boeiend als er hier en daar iets verandert; er zal niet ineens een gebruiker verwijderd zijn nadat een reactie van hem uit de database is gehaald. Hij kan hooguit van naam veranderd zijn.

Maar los daarvan kan je uiteraard al die selects in een transactie verpakken om zo te voorkomen dat andere schrijfacties je uitkomst veranderen.

[JJ93]

Leuk om te lezen hoe de achterkant van een tweetal features er uit ziet. Persoonlijk ben ik juist bezig met de overstap van MySQL naar ElasticSearch.

Vooral facetted search implementeren gaat dan meteen mooi met de beschikbare API. Inserten en zoeken op combinaties van meerdere fields gaat ook tig keer sneller.

Toch voel ik me nog prettiger bij gewone SQL-queries en tabellen. Nog wel even bezig met het doorgronden van ElasticSearch, maar dan ga ik de overstap toch wagen.

De pricewatch code lijkt me vooral interessant. Miljoenen producten, duizenden categorieën, andere facetten voor elke categorie, prijsgeschiedenis, de specificaties zelf, het tonen van aanbieders en het dagelijks updaten van de prijzen en nog veel meer...

[simon]

Nu kan het aan mij liggen, maar ik snap niet waarom je van MySQL naar ElasticSearch overstapt, wat ik wel kan voorstellen is dat je een fulltext search in je MySQL vervangt door elastic search. Maar ElasticSearch is, in mijn ogen, een goede implementatie van Apache Lucene, waarbij de database eigenlijk zoekbare items opslaat, die je weer kan koppelen aan informatie in je daadwerkelijke eigen database.

[JJ93]

Hier is nog een oude forum post: Facetted search bouwen 12 miljoen records, welke database?

ElasticSearch lijkt erg goed te voldoen. Ik wil op elk veld en elke combinatie van velden kunnen filteren.

Verder wil ik weekelijks of eigenlijk dagelijks de hele dataset controleren, als in, of RDW gegevens heeft gewijzigd en welke kentekens er zijn bijgekomen.

De insert snelheid is lekker snel, onderwater worden allemaal optimalisaties doorgevoerd en het doet precies wat ik wil.

[Anoniem: 366402]

Ligt er natuurlijk aan wat je wil met je systeem. Als je grote bakken data wil doorzoeken op patronen en wilt kunnen filteren, dan is ES natuurlijk ideaal. Wil complexere relationele data ophalen dan zal een RDB weer voldoen.

[ACM]

De filtering in onze zoektechnologie voor o.a. de pricewatch gaat niet via de sql-database

Conceptueel is het redelijk vergelijkbaar met elasticsearch.

Overigens de prijshistorie en de lijstjes met prijzen op de losse productpagina's komen weer wel gewoon uit de sql-database.

[JJ93]

Nog een interessant artikel, thx!
Het is in ieder geval erg belangrijk om goed na te denken over je database structuur en andere gebruikte tools. Sommige veranderingen maken een enorm verschil. Merk het zelf ook wel bij het opbouwen van queries, soms wel 10 mogelijkheden en de een is vaak een aantal keer zo snel als de anderen.

[Ramon]

Ik ben toch wel benieuwd waarom er niet voor Postgresql gekozen is eigenlijk. In mijn ervaring is dat een stuk robuuster en sneller. Scheelt weer een hele hoop optimalisatie-tijd

[ACM]

Ook daar geldt dat dit type queries sneller is met bulk-operaties dan losse queries... En we hebben nou eenmaal al een (grote) mysql-database die we niet zomaar even naar PostgreSQL kunnen migreren. Data heen- en weerkopieren is ook wat zonde en we zaten ook niet zo te wachten op extra onderhoudswerk

[cariolive23]

Offtopic: Jaren geleden hadden jullie nog wat (performance) vergelijkingen tussen MySQL en PostgreSQL. Jammer dat deze tests niet meer worden gedaan, ik zou graag eens zien hoe beiden zich anno 2015 tot elkaar verhouden, met de door Tweakers gebruikte techniek. De laatste die ik kan vinden stamt uit 2007, MySQL 5.0 en PostgreSQL 8.2

Zou leuk zijn om zoiets weer eens te zien maar dan met de laatste stable versions en op nieuwe hardware.

[Anoniem: 366402]

Inmiddels zijn MySQL en PostgreSQL zo ver uit elkaar gegroeid dat ze eigenlijk niet meer in hetzelfde segment zitten.

MySQL is een mooi dingetje voor database backed websites zoals fora ed. Terwijl PostgreSQL meer in de clientele van Oracle zit te roeren. Meer het verwerken van grote bakken wetenschappelijke data (mbv. GIST indexes ed.).

Kortom, een performance vergelijking tussen MySQL en PSQL is niet zo interessant meer omdat de producten niet echt meer met elkaar te vergelijken zijn.

MariaDB vs MySQL zou dan wel weer grappig zijn.

[ACM]

MySQL is in diezelfde periode ook enorm gegroeid. Het is niet meer de database die niks snapt van sub-queries of er helemaal niks van bakt qua performance als de queries een beetje complexer worden.
PostgreSQL had een voorsprong bij de moeilijkere zaken en zal daar vast nog wel een deel van behouden hebben, maar ze hebben domweg beiden op o.a. dat vlak niet stil gestaan.

Anders gezegd; MySQL zit ook in de clientele van Oracle te roeren

[cariolive23]

Maar dan de hamvraag: Behoort zo'n (hardware) test met MySQL en PostgreSQL als vergelijkingsmateriaal nog tot de Tweaker-mogelijkheden?

Was in de tijd dat AMD nog meedeed met de Opterons....

reviews: Databasetest: achtvoudige Opteron

[ACM]

De software die we toen gebruikten om de boel te testen was eigenlijk niet erg correct. Bovendien deden we het destijds niet zozeer om databases onderling te vergelijken, maar om de servers te vergelijken.

Als we het willen doen om databases te vergelijken, moeten we ook in beide (of alle) versies evenveel tuning-effort steken en kennis in huis halen van de geteste versies.

Bovendien moet e.e.a. opnieuw opgezet worden om aan te sluiten op de tegenwoordig veel snellere hardware. Het kon destijds vrij goed draaien met slechts 4GB geheugen en dat is voor moderne databases natuurlijk niet een erg reeele test... Maar doe je iets wat meer geheugen vergt, dan heb je weer kans dat het I/O-bound wordt.

Het kostte uiteindelijk onze systeembeheerder, mij en een redacteur aardig wat werk per artikel. Ik verwacht niet dat we de komende tijd zoiets weer opnieuw gaan opbouwen.

[Reactie gewijzigd door ACM op 22 juli 2024 19:33]

[VolvoGek]

grappig feitje natuurlijk dat MySQL van Oracle is

[Anoniem: 352205]

Ik denk dat dat nier zozeer een keuze is geweest, maar meer een WoT erfenis uit 1998

[Kees]

Nou niet helemaal. WoT is door een fout van femme gemaakt in Msql (minisql).

Dat is redelijk snel rechtgezet naar MySQL, puur omdat er veel meer voorbeelden en software was die met MySQL samenwerkte en er was veel minder dat postgresql ondersteunde. Maar daar kwam vervolgens al snel zoveel data in dat een overstap naar een andere database niet triviaal meer was en werd er gekozen om nieuwe features te bouwen en niet te werken aan een overstap die voor de gebruikers volledig onzichtbaar zou zijn.

Er is dus voor MySQL gekozen omdat het bekender was (in '98-'99) er meer voorbeelden voor waren, en het te boek stond als sneller dan postgresql in die tijd voor eenvoudige queries. Helaas was het ook verdraait instabiel en crashde het nogal vaak.

[ACM]

Zo goed PostgreSQL in die tijd vziw ook niet, vziw waren de 6.5 en 7.0-releases pas de eerste complete/goede en met 7.4 in 2003 kreeg het een serieuze performance-verbetering. Maar dan heb je het dus ook al over releases van rond 2000.

[[NUT]]

EINDELIJK iemand die begrijpt hoe het zit, mooi artikel en een goed voorbeeld van wat ik al jaren roep.

PHP is handig voor de bovenste handelingen maar gebruikt daarbij relatief veel resources.

De (my)sql server daarintegen is veel geschikter om de data die je wil te combineren en in hapklare brokken in php te proppen.

De hoogste performance winsten heb ik in de afgelopen jaren dus ook geboekt met de wildste en meest exotische sql die ik kon uitwerken.

Joins, unions, subqueries, enzovoorts, de grootste was bijna 3 A4 blaadjes groot, maar het veegde wel de vloer aan met alles wat wie dan ook had gemaakt of gesuggereerd. Het verzamelde data in een 50 miljard regels grote tabel en verzamelde daarbij een set gegevens uit 10 andere tabellen waar van er 1 miljoenen regels bevatte.

Het was gemiddeld 10 seconden sneller dan de snelste alternatieve methode die niet 1 maar 8 queries nodig had.

Laat de sql server doen waar het goed in is en je haalt er meer uit dan je ooit zou dromen uit php te kunnen halen

[PatrickH89]

Performance moet meestal in balans zijn met onderhoudbaarheid. Zeker als er een orm wordt gebruikt is het vaak veel eleganter in code op te lossen om enkele simpele queries uit te voeren dan 1 zeer complexe en onderhoudskosten zijn eigenlijk altijd veel lager.

Ik begrijp de keuze als het performanceverschil dusdanig groot is dat je eigenlijk niet anders kan, maar meestal heb je dan al schaalbaarheidsissues die met nosql veel eenvoudiger en wederom goedkoper (onderhoud) op te lossen zijn.

Mijn ervaring is dat mensen die regelmatig (ik wil niet insinueren dat dat geldt voor jou) cq elke dag grote queries schrijven dit vaak eerder doen voor de sport of omdat men zich er comfortabel mee voelt dan of het de juiste oplossing is.

[Reactie gewijzigd door PatrickH89 op 22 juli 2024 19:33]

[[NUT]]

Mijn ervaringen met orm systemen is dat ze juist erg veel overhead kosten en hoewel ze misschien leesbaarder en daardoor makkelijker te onderhouden zijn (en daarmee misschien ook wel kosten effectief zijn) is het handiger (en kosten effectiever) om gewoon een database specialist in te huren in plaats van een php specialist die wel 'wat' van sql weet.

Vergis je niet, ik reken mezelf tot de php specialisten maar heb een redelijke mysql kennis waarmee ik aardig uit de voeten kan.

Vaak werken frameworks en orm systemen vanuit het perspectief van de (php) ontwikkelaar en in heel grote databases is dat domweg gewoon de verkeerde kijk richting.

[PatrickH89]

Maar alleen als de performance echt een issue is, want ook een DB specialist doet langer over het onderhouden dan het duurt om goed leesbare code te onderhouden.

[[NUT]]

Op zich heb je gelijk, maar ik moet wel zeggen dat een sql specialist sql code net zo goed en leesbaar kan maken voor mede sql specialisten als dat wij als ontwikkelaars dat in php voor elkaar kunnen.

Specialisten zijn niet zonder reden een specialist

[PatrickH89]

Net zo goed durf ik wel te betwijfelen, maar als je de hele dag met databases bezig bent zal het redelijk leesbaar zijn wellicht.

[vdws]

Goed gebouwde SQL queries zijn net zo goed onderhoudbaar als goed gebouwde PHP.

Ik heb met mijn achtergrond als Oracle backend programmeur met verbazing php en java code gezien waar data uit een sql database wordt gefilterd, 'gejoined' en gesorteerd op een manier die in mijn ogen 10x zo complex is dan het simpel in SQL op te lossen aan de database backend. Desnoods met wat slimme views om de complexiteit te verhullen.

[PatrickH89]

Net als bij SQL heb je natuurlijk ook bij PHP slecht geschreven code, dat staat hier verder los van. Als ik dat tegenkom heb ik waarschijnlijk dezelfde verbazing als jij. Filteren, sorteren etc. zijn natuurlijk gewoon een taak van een database.

[VolvoGek]

Dat komt omdat Java ontwikkelaars denken dat ze het altijd beter weten/kunnen.
De mooiste die ik ooit ben tegengekomen op dit vlak was een developer die beweerde dat een query via tomcat en java naar een Oracle database sneller was dan via PL/SQL.
Toen heb ik 'm maar uitgelegd dat dat onmogelijk waar kon zijn, en hem een cursus pl/sql aangeraden.

[BadpunK]

Dat is de reden waarom je in (grotere) organisaties ontwikkelstandaarden gebruikt in SQL. Daarbij moet je denken aan naamgevingen, volgorde opbouw en structuur.

[Feanathiel]

Hoe test je zo'n complexe query vervolgens (gewoon uit interesse)? Juist door je stukken code klein te houden kun je delen van het systeem testen, vervolgens door samenhangende tests die de samenhang testen. Op zo'n schaal (3 A4'tjes) heb je toch een ontelbaar aantal mogelijkheden te testen?

[[NUT]]

Dat was met dank aan de unions makkelijker dan je zou denken

Het verder uitbreiden van de dataset die je verzameld kan soms wel een nachtmerrie zijn, maar over het algemeen goed te doen.