Sharding Windows Azure SQL Database

Windows Azure SQL Database is een database in de cloud. Het is een goedkoop alternatief voor een peperdure on-premise SqlServer database. De licenties voor alleen SqlServer kunnen kostbaar zijn en daarbij komen dan ook nog eens de hostingkosten en het onderhoud daaraan. Vaak kun je bij je hostingprovider zo'n SqlServer huren (voor een web editie kan dat al gauw 30 euro in de maand zijn, maar dat hangt natuurlijk van de provider af). Voor Windows Azure Sql Database betaal je ongeveer 20 euro in de maand voor 5GB en 170 euro voor 150GB. Dat is dan ook meteen het maximum. Momenteel kan Windows Azure Sql Database maar 150GB groot zijn. Voor veel bedrijven vormt die beperking een belemmering om over te gaan naar Sql Azure. Ik vrees dat die limiet niet eens het grootste probleem zal vormen. In tegenstelling tot cloud services, waar je in drukke periodes in no-time een paar instanties erbij zet, laat Sql Azure zich niet zo eenvoudig opschalen. Jouw Windows Azure Sql Database draait op een machine met 8 Cpu's, 32GB RAM en 12 harddisks en daar zul je het mee moeten doen. Klinkt ruim voldoende, maar die machine moet je wel delen met andere gebruikers (ik geloof vier). Om ervoor te zorgen dat een gebruiker niet alle resources opeist, heeft men er een Engine Throttling ingehangen. Wanneer jij te veel resources gebruikt, word je gewoon afgeknepen. Dit kan zich beperken tot het blokkeren van inserts of updates tot een complete blokkade van alle connecties. Hiervoor krijg je dan een error code (40501) terug met een specifieke reden, waarna je het nogmaals mag proberen. Je kunt je dus afvragen of die 150GB ooit haalbaar is.

Uit dit verhaal zou je kunnen concluderen dat Windows Azure Sql Database voor grote databases knap waardeloos is, maar deze limieten heb je bij traditionele on-premise databases ook. Uiteindelijk zit je aan de grenzen van de hardware. Ook hier zou je een flexibele schaling willen, maar voor databases is dat niet zo eenvoudig. 
Een techniek die tegenwoordig veel gebruikt wordt is Database Sharding. Bij Sharding wordt een enorme tabel in stukken geknipt. Dit knippen gebeurt horizontaal, dus op basis van rijen. Denk bijvoorbeeld aan een wereldwijde klantentabel met miljoenen (linkedin, facebook) records. Je kunt zo'n tabel in stukken hakken door klanten per werelddeel te groeperen in hun eigen tabel. Je krijgt dus een tabel KlantenEuropa, KlantenAzie enz. Dit is overigens geen sharding. Dit is gewoon horizontale partitionering (afbeelding 1), een techniek die al een tijdje bestaat waarbij je de indexgrootte reduceert en dus sneller kunt zoeken. Sharding gaat een stap verder en plaatst die partities op verschillende databaseservers (afbeelding 2). Er komen ook geen tabellen zoals KlantenEurope, maar gewoon Klanten. Binnen hun serverinstantie zijn ze tenslotte tóch uniek. Met deze benadering heb je de voordelen van de traditionele horizontale partitionering plus de extra resources omdat zo'n partitie op een eigen machine kan draaien. Zo'n database-instantie, met zijn eigen tabelfragment, noemt men een shard.

Afbeelding 1. Traditionele horizontale partitionering op basis van leeftijd. De User tabel is in stukken geknipt, maar leeft wel binnen dezelfde database-instantie (bron www.cubrid.org).

Afbeelding 2. Dezelfde horizontale partitionering, maar nu zijn de partities op verschillende database-instanties geplaatst. Dit is Sharding (bron www.cubrid.org).

Dit klinkt allemaal heel mooi en aardig, maar in de praktijk loop je met dit soort grappen tegen een aantal beperkingen aan zoals:

1. In welke database staat de data die jij zoekt?
2. De ene shard wordt enorm (klanten uit Azië) terwijl een andere shard klein blijft (klanten uit Australië)
3. Hoe ga je overweg met joins?
4. Hoe zit het met gegenereerde primary keys?

Een aantal van deze problemen kan ondervangen worden door gebruik te maken van een shard key. Zo'n key beschrijft eigenlijk de kolom waarop je wilt sharden, bijvoorbeeld de kolom werelddeel in de klantentabel, maar het zou ook de leeftijd kunnen zijn, een datum of een getal. Wat je ook kiest, je zult een criterium moeten bepalen op basis waarvan de sharding plaatsvindt. Je kunt hierbij denken aan de volgende schema's:

1. Range. Denk hierbij een leeftijd (personen tussen 10 en 20 jaar) of datum (geboren tussen van 1980 tot 1990).
2. Key. Hierbij kun je denken aan een bepaalde wiskundige operatie. Stel je hebt als shard key een integer getal gekozen. Je kunt nu sharden op basis van even of oneven getallen. Beter is het om modulo te gebruiken (met een modulo bepaal je de restwaarde van een deling. 6 % 5 = 1, 7 % 5 = 2,  8 % 5 = 3, 10 % 5 = 0, 11 % 5 = 1, enz). Op basis van die restwaarde weet je naar welke database je moet schrijven. In dit rekenvoorbeeld zou je dus vijf shards krijgen.
3. Houdt een lookup tabel bij van je shard keys waarin je aangeeft in welke shard je de data moet zoeken.

