Microsoft SQL Server-tietokantaratkaisun skaalaaminen ja big data

Jani Savolainen

Tämän blogin tarkoituksena on lisätä yleisesti tietoutta siitä, kuinka tietokantakeskeinen ohjelmistokehitys on välttämätöntä enterprise-tason järjestelmähankkeiden onnistumisen ja kannalta etenkin silloin, kun puhutaan big datasta. Blogissani otan kantaa lähinnä on-premises-ratkaisuihin, koska Microsoft Azure asettaa vielä tätä blogia kirjoittaessani tiettyjä rajoituksia ja reunaehtoja massiivisille SQL Server-pohjaisille tietokantaratkaisuille.

Usein tietokannan roolia vähätellään ohjelmistokehityksessä, vaikka se onkin miltei poikkeuksetta tärkein yksittäinen seikka tietojärjestelmän suorituskykyyn ja skaalautumiseen liittyen. SQL Server-tietokannat ovat monimutkaisia kokonaisuuksia, ja niihin kytkeytyy monenlaista skaalautumiseen, performanssiin ja saatavuuteen liittyvää osaamista. Hyvin harvoin kaikki nämä osa-alueet ovat yksittäisen tietokantaosaajan hallussa. Sen sijaan, mittavissa tietokantakeskeisissä ohjelmistokehityshankkeissa onkin varsin tavanomaista, että tietokanta-osaajia on useampi, kukin erikoistunut omalle osa-alueelleen:

• Data-arkkitehti (palvelininfrastruktuuri, virtualisointi, data-arkkitehtuuri, korkea käytettävyys, DR-suunnittelu, käyttäjät ja tietoturva)
• Tietomallintaja (ylätason käsitemallit ja tietokantamallinnus, indeksointi)
• Tietokantaohjelmoija (tietokantakyselyt ja optimointi, kuten indeksit ja konfiguraatiot)
• Datainsinööri (tietokantaintegraatiot, ETL ja ELT)
• DBA (tietokantarutiinit ja ylläpito)

SQL Server-tietokannan kokoluokan kasvaessa suurimpien käyttäjätaulujen osalta kymmeniin miljooniin riveihin, usein jopa kymmeniin miljardeihin riveihin tai enemmän, tietokanta ei enää skaalaa perinteisillä menetelmillä. Tällöin voidaan jo puhua big datasta SQL Serverin piirissä: Perinteiset keinot eivät enää riitä. Tämä tarkoittaa reaaliaikaisissa, operatiivisissa OLTP-kannoissa toistuvaa hidastuvuutta ja turhia käyttökatkoja, joskus jopa järjestelmän totaalista sakkaamista toimintakelvottomaksi, sekä tietovarastoissa liian pitkiä latausaikoja ja raporttien hidastumista, ym. konkreettista haittaa ja riskiä liiketoiminnalle. Tämän takia onkin ensiarvoisen tärkeää tunnistaa ne osa-alueet, joiden avulla tietokantaratkaisua voidaan systemaattisesti skaalata satoja, jopa tuhansia kertoja enemmän, kuin perus tietokantakehityksellä. Relaatiotietokantojen skaalaaminen jakaantuu erilaisiin osa-alueisiin. Näistä keskeisimpiä best practiceiden mukaisen data-arkkitehtuurin lisäksi ovat:

• Hyvä tietokantasuunnittelu
• Indeksointi ja statistiikka
• Partitiointi
• Materialisoidut näkymät ja Stored proseduret
• Tietokantakyselyiden optimointi
• Tietokantakonfiguraatiot
• Tietokantojen replikointi
• Vertikaalinen skaalaus (“rautaa rajalle”)
• In-Memory OLTP
• Caching
• Sharding (työläs, ei natiivi SQL Serverissä)
• Kapasiteettilähtöinen suunnittelu ja ennakointi

