Peter Zaitsev er medstifter og administrerende direktør forPercona.
En av måtene vi måler applikasjoner på er gjennom ytelse. En av beregningene for applikasjonsytelse er brukeropplevelse, som vanligvis oversettes til "trengte brukeren å vente lenger enn rimelig tid for å få det de ønsket".
Denne beregningen kan bety forskjellige ting i forskjellige scenarier. For en mobil shopping-app kan svartidene ikke være mer enn et par sekunder. For en ansattes HR-side kan svarene ta noen sekunder lenger.
Vi har mye forskning på hvordan ytelse påvirker brukeradferd:
- 79 prosent av kundene kommer mindre sannsynlig til å gå tilbake til et tregt nettsted
- 47 prosent av forbrukerne forventer at en webside lastes inn på to sekunder eller mindre
- 40 prosent av brukerne forlater et nettsted hvis det tar lengre tid enn tre sekunder å laste det inn
- En forsinkelse på innlastingstid på ett sekund kan føre til tap på 7 prosent i konvertering og 11 prosent færre sidevisninger
Uansett standard, er det viktig å opprettholde god ytelse for applikasjoner. Ellers vil brukere klage (eller verre, gå til et annet program). En av faktorene som påvirker applikasjonsytelsen, er databaseytelse. Samspillet mellom applikasjoner, nettsteder og databaser er avgjørende for å fastslå nivået på applikasjonsytelsen.
En sentral komponent i denne interaksjonen er hvordan applikasjoner spør etter databasen og hvordan databasen svarer på forespørsler. Uansett er MySQL et av de mest populære databasestyringssystemene. Flere bedrifter skifter til MySQL (og andre open source-databaser) som en databaseløsning i sine produksjonsmiljøer.
Det er mange metoder for å konfigurere MySQL som kan bidra til at databasen din reagerer raskt på spørsmål og med et minimum av nedbrytning av applikasjonsytelse.
Følgende er noen viktige tips som hjelper deg med å optimalisere ytelsen til MySQL-databasen.
MySQL optimaliseringsnøkkel nr. 1: Lær hvordan du bruker FORKLARE
De to viktigste beslutningene du tar med en hvilken som helst database, er å utforme hvordan relasjoner mellom applikasjonsenheter blir kartlagt til tabeller (databaseskjemaet) og å utforme hvordan applikasjoner får dataene de trenger i det formatet de trenger det (spørringer).
Kompliserte applikasjoner kan ha kompliserte skjemaer og spørsmål. Hvis du skal oppnå ytelsen og skalere applikasjonene dine, kan du ikke bare stole på intuisjon for å forstå hvordan spørsmål blir utført.
I stedet for å gjette og håpe, bør du lære hvordan du bruker FORKLARE kommando. Denne kommandoen viser deg hvordan en spørring vil bli utført, og gir deg innsikt i både hvilken ytelse du kan forvente deg og hvordan spørringen skaleres med endret datastørrelse.
Det finnes en rekke verktøy - for eksempel MySQL Workbench - som kan visualisere FORKLARE output for deg, men du må fortsatt forstå det grunnleggende for å få mening om det.
Det er to forskjellige formater der FORKLARE kommandoen gir utdata: det gammeldags tabellformatet, og et mer moderne, strukturert JSON-dokument som gir betydelig mer detaljer (vist nedenfor):
mysql> forklar format = json velg avg (k) fra sbtest1 hvor id mellom 1000 og 2000 \ G*************************** 1. rad ******************* *******
FORKLARE: {
“Query_block”: {
“Select_id”: 1,
“Cost_info”: {
“Query_cost”: “762.40”
“Bord”: {
“Table_name”: “sbtest1”,
“Access_type”: “range”,
“Mulige_taster”: [
"HOVED"
],
“Nøkkel”: “PRIMÆR”,
“Used_key_parts”: [
“Id”
],
“Key_length”: “4”,
“Rows_examined_per_scan”: 1874,
“Rows_produced_per_join”: 1874,
“Filtrert”: “100,00”,
“Cost_info”: {
“Read_cost”: “387.60”,
“Eval_cost”: “374.80”,
“Prefix_cost”: “762.40”,
“Data_read_per_join”: “351K”
},
“Brukt_kolonner”: [
“Id”,
“K”
],
“Attached_condition”: “(` sbtest`. `Sbtest1`.` Id 'mellom 1000 og 2000) "
}
}
}
En komponent du bør se på er "spørringskostnad." Spørringskostnad refererer til hvor dyrt MySQL vurderer denne spesielle spørringen når det gjelder den totale kostnaden for spørringskjøring, og er basert på mange forskjellige faktorer.
Enkle spørsmål har vanligvis en kostnad på mindre enn 1000. Spørringer med en kostnad mellom 1000 og 100.000 regnes som mellomstore spørsmål, og er vanligvis raske hvis du bare kjører hundrevis av slike spørsmål per sekund (ikke titusenvis).
Spørringer med en kostnad på mer enn 100.000 er dyre spørsmål. Ofte kjører disse spørsmålene fort når du er en enkelt bruker på systemet, men du bør tenke nøye gjennom hvor ofte du bruker slike spørsmål i de interaktive applikasjonene dine (spesielt etter hvert som antallet brukere øker).
Dette er selvfølgelig tall for ballpark-ytelse, men de viser det generelle prinsippet. Systemet ditt kan håndtere søkearbeid bedre eller verre, avhengig av arkitektur og konfigurasjon.
En av faktorene som bestemmer spørringskostnadene, er om spørringen bruker indekser riktig. De FORKLARE kommandoen kan fortelle deg om et spørsmål ikke bruker indekser (vanligvis på grunn av hvordan indeksene blir opprettet i databasen, eller hvordan selve spørringen er konstruert). Det er derfor det er så viktig å lære å bruke FORKLARE.
MySQL optimaliseringsnøkkel nr. 2: Lag de rette indeksene
En indeks forbedrer søkeytelsen ved å redusere datamengden i databasen som spørringene må skanne. Indekser i MySQL brukes til å få raskere tilgang til databasen og hjelpe til med å håndheve databasebegrensninger (for eksempel UNIK og UTENLANDSK NØKKEL).
Databaseindekser er omtrent som bokindekser. De oppbevares på sin egen plassering, og de inneholder informasjon som allerede er i hoveddatabasen. De er en referansemetode eller et kart hvor data ligger. Indekser endrer ikke dataene i en database. De peker bare på plasseringen av dataene.
Det er ingen indekser som alltid passer for arbeidsbelastning. Du bør alltid se på indekser i sammenheng med spørsmålene som systemet kjører.
Velindekserte databaser kjører ikke bare raskere, men til og med en enkelt manglende indeks kan redusere en database til en gjennomgang. Bruk FORKLARE (som anbefalt tidligere) for å finne manglende indekser og legge dem til. Men vær forsiktig: Ikke legg til indekser du ikke trenger! Unødvendige indekser sakte databaser ned (sjekk presentasjonen min om MySQL indeksering av beste praksis).
MySQL optimaliseringsnøkkel # 3: Ingen standardverdier!
Som enhver programvare har MySQL mange konfigurerbare innstillinger som kan brukes til å endre atferd (og til slutt ytelse). Og som hvilken som helst programvare, blir mange av disse konfigurerbare innstillingene ignorert av administratorer og ender opp med å bli brukt i standardmodus.
For å få best mulig ytelse fra MySQL, er det viktig å forstå de konfigurerbare MySQL-innstillingene og - enda viktigere - sette dem til å fungere best for databasemiljøet ditt.
Som standard er MySQL innstilt for en liten utviklingsinstallasjon, ikke for produksjonsskala. Du vil vanligvis konfigurere MySQL til å bruke alle tilgjengelige minnesressurser, samt å tillate antall tilkoblinger som applikasjonen din krever.
Her er tre MySQL ytelsesjusteringsinnstillinger som du alltid bør undersøke nøye:
innodb_buffer_pool_size: Bufferpoolen er der data og indekser er bufret. Dette er hovedårsaken til at du bruker et system med mye RAM som databaseserver. Hvis du bare kjører InnoDB-lagringsmotoren, tildeler du vanligvis rundt 80 prosent av minnet til buffergruppen. Hvis du kjører veldig kompliserte spørsmål, eller hvis du har veldig mange samtidige databaseforbindelser, eller hvis du har veldig mange tabeller, må du kanskje ta denne verdien ned et hakk for å tildele mer minne til andre formål.
Når du angir størrelsen på InnoDB-bufferbassenget, må du sørge for at du ikke angir det for stort, ellers vil det føre til bytte. Dette dreper absolutt databasens ytelse. En enkel måte å sjekke er å se på Bytteaktivitet i Systemoversikt-grafen i Percona Monitoring and Management:
PerconaSom denne grafen viser, er det noe fint å bytte. Hvis du imidlertid ser vedvarende bytteaktivitet på 1 MB per sekund eller mer, må du redusere størrelsen på bufferbassenget (eller annen minnebruk).
Hvis du ikke får verdien for innodb_buffer_pool_size riktig på første gang, ikke bekymre deg. Fra og med MySQL 5.7 kan du endre størrelsen på InnoDB-bufferbassenget dynamisk uten å starte databaseserveren på nytt.
innodb_log_file_size: Dette er størrelsen på en enkelt InnoDB-loggfil. Som standard bruker InnoDB to verdier slik at du kan doble dette tallet for å få størrelsen på det sirkulære omloggningsområdet InnoDB bruker for å sikre at transaksjonene dine er holdbare. Dette optimaliserer også bruk av endringer i databasen. Omgivelser innodb_log_file_size er et spørsmål om avveininger. Jo større omplassering du tildeler, desto bedre ytelse oppnår du for en skriveintensiv arbeidsbelastning, men jo lenger tid er det for krasjgjenoppretting hvis systemet lider av strømtap eller andre problemer.
Hvordan vet du om MySQL-ytelsen din er begrenset av din nåværende InnoDB-loggfilstørrelse? Du kan fortelle ved å se på hvor mye av det brukbare omstillingsplassen som faktisk brukes. Den enkleste måten er å se på Percona Monitoring and Management InnoDB Metrics dashboard. I grafen nedenfor er InnoDB-loggfilstørrelsen ikke stor nok, da plassen som brukes skyver veldig nær hvor mye brukbar omplassering av loggplass som er tilgjengelig (angitt med den røde linjen). Loggfilstørrelsen din skal være minst 20 prosent større enn mengden plass som brukes for å holde systemet optimalt.
Perconamax_connections: Storskala applikasjoner krever ofte mye mer enn standard antall tilkoblinger. I motsetning til andre variabler, vil du ikke ha ytelsesproblemer (i seg selv) hvis du ikke stiller dette riktig. I stedet, hvis antall tilkoblinger ikke er tilstrekkelig for applikasjonsbehovene dine, vil applikasjonen ganske enkelt ikke kunne koble til databasen (som ser ut som nedetid for brukerne). Å få denne variabelen riktig er viktig.
Det kan være vanskelig å vite hvor mange tilkoblinger du trenger for komplekse applikasjoner med mange komponenter som kjører på flere servere. Heldigvis gjør MySQL det veldig enkelt å se hvor mange tilkoblinger som brukes ved toppdrift. Vanligvis vil du sørge for at det er minst 30 prosent avstand mellom maksimalt antall tilkoblinger applikasjonen din bruker og maksimalt antall tilgjengelige tilkoblinger. En enkel måte å se disse tallene på er å bruke MySQL Connections Graph i MySQL Oversikt-dashbordet i Percona Monitoring and Management. Grafen nedenfor viser et sunt system, der det er et stort antall ekstra tilkoblinger tilgjengelig.
PerconaEn ting å huske på er at hvis databasen din går sakte, skaper applikasjoner ofte et stort antall tilkoblinger. I slike tilfeller bør du jobbe med databasens ytelsesproblem i stedet for bare å tillate flere tilkoblinger. Flere forbindelser kan gjøre det underliggende ytelsesproblemet verre.
(Merk: Når du stiller inn max_connections variabel betydelig høyere enn standardverdien, må du ofte vurdere å øke andre parametere, for eksempel størrelsen på tabellbufferen og antall åpne filer MySQL tillater. Dette går imidlertid utover omfanget av denne artikkelen.)
MySQL optimaliseringsnøkkel # 4: Hold databasen i minnet
Vi har sett en overgang til solid state-stasjoner (SSD) de siste årene. Selv om SSD-er er mye raskere enn å spinne harddisker, er de likevel ingen kamp for å ha data tilgjengelig i RAM. Denne forskjellen kommer ikke bare fra selve lagringsytelsen, men også fra tilleggsarbeidet databasen må gjøre når den henter data fra disk eller SSD-lagring.
Med de siste maskinvareforbedringene er det stadig mer mulig å få databasen din i minne - enten du kjører i skyen eller administrerer din egen maskinvare.
Den enda bedre nyheten er at du ikke trenger å passe hele databasen din i minnet for å få et flertall av ytelsesfordelene i minnet. Du trenger bare å passe arbeidssettet med data i minnet - dataene som er mest tilgjengelige.
Du har kanskje sett noen artikler som inneholder noen spesifikke tall om hvilken del av databasen du skal ha i minnet, fra 10 prosent til 33 prosent. Det er faktisk ikke noe "one size fits all" -nummer. Mengden data som passer inn i minnet for best ytelsesfordel, er arbeidsmengderelatert. I stedet for å lete etter et spesifikt “magisk” nummer, bør du sjekke hvor mye I / O databasen kjører i jevn tilstand (vanligvis noen få timer etter at den er startet). Se på leser, fordi lesinger kan elimineres fullstendig hvis databasen din er i minnet. Skriving vil alltid måtte skje, uansett hvor mye minne du har tilgjengelig.
Nedenfor kan du se at I / O skjer i InnoDB I / O-grafen i InnoDB Metrics dashboard i Percona Monitoring and Management.
PerconaI grafen over ser du pigger så høyt som 2000 I / O-operasjoner per sekund, noe som viser at databasens arbeidssett ikke passer godt inn i minnet (i det minste for noen deler av arbeidsmengden).
MySQL optimaliseringsnøkkel nr. 5: Bruk SSD-lagring
Hvis databasen din ikke passer i minnet (og selv om den gjør det), trenger du fortsatt rask lagring for å håndtere skriving og for å unngå ytelsesproblemer når databasen varmes opp (rett etter omstart). I disse dager betyr rask lagring SSD-er.
