Hjelp, jeg er blitt DBAJohan Evensberget

IntroJohan Benum Evensberget, DBA-by-accident. Informasjonsarkitekt, maskinlæringsekspert. Ferdig på Ifi 2011. Misliker trege datasystemer

Foredrag i dag:

● DBA-rollen og mine erfaringer● Litt om HW● En hel del om tabeller, indekser, og ORM● Hvordan jeg har opplevd DB brukt i praksis● Spør gjerne masse underveis!

Hva kan vi forvente av en tilfeldig valgt database?Det er masse eksemplarisk der ute, men ikke bli overrasket hvis du ser en eller flere av de følgende:

● Manglende backup :O :O● Trege spørringer● Dårlig indeksvedlikehold, databasen må “skrus av” om natten for defrag● Manglende indekser● Indekser som ikke burde vært der● Rare datatyper● FKer som mangler● Dårlig konfigurasjon● For beskjeden hardware● Isolasjonsnivåer

De gode nyhetene er at de fleste av disse problemene har løsninger!

SQL-databaser med litt tuning og forståelse for god bruk kan skalere til tusenvis av samtidige brukere og (noen) milliarder rader

Hjelp! Jeg har blitt DBA!

Typiske DBA oppgaver

Utvikle/forvalte spørringer e.g. rapporter, jobber

Lage nye tabeller

Bistå med datamodellering

Vedlikeholde databaser

Typiske utvikler-oppgaver

Utvikle/forvalte programkode

Lage nye skjermbilder / ny programlogikk

Lage persistering for ny funksjonalitet

For de fleste vil det være en glidende overgang mellom DBA og utvikler, og man er litt av begge deler samtidig

BACKUP

BackupHva er mest verdt? Dataene (og tilstanden) til et konkret system? Eller programkoden? Hvor mye koster et driftsavbrudd?

RAID/NAS/SAN: Disse tingene er ikke backup!

Vi har to hovedsituasjoner som kan resultere i datatap:

● Maskinen går i luften● Mennesker gjør feil

Menneskelig feil er som regel den vanligste årsaken. Det hjelper ikke at en skyleverandør har garantert oppetid på maskinvare hvis man tar DROP DATABASE på feil server! DBAer representerer forhøyet risiko for fat-fingering!

Oracle vs MSSQL vs PostgreSQL vs MySQL vs … SQL er en standard, og likhetene mellom de store databasene er større enn forskjellene. Det er likevel to ting som gjør livet vanskelig:

● De forskjellige plattformene etterlever standarden ganske ulikt, man kan ikke være sikker på at “best practice” på den ene plattformen er “best practice” på den andre.

● For å imøtekomme stadige økte krav og forventninger må vi ofte ty til verktøy som ligger utenfor standarden.

Ikke vær redd for å sette deg inn i dokumentasjonen på den databaseplattformen som skal brukes. Det er ofte utrolig mye å hente, og det alltid lettere å utnytte den plattformen man allerede har bedre enn å måtte migrere til en ny!

OvervåkningOvervåkning av serveren er dessverre ikke standardisert. Forskjellige løsninger på forskjellige plattformer. Overvåkning er allikevel viktig:

● For å sjekke om alt er i orden (HW, SW)● For å se om serveren har nok ressurser til å utføre spørringer● For å oppdage problem-spørringer som kanskje trenger ytelsestuning● For å oppdage om det mangler indekser

Hvis det ikke er satt opp noe overvåkningsregime for din løsning er dette noe man bør få til ASAP!

HW

HWHvorfor diskutere dette i 2019? Går ikke alt mot sky?

Likevel gunstig å ha en idé om hvilke typer ressurser en server krever, og hvordan diagnostisere problemer som dukker opp. Denne kunnskapen vil være nødvendig både for klassiske on-prem løsninger, men også for selv helt “managed” skyløsninger.

CPUCPU utfører beregninger, jo flere og større jo bedre?

Flere samtidige spørringer

Parallelliserbare spørringer (Postgres 9.6+, må skrus på!)

Lisensbegrensninger, mange kommersielle databaser krever lisens per kjerne! Det kan da være smart å velge de raskeste CPUene for et gitt kjerneantall. For alle kommersielle lisenser er lisenskostnaden alltid mye større en HW-kostnaden, så det lønner seg ofte å vurdere dette problemet. F.eks kan 8 veldig raske kjerner gi en server med mye bedre ytelse enn 10 tregere, og for en mye mindre lisensutgift.

RAM, Databaser

IO/Lagring og nettverk

I nesten alle tilfeller er det mye mye mer lesing enn skriving. Det er gode nyheter for det er mye lettere å cache resultater fra en leseoperasjon enn fra en skriveoperasjon. Hvorfor?

