Som med alle relasjonsdatabaser, kan MySQL vise seg å være et komplisert dyr, en som kan krype til et stopp med et øyeblikk, og la applikasjonene dine være i sving og virksomheten din på linjen.
Sannheten er at vanlige feil ligger til grunn for de fleste MySQL-ytelsesproblemer. For å sikre at MySQL-serveren surrer sammen i toppfart, og gir stabil og jevn ytelse, er det viktig å eliminere disse feilene, som ofte blir tilslørt av noe subtilitet i arbeidsmengden eller en konfigurasjonsfelle.
Heldigvis viser mange MySQL-ytelsesproblemer seg å ha lignende løsninger, noe som gjør feilsøking og tuning av MySQL til en håndterbar oppgave.
Her er 10 tips for å få god ytelse ut av MySQL.
MySQL ytelsestips nr. 1: Profil arbeidsmengden din
Den beste måten å forstå hvordan serveren bruker tiden sin er å profilere serverens arbeidsmengde. Ved å profilere arbeidsmengden din kan du avsløre de dyreste spørsmålene for videre innstilling. Her er tid den viktigste beregningen, for når du utsteder et spørsmål mot serveren, bryr du deg lite om noe, bortsett fra hvor raskt den fullføres.
Den beste måten å profilere arbeidsmengden på er med et verktøy som MySQL Enterprise Monitors spørringsanalysator eller pt-query-digest fra Percona Toolkit. Disse verktøyene fanger opp spørsmålene serveren utfører, og returnerer en oppgavetabell sortert etter redusert rekkefølge på responstid, og spruter øyeblikkelig de dyreste og tidkrevende oppgavene til toppen slik at du kan se hvor du skal fokusere.
Verktøyprofileringsverktøy grupperer lignende spørsmål sammen, slik at du kan se spørsmålene som er treg, samt spørsmålene som er raske, men utført mange ganger.
MySQL ytelsestips nr. 2: Forstå de fire grunnleggende ressursene
For å fungere trenger en databaseserver fire grunnleggende ressurser: CPU, minne, disk og nettverk. Hvis noen av disse er svake, uberegnelige eller overbelastede, vil databaseserveren med stor sannsynlighet prestere dårlig.
Forståelse av de grunnleggende ressursene er viktig på to bestemte områder: valg av maskinvare og feilsøkingsproblemer.
Når du velger maskinvare for MySQL, må du sørge for at komponenter som fungerer godt rundt. Like viktig, balanser dem rimelig godt mot hverandre. Ofte vil organisasjoner velge servere med raske CPUer og disker, men som er sultet etter minne. I noen tilfeller er å legge til minne en billig måte å øke ytelsen med størrelsesordener, spesielt på arbeidsbelastninger som er diskbundet. Dette kan virke motstridende, men i mange tilfeller blir diskene overutnyttet fordi det ikke er nok minne til å holde serverens arbeidssett med data.
Et annet godt eksempel på denne balansen gjelder CPUer. I de fleste tilfeller vil MySQL fungere bra med raske CPUer fordi hvert spørsmål kjøres i en enkelt tråd og ikke kan parallelliseres på tvers av CPUer.
Når det gjelder feilsøking, sjekk ytelsen og bruken av alle fire ressursene, med et nøye øye for å avgjøre om de presterer dårlig eller bare blir bedt om å gjøre for mye arbeid. Denne kunnskapen kan bidra til å løse problemer raskt.
MySQL ytelsestips nr. 3: Ikke bruk MySQL som kø
Køer og kølignende tilgangsmønstre kan snike seg inn i applikasjonen din uten at du vet det. Hvis du for eksempel angir statusen til et element slik at en bestemt arbeidsprosess kan kreve det før du handler på det, oppretter du uforvarende en kø. Det er et vanlig eksempel å merke e-post som ikke sendt, sende dem og deretter merke dem som sendt.
Køer forårsaker problemer av to hovedårsaker: De serierer arbeidsmengden din, og forhindrer at oppgaver blir utført parallelt, og de resulterer ofte i en tabell som inneholder arbeid i prosess, samt historiske data fra jobber som ble behandlet for lenge siden. Begge legger til latens i applikasjonen og laster til MySQL.
MySQL ytelsestips nr. 4: Filtrer resultatene etter billigste først
En flott måte å optimalisere MySQL på er å gjøre billig, upresist arbeid først, deretter det harde, presise arbeidet med de mindre, resulterende datasettene.
Anta for eksempel at du leter etter noe innenfor en gitt radius av et geografisk punkt. Det første verktøyet i verktøykassen til mange programmerere er formelen for stor sirkel (Haversine) for beregning av avstand langs overflaten av en kule. Problemet med denne teknikken er at formelen krever mange trigonometriske operasjoner, som er veldig CPU-intensive. Stor-sirkelberegninger har en tendens til å løpe sakte og få maskinens CPU-bruk til å skyte i været.
Før du bruker storsirkelformelen, del opp postene dine til en liten delmengde av totalen, og trim det resulterende settet til en presis sirkel. En firkant som inneholder sirkelen (presist eller upresist) er en enkel måte å gjøre dette på. På den måten blir verden utenfor torget aldri truffet med alle de dyre trig-funksjonene.
MySQL ytelsestips nr. 5: Kjenn til de to skalerbarhetsdødsfellene
Skalerbarhet er ikke så vag som du kanskje tror. Faktisk er det presise matematiske definisjoner av skalerbarhet som uttrykkes som ligninger. Disse ligningene fremhever hvorfor systemer ikke skaleres så bra som de burde.
Ta Universal Scalability Law, en definisjon som er nyttig for å uttrykke og kvantifisere et systems skalerbarhetsegenskaper. Det forklarer skaleringsproblemer i form av to grunnleggende kostnader: serialisering og overhør.
Parallelle prosesser som må stoppes for at noe serieutviklet skal finne sted, er i seg selv begrenset i skalerbarhet. På samme måte, hvis de parallelle prosessene trenger å chatte med hverandre hele tiden for å koordinere arbeidet sitt, begrenser de hverandre.
Unngå serialisering og crosstalk, og applikasjonen din skaleres mye bedre. Hva oversettes dette til innsiden av MySQL? Det varierer, men noen eksempler vil være å unngå eksklusive låser på rader. Køer, punkt nr. 3 ovenfor, har en tendens til å skaleres dårlig av denne grunn.
MySQL ytelsestips nr. 6: Ikke fokuser for mye på konfigurasjon
DBA-er pleier å bruke enormt mye tid på å finjustere konfigurasjoner. Resultatet er vanligvis ikke en stor forbedring og kan til og med være veldig skadelig. Jeg har sett mange “optimaliserte” servere som krasjet konstant, gikk tom for minne og presterte dårlig når arbeidsmengden ble litt mer intens.
Standardinnstillingene som leveres med MySQL er one-size-fits-none og dårlig utdaterte, men du trenger ikke å konfigurere alt. Det er bedre å få grunnleggende rett og endre andre innstillinger bare om nødvendig. I de fleste tilfeller kan du få 95 prosent av serverens toppytelse ved å angi omtrent 10 alternativer riktig. De få situasjonene der dette ikke gjelder, vil være kanten tilfeller som er unike for omstendighetene dine.
I de fleste tilfeller anbefales ikke "tuning" -verktøy fordi de pleier å gi retningslinjer som ikke gir mening for spesifikke tilfeller. Noen har til og med farlige, unøyaktige råd kodet inn i dem - for eksempel cache-treffforhold og minneforbrukformler. Disse hadde aldri rett, og de har blitt enda mindre korrekte etter hvert som tiden har gått.
MySQL ytelsestips nr. 7: Se opp for spørsmål om paginering
Applikasjoner som paginerer, har en tendens til å bringe serveren på kne. Ved å vise deg en resultatside, med en lenke for å gå til neste side, grupperer og sorterer disse applikasjonene på måter som ikke kan bruke indekser, og de bruker en GRENSE og forskyvning som får serveren til å gjøre mye arbeid med å generere, og deretter kaste rader.
Optimaliseringer finner du ofte i selve brukergrensesnittet. I stedet for å vise nøyaktig antall sider i resultatene og lenker til hver side individuelt, kan du bare vise en lenke til neste side. Du kan også forhindre at folk går til sider for langt fra første side.
På spørresiden, i stedet for å bruke GRENSE med forskyvning, kan du velge en rad til enn du trenger, og når brukeren klikker på "neste side" -linken, kan du angi den siste raden som utgangspunkt for neste sett med resultater. For eksempel, hvis brukeren så på en side med rad 101 til 120, ville du også velge rad 121; for å gjengi neste side, vil du spørre serveren etter rader som er større enn eller lik 121, grense 21.
MySQL-ytelsestips nr. 8: Lagre statistikk ivrig, våken motvillig
Overvåking og varsling er viktig, men hva skjer med det typiske overvåkingssystemet? Det begynner å sende falske positive, og systemadministratorer setter opp e-filtreringsregler for å stoppe støyen. Snart er overvåkingssystemet ditt helt ubrukelig.
Jeg liker å tenke på overvåking på to måter: fange beregninger og varsle. Det er veldig viktig å fange opp og lagre alle beregningene du kan, fordi du vil være glad for å ha dem når du prøver å finne ut hva som endret seg i systemet. En dag vil det oppstå et merkelig problem, og du vil elske muligheten til å peke på en graf og vise en endring i serverens arbeidsmengde.
Derimot er det en tendens til å varsle altfor mye. Folk varsler ofte om ting som buffer hit ratio eller antall midlertidige tabeller opprettet per sekund. Problemet er at det ikke er noen god terskel for et slikt forhold. Den rette terskelen er ikke bare forskjellig fra server til server, men fra time til time når arbeidsmengden endres.
Som et resultat må du varsle sparsomt og bare om forhold som indikerer et bestemt, handlingsbart problem. Et lavt buffer treffforhold kan ikke brukes, og det indikerer heller ikke et reelt problem, men en server som ikke svarer på et tilkoblingsforsøk, er et reelt problem som må løses.
MySQL ytelsestips nr. 9: Lær deg de tre reglene for indeksering
Indeksering er sannsynligvis det mest misforståtte emnet i databaser fordi det er så mange måter å bli forvirret om hvordan indekser fungerer og hvordan serveren bruker dem. Det krever mye innsats for å virkelig forstå hva som skjer.
Når indeksene er riktig utformet, tjener de tre viktige formål i en databaseserver:
- Indekser lar serveren finne grupper av tilstøtende rader i stedet for enkeltrader. Mange tror formålet med en indeks er å finne individuelle rader, men å finne enkeltrader fører til tilfeldige diskoperasjoner, noe som er tregt. Det er mye bedre å finne grupper av rader, som alle eller de fleste er interessante, enn å finne rader en om gangen.
- Indekser lar serveren unngå sortering ved å lese radene i ønsket rekkefølge. Sortering er kostbart. Å lese rader i ønsket rekkefølge er mye raskere.
- Indekser lar serveren tilfredsstille hele spørsmål fra indeksen alene, og unngår i det hele tatt behovet for å få tilgang til tabellen. Dette er forskjellig kjent som en dekningsindeks eller et bare indeksforespørsel.
Hvis du kan designe indeksene og spørsmålene dine for å utnytte disse tre mulighetene, kan du gjøre spørsmålene dine flere størrelsesordener raskere.
MySQL ytelsestips nr. 10: Utnytt ekspertisen til jevnaldrende
Ikke prøv å gå alene. Hvis du lurer på et problem og gjør det som virker logisk og fornuftig for deg, er det flott. Dette fungerer omtrent 19 ganger av 20. Den andre gangen vil du gå ned i et kaninhull som vil være veldig kostbart og tidkrevende, nettopp fordi løsningen du prøver ser ut til å være veldig fornuftig.
Bygg et nettverk av MySQL-relaterte ressurser - og dette går utover verktøysett og feilsøkingsveiledninger. Det er noen ekstremt kunnskapsrike mennesker som lurer på adresselister, fora, Q&A nettsteder og så videre. Konferanser, messer og lokale brukergruppearrangementer gir verdifulle muligheter for å få innsikt og bygge relasjoner med jevnaldrende som kan hjelpe deg i en klype.
For de som leter etter verktøy for å utfylle disse tipsene, kan du sjekke ut Percona Configuration Wizard for MySQL, Percona Query Advisor for MySQL og Percona Monitoring Plugins. (Merk: Du må opprette en Percona-konto for å få tilgang til de to første koblingene. Det er gratis.) Konfigurasjonsveiviseren kan hjelpe deg med å generere en grunnleggende my.cnf-fil for en ny server som er bedre enn prøvefilene som leveres med server. Spørringsrådgiveren vil analysere SQL-en din for å oppdage potensielt dårlige mønstre som pagineringsspørsmål (nr. 7). Percona Monitoring Plugins er et sett med plugins for overvåking og grafikk som hjelper deg med å lagre statistikk ivrig og varsle motvillig (nr. 8). Alle disse verktøyene er fritt tilgjengelige.