Jokaisesta näistä osa-alueesta olisi oikeutettua kirjoittaa oma bloginsa, mutta lähdetään nyt liikkeelle kuitenkin siitä, että kerron näistä osa-alueista lähinnä konseptuaalisesti sekä muutamia keskeisiä käytännön seikkoja.

Hyvä tietokantasuunnittelu

Hyvän tietokantasuunnittelun perusperiaatteena voidaan pitää sitä, että tietokanta normalisoidaan käyttötarkoituksensa mukaan. Esimerkiksi OLTP-tietokantaa suunniteltaessa tulee aina suunnitella tietokanta vähintään kolmanteen normaalimuotoon (3NF), jotta se suoriutuu runsaista pistemäisistä luku- ja kirjoitusoperaatioista mahdollisimman rivakasti. Vastaavasti tietovarastokannoissa on tyypillistä optimoida skeema ottamaan hyvin vastaan datamassoja suurina eräajoina ja raportoimaan massiivisia datamääriä tehokkaasti. Tämä taas vaatii tietokantaskeeman denormalisointia (2NF). Lue lisää tästä aiheesta varsin kattavasta blogisarjastani: Tietomallinnus – Osa 1: Intro – DB Pro Services

Indeksointi

Tehokas tietokanta elää ja hengittää indekseistä. Tämä on aivan oma taiteenlajinsa. Liikaa indeksejä ja päivitykset alkavat hidastua, liian vähän ja epäoptimaalisia indeksejä niin tietokantakyselyt alkavat hidastua. Väärät täyttöasteet fragmentoivat indeksejä syöden niiden tehokkuutta ja vanhentuneet indeksistatistiikat johtavat SQL Server kyselyoptimoijaa harhaan antaen epäoptimaalisia suoritussuunnitelmia. Hyvin suunniteltu indeksi taasen voi nopeuttaa hidasta tietokantakyselyä helposti jopa 1000-kertaisesti ja balansoidusti suunnitellut indeksit pitävät myös operatiiviset tietokantataulut suorituskykyisinä kaikissa tilanteissa. Kehittyneet indeksien ja statistiikan ylläpitorutiinit kuten DB Pro Servicen kehittämä ISM-komponentti turvaavat indeksien ja statistiikan suorituskyvyn myös silloin, kun niitä on enemmän kuin niitä ehtii päivittää ja datamäärät kasvavat teratavuihin tai enemmän. Indeksien tehokäyttöön liittyvät myös joskus Query Storen tehokas käyttö ja optimaalisten kyselyplänien tai indeksien eksplisiittinen pakottaminen silloin, kun Query Engine ei tähän pysty. Joskus joudutaan myös turvautumaan Legacy Cardinality Estimatorin käyttöön. Indeksointi lisää luonnollisesti myös levytilan tarvetta, useimmiten useita kymmeniä prosentteja.

Partitiointi

Partitioinnin ideana on pilkkoa SQL Serverin tietokantataulu vertikaalisesti siten, että se jaetaan useaan “alitauluun”, eli ns. partitioon. Partitiot ovat omia fyysisiä kokonaisuuksiaan, mutta muodostavat loogisesti ja eksplisiittisesti eheän kokonaisuuden, johon voi viitata aivan kuten muihinkin tietokantatauluihin. Partitioon liittyy looginen partitioavain, jonka perusteella kukin partitio eli n.s ”alitaulu” muodostuu. Partitioavaimessa voidaan viitata vaikkapa päivämäärään, jolloin partitioita syntyy yhtä monta kuin on päivämääriä taulussa kaikkiaan. Yleisesti nyrkkisääntönä on, että ~30 miljoonan rivin kokoinen tai sitä suurempi tietokantataulu kannattaa jo partitioida, koska tällöin taulun suorituskyky alkaa selvästi kärsiä. Partitioinnin suurimmat hyödyt ovat, että tietokantataulun suorituskyky säilyy riippumatta juurikaan taulun rivimäärästä, vaikka siellä olisi satoja miljardeja rivejä dataa. Toki tietokantakyselyn täytyy tuolloin kohdistua aina partitioavaimeen, jotta vältytään koko taulun skannaukselta ja sen sijaan haetaan data suoraan partitiosta. Toinen keskeinen partitioinnin hyödyistä on, että voidaan deletoida kokonaisia partitioita, vaikkapa à100M riviä, millisekunneissa sen sijaan, että deletointi kuormittaisi lokikantaa ja kestäisi rivi kerrallaan useita tunteja tai jopa päiviä, hidastaen radikaalisti koko tietokantaa. Kolmas hyöty on ns. ”partition switch”, jolla voidaan nopeuttaa esimerkiksi jatkuvasti muuttuvaa big dataa, kuten kuukausitason ETL-latauksia DW-kantaan vaikkapa tuoreimpien 3 kuukauden ajalta siten, että ladataan vastaavalla partitioinnilla implementoituun staging-tauluun ensin muuttunut data, ja sitten tehdään nopea ”partition switch” kuukausitason partitioihin, joka päivittää staging-taulun partitiot isoon historiatauluun. Operaatio tapahtuu yleensä millisekuinneissa, koska koko partitio ”istutetaan” kertaoperaationa kohdetauluun.