● Lese og skrive datafiler til og fra persistent lager. Disk, SAN, NAS● Skrive til loggfilen (WAL). Hva er viktig her? (Latency, falske

disk-kontrollere)● Tilfeldig vs sekvensiell IO. Hva går fortest?● Tilfeldig vs sekvensiell IO på SSDer. Hva går fortest nå?● Nettverk: Motta og sende spørringer og resultater til klienter

IO/Lagring og nettverk IIFor lite/dårlig IO-kapasistet er en svært vanlig en kilde til propper (kø-dannelse) i databasen. I en aktiv database med mange brukere kan proppene redusere tjenestenivået betraktelig! Typiske årsaker:

● Store og tunge spørringer som må lese mange rader● Backup● Annen aktivitet på serveren enn fra databasen● Selv om problemet tilsynelatende er for dårlig IO, er ofte både årsaken og løsningen

noe annet!

Vi kan redusere “IO-trykket” på flere måter:● Installere mer RAM som gir oss mulighet til å cache mer data● Skrive om tunge spørringer til forhåpentligvis bedre versjoner● Vurdere å legge på flere indekser (sjekk neste kapittel)● Hvis ingenting annet virker, forsøk å øke IO-kapasiteten

Tabellstrukturer

Hvorfor relasjonsdatabase i det hele tatt?La oss tenke etter, hva trenger en applikasjon av forskjellige komponenter?

● Brukergrensesnitt - brukerne må kunne kommunisere med applikasjonen● Logikk - vi ønsker at applikasjonen oppfører seg på en konkret måte● Lagring - de dataene vi ønsker å behandle

Veldig mange applikasjoner er en modell av virkeligheten, vi lar datamaskiner håndtere og manipulere prosesser, transaksjoner o.l og bruker deres overlegne evne til å lagre tilstand til å hjelpe oss å effektivisere noe. Hvis vi er litt fine på det kaller vi dette gjerne digitalisering.

LagringSQL lar oss lagre data. SQL lar oss også spørre data.

SQL definerer i teorien ingen “riktig” måte å spørre data på, alle spørringer er like mye verdt!

I sum betyr dette at hvis vi lager en vanlig relasjonsmodell for dataene våre kan vi stille alle tenkelige “spørsmål”, spørringer, og kjøre dem i samme datasystem som applikasjonen bruker.

I praktisk bruk er dette en av de største fordelene med klassiske SQL databaser, men man ser i dag stor konkurranse fra andre databasetyper. Viktig å passe på prosjektets egentlige mål, og ikke glemme verdien data gir! Husk også på transaksjoner!

TabellerTabellene danner grunnfjellet i datamodellen vår. Modellering av virkeligheten er noe av det vanskeligste vi gjør, og det er mye lettere å lage en dårlig datamodell enn en god. Vi har allikevel noen rettesnorer vi kan bruke:

● Gode tabellstrukturer må være tilpasset de spørringene de skal betjene!● Vi må modellere det applikasjonen trenger for å få utført oppgavene● Vi må også tilpasse dette til en SQL-verden, både for å lage et pragmatisk og

effektivt system, men også for å være lite overraskende for nestemann● Det lønner seg alltid å tenkte på hvilke utvidelser som kan forventes og ha en

plan for dem!● Ikke glem å ytelsesteste en DB med realistiske mengder data.

DenormaliseringVi lagrer gjerne data normalisert. Dette har en rekke fordeler og gjør det også mye enklere å lage spørringer på datasettet som ikke følger applikasjonsmodellen. Normalisering kan også by på utfordringer, særlig mtp ytelse. Det er særlig utfordringer med for mye JOINs man ønsker å få bukt med.

● I praktiske applikasjoner hender det ganske ofte at vi må denormalisere data for å lagre data tettere sammen. Det går alltid raskere å lese og skrive på samme sted enn flere.

● Det kan være vanskelig å forutsi ytelsesgevinsten kontra verditapet i økt kompleksitet. Lurt å se på andre alternativer først. Begynn alltid i det små. Behovet for denormalisering betyr ikke at man skal gi opp normalisering overalt.

● Flere DBMSer tilbyr JSON-aktige kolonnetyper, også med egne indeksvarianter! Der hvor datamodellen blir kompleks og veldig variert kan normalisering bli vanskelig. Bruk av JSON-kolonner kan gi stor gevinst her. Lurt å legge de mer “strukturerte” dataverdiene som klassiske kolonner og la de mer “kaotiske” være i JSON. Slik kan vi få det beste fra begge verdner.

