10 viktiga prestandatips för MySQL

Som med alla relationsdatabaser kan MySQL visa sig vara ett komplicerat odjur, en som kan krypa till ett stopp med ett ögonblicks varsel och lämna dina applikationer i sväng och ditt företag på spel.

Sanningen är att vanliga misstag ligger till grund för de flesta MySQL-prestandaproblem. För att säkerställa att din MySQL-server surrar med högsta hastighet, vilket ger stabil och konsekvent prestanda, är det viktigt att eliminera dessa misstag, som ofta döljs av en del subtilitet i din arbetsbelastning eller en konfigurationsfälla.

Lyckligtvis visar sig många MySQL-prestandaproblem ha liknande lösningar, vilket gör felsökning och inställning av MySQL till en hanterbar uppgift.

Här är 10 tips för att få bra prestanda ur MySQL.

MySQL-prestanda tips nr 1: Profilera din arbetsbelastning

Det bästa sättet att förstå hur din server tillbringar sin tid är att profilera serverns arbetsbelastning. Genom att profilera din arbetsbelastning kan du exponera de dyraste frågorna för ytterligare inställning. Här är tiden det viktigaste måttet, för när du skickar en fråga mot servern bryr du dig väldigt lite om någonting förutom hur snabbt den slutförs.

Det bästa sättet att profilera din arbetsbelastning är med ett verktyg som MySQL Enterprise Monitors frågeanalysator eller pt-query-digest från Percona Toolkit. Dessa verktyg fångar frågor som servern kör och returnerar en tabell över uppgifter sorterade efter minskad ordning på svarstid och bubblar direkt upp de dyraste och tidskrävande uppgifterna till toppen så att du kan se var du ska fokusera dina ansträngningar.

Arbetsbelastningsprofileringsverktyg grupperar liknande frågor, så att du kan se frågorna som är långsamma, liksom frågorna som är snabba men körs många gånger.

MySQL-prestandatips nr 2: Förstå de fyra grundläggande resurserna

För att fungera behöver en databasserver fyra grundläggande resurser: CPU, minne, disk och nätverk. Om någon av dessa är svag, oregelbunden eller överbelastad kommer databaservern mycket troligt att prestera dåligt.

Att förstå de grundläggande resurserna är viktigt inom två specifika områden: att välja hårdvara och felsökningsproblem.

När du väljer hårdvara för MySQL, se till att komponenter som fungerar bra runt. Lika viktigt, balansera dem ganska bra mot varandra. Ofta väljer organisationer servrar med snabba processorer och skivor men som svälter för minne. I vissa fall är att lägga till minne ett billigt sätt att öka prestandan med storleksordningar, särskilt på arbetsbelastningar som är diskbundna. Detta kan verka kontraintuitivt, men i många fall är diskarna överutnyttjade eftersom det inte finns tillräckligt med minne för att hålla serverns arbetsuppsättning.

Ett annat bra exempel på denna balans gäller CPU: er. I de flesta fall kommer MySQL att fungera bra med snabba processorer eftersom varje fråga körs i en enda tråd och inte kan parallelliseras över processorer.

När det gäller felsökning, kontrollera prestanda och användning av alla fyra resurserna, med ett noggrant öga för att avgöra om de presterar dåligt eller helt enkelt blir ombedd att göra för mycket arbete. Denna kunskap kan hjälpa till att lösa problem snabbt.

MySQL-prestandatips nr 3: Använd inte MySQL som en kö

Köer och köliknande åtkomstmönster kan smyga in i din applikation utan att du vet det. Till exempel, om du ställer in statusen för ett objekt så att en viss arbetsprocess kan göra anspråk på det innan du agerar på det, skapar du oavsiktligt en kö. Att markera e-postmeddelanden som osända, skicka dem och sedan markera dem som skickade är ett vanligt exempel.

Köer orsakar problem av två huvudskäl: De serierar din arbetsbelastning, vilket förhindrar att uppgifter utförs parallellt, och de resulterar ofta i en tabell som innehåller arbete i processen samt historisk data från jobb som bearbetades för länge sedan. Båda lägger till latens i applikationen och laddas till MySQL.

MySQL-prestandatips nr 4: Filtrera resultat efter billigaste först

