10 bitnih savjeta za izvedbu MySQL-a

Kao i kod svih relacijskih baza podataka, MySQL se može pokazati kompliciranom zvijeri koja se može zaustaviti u trenu, ostavljajući vaše aplikacije na cjedilu, a vaše poslovanje na vezi.

Istina je da su uobičajene pogreške u osnovi većine problema s izvedbom MySQL-a. Da biste osigurali da vaš MySQL poslužitelj bruji najvećom brzinom, pružajući stabilne i dosljedne performanse, važno je ukloniti ove pogreške, koje su često prikrivene nekom suptilnošću u vašem radnom opterećenju ili konfiguracijskom zamkom.

Srećom, ispostavilo se da mnogi problemi s izvedbom MySQL-a imaju slična rješenja, što rješavanje problema i podešavanje MySQL-a čini upravljivim zadatkom.

Evo 10 savjeta za postizanje izvrsnih performansi MySQL-a.

MySQL savjet za izvedbu br. 1: Profilirajte svoje radno opterećenje

Najbolji način da shvatite kako vaš poslužitelj provodi vrijeme je profiliranje radnog opterećenja poslužitelja. Profiliranjem radnog opterećenja možete izložiti najskuplje upite za daljnje podešavanje. Ovdje je vrijeme najvažnija mjerna vrijednost jer kada izdate upit prema poslužitelju, vrlo vam je stalo do bilo čega, osim do toga kako brzo završava.

Najbolji način za profiliranje vašeg radnog opterećenja je alat kao što je analizator upita MySQL Enterprise Monitor ili pt-query-digest iz Percona Toolkit-a. Ovi alati hvataju upite koje poslužitelj izvršava i vraćaju tablicu zadataka poredanih prema smanjivanju redoslijeda vremena odziva, trenutno na vrh skupljajući najskuplje i najduže zadatke tako da možete vidjeti gdje usmjeriti svoje napore.

Alati za profiliranje radnog opterećenja grupiraju slične upite, omogućujući vam da vidite spore upite, kao i brze, ali izvršene mnogo puta.

Savjet za izvedbu MySQL-a br. 2: Razumijevanje četiri temeljna resursa

Da bi poslužio, poslužitelj baze podataka treba četiri temeljna resursa: CPU, memorija, disk i mreža. Ako je bilo što od toga slabo, nestalno ili preopterećeno, tada će poslužitelj baze podataka vrlo vjerojatno raditi loše.

Razumijevanje temeljnih resursa važno je u dva posebna područja: odabiru hardvera i rješavanju problema.

Kada odabirete hardver za MySQL, pobrinite se za komponente s dobrom izvedbom. Jednako je važno, razumno ih uravnotežite. Organizacije će često odabrati poslužitelje s brzim procesorima i diskovima, ali koji su izgladnjeli zbog memorije. U nekim je slučajevima dodavanje memorije jeftin način povećanja performansi po redoslijedima, posebno na radnim opterećenjima koja su vezana uz disk. To se može činiti protuintuitivnim, ali u mnogim se slučajevima diskovi pretjerano koriste jer nema dovoljno memorije za radni set podataka poslužitelja.

Još jedan dobar primjer ove ravnoteže odnosi se na procesore. U većini slučajeva MySQL će se dobro izvoditi s brzim procesorima, jer se svaki upit izvodi u jednoj niti i ne može se paralelizirati između procesora.

Kada je riječ o rješavanju problema, provjerite izvedbu i iskorištenost sva četiri resursa, pažljivo pazeći na utvrđivanje rade li loše ili se jednostavno od njih traži previše posla. Ovo znanje može pomoći u brzom rješavanju problema.

Savjet za izvedbu MySQL-a br. 3: Ne koristite MySQL kao red

Redovi i obrasci pristupa slični redovima mogu se ušuljati u vašu aplikaciju, a da vi to ne znate. Na primjer, ako postavite status stavke tako da određeni radnički proces može položiti pravo na nju prije nego što djeluje na nju, tada nesvjesno stvarate red. Čest je primjer označavanje e-poruka kao neposlanih, slanje, zatim označavanje kao poslano.

Redovi stvaraju probleme iz dva glavna razloga: oni seriraju vaše opterećenje, sprječavajući paralelno obavljanje zadataka, a često rezultiraju tablicom koja sadrži rad u tijeku, kao i povijesne podatke iz poslova koji su davno obrađeni. Oboje dodaju latenciju aplikaciji i učitavaju MySQL.

Savjet za izvedbu MySQL-a br. 4: Prvo filtrirajte rezultate prema najjeftinijim

Sjajan način za optimizaciju MySQL-a je prvo napraviti jeftin, neprecizan posao, a zatim naporan i precizan rad na manjem, rezultirajućem skupu podataka.