IsolasjonsnivåerIsolasjon er kanskje det mest teoretiske, men allikevel mest sentrale som må være på plass i et databasesystem. Nesten alle DBer vil ha flere brukere samtidig, så vi må forholde oss til dette på noe vis.

● DBMS-versjonene varerer, dessverre, vilt. Flere samtidige strategier, låsing og MVCC. Begge med fordeler og ulemper

● Generell strategi: La (tung) lesing og skriving skje, med så lite som mulig venting

● Noen databaser tillater READ UNCOMITTED, (NOLOCK), og dette blir flittig benyttet av overivrige utviklere

● Låse-eskalering, og den fryktede tabell-låsen● Vranglås og låsing som default mekanisme (SQL Server)

ORMDen såkalte “object-relation impedance mismatch”. Vi må ofte forholde oss til en ORM, (Hibernate, SQLAlchemy, EntityFramework).

● Fordeler: Lettere å lage nye domeneobjeter. “Pakker vekk” tilstand og transaksjonshåndtering. L1 og L2 caching av objekter på appserveren

● Tilbyr “integererte” spørremuligheter. Går over L1 og L2 og SQL. ● Ulemper: Lager ikke alltid pragmatisk SQL, særlig for store objektgrafer. Øker

kompleksiteten i hele persisteringslaget, prosjektet må ha tilgang på både ORM og SQL-kompetanse. Lazy loading noen ganger nødvendig, men kompliserer applikasjonen.

● Kombinasjon av ORM og SQL for tyngre spørringer er ofte smart!

ORM og ArvTabeller og objekt-hierarki passer ikke i utgangspunktet godt sammen. Hvordan persistere objekter i et arvehierarki?

● Én tabell pr hierarki: Legger på en typekolonne for å diskrimenere mellom klasser. Verdier som bare finnes i undertypene blir NULL-kolonner ellers. Fungerer bra for “enkle” hierarkier. Vi slipper også en JOIN.

● Flere taballer pr hierarki: Gir mye større fleksibilitet, men vi må JOINe for å hente ut objekter.

● Pass også på “er en/har en”-relasjoner. Objekthierarki har ofte mye 1:1 relasjoner. Disse kan persisteres i sin egen tabell, eller fordeles utover på de tabellene som ellers persisterer “eier”-objektet. Dette er denormalisering! E.g. adresseinformasjon. Kompliserte systemer kan få mange FKer for holde på 1:1 relasjoner. Disse kan optimaliseres vekk.

NULLHvordan skal vi representere at en verdi mangler?

Flere strategier finnes:

● Nekte● Default-verdier (ALTER TABLE ADD DEFAULT)● “Hjemmelagde” verdi-mangler placeholdere● NULL

NULLFra vanlig toverdi-logikk til treverdi!

Kolonner som kan inneholde NULL-verdier oppfører seg annerledes enn vanlig intuisjon ( = NULL, IS NULL)

Hva skjer med aggereringer?

Hva skjer med NOT IN og NOT EXISTS?

NULL er en av de mer forvirrende konseptene i SQL, og det er helt vanlig at intuisjonen, selv til erfarne folk, ikke stemmer overens med virkeligheten.

Eksempel: NOT IN vs NOT EXISTSWHERE a NOT IN (SELECT foo FROM B)

WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.foo = a)

Disse spørringene ser helt like ut! Hva skjer hvis kolonnen ‘foo’ blir NULLABLE?

Eksempel: NOT IN vs NOT EXISTS (SQL Server)NOT IN: Vi må scanne hele tabellen for å se om det finnes en null-verdi. Hvorfor?

NOT EXISTS: Vi kan bruke et indeksoppslag som vanlig. Hvorfor?

Teori og praksis.

IndekserUheldigvis meget myteomspunnet tema!

Riktige indekser kan endre en database fra sirup til Formel-1

Riktige indekser kan redusere HW-krav, databasestørrelse og stressnivå til prosjektdeltagere

Riktige indekser kan føles vanskelig, men det finnes enkle huskeregler som nesten alltid vil gi 90% ut av boksen!

Hva er en indeksB-tre. Noen DBMSer har også andre varianter.

Må ha et utgangspunkt for å slippe å lese en hel tabell.

Indekser og indeksvedlikehold er viktig. Statistikk og histogram kan bli utdatert, og databasen “nekter” å bruke indekser.

For mange, for få eller feil indekser kan være helt ødeleggende.

Det er allikevel nesten alltid bedre å ha én, to eller kanskje tre for mange, enn én for lite.

Hva krever indekser (del 1)Alle nøkler. PK og FK.

FKer må også ha indekser på begge sider av nøkkelen. Hvorfor?

Hva krever indekser (del 1)Alle nøkler. PK og FK.

FKer må også ha indekser på begge sider av nøkkelen. Hvorfor?