Materialisoidut näkymät ja stored proceduret

Materialisoidut näkymät, joita SQL Serverissä kutsutaan indeksoiduiksi näkymiksi, tarjoavat useita etuja, jotka voivat parantaa merkittävästi tietokannan suorituskykyä ja käytettävyyttä. Nopeampi tietokantakyselyn suorittaminen johtuu siitä, että  indeksoidut näkymät tallentavat kyselyn esilasketut tulokset, mikä lyhentää monimutkaisten tai resursseja vaativien kyselyjen suorittamiseen kuluvaa aikaa. Tämän lisäksi koska tulokset on esilaskettu ja tallennettu, järjestelmän ei tarvitse toistuvasti käyttää ja koota tietoja pohjana olevista taulukoista, mikä vähentää työmäärää. Näkymiin voi myös luoda omia indeksejä, mikä voi tehostaa kyselyitä entisestään. Vähentämällä järjestelmän laskennallista kuormitusta indeksoidut näkymät auttavat tietokantaa käsittelemään enemmän samanaikaisia käyttäjiä ja kyselyitä, mikä parantaa skaalautuvuutta. Myös kyselyt ovat yksinkertaisempia, koska indeksoidut näkymät voivat sisältää taulujen joinausta sekä aggregointia. Myös SQL Serverin Query Optimizer osaa hyödyntää tehokkaasti indeksoituja näkymiä. Vaikka indeksoidut näkymät ovatkin omiaan vähentämään esim. levy IO:ta, niiden haittapuolina mainittakoon ekstra levytila sekä kasvava kirjoituslatenssi (tietojen propagointi käyttäjätauluista näkymiin).

Stored proceduret ovat oiva tapa skaalata SQL Server -tietokannan suorituskykyä. Kun kapseloidaan useita T-SQL statementteja samaan batchiin proseduurin sisälle ja pitkien kyselyiden sijaan lähetetään pelkät parametrit osana proseduurikutsua, syntyy paljon vähemmän verkkoliikennettä clienttien ja serverin välille. Edelleen, SQL Server kääntää ja tallentaa stored proceruden suoritussuunnitelman muistiin sen ensimmäisen suorituksen yhteydessä. Seuraavat proseduurikutsut käyttävät suunnitelmaa uudelleen välttäen kyselyn parsimisen ja optimoinnin overheadia. Stored proceduret ovat myös vähemmän alttiita toistuvalle uudelleenkääntämiselle (recompilation) kuin ad hoc -kyselyt, varsinkin jos niissä käytetään parametreja. Keskittämällä logiikan tietokantaan stored proceduret vähentävät riippuvuutta client-sovellusten käsittelystä, mikä mahdollistaa paremman skaalautuvuuden korkean samanaikaisuuden OLTP-järjestelmissä. Stored proceduret myös vähentävät sovelluspalvelimien laskennallista kuormitusta suorittamalla raskasta tietojenkäsittelyä tietokannassa. Transaktionaalisuus voidaan myös kätevästi kapseloida osaksi stored proseduurien logiikkaa, jolloin tietokanta toimii mahdollisimman tehokkaasti.