Na primjer, pretpostavimo da tražite nešto unutar određenog radijusa od geografske točke. Prvi alat u alatu mnogih programera je formula velikog kruga (Haversine) za izračunavanje udaljenosti duž površine kugle. Problem ove tehnike je taj što formula zahtijeva puno trigonometrijskih operacija koje su vrlo zahtjevne za procesor. Izračuni velikih krugova obično rade polako i čine porast CPU-a uređaja nebeskim.

Prije primjene formule velikog kruga, smanjite svoje zapise na mali podskup ukupnog iznosa i rezultirajte skup na precizan krug. Kvadrat koji sadrži krug (precizno ili neprecizno) jednostavan je način za to. Na taj način svijet izvan trga nikad ne pogađaju sve te skupe trig funkcije.

MySQL savjet za izvedbu br. 5: Upoznajte dvije zamke smrti koje se mogu prilagoditi

Skalabilnost nije toliko nejasna koliko možda vjerujete. Zapravo postoje precizne matematičke definicije skalabilnosti koje se izražavaju kao jednadžbe. Ove jednadžbe ističu zašto se sustavi ne skaliraju onako kako bi trebali.

Uzmimo Univerzalni zakon o skalabilnosti, definiciju koja je korisna u izražavanju i kvantificiranju karakteristika skalabilnosti sustava. Objašnjava probleme skaliranja u smislu dva temeljna troškova: serializacije i preslušavanja.

Paralelni procesi koji se moraju zaustaviti da bi se dogodilo nešto serializirano u osnovi su ograničeni u svojoj skalabilnosti. Isto tako, ako paralelni procesi trebaju stalno međusobno čavrljati kako bi koordinirali svoj posao, oni se međusobno ograničavaju.

Izbjegavajte serializaciju i preslušavanje, a vaša će se aplikacija puno bolje skalirati. Što se to prevodi u MySQL? Razlikuje se, ali neki bi primjeri bili izbjegavanje ekskluzivnih brava na redovima. Redovi, točka br. 3 gore, iz tog se razloga imaju tendenciju slabijeg mjerenja.

Savjet za izvedbu MySQL-a br. 6: Ne fokusirajte se previše na konfiguraciju

DBA obično troše ogromnu količinu vremena podešavajući konfiguracije. Rezultat obično nije veliko poboljšanje, a ponekad može biti i vrlo štetan. Vidio sam puno "optimiziranih" poslužitelja koji su se neprestano srušili, ostali bez memorije i loše su se izvodili kad je radno opterećenje postalo malo intenzivnije.

Zadane vrijednosti koje se isporučuju s MySQL-om su jednoznačne i loše su zastarjele, ali ne trebate sve konfigurirati. Bolje je ispraviti osnove i promijeniti ostale postavke samo ako je potrebno. U većini slučajeva možete dobiti 95 posto vrhunskih performansi poslužitelja ispravnim postavljanjem 10-ak opcija. Nekoliko situacija u kojima se to ne odnosi bit će rubni slučajevi jedinstveni za vaše okolnosti.

U većini slučajeva ne preporučuju se alati za "podešavanje" poslužitelja jer imaju tendenciju davati smjernice koje nemaju smisla za određene slučajeve. Neki čak imaju i kodirane opasne, netočne savjete - poput omjera pogođenih predmemorije i formula potrošnje memorije. To nikad nisu bile u pravu, a s vremenom su postale još manje ispravne.

Savjet za izvedbu MySQL-a br. 7: Pripazite na paginacijske upite

Aplikacije koje paginiraju imaju tendenciju da poslužitelj bace na koljena. Prikazujući vam stranicu rezultata, s vezom za prelazak na sljedeću stranicu, ove se aplikacije obično grupiraju i razvrstavaju na načine koji ne mogu koristiti indekse, a koriste LIMITi offsetzbog kojih poslužitelj obavlja puno posla generirajući, zatim odbacivanje redova.

Optimizacije se često mogu naći u samom korisničkom sučelju. Umjesto da prikazujete točan broj stranica u rezultatima i veze do svake stranice pojedinačno, možete samo prikazati vezu do sljedeće stranice. Također možete spriječiti ljude da odlaze na stranice predaleko od prve stranice.

Na strani upita, umjesto upotrebe LIMITsa offset, možete odabrati jedan redak više nego što vam treba, a kada korisnik klikne na vezu "sljedeća stranica", taj zadnji redak možete odrediti kao početnu točku za sljedeći niz rezultata. Na primjer, ako je korisnik pregledavao stranicu s redovima od 101 do 120, odabrali biste i redak 121; da biste prikazali sljedeću stranicu, pitali biste poslužitelja za retke veće ili jednake 121, ograničenje 21.

Savjet za izvedbu MySQL-a br. 8: Spremite statistike nestrpljivo, upozorite nevoljko

Nadzor i uzbunjivanje su neophodni, ali što se događa s tipičnim sustavom praćenja? Počinje slati lažne pozitivne rezultate, a administratori sustava postavljaju pravila filtriranja e-pošte kako bi zaustavili buku. Uskoro je vaš nadzorni sustav potpuno beskoristan.

O praćenju volim razmišljati na dva načina: hvatanje mjernih podataka i upozoravanje. Vrlo je važno uhvatiti i spremiti sve mjerne podatke koje eventualno možete jer će vam biti drago kad ih pokušate shvatiti što se promijenilo u sustavu. Jednog dana će se pojaviti neobičan problem i svidjet će vam se mogućnost pokazivanja na grafikon i prikazivanja promjene u radnom opterećenju poslužitelja.

Suprotno tome, postoji tendencija previše upozoravanja. Ljudi često upozoravaju na stvari poput omjera učitavanja međuspremnika ili broja privremenih tablica stvorenih u sekundi. Problem je što ne postoji dobar prag za takav omjer. Pravi prag razlikuje se ne samo od poslužitelja do poslužitelja, već i od sata do sata kako se vaše radno opterećenje mijenja.

Kao rezultat toga, upozoravajte rijetko i samo u uvjetima koji ukazuju na određeni problem koji se može riješiti. Nizak omjer učitavanja međuspremnika nije koristan niti ukazuje na stvarni problem, ali poslužitelj koji ne reagira na pokušaj povezivanja stvarni je problem koji treba riješiti.

MySQL savjet za izvedbu br. 9: Naučite tri pravila indeksiranja

Indeksiranje je vjerojatno najviše pogrešno shvaćena tema u bazama podataka jer postoji toliko mnogo načina da se zbunite oko toga kako indeksi rade i kako ih poslužitelj koristi. Potreban je velik napor da se stvarno shvati što se događa.

Indeksi, kada su pravilno dizajnirani, služe tri važne svrhe na poslužitelju baze podataka:

  1. Indeksi dopuštaju poslužitelju da pronađe grupe susjednih redaka umjesto pojedinačnih redaka. Mnogi ljudi misle da je svrha indeksa pronaći pojedinačne retke, ali pronalazak pojedinačnih redaka dovodi do slučajnih operacija diska, što je sporo. Puno je bolje pronaći grupe redaka, koje su sve ili većina zanimljive, nego pronaći redove jedan po jedan.
  2. Indeksi omogućuju poslužitelju da izbjegava sortiranje čitanjem redaka u željenom redoslijedu. Razvrstavanje je skupo. Čitanje redova željenim redoslijedom mnogo je brže.
  3. Indeksi omogućuju poslužitelju da zadovoljava čitave upite samo iz indeksa, izbjegavajući uopće potrebu za pristupom tablici. To je različito poznato kao indeks pokrića ili upit samo za indeks.

Ako možete dizajnirati svoje indekse i upite kako biste iskoristili ove tri mogućnosti, možete svoje upite učiniti bržim za nekoliko redova veličine.

MySQL savjet za izvedbu br. 10: Iskoristite stručnost svojih vršnjaka

Ne pokušavajte ići sami. Ako zbunjujete problem i radite ono što vam se čini logično i razumno, to je sjajno. Ovo će uspjeti oko 19 puta od 20. Drugi put ćete se spustiti niz zečju rupu koja će biti vrlo skupa i dugotrajna, upravo zato što rješenje koje pokušavate ima puno smisla.

Izgradite mrežu resursa povezanih s MySQL-om - i to nadilazi skupove alata i vodiče za rješavanje problema. Neki izuzetno upućeni ljudi vrebaju na mailing listama, forumima, web stranicama s pitanjima i slično. Konferencije, sajmovi i događaji lokalnih korisničkih grupa pružaju dragocjene mogućnosti za stjecanje uvida i izgradnju odnosa s vršnjacima koji vam mogu pomoći u kratkom roku.

Za one koji traže alate koji će nadopuniti ove savjete, možete provjeriti čarobnjaka za konfiguraciju Percona za MySQL, savjetnika za upit Percona za MySQL i dodatke za nadzor Percona. (Napomena: Za pristup tim prvim dvjema vezama morat ćete stvoriti račun Percona. Besplatno je.) Čarobnjak za konfiguraciju može vam pomoći generirati osnovnu datoteku my.cnf za novi poslužitelj koji je superiorniji od uzoraka datoteka koje se isporučuju s poslužitelju. Savjetnik za upite analizirat će vaš SQL kako bi pomogao u otkrivanju potencijalno loših obrazaca, poput upita za paginiranje (br. 7). Dodaci za nadzor Percona skup su dodataka za praćenje i grafički prikaz koji će vam pomoći da spremite statistike i spremno ih upozorite (br. 8). Svi ovi alati su slobodno dostupni.