Ett bra sätt att optimera MySQL är att göra billigt, exakt arbete först, sedan det hårda, exakta arbetet med den mindre, resulterande uppsättningen data.

Antag till exempel att du letar efter något inom en given radie av en geografisk punkt. Det första verktyget i många programmerares verktygslåda är storcirkelformeln (Haversine) för beräkning av avstånd längs ytan på en sfär. Problemet med denna teknik är att formeln kräver mycket trigonometriska operationer, som är mycket CPU-intensiva. Storcirkelberäkningar tenderar att gå långsamt och få maskinens CPU-användning att skjuta i höjden.

Innan du använder storcirkelformeln, para ner dina poster till en liten delmängd av summan och trimma den resulterande uppsättningen till en exakt cirkel. En fyrkant som innehåller cirkeln (exakt eller exakt) är ett enkelt sätt att göra detta. På det sättet blir världen utanför torget aldrig träffad med alla de dyra trig-funktionerna.

MySQL-prestandatips nr 5: Känn de två skalbarhetsdödsfällorna

Skalbarhet är inte så vag som du kanske tror. Faktum är att det finns exakta matematiska definitioner av skalbarhet som uttrycks som ekvationer. Dessa ekvationer belyser varför systemen inte skalas så bra som de borde.

Ta Universal Scalability Law, en definition som är användbar för att uttrycka och kvantifiera systemets skalbarhetsegenskaper. Det förklarar skalningsproblem i termer av två grundläggande kostnader: serialisering och överhörning.

Parallella processer som måste stoppas för att något seriell ska kunna äga rum är i sig begränsade i skalbarheten. På samma sätt, om de parallella processerna behöver chatta med varandra hela tiden för att samordna sitt arbete, begränsar de varandra.

Undvik serialisering och överhörning, och din applikation kommer att skala mycket bättre. Vad översätter detta till insidan av MySQL? Det varierar, men några exempel skulle vara att undvika exklusiva lås på rader. Köer, punkt 3 ovan, tenderar att skala dåligt av denna anledning.

MySQL-prestandatips nr 6: Fokusera inte för mycket på konfiguration

DBA: er tenderar att spendera mycket tid på att justera konfigurationer. Resultatet är vanligtvis inte en stor förbättring och kan ibland till och med vara mycket skadligt. Jag har sett många ”optimerade” servrar som kraschade ständigt, fick slut på minne och presterade dåligt när arbetsbelastningen blev lite mer intensiv.

Standardinställningarna som levereras med MySQL är one-size-fits-none och dåligt föråldrade, men du behöver inte konfigurera allt. Det är bättre att få grunden rätt och ändra andra inställningar endast om det behövs. I de flesta fall kan du få 95 procent av serverns högsta prestanda genom att ställa in cirka 10 alternativ korrekt. De få situationer där detta inte gäller kommer att vara kantfall som är unika för dina omständigheter.

I de flesta fall rekommenderas inte serverinställningsverktyg eftersom de brukar ge riktlinjer som inte är meningsfulla för specifika fall. Vissa har till och med farliga, felaktiga råd som är kodade i dem - till exempel cache-träffförhållanden och minnesförbrukningsformler. Dessa var aldrig rätta, och de har blivit ännu mindre korrekta med tiden.

MySQL-prestandatips nr 7: Se upp för pagineringsfrågor

Applikationer som paginerar tenderar att sätta servern på knä. När de visar en sida med resultat, med en länk för att gå till nästa sida, grupperar och sorterar dessa applikationer vanligtvis på sätt som inte kan använda index, och de använder en LIMIToch offsetsom får servern att göra mycket arbete att generera, kasserar sedan rader.

Optimeringar finns ofta i själva användargränssnittet. Istället för att visa det exakta antalet sidor i resultaten och länkar till varje sida individuellt kan du bara visa en länk till nästa sida. Du kan också förhindra att människor går till sidor för långt från första sidan.

På frågesidan, istället för att använda LIMITmed offset, kan du välja en rad till mer än du behöver, och när användaren klickar på länken "nästa sida" kan du ange den sista raden som startpunkt för nästa uppsättning resultat. Om användaren till exempel tittade på en sida med raderna 101 till 120 skulle du också välja rad 121; för att göra nästa sida skulle du fråga servern för rader som är större än eller lika med 121, gräns 21.

MySQL-prestandatips nr 8: Spara statistik ivrigt, alert motvilligt