Het joinen van tabellen is bij sharding lastig omdat deze joins over databases gaan. Je zou dat probleem kunnen ondervangen door gerelateerde data in dezelfde shard te plaatsen (een klant met zijn orders in dezelfde shard). Helaas zul je niet om handmatig bijlezen heen komen, immers de producten die bij een order horen kun je niet sharden. Voordeel is wel dat je die lookup query parallel kunt uitvoeren.

Het wordt ook vervelend bij sql-funties zoals AVG() (gemiddelde). Hierbij zul je het gemiddelde van iedere shard afzondelijk moeten bepalen en de resultaten zien te combineren om tot het eindresultaat te komen.

Het valt allemaal niet mee, maar als je het goed inricht en de juiste shard key kiest, heb je wel de ultieme schaalbare oplossing.

In Windows Azure SQL Database zit reeds een goede voorziening voor sharding, alleen gebruiken ze daar de naam federation. Een shard wordt een federation member en de shard key wordt een federation key. Sql Azure gebruikt een aparte database die bijhoudt welke federations er leven. Deze database noemt men de federation root.
Om een federated database op te zetten, begin je met het aanmaken van een gewone database in de Azure Portal (Zie eerdere blog). Wanneer je inlogt op de database en naar "Administration" gaat, kun je een nieuwe federation aanmaken (afbeelding 3). In het volgende venster (afbeelding 4) moet je de federation key specificeren. Je begint met een naam, vervolgens bepaal je wat voor een type de federation key is. Tenslotte moet je het sharding criterium opgeven. Momenteel weinig keus, we hebben alleen RANGE.

Afbeelding 3. Een nieuwe federation aanmaken

Afbeelding 4. De federation key definiëren

In Windows Azure SQL Database wordt nu de database met de federation root (fedtest) aangemaakt en een federation member. In de Azure portal kunnen we die federation ook zien (afbeelding 5)

Afbeelding 5. De federation root (fedtest) en een federation member (system-bd1c... de onderste).

Wanneer we vanuit de database portal naar de federation root gaan, krijgen we een overzicht van alle federation members (shards) die op dat moment aanwezig zijn (afbeelding 6). Hier heb je ook een overzicht van het verbruik van de diverse federation members. Daarnaast heb je ook toegang tot de diverse federation members en kun je, indien nodig, de database opschalen.

Afbeelding 6. Het overzicht van de federation members.

Allereerst gaan we maar eens de database aanmaken. Dat doe je door via het context menu van een federation member, de optie New Query te kiezen. Het leidt tot het venster in afbeelding 7. Bovenin zie je alvast een belangrijk (gegenereerd) statement staan:


USE FEDERATION (fed_brand = -1) WITH FILTERING = OFF, RESET
GO


Dit statement zorgt ervoor dat de queries nu naar de eerste (en vooralsnog enige) federation member gaan. (fed_brand = -1) geeft aan dat we de federation member met RANGE minimum -1 willen aanspreken, FILTERING = OFF geeft aan dat we alle federation keys willen aanspreken (Met waarde ON krijg je alleen het record, behorende bij de bij fed_brand gespecificeerde waarde, -1 in dit geval). RESET om de connectie te resetten.

Afbeelding 7. Een query die naar de eerste federation member gestuurd wordt.

Vervolgens komen er een aantal standaard DDL-statements die eindigen met een speciaal commando:

FEDERATED ON (BrandID = [ID])

De betekenis van dit statement is tweeledig: het geeft aan dat de data gesplitst kan worden en welk veld als federation key moet worden gebruikt (ID in dit geval). Bij de creatie van de producttabel zie je nog zo'n statement, maar nu op de foreign key van de producttabel. Ook deze tabel wordt nu in de splitsing meegenomen. Het zal ertoe leiden dat alle merken met hun bijbehorende producten in dezelfde federation member komen te staan (herinner je dat een join over shards niet mogelijk is).
Wanneer de tabel goed gevuld is, kun je hem gaan splitsten (afbeelding 8). Bij "Split" geef je aan vanaf welke waarde er gesplitst moet worden.

Afbeelding 8. Splitsten vanaf ID=4000

Uiteindelijk heb ik nu drie federation members aangemaakt zoals te zien is in afbeelding 9.

Afbeelding 9. De federation root (fedtest) met drie federation members.

We kunnen nu queries afvuren op de diverse federation members. Wel zullen we altijd eerst moeten aangeven op welke member de query uitgevoerd moet worden. Dit doen we via het statement USE FEDERATION ... Dus om een merk met een ID groter of gelijk aan 4000 te kunnen opvragen, specificeren we eerst dat we de federation member willen hebben waar 4000 in de range valt (mag overigens een willekeurig getal in die range zijn, dus 5387 was ook goed).

USE FEDERATION (fed_brand = 4000) WITH FILTERING = OFF, RESET
GO
SELECT * FROM Core.Brand

Deze query zal alle merken vanaf ID = 4000 terug geven. Als je FILTERING=ON zou specificeren, zou je alleen het merk met ID=4000 krijgen.

Sharding is een interessante techniek om schalen van databases mogelijk te maken. Windows Azure SQL Database heeft met federations een fraai initiatief gelanceerd, dat zeker perspectief biedt. Laat onverlet dat sharding een lastige techniek is om te gebruiken. Het vereist een goede keuze voor de sharding key. (Mijn keuze voor de primary key van Brand bleek niet zo'n juiste te zijn. Omdat de grote merken al in het begin van de tabel zaten, leverde dat ongebalanceerde shards op. Te veel producten in de eerste shard).
De volgende keer bespreek ik hoe je met Entity Framework de data uit een federated database trekt.