Programmering

7 nøgler til bedre MySQL-ydeevne

Peter Zaitsev er medstifter og administrerende direktør forPercona.

En af måderne vi måler applikationer på er ydeevne. En af målingerne for applikationsydelse er brugeroplevelse, som generelt oversættes til "behøvede brugeren at vente længere end en rimelig tid for at få det, de ønskede."

Denne måling kan betyde forskellige ting i forskellige scenarier. I en mobil shopping-app kan svartiderne ikke være mere end et par sekunder. For en medarbejders HR-side kan svarene muligvis tage et par sekunder længere.

Vi har meget forskning i, hvordan ydeevne påvirker brugeradfærd:

  • 79 procent af kunderne er mindre tilbøjelige til at vende tilbage til et langsomt websted
  • 47 procent af forbrugerne forventer, at en webside indlæses på 2 sekunder eller mindre
  • 40 procent af brugerne opgiver et websted, hvis det tager længere tid end tre sekunder at indlæse det
  • En forsinkelse på et sekund i sideindlæsningstiden kan medføre et tab på 7 procent i konvertering og 11 procent færre sidevisninger

Uanset hvilken standard det er, er det vigtigt at opretholde en god ydelse til applikationer. Ellers klager brugerne (eller værre, gå til en anden applikation). En af de faktorer, der påvirker applikationsydelsen, er databaseydelse. Samspillet mellem applikationer, websteder og databaser er afgørende for at fastslå applikationspræstationsniveauet.

En central komponent i denne interaktion er, hvordan applikationer søger i databasen, og hvordan databasen reagerer på anmodninger. Under alle omstændigheder er MySQL et af de mest populære databasestyringssystemer. Flere virksomheder skifter til MySQL (og andre open source-databaser) som en databaseløsning i deres produktionsmiljøer.

Der er mange metoder til konfiguration af MySQL, der kan hjælpe med at sikre, at din database reagerer hurtigt på forespørgsler og med et minimum af forringelse af applikationsydelse.

Følgende er nogle vigtige tip, der hjælper dig med at optimere ydeevnen i din MySQL-database.

MySQL-optimeringsnøgle nr. 1: Lær, hvordan du bruger FORKLARE

De to vigtigste beslutninger, du tager med en hvilken som helst database, er at designe, hvordan relationer mellem applikationsenheder kortlægges til tabeller (databaseskemaet) og designe, hvordan applikationer får de data, de har brug for, i det format, de har brug for det (forespørgsler).

Komplicerede applikationer kan have komplicerede skemaer og forespørgsler. Hvis du får den ydeevne og skalering, som dine applikationer kræver, kan du ikke bare stole på intuition for at forstå, hvordan forespørgsler udføres.

I stedet for at gætte og håbe, bør du lære at bruge FORKLARE kommando. Denne kommando viser dig, hvordan en forespørgsel udføres, og giver dig indsigt i både, hvilken præstation du kan forvente, og hvordan forespørgslen skaleres med ændret datastørrelse.

Der er en række værktøjer - såsom MySQL Workbench - der kan visualisere FORKLARE output til dig, men du skal stadig forstå det grundlæggende for at få mening ud af det.

Der er to forskellige formater, hvor FORKLARE kommando giver output: det gammeldags tabelformat og et mere moderne, struktureret JSON-dokument, der giver betydeligt flere detaljer (vist nedenfor):

mysql> forklar format = json vælg gennemsnit (k) fra sbtest1 hvor id mellem 1000 og 2000 \ G

*************************** 1. række ******************* *******

FORKLARE: {

“Query_block”: {

“Select_id”: 1,

“Cost_info”: {

   “Query_cost”: “762.40”

"bord": {

“Tabelnavn”: “sbtest1”,

“Access_type”: “range”,

“Mulige_taster”: [

"PRIMÆR"

      ],

“Nøgle”: “PRIMÆR”,

“Brugt_nøgle-dele”: [

“Id”

      ],

“Nøglelængde”: “4”,

“Rows_examined_per_scan”: 1874,

“Rows_produced_per_join”: 1874,

“Filtreret”: “100,00”,

“Cost_info”: {

“Read_cost”: “387.60”,

“Eval_cost”: “374.80”,

“Prefix_cost”: “762.40”,

“Data_read_per_join”: “351K”

      },

“Brugt_kolonner”: [

“Id”,

“K”

      ],

“Attached_condition”: “(` sbtest`.`btest1`. `Id` mellem 1000 og 2000)"

    }

  }

}

En komponent, du skal se på, er "forespørgselsomkostninger." Forespørgselsomkostninger henviser til, hvor dyrt MySQL betragter denne særlige forespørgsel med hensyn til de samlede omkostninger ved udførelse af forespørgsler og er baseret på mange forskellige faktorer.

Enkle forespørgsler har generelt forespørgselsomkostninger på mindre end 1.000. Forespørgsler med en pris mellem 1.000 og 100.000 betragtes som mellemstore forespørgsler og er generelt hurtige, hvis du kun kører hundreder af sådanne forespørgsler pr. Sekund (ikke titusinder).

Forespørgsler med en pris på mere end 100.000 er dyre forespørgsler. Ofte kører disse forespørgsler stadig hurtigt, når du er en enkelt bruger på systemet, men du bør overveje nøje, hvor ofte du bruger sådanne forespørgsler i dine interaktive applikationer (især når antallet af brugere vokser).

Naturligvis er dette ballpark-præstationsnumre, men de demonstrerer det generelle princip. Dit system håndterer muligvis forespørgselsbelastninger bedre eller dårligere afhængigt af dets arkitektur og konfiguration.

Blandt de faktorer, der bestemmer forespørgselsomkostningerne, er, om forespørgslen bruger indekser korrekt. Det FORKLARE kommandoen kan fortælle dig, om en forespørgsel ikke bruger indekser (normalt på grund af hvordan indekserne oprettes i databasen, eller hvordan selve forespørgslen er konstrueret). Derfor er det så vigtigt at lære at bruge FORKLARE.

MySQL-optimeringsnøgle # 2: Opret de rigtige indekser

Et indeks forbedrer forespørgselsydelsen ved at reducere mængden af ​​data i databasen, som forespørgsler skal scanne. Indekser i MySQL bruges til at fremskynde adgangen til databasen og hjælpe med at håndhæve databasebegrænsninger (f.eks ENESTÅENDE og FREMMED NØGLE).

Databaseindekser ligner meget bogindekser. De opbevares på deres egen placering, og de indeholder allerede oplysninger i hoveddatabasen. De er en referencemetode eller et kort, hvor data findes. Indekser ændrer ikke nogen af ​​dataene i en database. De peger simpelthen på placeringen af ​​dataene.

Der er ingen indekser, der altid passer til enhver arbejdsbyrde. Du bør altid se på indekser i sammenhæng med de forespørgsler, som systemet kører.

Velindekserede databaser kører ikke kun hurtigere, men selv et enkelt manglende indeks kan bremse en database til en gennemgang. Brug FORKLARE (som tidligere anbefalet) for at finde manglende indekser og tilføje dem. Men pas på: Tilføj ikke indekser, du ikke har brug for! Unødvendige indekser sænker databaser ned (tjek min præsentation om MySQL-indeksering af bedste praksis).

MySQL-optimeringsnøgle # 3: Ingen standardindstillinger!

Som enhver software har MySQL mange konfigurerbare indstillinger, der kan bruges til at ændre adfærd (og i sidste ende ydeevne). Og som enhver software ignoreres mange af disse konfigurerbare indstillinger af administratorer og ender med at blive brugt i deres standardtilstand.

For at få den bedste ydelse fra MySQL er det vigtigt at forstå de konfigurerbare MySQL-indstillinger og - vigtigere - indstille dem til at fungere bedst for dit databasemiljø.

Som standard er MySQL tunet til en mindre udviklingsinstallation, ikke til produktionsskala. Du vil typisk konfigurere MySQL til at bruge alle tilgængelige hukommelsesressourcer samt tillade det antal forbindelser, som din applikation kræver.

Her er tre MySQL-indstillinger for præstationsindstilling, som du altid skal undersøge nøje:

innodb_buffer_pool_size: Bufferpuljen er, hvor data og indekser cachelagres. Dette er hovedårsagen til at bruge et system med en stor mængde RAM som din databaseserver. Hvis du kun kører InnoDB-lagringsmotoren, tildeler du typisk omkring 80 procent af din hukommelse til bufferpuljen. Hvis du kører meget komplicerede forespørgsler, eller hvis du har et meget stort antal samtidige databaseforbindelser, eller hvis du har et meget stort antal tabeller, skal du muligvis nedtage denne værdi for at allokere mere hukommelse til andre formål.

Når du indstiller InnoDB-bufferstørrelsen, skal du sørge for ikke at indstille den for stor, ellers vil det medføre bytte. Dette dræber absolut din databaseydelse. En nem måde at kontrollere er at se på Bytte aktivitet i systemoversigtsgrafen i Percona Monitoring and Management:

Percona

Som denne graf viser, er det let at bytte noget så ofte. Hvis du dog ser vedvarende swapping-aktivitet på 1 MB pr. Sekund eller mere, skal du reducere din bufferpuljestørrelse (eller anden hukommelsesanvendelse).

Hvis du ikke får værdien for innodb_buffer_pool_size korrekt i første omgang, skal du ikke bekymre dig. Fra og med MySQL 5.7 kan du ændre størrelsen på InnoDB-bufferpuljen dynamisk uden at genstarte databaseserveren.

innodb_log_file_size: Dette er størrelsen på en enkelt InnoDB-logfil. Som standard bruger InnoDB to værdier, så du kan fordoble dette tal for at få størrelsen på det cirkulære omfordelingslogrum, som InnoDB bruger for at sikre, at dine transaktioner er holdbare. Dette optimerer også anvendelse af ændringer i databasen. Indstilling innodb_log_file_size er et spørgsmål om kompromiser. Jo større det plads, du tildeler igen, desto bedre opnår du den ydeevne, du får for en skriveintensiv arbejdsbyrde, men jo længere tid er det for gendannelse af nedbrud, hvis dit system lider af strømtab eller andre problemer.

Hvordan ved du, om din MySQL-ydelse er begrænset af din nuværende InnoDB-logfilstørrelse? Du kan fortælle ved at se på, hvor meget af det anvendelige genopretningsrum, der faktisk bruges. Den nemmeste måde er at se på Percona Monitoring and Management InnoDB Metrics dashboard. I nedenstående graf er InnoDB-logfilstørrelsen ikke stor nok, da den anvendte plads skubber meget tæt på, hvor meget anvendelig, der er til rådighed igen, der er tilgængelig (angivet med den røde linje). Din logfilstørrelse skal være mindst 20 procent større end den mængde plads, der bruges til at holde dit system optimalt.

Percona

max_connections: Store applikationer kræver ofte meget mere end standardantal forbindelser. I modsætning til andre variabler, hvis du ikke indstiller dette korrekt, har du ikke præstationsproblemer (i sig selv). I stedet for, hvis antallet af forbindelser ikke er tilstrækkeligt til dine applikationsbehov, kan din applikation simpelthen ikke oprette forbindelse til databasen (som ligner nedetid for dine brugere). Det er vigtigt at få denne variabel ret.

Det kan være svært at vide, hvor mange forbindelser du har brug for til komplekse applikationer med mange komponenter, der kører på flere servere. Heldigvis gør MySQL det meget let at se, hvor mange forbindelser der bruges ved peak operation. Typisk vil du sikre, at der er mindst 30 procent afstand mellem det maksimale antal forbindelser, din applikation bruger, og det maksimale antal tilgængelige forbindelser. En nem måde at se disse numre på er at bruge MySQL-forbindelsesgrafen i MySQL Oversigt-dashboardet i Percona Monitoring and Management. Grafen nedenfor viser et sundt system, hvor der er et stort antal yderligere forbindelser til rådighed.

Percona

En ting at huske på er, at hvis din database kører langsomt, opretter applikationer ofte et for stort antal forbindelser. I sådanne tilfælde skal du arbejde på databasens ydeevneproblem i stedet for blot at tillade flere forbindelser. Flere forbindelser kan gøre det underliggende præstationsproblem værre.

(Bemærk: Når du indstiller max_connections variabel, der er væsentligt højere end standardværdien, skal du ofte overveje at øge andre parametre, f.eks. størrelsen på tabelcache og antallet af åbne filer, MySQL tillader. Dette går dog ud over omfanget af denne artikel.) 

MySQL-optimeringsnøgle # 4: Gem databasen i hukommelsen

Vi har set en overgang til solid state-drev (SSD'er) i de seneste år. Selvom SSD'er er meget hurtigere end at dreje harddiske, er de stadig ingen match for at have data tilgængelige i RAM. Denne forskel kommer ikke kun fra selve lagerets ydeevne, men også fra det ekstra arbejde, databasen skal udføre, når den henter data fra disk eller SSD-lagring.

Med de seneste hardwareforbedringer er det i stigende grad muligt at få din database i hukommelsen - uanset om du kører i skyen eller administrerer din egen hardware.

Den endnu bedre nyhed er, at du ikke behøver at tilpasse al din database i hukommelsen for at få et flertal af fordelene ved ydeevne i hukommelsen. Du skal bare tilpasse arbejdssættet med data i hukommelsen - de data, der ofte er adgang til.

Du har muligvis set nogle artikler, der indeholder nogle specifikke tal om, hvilken del af databasen du skal gemme i hukommelsen, der spænder fra 10 procent til 33 procent. Faktisk er der ikke noget ”one size fits all” nummer. Mængden af ​​data, der passer til hukommelsen for at opnå den bedste ydelsesfordel, er relateret til arbejdsbyrden. I stedet for at kigge efter et specifikt "magisk" nummer, skal du kontrollere, hvor meget I / O databasen kører i sin stabile tilstand (typisk et par timer efter start). Se på læser, fordi læsninger kan fjernes fuldstændigt, hvis din database er i hukommelsen. Skrivning skal altid ske, uanset hvor meget hukommelse du har til rådighed.

Nedenfor kan du se I / O sker i InnoDB I / O-grafen i InnoDB Metrics dashboard i Percona Monitoring and Management.

Percona

I grafen ovenfor ser du spikes så højt som 2.000 I / O-operationer pr. Sekund, hvilket viser, at databasens arbejdssæt (i det mindste for nogle dele af arbejdsbelastningen) ikke passer godt ind i hukommelsen.

MySQL-optimeringsnøgle nr. 5: Brug SSD-lagring

Hvis din database ikke passer i hukommelsen (og selvom den gør det), har du stadig brug for hurtig opbevaring for at håndtere skrivning og for at undgå ydelsesproblemer, når databasen varmes op (lige efter genstart). I disse dage betyder hurtig opbevaring SSD'er.