Övervakning och varning är viktigt, men vad händer med det typiska övervakningssystemet? Det börjar skicka falska positiva och systemadministratörer ställer in e-postfiltreringsregler för att stoppa bullret. Snart är ditt övervakningssystem helt värdelöst.

Jag gillar att tänka på övervakning på två sätt: fånga mätvärden och varna. Det är mycket viktigt att fånga och spara alla mätvärden du kan, eftersom du kommer att vara glad att ha dem när du försöker lista ut vad som har förändrats i systemet. En dag kommer ett konstigt problem att dyka upp och du kommer att älska förmågan att peka på en graf och visa en förändring i serverns arbetsbelastning.

Däremot finns det en tendens att varna alldeles för mycket. Människor varnar ofta om saker som buffertens träffförhållande eller antalet tillfälliga tabeller som skapats per sekund. Problemet är att det inte finns någon bra tröskel för ett sådant förhållande. Rätt tröskel skiljer sig inte bara från server till server utan från timme till timme när din arbetsbelastning ändras.

Som ett resultat, varna sparsamt och endast om förhållanden som indikerar ett definitivt, åtgärdbart problem. Ett lågt bufferträffningsförhållande kan inte användas, och det indikerar inte heller ett verkligt problem, men en server som inte svarar på ett anslutningsförsök är ett faktiskt problem som måste lösas.

MySQL-prestandatips nr 9: Lär dig de tre reglerna för indexering

Indexering är förmodligen det mest missförstådda ämnet i databaser eftersom det finns så många sätt att bli förvirrad om hur index fungerar och hur servern använder dem. Det tar mycket ansträngningar för att verkligen förstå vad som händer.

När index är korrekt utformade tjänar de tre viktiga syften på en databasserver:

  1. Index låter servern hitta grupper av intilliggande rader istället för enstaka rader. Många tror att syftet med ett index är att hitta enskilda rader, men att hitta enstaka rader leder till slumpmässiga diskoperationer, vilket är långsamt. Det är mycket bättre att hitta grupper av rader, som alla eller de flesta är intressanta, än att hitta rader en i taget.
  2. Index låter servern undvika sortering genom att läsa raderna i önskad ordning. Sortering är kostsamt. Att läsa rader i önskad ordning är mycket snabbare.
  3. Index låter servern tillgodose hela frågor från indexet enbart, vilket undviker behovet av att komma åt tabellen alls. Detta är olika känt som ett täckningsindex eller en endast indexfråga.

Om du kan utforma dina index och frågor för att utnyttja dessa tre möjligheter kan du göra dina frågor flera storleksordningar snabbare.

MySQL-prestandatips nr 10: Utnyttja expertisen hos dina kamrater

Försök inte gå ensam. Om du undrar över ett problem och gör det som verkar logiskt och förnuftigt för dig är det bra. Detta kommer att fungera ungefär 19 gånger av 20. Den andra gången kommer du att gå ner i ett kaninhål som kommer att bli mycket kostsamt och tidskrävande, just för att lösningen du försöker verkar vara mycket vettig.

Bygg ett nätverk av MySQL-relaterade resurser - och detta går utöver verktygssatser och felsökningsguider. Det finns några extremt kunniga människor som lurar på e-postlistor, forum, Q & A-webbplatser och så vidare. Konferenser, mässor och lokala användargruppshändelser ger värdefulla möjligheter att få insikter och bygga relationer med kamrater som kan hjälpa dig i en nypa.

För dem som letar efter verktyg som kompletterar dessa tips kan du kolla in Percona Configuration Wizard för MySQL, Percona Query Advisor for MySQL och Percona Monitoring Plugins. (Obs! Du måste skapa ett Percona-konto för att få åtkomst till de två första länkarna. Det är gratis.) Konfigurationsguiden kan hjälpa dig att skapa en baslinje my.cnf-fil för en ny server som är överlägsen de exempelfiler som levereras med server. Frågerådgivaren kommer att analysera din SQL för att upptäcka potentiellt dåliga mönster som sidfrågor (nr 7). Percona Monitoring Plugins är en uppsättning övervaknings- och diagramplugins som hjälper dig att spara statistik ivrigt och varna motvilligt (nr 8). Alla dessa verktyg är fritt tillgängliga.