Tietokantakyselyiden optimointi

Tietokantakyselyitä optimoimalla, samoin kuin tehokkaalla indeksoinnillakin, voidaan saavuttaa merkittäviä suorituskykyhyötyjä, käytännössä miltei yhtä merkittäviä kuin indeksoinnillakin. Esimerkiksi, huonosti tehty tietokantakysely ei osaa käyttää tietokantatauluun luotuja indeksejä, jolloin suoritussuunnitelma romahtaa. Tietokantaoptimointia tehtäessä tulee ymmärtää hyvin indeksien ja statistiikan taikutus, erilaisten muistinvaraisten ja persistoitujen taulurakenteiden, kursoreiden, operaattoreiden, joinien ja funktioiden vaikutus suorituskykyyn jne jne. Myös SQL Serverin versiolla on iso merkitys tässä asiassa. Tietokantakyselyiden optimoinnissa pääsee vauhtiin jo muutamassa päivässä, mutta tämän osa-alueen suvereeniin hallitsemiseen menee vuosia. Hyvän esimerkin tietokantaoptimoinnista löydät tuotesivuiltamme, liittyen komposiitti-indeksien käyttöön moniehtoisissa kyselyissä: SQL Server performance insights – OR vs UNION ALL is not always what it seems

Tietokantakonfiguraatiot

Käyttöjärjestelmätasontason asetukset, kuten power plan ja instanssitason konfiguraatiot, kuten max degree of parallelism ja cost threshold for parallelism sekä tietyt tietokanta-asetukset, kuten tempdb:n datatiedostojen konfigurointi, käyttäjätietokantojen filegroup- sekä datafileasetukset sekä tietyt tietokantaoptiot kuten statistiikkojen päivityssäännöt voivat kaikkiaan vaikuttaa yhdessä ja oikein konfiguroituina OLTP-tietokantapalvelimen suorituskykyyn helposti useita kymmeniä prosentteja, joskus jopa enemmän.

Tietokantojen replikointi

Tietokantojen replikointia voidaan harrastaa SQL Serverissä esimerkiksi log shipping-mekanismilla tietyissä rajoissa, sekä joustavammin ja reaaliaikaisesti Always On availability group -menetelmällä, joka on perinteisestä SQL Server -klusteroinnista pidemmälle kehitetty, tietokantatason HA-ratkaisu. Replikoinnin ideana on, että voidaan ns. ”ulos-skaalata”  operatiivista OLTP-työkuormaa, ja lukea dataa readable replikasta, joka on sijoitettu eri palvelimelle. Tällöin esimerkiksi ad hoc-raportointikuormat eivät rasita lainkaan operatiivista palvelinta. Edelleen; data engineerit voivat ladata operatiivisista kannoista eräajoina uusia datageneraatioita erilliselle tietovarastopalvelimelle ns. raportointikantaan eli datamarttiin, jolloin raportointi saadaan optimoitua sekä tietokantaskeeman että -kuorman kannalta optimaaliseksi.

Lue lisää log shippingistä täältä: Mitä tehdä SQL Server Log Shipping -ominaisuudella? – DB Pro Services.

Vertikaalinen skaalaus