Hvis tabell B har en FK til A. Hva skjer under INSERT / UPDATE i tabell B? Tabell A må sjekkes for FKen.

Hva skjer under DELETE / UPDATE i tabell A? Hele tabell B må sjekkes for FKen!

Hva krever indekser (del 2)JOINS kan også ha nytte av indekser. I mange tilfeller joiner vi FK mot PK. I ekstratilfellene må vi lage en eksplisitt indeks.

WHERE - trenger gjerne også indekser

I begge tilfeller ønsker vi å ha indekser med så høy selektivitet som mulig

Hva kan databasen selv fortelle om indekser?De fleste DBMSer opererer med detaljert statistikk om indeksbruk. Noen kan også gi advarsler om manglende indekser. I en ytelsespresset situasjon blir denne dataen meget viktig å følge med på.

Index organzied Tables (CLUSTERED INDEX)Tilbys av noen DBMSer, standard på SQL Server. Én indeks “blir” tabellen, de andre indeksene blir nesten som før. Viktig å huske på hva som blir cluster key nå!

Clustered index gir fordeler, men kommer også med ulemper:

● Trenger ikke gjøre bookmark lookup● INSERT-rekkefølge blir viktig!● Kan kreve ekstra vedlikehold

DatatyperDefinerer hva som kan og ikke kan lagres i en kolonne. Definerer også hvordan noe blir lagret

Bidrar til korrekthet! Bare tall kan lagres i INT. “Alt” kan lagres i VARCHAR

Bidrar også til ytelse!

Størrelsen på datatyper varierer vilt! Jo smalere jo bedre.

VARCHAR vs RåtyperAlt kan lagres i VARCHAR. Ferdig?

Tenk på en UUID: d5f647c6-66c9-11e9-a923-1681be663d3e

Lagret som varchar er dette 36, 72 (32,64) bytes stort. Som bytes er dette 16.

Tenk et løpenummer under 2147483648 (~ to milliarder). Som varchar er dette opptil 10,20 bytes stort. Som bytes er dette 4.

VARCHAR vs RåtyperBegge disse eksemplene var typiske IDer. I en normalisert database vil vi gjerne lagre mange IDer som PKer og FKer mange steder. Selv om forskjellen for én rad er isolert sett liten, blir dette stort når det kommer flere millioner rader og mange fremmednøkler. I kompliserte systemer er det ikke uvanlig å se tabeller med et titalls nøkler! Denne ekstra kostnaden må også bæres i alle ledd av hele systemet, appserver, integrasjonslag og klient!

DBen arbeider også med collation, lingvistiske regler for hvordan strenger skal sammenlignes. Det betyr at sammenligningsreglene (,=) blir betydelig mer kompliserte, og vi må invokere subrutiner for å utføre dem, isteden for å bruke vanlige CPU-instruksjoner. Dette gjør også at vi må materialisere hver rad i en table-scan, istedet for å bruke lavnivå kode.

Erfaring fra konvertering fra VARCHAR tilbake til bytes:Størrelsesreduksjon på ca 90%. OLTP-ytelse ca 2-3 ganger bedre. Rapporter flere hundre ganger bedre.

Hvor mange trinn trenger B-treet i en indeks?Hvor dyp er en indeks?

N = 1 + log{Nøkler Pr Page} (Antall datapage / Antall nøkler per Page) rundet opp

Med gode datatyper får vi flere hundre nøkler per page. P = 600 En indeks må ha milliarder pages for å bli nivå 4 og hundrevis av milliarder for fem

Med dårlige datatyper får vi bare noen få nøkler per page. P = 30 En indeks må ha ~ 100k pages for å ha dybde 4 og ~ 1M for dybde fem!

Avslutning og appellÅ være god i SQL er en av de mest verdifulle ferdighetene man kan tilegne seg som utvikler. Utrolig mange programmeringsoppgaver handler om å transformere et datasett til noe annet. SQL er uovertruffet for dette! SQL og DB-tuning er likevel plaget av flere myter. Vær ydmyk, men dokumenter og test ut alternative løsninger og hypoteser. Bruk EXPLAIN PLAN flittig! Det er en av de beste måtene å lære mer om hvordan databasen fungerer i praksis.

Vi ser også en stor utvidelse av databasesystemer som benyttes i dag, mye takket være skyløsninger. Allikevel er SQL basis for flere av dem, selv om den tradisjonelle “big-iron” databasen blir mindre vanlig. Ikke vær redd for å “løfte på lokket” og gå inn i detaljene på hvordan databasesystemer virker. Med solid bakgrunnskunnskap er det mindre vanskelig enn man skulle tro.

Noen ekstra kilder: