Grunnleggende tuning av MySQL

Nylig gjort erfaring tilsier at det er svært få som forstår grunnleggende tuning av MySQL, selv om det ikke er spesielt komplisert. Mange slår seg til ro hvis applikasjonen fungerer og svarer innen rimelig tid, men det er ofte mulig å oppnå langt bedre responstider for mange flere klienter hvis man bruker 30 minutter ekstra. Følgende er en kjapp innledning.

RAM

Du kan aldri ha for mye minne, og dette er spesielt viktig når du jobber med databaser. Ideelt sett bør du ha minst dobbelt så mye minne som det databasen bruker på disk. Årsaken er at en del av minnet skal brukes av MySQL, for å holde styr på sorterte rader og ting som folk har spurt om tidligere. Dette er forklart nærmere nedenfor.

Det andre er at minne som ikke brukes av applikasjoner brukes for å cache blokker fra disken. Det vil si at dersom den samme informasjonen blir etterspurt 4 ganger, eller mer enn andre blokker på disken, vil Linux beholde en kopi i minne. Dette sparer diskhodet for å bevege seg til denne posisjonen neste gang, reduserer responstiden og øker antall samtidige forespørsler maskinen kan håndtere.

Filsystemet

Filene ligger som regel i /var/lib/mysql. Teoretisk sette er det mulig å kjøre MySQL på partisjoner uten filsystem, men dette gir kun en ytelsesforbedring på 1 til 2 prosent og oppveier sjelden ulempene.

Det viktigste i denne sammeheng er at "noatime" er satt for den aktuelle partisjonen /etc/fstab . Dette gjør at du sparer en skriveoperasjon for hver fil som leses. For LAMP (Linux, Apache, MySQL, PHP) applikasjoner kan dette fort spare 40-50 per sidevisning.

Har du flere disker eller RAID bør du legge dataområdet på den raskeste disken. Merk at dette er langt mindre viktig dersom du har tilstrekkelig med minne. Du kan også slå av eller flytte logging til en separat disk, men det utgjør sjelden stor forskjell.

Buffers

Du har sikkert lært at databaser brukes indekser, det vil si sorterte lister, for å slå opp hvor informasjon ligger i databasen og så finne frem til den. Men for at dette skal gå raskt må disse indeksene være lett tilgjengelige, og det er her key_buffer kommer inn.

Har du en dedikert database server og drifter mange forskjellige databaser er det ikke helt urimelig å bruke halvparten av minne til key_buffer. Hvis du har god kjennskap til hva som kjører på maskinen kan du være mere presis:

Se på tabellene som har mange oppslag eller inneholder mange rader. Hvis det dreier seg om et forum kan du for eksempel finne tabellen med innlegg. Se hvor stor denne indeksen er, den kan ofte være større enn dataene som lagres, og sett key_buffer til minst denne verdien. Har du mange tabeller bør du vurdere mere, men så og si aldri mer enn halvparten av minnet i systemet.

Forutsatt at du har minst 2Gb minne, og at MySQL er en viktig del av maskinens oppgaver, er det greit å sette av en halv gigabyte:
key_buffer = 512M

Cache

Dette er kanskje de to viktigste innstillingene og se slik ut:
query_cache_limit = 1M
query_cache_size = 64M

Den andre sier at man totalt skal bruke 64 megabyte med minne for å lagre resultatet av tidligere spørringer. Et prima eksempel er oversikten over artikler på forsiden av Linux1.no. Denne endrer seg kun få ganger i løpet av en dag, så det er bra å ta vare på det gamle resultatet.

Ulempen med å ha en stor query_cache_size er at det blir mye å sjekke når informasjonen oppdateres. Det finnes ikke noen eksakt formel for hva denne verdien bør være, men phpMyAdmin (se i bunn av denne artikkelen) har noen råd. Den bør sjelden være betydelig mer enn 128 Mb, isåfall bør du heller tenke på om dine applikasjoner trenger caching på et høyere nivå.

query_cache_limit styrer størrelsen på resultatet som lagres. Det vil si at dersom mer enn 1 megabyte med tekst blir retunert så lagrer vi ikke dette. Dette er for å unngå at 5 spørringer på rad skal erstatte hele cachen. Igjen, dette avhenger av hvordan spørringer og resultater du forventer.

packet size

Packet size er hvor mye som kan sendes til og fra MySQL. Standard er ofte 1 megabyte, noe som kan være litt tynt for dagens applikasjoner. Ofte er 16 et godt kompromiss:

max_allowed_packet = 16M

TCP eller UNIX socket

Mange applikasjoner kan koble seg til database enten via TCP, det vil si på samme måte som til nettverkstjenester, eller via UNIX sockets. Det siste er en snarvei som man kan ta når programmene kjører på samme maskin, og gjør at man slipper å opprette forbindelsen hver gang, og pakkene trenger ikke å bli undersøkt av IPTables, gå gjennom route tabellen eller lignende.

I LAMP applikasjoner er forskjellen mellom disse to 127.0.0.1 (TCP) og localhost (UNIX), så sant både php.ini og my.cnf er konfigurert til å kunne bruke sockets. Det er de som regel.

"Persistent connections"

Et spørsmål som ofte dukker opp er om man skal bruke persistent connections eller ikke. I klartekst er dette om PHP skal holde forbindelsen til databasen åpen, slik at den kan brukes av flere. Men dette er ikke helt enkelt, blant annet blir det gjerne et par forbindelser som henger i løse luften i 60 sekunder før de forsvinner, så svaret er som regel nei. MySQL er laget for å kunne opprette forbindelser lynkjapt, spesielt når kommunikasjonen går gjennom UNIX sockets.

Unntaket er om man passerer firewaller og lignende på vei til databaseserveren. Disse forsinker i noen tilfeller når man åpner en ny TCP forbindelse, slik at det likevel lønner seg å gjenbruke de gamle.

MyISAM eller InnoDB?

MyISAM og InnoDB er databasemotorer som støttes av MySQL. Det vil si det er drivere som styrer hvordan informasjonen blir lagret på disken, noe som igjen påvirker hvordan operasjoner man kan gjøre direkte.

MyISAM er den enklere varianten og er først og fremst rask. Den mangler noen av de mer avanserte funksjonene, men så er heller ikke MySQL den foretrukne databasen for avanserte spørringer.

InnoDB kan eksempelvis være interessant hvis du ikke ønsker å låse hele tabeller, for InnoDB låser bare raden som leses i øyeblikket. Dette er kjekt hvis du for eksempel har et stort forum og ønsker at det skal være tilgjengelig mens sikkerhetskopiering foregår.

Gigabit Ethernet

Gigabit Ethernet er under de fleste omstendigheter langt bedre å bruke med databaser enn 100 Mbps. Årsaken til dette er først og fremst at responstiden er langt raskere, den økte båndbredden bidrar kun i liten grad. Dette hjelper altså spesielt hvis du jobber med mange små spørringer, kun i mindre grad dersom spørringene tar mer enn 10 millisekunder å eksekvere.

Sjekk dokumentasjonen

Hvis du skal kjøre en spesiell applikasjon, enten det er WordPress eller SugarCRM, bør du først sjekke dokumentasjonen. Noen applikasjoner har bruksmønster som gjør det fordelaktig å endre de nevnte parametere. Men hvis informasjonen er lagt ut av andre enn utviklerne bør du ta den med en klype salt, det finnes flere eksempler der ute hvor folk anbefaler å sette av en gigabyte til query cache, noe som sjelden er hensiktsmessig.

Verktøy

• top - følg med på minne og CPU bruk. Sjekk først og fremst at du ikke bruker swap og at mye minne er tilgjengelig for disk blokk caching. Deretter sjekk at prosenten foran "wa", på CPU linjen, er lav. Dette er tiden som prosessoren er i I/O wait, sannsynligvis venter den på harddisken.
• Munin - ofte er man ikke tilstede når problemet oppstår. Da er det greit om man har et verktøy som samler statistikk, så kan man fort se om det var et forbigående problem eller om det er et tidspunkt på dagen der diskene ikke klarer å ta unna trafikken.
• Hvis du oppdager en spørring som er sakte kan du bruke EXPLAIN for å se hvordan MySQL besvarer den.