Vertikaalisen skaalauksen idea on yksinkertainen: Jos tietokantapalvelimen muisti loppuu, lisää muistia. Jos prosessoriteho loppuu, lisää CPU:ta. Jos storage yskii, ota tiukempi tier tai dedikoitu storage käyttöön. Tässä on kuitenkin yksi ongelma: Tällöin hoidetaan ”oiretta, ei sairautta”, joka usein johtaa paisuvaan, hallitsemattomaan TCO:hon sekä huomattavasti järeämpiin SQL Server-lisenssikustannuksiin. SQL Server käyttää syystä eri resursseja paljon, ja jos telemetriikkaa devioi ns. baselinestä, on syytä olettaa, että jokin SQL Serverin syövereissä ei toimi tehokkaasti. Syypäänä on usein jokin asetus, indeksi, tietokantakysely tai tai muu vastaava. On paljon tehokkaampaa hoitaa kuntoon alkusyy kuin oire, ja usein tähän ”parantamiseen” riittää kokenut DBA. Joskus on kuitenkin aiheellista lisätä fyysistä kapasiteettia. Tämä on täysin normaalia, mutta sitä ei kannata tyypillisesti tehdä sokeasti, vaan vasta perusteellisen kapasiteetti- ja performanssidiagnostiikan jälkeen.

In-Memory OLTP

Tietokannan sisältöä voi myös nostaa tietokantaplvelininstanssin käyttömuistiin: In-Memory OLTP on SQL Serverin ominaisuus, joka on suunniteltu parantamaan merkittävästi tapahtumakuormitusten suorituskykyä hyödyntämällä muistissa olevia tietorakenteita ja optimoituja käsittelytekniikoita. Tämä SQL Server 2014:ssä esitelty ja myöhemmissä versioissa parannettu tekniikka sallii tiettyjen taulujen ja stored proseduurien sijaita ja suorittaa ne kokonaan muistissa.

Caching

Tallennusjärjestelmän levypintaa voidaan jakaa kylmään ja kuumaan dataan. Tällä tavalla saadaan skaalattua levyjärjestelmän performanssia niillä osa-alueilla, joissa se on oleellista. Relaatiotietokantojen tallennusvälimuistilla tarkoitetaankin välimuistimekanismin käyttöä usein käytettyjen tietojen tallentamiseksi nopeampiin tallennuskerroksiin, kuten muistiin, viiveen vähentämiseksi ja tietokannan suorituskyvyn parantamiseksi. Tallennusvälimuistia hyödyntämällä tietokannat voivat minimoida tarpeen hakea tietoja toistuvasti hitaammasta levypohjaisesta tallennustilasta, mikä parantaa merkittävästi luku- ja kirjoitustehokkuutta.

Sharding

Relaatiotietokannan sharding on tietokanta-arkkitehtuuritekniikka, joka partitioi tietokannan horisontaalisesti tietojen jakamiseksi useisiin tietokantoihin tai palvelimiin, joita kutsutaan shardeiksi. Jokainen shard sisältää avaimineen (=shard key) osan kokonaistiedoista, joiden avulla järjestelmät voivat skaalautua jakamalla kuorman useiden palvelimien kesken, mikä parantaa suorituskykyä, skaalautuvuutta ja vikasietoisuutta.

Huom! SQL Server ei tue shardingia natiivisti sisäänrakennettuna ominaisuutena, mutta sharding voidaan silti toteuttaa SQL Serverin sovellustason logiikan, tietokannan suunnittelutekniikoiden ja kolmannen osapuolen työkalujen tai frameworkkien yhdistelmällä.

SQL Serverin kapasiteettilähtöinen suunnittelu ja ennakointi

Mikäli haluat SQL Server-tietokanta-alustastasi TCO-optimaalisen, sekä sellaisen, että kapasiteetin pullonkaulat ja SLA:ta jyrsivät epäkäytettävyystilanteet eivät kuitenkaan pääse tekemään tuhojaan, on tärkeää mitata ja ennakoida tuotantoympäristön performanssin baselinea ja sen muutoksia jatkuvasti. Tähän erinomainen työkalu on SQL Governor-ohjelmisto, jolla voit ottaa koko data-alustasi performanssioptimoinnin ja kapasiteettioptimoinnin haltuun, riippumatta SQL Server data-alustasi kokoluokasta. Kiinnostuitko? Ole hyvä ja ota yhteyttä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy