Som med alle relationelle databaser kan MySQL vise sig at være et kompliceret udyr, et, der med et øjebliks varsel kan kravle stille og lade dine applikationer stå i stå og din forretning på spil.
Sandheden er, at almindelige fejl ligger til grund for de fleste MySQL-ydelsesproblemer. For at sikre, at din MySQL-server brummer sammen i topfart og giver stabil og konsistent ydeevne, er det vigtigt at fjerne disse fejl, som ofte skjules af en eller anden subtilitet i din arbejdsbyrde eller en konfigurationsfælde.
Heldigvis viser mange MySQL-ydelsesproblemer sig at have lignende løsninger, hvilket gør fejlfinding og tuning af MySQL til en håndterbar opgave.
Her er 10 tip til at få god ydeevne ud af MySQL.
MySQL-ydelsestip nr. 1: Profil din arbejdsbyrde
Den bedste måde at forstå, hvordan din server bruger sin tid, er at profilere serverens arbejdsbyrde. Ved at profilere din arbejdsbyrde kan du udsætte de dyreste forespørgsler til yderligere indstilling. Her er tid den vigtigste metric, for når du udsteder en forespørgsel mod serveren, er du meget ligeglad med andet end hvor hurtigt den er færdig.
Den bedste måde at profilere din arbejdsbyrde på er med et værktøj som MySQL Enterprise Monitors forespørgselsanalysator eller pt-query-digest fra Percona Toolkit. Disse værktøjer registrerer forespørgsler, som serveren udfører, og returnerer en tabel med opgaver sorteret efter faldende rækkefølge for responstid og øjeblikkeligt bobler de dyreste og tidskrævende opgaver op til toppen, så du kan se, hvor du skal fokusere din indsats.
Workload-profileringsværktøjer grupperer lignende forespørgsler sammen, så du kan se de forespørgsler, der er langsomme, såvel som de forespørgsler, der er hurtige, men udføres mange gange.
MySQL-præstationstip nr. 2: Forstå de fire grundlæggende ressourcer
For at fungere har en databaseserver brug for fire grundlæggende ressourcer: CPU, hukommelse, disk og netværk. Hvis nogen af disse er svage, uregelmæssige eller overbelastede, er det meget sandsynligt, at databaseserveren klarer sig dårligt.
At forstå de grundlæggende ressourcer er vigtig inden for to bestemte områder: valg af hardware og fejlfindingsproblemer.
Når du vælger hardware til MySQL, skal du sikre komponenter med gode resultater overalt. Lige så vigtigt skal du afbalancere dem rimeligt godt mod hinanden. Ofte vælger organisationer servere med hurtige CPU'er og diske, men som sulter efter hukommelse. I nogle tilfælde er tilføjelse af hukommelse en billig måde at øge ydeevnen på med størrelsesordener, især på arbejdsbelastninger, der er diskbundet. Dette kan virke kontraintuitivt, men i mange tilfælde er diske overudnyttet, fordi der ikke er nok hukommelse til at holde serverens arbejdssæt af data.
Et andet godt eksempel på denne balance vedrører CPU'er. I de fleste tilfælde fungerer MySQL godt med hurtige CPU'er, fordi hver forespørgsel kører i en enkelt tråd og ikke kan paralleliseres på tværs af CPU'er.
Når det kommer til fejlfinding, skal du kontrollere ydeevnen og udnyttelsen af alle fire ressourcer med et nøje øje med at afgøre, om de klarer sig dårligt eller simpelthen bliver bedt om at gøre for meget arbejde. Denne viden kan hjælpe med at løse problemer hurtigt.
MySQL-ydelsestip nr. 3: Brug ikke MySQL som en kø
Køer og kølignende adgangsmønstre kan snige sig ind i din applikation uden at du ved det. For eksempel, hvis du indstiller status for et element, så en bestemt arbejdsproces kan kræve det, før du handler på det, opretter du uforvarende en kø. Det er et almindeligt eksempel at markere e-mails som ikke sendt, sende dem og derefter markere dem som sendt.
Køer forårsager problemer af to hovedårsager: De serierer din arbejdsbyrde, hvilket forhindrer, at opgaver udføres parallelt, og de resulterer ofte i en tabel, der indeholder arbejde i processen samt historiske data fra job, der blev behandlet for længe siden. Begge tilføjer latens til applikationen og indlæses til MySQL.
MySQL-tip nr. 4: Filtrer resultater efter billigste først
En god måde at optimere MySQL på er at udføre billigt, upræcist arbejde først, derefter det hårde, præcise arbejde på det mindre, resulterende datasæt.
Antag for eksempel, at du leder efter noget inden for en given radius af et geografisk punkt. Det første værktøj i mange programmørers værktøjskasse er formlen til storcirkel (Haversine) til beregning af afstand langs en kugles overflade. Problemet med denne teknik er, at formlen kræver mange trigonometriske operationer, som er meget CPU-intensive. Storcirkelberegninger har tendens til at køre langsomt og gøre maskinens CPU-udnyttelse skyrocket.
Inden du anvender storcirkelformlen, skal du oprette dine poster til en lille delmængde af summen og trimme det resulterende sæt til en præcis cirkel. En firkant, der indeholder cirklen (præcist eller upræcist) er en nem måde at gøre dette på. På den måde bliver verden uden for pladsen aldrig ramt af alle de dyre trig-funktioner.
MySQL-ydelsestip nr. 5: Kend de to skalerbarhedsdødsfælder
Skalerbarhed er ikke så vag som du måske tror. Faktisk er der præcise matematiske definitioner af skalerbarhed, der udtrykkes som ligninger. Disse ligninger fremhæver, hvorfor systemer ikke skaleres så godt, som de burde.
Tag Universal Scalability Law, en definition, der er praktisk til at udtrykke og kvantificere et systems skalerbarhedsegenskaber. Det forklarer skaleringsproblemer med to grundlæggende omkostninger: serialisering og krydstale.
Parallelle processer, der skal standse for, at noget serielt kan finde sted, er i sagens natur begrænset i deres skalerbarhed. Ligeledes, hvis de parallelle processer har brug for at chatte med hinanden hele tiden for at koordinere deres arbejde, begrænser de hinanden.
Undgå serialisering og krydstale, og din applikation skaleres meget bedre. Hvad oversætter dette til inde i MySQL? Det varierer, men nogle eksempler er at undgå eksklusive låse på rækker. Køer, punkt nr. 3 ovenfor, har tendens til at skaleres dårligt af denne grund.
MySQL-ydelsestip nr. 6: Fokuser ikke for meget på konfiguration
DBA'er har en tendens til at bruge en enorm mængde tid på at finjustere konfigurationer. Resultatet er normalt ikke en stor forbedring og kan undertiden endda være meget skadeligt. Jeg har set mange "optimerede" servere, der styrtede konstant, løb tør for hukommelse og fungerede dårligt, da arbejdsbelastningen blev lidt mere intens.
Standardindstillingerne, der leveres med MySQL, er one-size-fits-none og dårligt forældede, men du behøver ikke konfigurere alt. Det er bedre at få det grundlæggende rigtigt og kun ændre andre indstillinger, hvis det er nødvendigt. I de fleste tilfælde kan du få 95 procent af serverens maksimale ydeevne ved at indstille ca. 10 indstillinger korrekt. De få situationer, hvor dette ikke gælder, vil være kanttilfælde, der er unikke for dine omstændigheder.
I de fleste tilfælde anbefales ikke "tuning" -værktøjer, fordi de har en tendens til at give retningslinjer, der ikke giver mening i specifikke sager. Nogle har endda farlige, unøjagtige råd kodet ind i dem - såsom cache-hitforhold og hukommelsesforbrugsformler. Disse var aldrig rigtige, og de er blevet endnu mindre korrekte efterhånden som tiden er gået.
MySQL-tip nr. 7: Pas på sideforespørgsler
Applikationer, der paginerer, har tendens til at bringe serveren på knæ. Ved at vise dig en side med resultater med et link til at gå til næste side grupperer og sorterer disse applikationer typisk på måder, der ikke kan bruge indekser, og de anvender en BEGRÆNSE og forskudt der får serveren til at arbejde meget med at generere og derefter kassere rækker.
Optimeringer kan ofte findes i selve brugergrænsefladen. I stedet for at vise det nøjagtige antal sider i resultaterne og links til hver side individuelt, kan du bare vise et link til den næste side. Du kan også forhindre folk i at gå til sider for langt fra den første side.
På forespørgselssiden i stedet for at bruge BEGRÆNSE med forskudt, kan du vælge en række mere, end du har brug for, og når brugeren klikker på linket "næste side", kan du angive den sidste række som startpunkt for det næste sæt resultater. For eksempel, hvis brugeren så en side med række 101 til 120, ville du også vælge række 121; for at gengive den næste side, skal du spørge serveren efter rækker større end eller lig med 121, grænse 21.
MySQL-præstationstip nr. 8: Gem statistik ivrigt, alarm modvilligt
Overvågning og alarmering er afgørende, men hvad sker der med det typiske overvågningssystem? Det begynder at sende falske positive, og systemadministratorer opretter regler for e-mail-filtrering for at stoppe støj. Snart er dit overvågningssystem helt ubrugeligt.
Jeg kan godt lide at tænke på overvågning på to måder: at registrere målinger og advare. Det er meget vigtigt at registrere og gemme alle de målinger, du muligvis kan, fordi du vil være glad for at have dem, når du prøver at finde ud af, hvad der har ændret sig i systemet. En dag opstår der et mærkeligt problem, og du vil elske evnen til at pege på en graf og vise en ændring i serverens arbejdsbyrde.
Derimod er der en tendens til at advare alt for meget. Folk advarer ofte om ting som buffer hit ratio eller antallet af midlertidige tabeller oprettet pr. Sekund. Problemet er, at der ikke er nogen god tærskel for et sådant forhold. Den rigtige tærskel er ikke kun forskellig fra server til server, men fra time til time, når din arbejdsbyrde ændres.
Som et resultat skal du advare sparsomt og kun om forhold, der indikerer et bestemt, handlingsmæssigt problem. Et lavt buffer hit-forhold kan ikke handles, og det indikerer heller ikke et reelt problem, men en server, der ikke reagerer på et forbindelsesforsøg, er et reelt problem, der skal løses.
MySQL-ydelsestip nr. 9: Lær de tre regler for indeksering
Indeksering er sandsynligvis det mest misforståede emne i databaser, fordi der er så mange måder at blive forvirret over, hvordan indekser fungerer, og hvordan serveren bruger dem. Det kræver en stor indsats for virkelig at forstå, hvad der foregår.
Når indekser er korrekt designet, tjener de tre vigtige formål i en databaseserver:
- Indekser lader serveren finde grupper af tilstødende rækker i stedet for enkeltrækker. Mange mennesker tror, at formålet med et indeks er at finde individuelle rækker, men at finde enkelte rækker fører til tilfældige diskoperationer, hvilket er langsomt. Det er meget bedre at finde grupper af rækker, hvoraf alle eller de fleste er interessante, end at finde rækker én ad gangen.
- Indekser tillader serveren at sortere ved at læse rækkerne i den ønskede rækkefølge. Sortering er dyrt. Det er meget hurtigere at læse rækker i den ønskede rækkefølge.
- Indekser lader serveren tilfredsstille hele forespørgsler fra indekset alene og undgår overhovedet behovet for at få adgang til tabellen. Dette er forskelligt kendt som et dækningsindeks eller en indeksforespørgsel.
Hvis du kan designe dine indekser og forespørgsler til at udnytte disse tre muligheder, kan du gøre dine forespørgsler flere størrelsesordener hurtigere.
MySQL-præstationstip nr. 10: Udnyt ekspertisen fra dine jævnaldrende
Forsøg ikke at gå alene. Hvis du forvirrer dig over et problem og gør det, der synes logisk og fornuftigt for dig, er det godt. Dette fungerer omkring 19 gange ud af 20. Den anden gang går du ned i et kaninhul, der vil være meget dyrt og tidskrævende, netop fordi den løsning, du prøver, synes at give meget mening.
Byg et netværk af MySQL-relaterede ressourcer - og dette går ud over værktøjssæt og fejlfindingsvejledninger. Der er nogle yderst kyndige mennesker, der lurer på mailinglister, fora, Q&A websteder og så videre. Konferencer, messer og lokale brugergruppebegivenheder giver værdifulde muligheder for at få indsigt og opbygge relationer med jævnaldrende, der kan hjælpe dig i en knivspids.
For dem, der leder efter værktøjer til at supplere disse tip, kan du tjekke Percona Configuration Wizard for MySQL, Percona Query Advisor for MySQL og Percona Monitoring Plugins. (Bemærk: Du skal oprette en Percona-konto for at få adgang til de første to links. Det er gratis.) Konfigurationsguiden kan hjælpe dig med at generere en baseline my.cnf-fil til en ny server, der er bedre end de eksempelfiler, der leveres med server. Forespørgselsrådgiveren analyserer din SQL for at hjælpe med at opdage potentielt dårlige mønstre såsom pagineringsforespørgsler (nr. 7). Percona Monitoring Plugins er et sæt overvågnings- og grafiske plugins, der hjælper dig med at gemme statistik ivrigt og advare modvilligt (nr. 8). Alle disse værktøjer er frit tilgængelige.
